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;
(
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;
解决方案 »
- 中文数字转阿拉伯数字 oracle函数
- 专家级问题! Oracle中有没有象SQLSERVER那样的聚集索引?
- oracle存储过程操作相邻记录
- 用户无法直接访问默认表空间??
- oracle10客户端与服务器
- 2000 server、xp下安装oracle8i, 怎么弄?要改什么配置??
- 同一数据库(oracle)完整迁移的问题
- 系统重做,原来的数据库中已经存的表如何可以倒入新库中。
- 高手必看:ORACLE中加了日期条件的SQL查询比没有加日期条件的SQL查询慢,哪位高手能找到原因,提出解决办法吗?(附EXPLAIN 情况)
- 如何用UTL_FILE.FOPEN打开客户端的文件?急!
- 如何将dmp文件导入到sqlserver2005中
- oracle并行处理
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),
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;