oracle 預存程序-動態行轉列，解決。

標籤：連結地址   技術   replace   package   inf   log   union   tput   結束時間   

包頭

create or replace package pro_test as      TYPE out_cursor IS REF CURSOR; procedure Alarm_ContentsByTime(                              p_StartTime      varchar2, ----開始時間                              p_EndTime      varchar2, ----結束時間                              io_cursor in OUT out_cursor                        );end pro_test;

包體

create or replace package body pro_test is  procedure Alarm_ContentsByTime(                               p_StartTime      varchar2, ----開始時間                               p_EndTime      varchar2, ----結束時間                                io_cursor in OUT out_cursor                        ) is  cursor cursor_1 IS   --擷取 列用於迴圈。  select DISTINCT t.ps_productorcode code, t.PS_PRODUCTORNAME name,1 type FROM PSINFO_PRODUCTYIELD_SB t                            union all                           select DISTINCT f.ps_rawcode code,f.ps_rawname name,2 type from PSINFO_RAWMATERIALS_SB f                           ORDER BY type,code;  V_SQL VARCHAR2(6000);  begin      V_SQL := ‘select ‘‘市本級‘‘ as c0003_stname, 1 as c0003_stcode‘;        FOR V_XCLCK IN cursor_1      LOOP        V_SQL := V_SQL || ‘,‘ || ‘min(case when t1.ps_name=‘‘‘ || V_XCLCK.name ||                 ‘‘‘ then t1.ps_name end) as ‘ || V_XCLCK.name||‘mc‘|| ‘,‘ || ‘min(case when t1.ps_name=‘‘‘ || V_XCLCK.name ||                 ‘‘‘ then t1.cl end) as ‘ || V_XCLCK.name||‘cl‘|| ‘,‘ || ‘min(case when t1.ps_name=‘‘‘ || V_XCLCK.name ||                 ‘‘‘ then t1.ps_unit end) as ‘ || V_XCLCK.name||‘dw‘;      END LOOP;      V_SQL := V_SQL || ‘  from t0003_station t                           inner join (                           select                            c.c0003_stcode,                           c.c0003_stname,                           a.ps_productorname ps_name,                           a.ps_unit,                           sum(nvl(a.ps_productoryield,0)) cl                           from t0003_station c,PSINFO_PRODUCTYIELD_SB a                           where a.c0003_stcode=c.c0003_stcode                            and a.ps_month>=to_date(‘‘‘||p_StartTime||‘‘‘,‘‘yyyy-mm-dd hh24:mi:ss‘‘)                           and a.ps_month<=to_date(‘‘‘||p_EndTime||‘‘‘,‘‘yyyy-mm-dd hh24:mi:ss‘‘)                           group by c.c0003_stcode,c.c0003_stname,a.ps_productorname,a.ps_unit                           union all                            select                            c.c0003_stcode,                           c.c0003_stname,                           b.ps_rawname ps_name,                           b.ps_unit,                           sum(nvl(b.ps_productoryield,0)) cl                           from t0003_station c,PSINFO_RAWMATERIALS_SB b                           where b.c0003_stcode=c.c0003_stcode                           and b.ps_month>=to_date(‘‘‘||p_StartTime||‘‘‘,‘‘yyyy-mm-dd hh24:mi:ss‘‘)                           and b.ps_month<=to_date(‘‘‘||p_EndTime||‘‘‘,‘‘yyyy-mm-dd hh24:mi:ss‘‘)                           group by c.c0003_stcode,c.c0003_stname,b.ps_rawname,b.ps_unit                           ) t1 on t.c0003_stcode=t1.c0003_stcode                           left join t0070_enterprise t70 on                            t.c0003_stcode=t70.c0003_stcode                            and t70.c0003_year=t1.c0003_stcode                            and t70.c0070_stcode_ctrl=‘‘320100‘‘                           group by t.c0003_stname,t.c0003_stcode ‘;      DBMS_OUTPUT.PUT_LINE(V_SQL);      --V_SQL := ‘CREATE OR REPLACE VIEW RESULT  AS ‘||  V_SQL;       open io_cursor for V_SQL;  end Alarm_ContentsByTime;end pro_test;

執行之後

連結地址：http://www.cnblogs.com/blue123/p/8441112.html

 

 

oracle 預存程序-動態行轉列，解決。