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;