create or replace package body pro_test isprocedure Alarm_ContentsByTime(
p_StartTime varchar2,----开始时间
p_EndTime varchar2,----结束时间
io_cursorinOUT out_cursor
)iscursor cursor_1 IS--获取 列用于循环。select DISTINCT t.ps_productorcode code, t.PS_PRODUCTORNAME name,1type FROM PSINFO_PRODUCTYIELD_SB t
union allselect DISTINCT f.ps_rawcode code,f.ps_rawname name,2 type fromPSINFO_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 (selectc.c0003_stcode,
c.c0003_stname,
a.ps_productorname ps_name,
a.ps_unit,
sum(nvl(a.ps_productoryield,0)) clfromt0003_station c,PSINFO_PRODUCTYIELD_SB awhere 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 allselectc.c0003_stcode,
c.c0003_stname,
b.ps_rawname ps_name,
b.ps_unit,
sum(nvl(b.ps_productoryield,0)) clfromt0003_station c,PSINFO_RAWMATERIALS_SB bwhere 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_cursorforV_SQL;
end Alarm_ContentsByTime;
end pro_test;