一下是我写的oracle 过程，其中有嵌套游标。编译有错误，请各位指点 CREATE OR REPLACE PROCEDURE daily_stat ( is_Projectid IN DAILY_INFO.PROJECTID%TYPE, on_Code OUT NUMBER, os_Msg OUT VARCHAR2 )
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
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;
修改如下编译通过，但在程序中调用后出错，通过调试只知道是走入了过程的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
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;
错误我自己解决了。 出在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'))
