游标死循环:
报错信息:
EDU.PROC_A:ORA-01000: maximum open cursors exceeded ORA-06512: at "EDU.PROC_A", line 29 ORA-01000: maximum open cursors exceeded ORA-06512: at "EDU.PROC_A", line 25 ORA-01000: maximum open cursors exceeded ORA-0
一、建表:
/*
CREATE TABLE A_B(
NUM NUMBER(8)
)*/
二、建过程:
CREATE OR REPLACE PROCEDURE PROC_A(
I_NUM NUMBER
)
AS
TYPE CUR_TYPE IS REF CURSOR;
CUR CUR_TYPE;
V_NUM NUMBER(8);
V_ERR_CODE NUMBER(6);
V_ERR_TXT VARCHAR2(300);
BEGIN
OPEN CUR FOR SELECT N FROM (SELECT LEVEL N FROM DUAL CONNECT BY LEVEL<=10);
LOOP
FETCH CUR INTO V_NUM;
EXIT WHEN CUR%NOTFOUND;
IF V_NUM=1 THEN
PROC_A(V_NUM);
END IF;
END LOOP;
COMMIT;
INSERT INTO A_B VALUES(I_NUM);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
--记录存储过程错误日志并提交
V_ERR_CODE := SQLCODE;
V_ERR_TXT := 'EDU.PROC_A:'||SUBSTR(SQLERRM, 1, 200);
INSERT
INTO PROC_ERR_LOGS(CODE,MESSAGE,INFO) VALUES(V_ERR_CODE,V_ERR_TXT,'EXCEPTION');
COMMIT;
END;
三、执行过程:
BEGIN
PROC_A(1);
END;
四、查询结果:
SELECT * FROM A_B
五、问题:
①、死循环问题
②、异常捕捉,抛出异常,虽然游标达到了最大值,程序报错,但是只影响了最后一次调用,前边的调用都成功执行了。
③、游标的打开最大值:初始化文件中设置OPEN_CURSORS参数
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-732725/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25323853/viewspace-732725/