一下是我写的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;
/
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;
/
解决方案 »
- 用instantclient +plsql连接oracle 出现ORA-12514怎么解决啊
- 需求:PL/SQL递归求解,如何实现?
- win7 oracle 10g服务启动不起来
- 把一个字段中的blob内容通过存储过程复制到另外一个blob字段该怎么做?
- ORACLE隐式游标与显式游标有何区别?
- .NET下ORACLE连接问题,请帮忙指点@_@
- 存储过程中错误屏蔽
- not in 和not exists有什么区别?使用这两个条件查出的结果不一样啊?
- 新手提问:请问图片怎样存储?
- ora-01033 ORACLE initialization or shutdown in process
- 连接Oracle数据库问题
- 怎么把日期格式化为:2008-02-11:2008-02
应该是
CURSOR
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;
/
2)错误代码是什么?
pls-00382:表达式类型错误
pl/sql:statement ignored
ora-06550:第11行,第17列:
pls-00382:表达式类型错误
ora-06550:第11行,第3列:
pl/sql:statement ignored
出在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'))