包头
- 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.bubuko.com/infodetail-2495161.html