oracle sql open cursor for,oracle pl sql CURSOR_ALREADY_OPEN

先创建表：

CREATE TABLE TB_USER

(

ID INTEGER PRIMARY KEY,

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

CREATE SEQUENCE SEQ_USER

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOCYCLE

CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW

BEGIN

SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

插入一些数据。

遍历数据：

DECLARE

V_AGE TB_USER.USER_AGE%TYPE;

V_NAME TB_USER.USER_NAME%TYPE;

CURSOR USER_CURSOR IS SELECT USER_NAME, USER_AGE FROM TB_USER;

BEGIN

FOR USER_RECORD IN USER_CURSOR LOOP

DBMS_OUTPUT.put_line(USER_RECORD.USER_NAME || ', ' || USER_RECORD.USER_AGE);

END LOOP;

EXCEPTION

WHEN CURSOR_ALREADY_OPEN THEN

DBMS_OUTPUT.put_line('cursor already open');

END;

执行上述代码，会显示每条记录。

FOR LOOP前不能OPEN，否则会报CURSOR_ALREADY_OPEN，错误代码如下：

DECLARE

V_AGE TB_USER.USER_AGE%TYPE;

V_NAME TB_USER.USER_NAME%TYPE;

CURSOR USER_CURSOR IS SELECT USER_NAME, USER_AGE FROM TB_USER;

BEGIN

OPEN USER_CURSOR;--错误代码

FOR USER_RECORD IN USER_CURSOR LOOP

DBMS_OUTPUT.put_line(USER_RECORD.USER_NAME || ', ' || USER_RECORD.USER_AGE);

END LOOP;

EXCEPTION

WHEN CURSOR_ALREADY_OPEN THEN

DBMS_OUTPUT.put_line('cursor already open');

END; 上面OPEN了两次。