错误提示:
行号= 7 列号= 3 错误文本= PLS-00103: 出现符号 "IF"在需要下列之一时: . ( * @ % & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || 符号 ";" 被替换为 "IF" 后继续。
行号= 12 列号= 3 错误文本= PLS-00103: 出现符号 "ELSE"在需要下列之一时: , ; for 符号 ";" 被替换为 "ELSE" 后继续。
行号= 17 列号= 3 错误文本= PLS-00103: 出现符号 "ELSE"在需要下列之一时: , ; for 符号 ";" 被替换为 "ELSE" 后继续。
行号= 22 列号= 3 错误文本= PLS-00103: 出现符号 "END"在需要下列之一时: , ; for
行号= 7 列号= 3 错误文本= PLS-00103: 出现符号 "IF"在需要下列之一时: . ( * @ % & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || 符号 ";" 被替换为 "IF" 后继续。
行号= 12 列号= 3 错误文本= PLS-00103: 出现符号 "ELSE"在需要下列之一时: , ; for 符号 ";" 被替换为 "ELSE" 后继续。
行号= 17 列号= 3 错误文本= PLS-00103: 出现符号 "ELSE"在需要下列之一时: , ; for 符号 ";" 被替换为 "ELSE" 后继续。
行号= 22 列号= 3 错误文本= PLS-00103: 出现符号 "END"在需要下列之一时: , ; for
其他的也一样
WHERE sjms='考试' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and a.dcks=1) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC
在存储过程里也是不允许的。
as
type my_rc is ref cursor;
end;
/
CREATE PROCEDURE pExam(Fenlei IN Varchar2,RenID IN NUMBER,p_rc test_age.my_rc)
is
pDlm Varchar2(20);
PGuest NUMBER;
begin
pDlm:='dlm'
IF Fenlei='KS' THEN--考试
open p_rc for
SELECT sjid,sjmc,sjms,sjlx,kssj,jssj,dtsj,cjr FROM ksj_main a
WHERE sjms='考试' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and a.dcks=1) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC;
ELSIf Fenlei='LX' THEN--练习
open p_rc for
SELECT sjid,sjmc,sjms,sjlx,kssj,jssj,dtsj,cjr FROM ksj_main a
WHERE sjms='练习' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and a.dcks=1) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC;
ELSIF Fenlei='ZY' THEN--作业
open p_rc for
SELECT sjid,sjmc,sjms,sjlx,kssj,jssj,dtsj,cjr FROM ksj_main a
WHERE sjms='作业' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and kszt=2) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC;
END IF
END;
/
IF FENLEI =考试
ELSE FENLEI=联系
ELSE FENLEI=作业然后分别处理,如果事我坐,我就用CASE语句坐类别甄选,而不用IF语句.
另外,对你的表结构不是很了解,看不懂你要坐什么操作.
is
pDlm Varchar2(20);
PGuest NUMBER;
begin
pDlm:='dlm'
IF (Fenlei='KS') THEN--考试
SELECT sjid,sjmc,sjms,sjlx,kssj,jssj,dtsj,cjr FROM ksj_main a
WHERE sjms='考试' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and a.dcks=1) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC;
end if;
ELSE IF (Fenlei='LX') THEN--练习
SELECT sjid,sjmc,sjms,sjlx,kssj,jssj,dtsj,cjr FROM ksj_main a
WHERE sjms='练习' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and a.dcks=1) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC;
end if;
ELSE IF (Fenlei='ZY') THEN--作业
SELECT sjid,sjmc,sjms,sjlx,kssj,jssj,dtsj,cjr FROM ksj_main a
WHERE sjms='作业' and sjbz=0 and (kssj<SYSDATE and jssj>SYSDATE) and ((not exists(select * from ksj_ren where sjid=a.sjid and czlb=0)) or exists(select * from ksj_ren where czlb=0 and sjid=a.sjid and renid=RenID))
and (exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID and kszt=2) or (not exists(select * from kaoshi_master where sjid=a.sjid and renid=RenID)))
ORDER BY a.sjid DESC;
end if;
END IF;
END;