Oracle SQL如何在循环中重复执行查询并输出，解决游标复用报错？

你原有代码的核心报错原因不是“无法多次打开同一个游标”，而是调用dbms_sql.return_result(rc)之后，游标控制权已经移交Oracle的客户端输出框架，不允许手动执行close rc操作，重复循环到下一次open的时候就会触发游标状态异常。

可以通过游标实现需求，只需要删除手动关闭游标的代码即可，每次循环open的都是全新的游标实例，不存在重复打开同一个游标的问题，适配Oracle 12c及以上版本：

CREATE OR REPLACE PROCEDURE writer
AS
    rc sys_refcursor;
BEGIN
    LOOP
        OPEN rc FOR SELECT 
                        (SELECT username FROM v$session WHERE sid=a.sid) blocker, 
                        a.sid blocker_sid, 
                        (SELECT username FROM v$session WHERE sid=b.sid) blockee, 
                        b.sid blockee_sid 
                    FROM v$lock a, v$lock b 
                    WHERE a.block = 1 
                      AND b.request > 0 
                      AND a.id1 = b.id1 
                      AND a.id2 = b.id2;
        -- 输出结果集，Oracle自动管理该游标生命周期，无需手动关闭
        dbms_sql.return_result(rc);
        SYS.dbms_session.sleep(1);
    END LOOP;
END;
/

显式游标循环输出方案 #

如果不需要返回结果集、只需要打印阻塞信息，用FOR循环游标更稳定，Oracle会自动处理游标的打开/关闭逻辑，不会出现状态异常：

CREATE OR REPLACE PROCEDURE writer
AS
BEGIN
    LOOP
        FOR rec IN (
            SELECT 
                (SELECT username FROM v$session WHERE sid=a.sid) blocker, 
                a.sid blocker_sid, 
                (SELECT username FROM v$session WHERE sid=b.sid) blockee, 
                b.sid blockee_sid 
            FROM v$lock a, v$lock b 
            WHERE a.block = 1 
              AND b.request > 0 
              AND a.id1 = b.id1 
              AND a.id2 = b.id2
        ) LOOP
            DBMS_OUTPUT.PUT_LINE('['||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'] 阻塞会话: '||rec.blocker||'('||rec.blocker_sid||') -> 被阻塞会话: '||rec.blockee||'('||rec.blockee_sid||')');
        END LOOP;
        SYS.dbms_session.sleep(1);
    END LOOP;
END;
/

使用前需要提前执行SET SERVEROUTPUT ON开启输出。

临时排查免存储过程方案 #

如果只是临时排查锁问题，不需要创建存储过程，可以直接用匿名块执行：

SET SERVEROUTPUT ON
EXEC FOR i IN 1..1000 LOOP FOR rec IN (SELECT (SELECT username FROM v$session WHERE sid=a.sid) blocker, a.sid blocker_sid, (SELECT username FROM v$session WHERE sid=b.sid) blockee, b.sid blockee_sid FROM v$lock a, v$lock b WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2) LOOP DBMS_OUTPUT.PUT_LINE('['||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'] 阻塞: '||rec.blocker||'('||rec.blocker_sid||') -> 被阻塞: '||rec.blockee||'('||rec.blockee_sid||')'); END LOOP; DBMS_SESSION.sleep(1); END LOOP;

• 无限循环的存储过程执行后，需要手动终止会话才能停止运行
• 若当前用户没有DBMS_SESSION包执行权限，可以用DBMS_LOCK.SLEEP替代，前提是有DBMS_LOCK包的执行权限

内容的提问来源于stack exchange，提问作者Vladislav