create or replace procedure sp_tab(c_result out varchar2) as sqlstr varchar2(4000) := 'select year, superetname ' ; c1 sys_refcursor; v1 varchar2(100); begin open c1 for 'select distinct month from tab'; loop fetch c1 into v1; exit when c1%notfound; sqlstr := sqlstr ||',max(decode(month,''' || v1 || ''',sales))"' || v1 || '月"'; end loop; close c1; sqlstr := sqlstr || ' from tab group by year, superetname';a c_result:= sqlstr; dbms_output.put_line(c_result); end sp_tab;
或者直接通过FOR循环 CREATE OR REPLACE PROCEDURE P_TEST IS V_SQL VARCHAR2(2000);
BEGIN V_SQL := 'SELECT YEAR,SUPERMAKETNAME '; FOR T_MONTH IN 1..12 LOOP V_SQL := V_SQL || ',' || 'MAX(DECODE(MONTH,'||T_MONTH||',sales,0)) "'||T_MONTH||'月"'; END LOOP; V_SQL := V_SQL || ' FROM TB' ; V_SQL := V_SQL || ' GROUP BY YEAR,SUPERMAKETNAME'; DBMS_OUTPUT.PUT_LINE(V_SQL); --EXECUTE IMMEDIATE V_SQL; END;
as
sqlstr varchar2(4000) := 'select year, superetname ' ;
c1 sys_refcursor;
v1 varchar2(100);
begin
open c1 for 'select distinct month from tab';
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr := sqlstr ||',max(decode(month,''' || v1 || ''',sales))"' || v1 || '月"';
end loop;
close c1;
sqlstr := sqlstr || ' from tab group by year, superetname';a
c_result:= sqlstr;
dbms_output.put_line(c_result);
end sp_tab;
CREATE OR REPLACE PROCEDURE P_TEST
IS
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'SELECT YEAR,SUPERMAKETNAME ';
FOR T_MONTH IN 1..12
LOOP
V_SQL := V_SQL || ',' || 'MAX(DECODE(MONTH,'||T_MONTH||',sales,0)) "'||T_MONTH||'月"';
END LOOP;
V_SQL := V_SQL || ' FROM TB' ;
V_SQL := V_SQL || ' GROUP BY YEAR,SUPERMAKETNAME';
DBMS_OUTPUT.PUT_LINE(V_SQL);
--EXECUTE IMMEDIATE V_SQL;
END;