CREATE OR REPLACE PROCEDURE TJ_XYKS
(
v_jgid in number,
v_zkfl out number,
v_zkyy out number,
v_cnlfl out number,
v_cnlyy out number,
v_lkfl out number,
v_lkyy out number
)
is
begin
select
nvl(sum(zkfl),0) into v_zkfl,
nvl(sum(zkyy),0) into v_zkyy,
nvl(sum(cnlfl),0) into v_cnlfl,
nvl(sum(cnlyy),0) into v_cnlyy,
nvl(sum(lkfl),0) into v_lkfl,
nvl(sum(lkyy),0) into v_lkyy
from(
select sum(zkrs) as zkfl,sum(rs) zkyy, 0 cnlfl, 0 cnlyy,0 lkfl,0 lkyy,jgid from Yy_Yyzk where jgid like ''||v_jgid||'%' group by jgid
union all
select 0 zkfl,0 zky, sum(zkrs) as cnlfl,sum(rs) as cnlyy,0 lkfl,0 lkyy,jgid from YY_YYCNL where jgid like ''||v_jgid||'%' group by jgid
union all
select 0 zkfl,0 zky, 0 as cnlfl,0 as cnlyy,sum(lkrs) lkfl,sum(rs) lkyy,jgid from YY_YYLK where jgid like ''||v_jgid||'%' group by jgid
);
end;
------解决方案--------------------
- SQL code
CREATE OR REPLACE PROCEDURE TJ_XYKS(v_jgid in number, v_zkfl out number, v_zkyy out number, v_cnlfl out number, v_cnlyy out number, v_lkfl out number, v_lkyy out number) isbegin select nvl(sum(zkfl), 0), nvl(sum(zkyy), 0), nvl(sum(cnlfl), 0), nvl(sum(cnlyy), 0), nvl(sum(lkfl), 0), nvl(sum(lkyy), 0) into v_zkfl, v_zkyy, v_cnlfl, v_cnlyy, v_lkfl, v_lkyy --into应该这样写才对 from (select sum(zkrs) as zkfl, sum(rs) zkyy, 0 cnlfl, 0 cnlyy, 0 lkfl, 0 lkyy, jgid from Yy_Yyzk where jgid like '' || v_jgid || '%' group by jgid union all select 0 zkfl, 0 zky, sum(zkrs) as cnlfl, sum(rs) as cnlyy, 0 lkfl, 0 lkyy, jgid from YY_YYCNL where jgid like '' || v_jgid || '%' group by jgid union all select 0 zkfl, 0 zky, 0 as cnlfl, 0 as cnlyy, sum(lkrs) lkfl, sum(rs) lkyy, jgid from YY_YYLK where jgid like '' || v_jgid || '%' group by jgid);end;