如何在PL/SQL存储过程中统计Ref Cursor的执行时间？

首先得明确你当前代码的核心问题：Ref Cursor是延迟执行的，OPEN P_REF_CUR FOR <SELECT QUERY>这行代码只是完成了查询的解析和执行准备，实际的查询执行、数据检索操作，是在调用方（比如Java程序、SQL*Plus客户端）开始fetch游标数据的时候才会发生。所以你现在在OPEN之后插入日志的SYSTIMESTAMP，记录的只是“打开游标”的时间，根本不是查询实际执行完成的时间。

下面给你两种可行的解决方案，根据你的结果集大小来选择：

方案1：用集合暂存结果（适合中小数据集） #

通过BULK COLLECT把查询结果先存入内存集合，这样查询会在存储过程内部立即执行，就能准确捕获到查询的开始和结束时间，之后再把游标指向这个集合返回给调用方。

示例代码：

CREATE OR REPLACE Procedure YOUR_PROC_NAME(P1 IN NUMBER, P_REF_CUR OUT SYS_REFCURSOR) IS 
    V_TS_START TIMESTAMP;
    V_TS_END TIMESTAMP;
    -- 定义与查询结果匹配的集合类型（替换成你的实际表结构或自定义类型）
    TYPE T_QUERY_RESULT IS TABLE OF YOUR_TARGET_TABLE%ROWTYPE;
    V_RESULTS T_QUERY_RESULT;
BEGIN 
    -- 记录查询开始时间
    V_TS_START := SYSTIMESTAMP;
    
    -- 执行查询并将结果批量存入集合（此时查询实际执行完毕）
    SELECT col1, col2, col3  -- 替换成你的实际查询字段
    BULK COLLECT INTO V_RESULTS 
    FROM YOUR_TARGET_TABLE 
    WHERE your_condition = P1;  -- 替换成你的业务逻辑条件
    
    -- 记录查询结束时间
    V_TS_END := SYSTIMESTAMP;
    
    -- 插入日志表，此时的结束时间是查询真正完成的时间
    INSERT INTO LOG_TABLE(ID, STR_TIME, END_TIME, PROC_NAME) 
    VALUES (1, V_TS_START, V_TS_END, 'YOUR_PROC_NAME');
    
    -- 打开游标，指向内存中的集合数据返回给调用方
    OPEN P_REF_CUR FOR 
        SELECT * FROM TABLE(V_RESULTS);
END;
/

方案1的优缺点： #

• 优点：无需额外数据库对象，实现简单，能精准记录查询执行的起止时间
• 缺点：如果结果集过大，会占用较多会话内存，可能引发内存不足的问题

方案2：用临时表暂存结果（适合大数据集） #

如果你的查询结果集很大，用集合会有内存压力，那就可以用会话级临时表来暂存数据，同样能在存储过程内部完成查询执行，准确记录时间。

示例代码：

-- 先创建会话级临时表（只需创建一次）
CREATE GLOBAL TEMPORARY TABLE TMP_PROC_RESULTS (
    col1 NUMBER,
    col2 VARCHAR2(100),
    col3 DATE
    -- 替换成你的查询结果字段
) ON COMMIT PRESERVE ROWS;

-- 存储过程代码
CREATE OR REPLACE Procedure YOUR_PROC_NAME(P1 IN NUMBER, P_REF_CUR OUT SYS_REFCURSOR) IS 
    V_TS_START TIMESTAMP;
    V_TS_END TIMESTAMP;
BEGIN 
    V_TS_START := SYSTIMESTAMP;
    
    -- 先清空当前会话的临时表数据（可选，确保数据干净）
    DELETE FROM TMP_PROC_RESULTS;
    
    -- 执行查询并插入临时表（此时查询实际执行完毕）
    INSERT INTO TMP_PROC_RESULTS(col1, col2, col3)
    SELECT col1, col2, col3 
    FROM YOUR_TARGET_TABLE 
    WHERE your_condition = P1;
    
    V_TS_END := SYSTIMESTAMP;
    
    -- 插入日志
    INSERT INTO LOG_TABLE(ID, STR_TIME, END_TIME, PROC_NAME) 
    VALUES (1, V_TS_START, V_TS_END, 'YOUR_PROC_NAME');
    
    -- 打开游标指向临时表数据
    OPEN P_REF_CUR FOR 
        SELECT * FROM TMP_PROC_RESULTS;
END;
/

方案2的优缺点： #

• 优点：支持超大结果集，内存占用低
• 缺点：需要预先创建临时表，且要注意会话级数据的隔离性（不用担心，临时表的数据只属于当前会话）

额外说明 #

如果你的调用方是完全可控的（比如你自己写的Java程序），也可以把日志逻辑拆分到调用方：在调用存储过程前记录开始时间，等调用方完全fetch完游标所有数据后，再记录结束时间并插入日志。但这种方式的缺点是日志逻辑没有封装在存储过程内部，耦合了调用方代码。

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