oracle库中case语句在存储过程的问题 ( 积分: 50 )

CREATE OR REPLACE PROCEDURE gbgl_pro_hbgbtj AS
i number(9,0);
begin
delete from gbgl_qy_cjgbtjb;
declare
cursor plts_cursor1 is
SELECT swhere as sid from gbgl_temp_qycjhbgbtjb;
pltsrow plts_cursor1%ROWTYPE;
begin
open plts_cursor1;
loop
fetch plts_cursor1 into pltsrow;
exit when plts_cursor1%notfound;
select nvl(max(id),0) into i from gbgl_qy_cjgbtjb;
insert into gbgl_qy_cjgbtjb
(id,hj,dy,fn,dqgb,pjnl,nl35,nl40,nl45,nl50,nl55,nl56,yjs,dx,dz,zz,g
z,jsj,gj,zj,zlj,yj)
select i+1 id,count(distinct a.id) hj,
sum(case when d.zzmmid in (select id from ryxx_bm_zzmm where id=3)
then 1 else 0 end) dy,
sum(case when a.xbid in (select id from ryxx_bm_xbbm where id=2)
then 1 else 0 end) fn,
sum(case when a.dp_id in (select id from t_um_department where
gwybrs=1) then 1 else 0 end) dqgb,
round(avg(to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4)))) pjnl,
sum(case when to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))<=35 then 1 else 0 end) nl35,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=36) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=40) then 1 else 0 end) nl40,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=41) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=45) then 1 else 0 end) nl45,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=46) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=50) then 1 else 0 end) nl50,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=51) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=55) then 1 else 0 end) nl55,
sum(case when to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=56 then 1 else 0 end) nl56,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
id<=8) then 1 else 0 end) yjs,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where (id=9
or f_id=9)) then 1 else 0 end) dx,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
(id=23 or f_id=23)) then 1 else 0 end) dz,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
(id=37 or f_id=37)) then 1 else 0 end) zz,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
id>=47) then 1 else 0 end) gz,
0 jsj,0 gj,0 zj,0 zlj,0 yj
from t_um_user a,(select xzjbid,ryid from ryxx_gzjl where xzbs=1)
b,ryxx_bm_xzjb c,
(select zzmmid,ryid from ryxx_zzmm where xzbs=1) d,ryxx_bm_ryfl e,
(select whcdid,ryid from ryxx_xxjl where xzbs=1) f
where a.id=b.ryid(+) and b.xzjbid=c.id(+) and a.id=f.ryid(+) and
a.id=d.ryid(+) and a.ryflid=e.id(+) and substr(e.class,1,5)
='00001' and a.id<>1 +pltsrow.sid;
commit;
end loop;
close plts_cursor1;
end;
commit;
end;



编译的时候出现下列错误，sql没有问题 单独运行insert语句都正常

LINE 17 (5): PLS-00103: 出现符号 "CASE"在需要下列之一时：
(-+allmodnull
<an identifier><a double-quoted delimited-identifier>
<a bind variable>avgcountcurrentdistinctmaxminpriorsqlstddev
sumuniquevarianceexecuteforalltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>

CREATE OR REPLACE PROCEDURE gbgl_pro_hbgbtj AS
i number(9,0);
begin
delete from gbgl_qy_cjgbtjb;
declare
cursor plts_cursor1 is
SELECT swhere as sid from gbgl_temp_qycjhbgbtjb;
pltsrow plts_cursor1%ROWTYPE;
begin
open plts_cursor1;
loop
fetch plts_cursor1 into pltsrow;
exit when plts_cursor1%notfound;
select nvl(max(id),0) into i from gbgl_qy_cjgbtjb;
insert into gbgl_qy_cjgbtjb
(id,hj,dy,fn,dqgb,pjnl,nl35,nl40,nl45,nl50,nl55,nl56,yjs,dx,dz,zz,g
z,jsj,gj,zj,zlj,yj)
select i+1 id,count(distinct a.id) hj,
sum(case when d.zzmmid in (select id from ryxx_bm_zzmm where id=3)
then 1 else 0 end) dy,
sum(case when a.xbid in (select id from ryxx_bm_xbbm where id=2)
then 1 else 0 end) fn,
sum(case when a.dp_id in (select id from t_um_department where
gwybrs=1) then 1 else 0 end) dqgb,
round(avg(to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4)))) pjnl,
sum(case when to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))<=35 then 1 else 0 end) nl35,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=36) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=40) then 1 else 0 end) nl40,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=41) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=45) then 1 else 0 end) nl45,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=46) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=50) then 1 else 0 end) nl50,
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=51) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=55) then 1 else 0 end) nl55,
sum(case when to_number(to_char(sysdate,'yyyy'))-to_number(substr
(a.csrq,1,4))>=56 then 1 else 0 end) nl56,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
id<=8) then 1 else 0 end) yjs,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where (id=9
or f_id=9)) then 1 else 0 end) dx,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
(id=23 or f_id=23)) then 1 else 0 end) dz,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
(id=37 or f_id=37)) then 1 else 0 end) zz,
sum(case when f.whcdid in (select id from ryxx_bm_whcd where
id>=47) then 1 else 0 end) gz,
0 jsj,0 gj,0 zj,0 zlj,0 yj
from t_um_user a,(select xzjbid,ryid from ryxx_gzjl where xzbs=1)
b,ryxx_bm_xzjb c,
(select zzmmid,ryid from ryxx_zzmm where xzbs=1) d,ryxx_bm_ryfl e,
(select whcdid,ryid from ryxx_xxjl where xzbs=1) f
where a.id=b.ryid(+) and b.xzjbid=c.id(+) and a.id=f.ryid(+) and
a.id=d.ryid(+) and a.ryflid=e.id(+) and substr(e.class,1,5)
='00001' and a.id<>1 +pltsrow.sid;
commit;
end loop;
close plts_cursor1;
end;
commit;
end;



编译的时候出现下列错误，sql没有问题 单独运行insert语句都正常

LINE 17 (5): PLS-00103: 出现符号 "CASE"在需要下列之一时：
(-+allmodnull
<an identifier><a double-quoted delimited-identifier>
<a bind variable>avgcountcurrentdistinctmaxminpriorsqlstddev
sumuniquevarianceexecuteforalltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>

sum(case when d.zzmmid in (select id from ryxx_bm_zzmm where id=3)
then 1 else 0 end) dy
=======================
表达式错误吧？
下面我的程序中的一个proc

/*
SoftWareName: Prc_FactTotal
CreateDate: 2005-01-14
Author: jfyes
Version: V1.0
function: 取得指定物料编号的可用库存(实际库存, 0, 1, 2, 3)
ParamStr: code_id = 物料编号 codeType= 类型
Remark: for tai shing ind work
Return: (0)
*/

(strcode in varchar,
codeType0 out number,
codeType1 out number,
codeType2 out number,
codeType3 out number)
as
cursor curStock is
SELECT s.serialno, code_id, s.name, spec,
ccode, s.cname, codetype_id,
round(SUM(t.amount * t.nature), 2)as totalamt
FROM w_serial s, w_tranlist t
WHERE s.serialno = t.serialno
AND code_id = strcode
GROUP BY s.serialno, code_id, s.name, spec,
ccode, s.cname, codetype_id
HAVING round(SUM(t.amount * t.nature), 2) > 0;
curDataSet curStock%rowtype;
begin
codeType0 := 0;
codeType1 := 0;
codeType2 := 0;
codeType3 := 0;
open curStock;
Loop
fetch curStock into curDataSet;
exit when curStock%notfound;
case curDataSet.codetype_id
when 0 then
codeType0 := codeType0 + curDataSet.totalamt;
when 1 then
codeType1 := codeType1 + curDataSet.totalamt;
when 2 then
codeType2 := codeType2 + curDataSet.totalamt;
when 3 then
codeType3 := codeType3 + curDataSet.totalamt;
end case;
end loop;
close curStock;
end;

sum(case when d.zzmmid in (select id from ryxx_bm_zzmm where id=3)
then 1 else 0 end) dy
=======================
表达式错误吧？
下面我的程序中的一个proc


我用的是oracle的数据库 我查了一些资料好像是case语句的问题
不过用decode好像还不能进行一定范围的条件阿，比如阿我需要
sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr(a.csrq,1,4))>=41) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=45) then 1 else 0 end)
怎么用decode实现呢？
谢谢jfyes了呀 我看了你写的代码了 看得不是太懂 不过大概能明白点
感觉和我需要的比较远啊

decode 只是对特定值操作,能不能像case那样对特定的范围操作

sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr(a.csrq,1,4))>=41) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=45) then 1 else 0 end)
怎么用decode实现呢？

oracle我不熟

yu men a

case vartype
when varValue1 then
begin
end;
when varValue1 then
begin
end;
else
begin
end ;
end case;

case vartype
when varValue1 then
表达式;
when varValue2 then
表达式;
else
表达式
end case;

jfyes 下面

sum(case when (to_number(to_char(sysdate,'yyyy'))-to_number(substr(a.csrq,1,4))>=41) and (to_number(to_char(sysdate,'yyyy'))
-to_number(substr(a.csrq,1,4))<=45) then 1 else 0 end)
怎么用decode实现呢？

Decode和 sign()配合可以限制负数， 0和正数
demo===
SELECT DISTINCT workno, team_custid,
DECODE(
SIGN(MIN(awardamt)),
-1,SUM(DECODE(SIGN(awardamt), -1, awardamt)),
SUM(awardamt)/2
)as awardamt,
monwk, yearWk, enterdate
FROM cnown.T_v4

我也在看这个 sign 不过两个条件同时成立的呢 怎么写呢

一个范围条件可以这么写
sum(decode(
sign(
(
to_number(to_char(sysdate,'yyyy'))-
to_number(substr(csrq,1,4))-36
),-1,1,
0
))

多个条件怎么写啊 比如 >36 并且<40

说明一下
sum(decode(
sign(
(
to_number(to_char(sysdate,'yyyy'))-
to_number(substr(csrq,1,4))-36
),-1,1,
0
))

是<36的条件集合

如果需要多个条件的话 我就有点晕了
大家来看看啊 有没有什么好办法啊

decode(
sign(
可以嵌套多层

用这个为例吧
sum(decode(
sign(
(
to_number(to_char(sysdate,'yyyy'))-
to_number(substr(csrq,1,4))-36
),-1,1,
0
))
看看怎么把他写成 36-40之间的集合

如果Sql句子复杂,可以写Function来简化,像你上面的那段句,其目的只有一个表达式为真结果是1否则0,写个func套用不方便多了

有道理 一下提醒了我
不过我还没在后台数据库写过函数
请给点指点 格式 我就省着查了 谢谢 时间宝贵阿

demo========
CREATE OR REPLACE FUNCTION "CNOWN"."FUN_USETOTAL" /*
CreateDate: 2005-01-14
Author: Jinfeng
Version: V1.0
fiunction: 取得指定物料编号的物料预留后的可用库存
ParamStr: code_id = 物料编号 codeType= 类型
Remark: for tai shing ind work
Note: 调用是这的( select FUN_USETOTAL('125I(2)')totalamt from dual)
*/
(strCode varchar) return VARCHAR2
as
cursor CurObligate is
SELECT p.workno, p.code_ID, p.ccode, round(sum(p.qty), 2)as Obligate
FROM plansum p inner join wkplan w
on (p.workno = w.workno)
WHERE p.code_ID = strCode
AND w.docstatus_id in (0,1,2,3)
group by p.workno, p.code_ID, p.ccode;

curDataSet CurObligate%rowtype;
Resdata VARCHAR2(200) := '[0] [0] [0] [0]';
codeType0 number(12, 3); /*实际库存0*/
codeType1 number(12, 3); /*实际库存1*/
codeType2 number(12, 3); /*实际库存2*/
codeType3 number(12, 3); /*实际库存3*/
codeType_0 number(12, 3); /*工作单物料数量0*/
codeType_1 number(12, 3); /*工作单物料数量1*/
codeType_2 number(12, 3); /*工作单物料数量2*/
codeType_3 number(12, 3); /*工作单物料数量3*/
begin
if nvl(trim(strcode), 0) <> '0' then
prc_factTotal(strCode, codeType0, codeType1, codeType2, codeType3);
open CurObligate;
Loop
fetch CurObligate into curDataSet;
exit when CurObligate%notfound;
--prc_MateByWkno(strCode, curDataSet.workno,
--codeType_0, codeType_1, codeType_2, codeType_3);

codeType_0 := GETMATEBYWKNO(strCode, curDataSet.workno, 0);
if codeType_0 < curDataSet.Obligate then
codeType0 := codeType0 -(curDataSet.Obligate - codeType_0);
end if;

codeType_1 := GETMATEBYWKNO(strCode, curDataSet.workno, 1);
if codeType_1 < curDataSet.Obligate then
codeType1 := codeType1 -(curDataSet.Obligate - codeType_1);
end if;

codeType_2 := GETMATEBYWKNO(strCode, curDataSet.workno, 2);
if codeType_2 < curDataSet.Obligate then
codeType2 := codeType2 -(curDataSet.Obligate - codeType_2);
end if;

codeType_3 := GETMATEBYWKNO(strCode, curDataSet.workno, 3);
if codeType_3 < curDataSet.Obligate then
codeType3 := codeType3 -(curDataSet.Obligate - codeType_3);
end if;

end loop;
if CurObligate%rowcount <> 0 then
if sign(codeType3) = -1 then
codeType3 := 0;
end if;
if sign(codeType2) = -1 then
codeType2 := 0;
end if;
Resdata := to_char('['||codeType0 ||'] ['||codeType1 ||'] ['||codeType2 ||'] ['||codeType3||']');
end if;
close CurObligate;
end if;
return Resdata;
end;