create table AA
(
HJD VARCHAR2(10),
SJD VARCHAR2(10),
JCD NUMBER
);
insert into AA (HJD, SJD, JCD)
values ('A', 'D', 1);
insert into AA (HJD, SJD, JCD)
values ('B', 'D', 2);
insert into AA (HJD, SJD, JCD)
values ('C', 'D', 3);
insert into AA (HJD, SJD, JCD)
values ('A', 'E', 4);
insert into AA (HJD, SJD, JCD)
values ('B', 'E', 5.1);
insert into AA (HJD, SJD, JCD)
values ('C', 'E', 6.2);
insert into AA (HJD, SJD, JCD)
values ('A', 'F', .6);
insert into AA (HJD, SJD, JCD)
values ('B', 'F', .8);
insert into AA (HJD, SJD, JCD)
values ('C', 'F', .3);
commit;
创建存储过程返回动态组合的sql;
create or replace procedure P_ATEST(
as_sql out varchar2
)is
type v_cursor is ref cursor;
v_cur v_cursor;
v_hjd varchar2(100);
v_str varchar2(300);
v_spa varchar2(10);
v_sgn varchar2(10);
v_sql varchar2(40);
v_tmp varchar2(4000);
begin
v_sql := 'select sjd ';
v_str :='';
v_spa := ' ';
v_sgn := ',';
open v_cur for
select distinct HJD from AA;
loop
fetch v_cur into v_hjd;
exit when v_cur%notfound;
v_str := v_str||v_sgn||'sum(decode(hjd,'''||v_hjd||''',jcd)) as '||v_hjd||' ';
end loop;
v_tmp := v_sql||v_str||' FROM aa GROUP BY sjd';
as_sql :=v_tmp;
close v_cur;