oracle 嵌套游标写法

[wangsa7013]

一下是我写的oracle 过程，其中有嵌套游标。编译有错误，请各位指点
CREATE OR REPLACE PROCEDURE daily_stat
(
is_Projectid IN DAILY_INFO.PROJECTID%TYPE,
on_Code OUT NUMBER,
os_Msg OUT VARCHAR2
)

AS
vs_Projectid PROJECT_BASE.PROJECTID%TYPE;
vs_Projectname SPPROJECTINFOTABLE.PROJECTNAME%TYPE;
vs_Projectstage PROJECT_STAGE.STAGE_NAME%TYPE;
vn_PM SPEMPLOYEEINFOTABLE.EMPLOYEENAME%TYPE;
vn_Plantime PROJECT_STAGE.PLANTIME%TYPE;
vn_Facttime PROJECT_STAGE.FACTTIME%TYPE;
vs_Coderows PROJECT_STAGE.CODENUM%TYPE;
all_Facttime PROJECT_STAGE.FACTTIME%TYPE;
all_Coderows PROJECT_STAGE.CODENUM%TYPE;
vs_Content DAILY_INFO.CONTENT%TYPE;

COURSOR cur_Stage IS
SELECT PROJECTID,PROJECT_STAGE
FROM DAILY_INFO
WHERE(PROJECTID = is_Projectid OR '0'= is_Projectid)
AND flag=0;


CURSOR cur_GetPoInfo IS
SELECT TIME_TAKE,CODENUM
FROM DAILY_INFO
WHERE PROJECTID = is_Projectid
AND project_stage = vs_Projectstage
AND flag = 0;
begin
open cur_Stage ;
fetch cur_Stage into vs_Projectid,vs_Projectstage;
while cur_Stage%found loop

on_Code := 0;
os_Msg := '';

OPEN cur_GetPoInfo;
FETCH cur_GetPoInfo INTO vn_Facttime,vs_Coderows;
WHILE cur_GetPoInfo%FOUND LOOP

all_Facttime:=0+vn_Facttime;
all_Coderows:=0+vs_Coderows;

update project_stage set facttime=all_Facttime,codenum=all_Coderows where projectid=is_Projectid and stage_name=vs_Projectstage;
UPDATE DAILY_INFO SET FLAG=1 WHERE PROJECTID = IS_PROJECTID;
FETCH cur_GetPoInfo INTO vn_Facttime,vs_Coderows;
END LOOP;
CLOSE cur_GetPoInfo;

FETCH cur_Stage INTO vs_Projectid,vs_Projectstage;
END LOOP;
CLOSE cur_Stage;

EXCEPTION
WHEN NO_DATA_FOUND THEN
on_Code := -SQLCODE;

ROLLBACK;
RETURN;

WHEN OTHERS THEN
on_Code := SQLCODE;

ROLLBACK;
RETURN;
END;
/

[anmoi_yang]

WHEN NO_DATA_FOUND THEN 与WHEN OTHERS THEN 下面仅是执行一句吗? 缺少being ...end;

[chliang315]

没有begin

[KingSunSha]

COURSOR cur_Stage IS
应该是
CURSOR

[wangsa7013]

修改如下编译通过，但在程序中调用后出错，通过调试只知道是走入了过程的EXCEPTION。请指教
CREATE OR REPLACE PROCEDURE daily_stat4(
is_Projectid IN DAILY_INFO.PROJECTID%TYPE,
is_Begdate IN DAILY_SEQUENCE.REPORT_DATE%TYPE,
is_Enddate IN DAILY_SEQUENCE.REPORT_DATE%TYPE,
on_Code OUT NUMBER,
os_Msg OUT VARCHAR2
)
AS

vs_Projectname SPPROJECTINFOTABLE.PROJECTNAME%TYPE;
vs_Projectstage PROJECT_STAGE.STAGE_NAME%TYPE;
vn_PM SPEMPLOYEEINFOTABLE.EMPLOYEENAME%TYPE;
vn_Plantime PROJECT_STAGE.PLANTIME%TYPE;
vn_Facttime PROJECT_STAGE.FACTTIME%TYPE;
vs_Coderows PROJECT_STAGE.CODENUM%TYPE;
all_Facttime PROJECT_STAGE.FACTTIME%TYPE;
all_Coderows PROJECT_STAGE.CODENUM%TYPE;
vs_Content DAILY_INFO.CONTENT%TYPE;
vn_time DAILY_INFO.TIME_TAKE%type;
vn_codenum DAILY_INFO.CODENUM%type;
vs_Projectidtemp PROJECT_BASE.PROJECTID%TYPE;
vs_Projectid PROJECT_BASE.PROJECTID%TYPE;

CURSOR cur_Stage IS
select PROJECTID,project_stage,sum(TIME_TAKE),sum(CODENUM)
FROM DAILY_INFO
WHERE PROJECTID = (select projectname from spprojectinfotable where projectid=is_Projectid)
AND flag=0
and sequence in (select sequence from daily_sequence where report_date >=any to_date(is_Begdate,'yyyy-mm-dd') and report_date <=any to_date(is_Enddate,'yyyy-mm-dd'))
group by PROJECTID,project_stage;
begin
on_Code := 0;
os_Msg := '';
all_Coderows :=0.00;
all_Facttime :=0;

open cur_Stage ;
fetch cur_Stage into vs_Projectidtemp,vs_Projectstage,vn_time,vn_codenum;
while cur_Stage%found loop

UPDATE DAILY_INFO SET FLAG=1 WHERE PROJECTID = vs_Projectidtemp and project_stage=vs_Projectstage ;
UPDATE project_stage set facttime=vn_time,codenum=vn_codenum where projectid=vs_Projectidtemp and stage_name=vs_Projectstage;

FETCH cur_Stage INTO vs_Projectidtemp,vs_Projectstage,vn_time,vn_codenum;
END LOOP;
CLOSE cur_Stage;

EXCEPTION
WHEN NO_DATA_FOUND THEN
on_Code := -SQLCODE;

ROLLBACK;
RETURN;

WHEN OTHERS THEN
on_Code := SQLCODE;

ROLLBACK;
RETURN;
END;
/

[KingSunSha]

１）两个RETURN都不需要。
２）错误代码是什么？

[wangsa7013]

ora-06550:第10行，第17列：
pls-00382:表达式类型错误
pl/sql:statement ignored
ora-06550:第11行，第17列：
pls-00382:表达式类型错误
ora-06550:第11行，第3列：
pl/sql:statement ignored

[wangsa7013]

错误代码列出了，但好像没什么用。

[wangsa7013]

错误我自己解决了。
出在and sequence in (select sequence from daily_sequence where report_date > =any to_date(is_Begdate,'yyyy-mm-dd') and report_date <=any to_date(is_Enddate,'yyyy-mm-dd'))