Oracle PL/SQL如何根据Diagnostic Pack授权状态动态声明游标

针对静态游标声明会触发Oracle对DBA_HIST视图使用检测的问题，以下是三种可行的处理方案：

方案1：动态SQL+通用记录类型（推荐运行时动态判断场景） #

动态SQL仅在执行阶段解析，未授权时不会触及DBA_HIST相关对象，完美规避检测问题。同时定义通用记录类型适配两种场景的结果结构：

DECLARE
    l_diagnostic_pack_enabled NUMBER;
    -- 定义通用记录类型，匹配两种查询的输出结构
    TYPE t_metric_row IS RECORD (
        snap_id NUMBER,
        end_time DATE,
        cpu_per_s NUMBER
    );
    l_metric_row t_metric_row;
    l_cursor SYS_REFCURSOR;
BEGIN
    -- 检测Diagnostic Pack授权状态
    SELECT SUM(detected_usages)
    INTO l_diagnostic_pack_enabled
    FROM DBA_FEATURE_USAGE_STATISTICS 
    WHERE name IN ('ADDM','AWR Baseline','AWR Baseline Template','AWR Report','Automatic Workload Repository','Baseline Adaptive Thresholds','Baseline Static Computations','Diagnostic Pack','EM Performance Page');

    IF l_diagnostic_pack_enabled > 0 THEN
        -- 授权时使用DBA_HIST视图
        OPEN l_cursor FOR
            WITH main_metrics AS (
                SELECT snap_id, end_time, ROUND(average/100,2) cpu_per_s
                FROM (
                    SELECT snap_id, end_time, instance_number, metric_name, average, maxval
                    FROM DBA_HIST_sysmetric_summary
                    WHERE metric_name IN ('CPU Usage Per Sec')
                )
                GROUP BY snap_id, end_time
                ORDER BY snap_id, end_time
            )
            SELECT * FROM main_metrics;
    ELSE
        -- 未授权时使用非Diagnostic Pack依赖的视图（如V$视图）
        OPEN l_cursor FOR
            WITH main_metrics AS (
                SELECT snap_id, end_time, ROUND(average/100,2) cpu_per_s
                FROM (
                    SELECT snap_id, end_time, instance_number, metric_name, average, maxval
                    FROM V$SYSMETRIC_SUMMARY
                    WHERE metric_name IN ('CPU Usage Per Sec')
                )
                GROUP BY snap_id, end_time
                ORDER BY snap_id, end_time
            )
            SELECT * FROM main_metrics;
    END IF;

    -- 游标数据处理逻辑
    LOOP
        FETCH l_cursor INTO l_metric_row;
        EXIT WHEN l_cursor%NOTFOUND;
        -- 替换为你的业务逻辑
        DBMS_OUTPUT.PUT_LINE('Snap ID: ' || l_metric_row.snap_id || ', CPU使用率/秒: ' || l_metric_row.cpu_per_s);
    END LOOP;
    CLOSE l_cursor;
EXCEPTION
    WHEN OTHERS THEN
        IF l_cursor%ISOPEN THEN
            CLOSE l_cursor;
        END IF;
        RAISE;
END;
/

方案2：条件编译（适合部署时固定授权状态场景） #

利用Oracle条件编译特性，在编译阶段剔除未使用的代码分支，未授权时编译后的代码完全不包含DBA_HIST相关声明：

1. 先设置编译参数（根据实际授权状态设置1或0）：

ALTER SESSION SET PLSQL_CCFLAGS = 'DIAGNOSTIC_PACK_ENABLED:1';

2. 编写带条件编译的PL/SQL代码：

DECLARE
    $IF $$DIAGNOSTIC_PACK_ENABLED = 1 $THEN
        -- 授权时的游标定义
        CURSOR main_metrics_cursor IS
            WITH main_metrics AS (
                SELECT snap_id, end_time, ROUND(average/100,2) cpu_per_s
                FROM (
                    SELECT snap_id, end_time, instance_number, metric_name, average, maxval
                    FROM DBA_HIST_sysmetric_summary
                    WHERE metric_name IN ('CPU Usage Per Sec')
                )
                GROUP BY snap_id, end_time
                ORDER BY snap_id, end_time
            )
            SELECT * FROM main_metrics;
        metric_row main_metrics_cursor%ROWTYPE;
    $ELSE
        -- 未授权时的游标定义
        CURSOR main_metrics_cursor IS
            WITH main_metrics AS (
                SELECT snap_id, end_time, ROUND(average/100,2) cpu_per_s
                FROM (
                    SELECT snap_id, end_time, instance_number, metric_name, average, maxval
                    FROM V$SYSMETRIC_SUMMARY
                    WHERE metric_name IN ('CPU Usage Per Sec')
                )
                GROUP BY snap_id, end_time
                ORDER BY snap_id, end_time
            )
            SELECT * FROM main_metrics;
        metric_row main_metrics_cursor%ROWTYPE;
    $END
BEGIN
    OPEN main_metrics_cursor;
    LOOP
        FETCH main_metrics_cursor INTO metric_row;
        EXIT WHEN main_metrics_cursor%NOTFOUND;
        -- 业务逻辑
        DBMS_OUTPUT.PUT_LINE('Snap ID: ' || metric_row.snap_id || ', CPU使用率/秒: ' || metric_row.cpu_per_s);
    END LOOP;
    CLOSE main_metrics_cursor;
END;
/

方案3：拆分程序单元（适合复杂业务逻辑场景） #

将授权与未授权逻辑拆分到独立存储过程中，主程序根据授权状态调用对应单元，未授权时完全不会加载DBA_HIST相关代码：

1. 创建授权版存储过程：

CREATE OR REPLACE PROCEDURE process_metrics_diag_pack IS
    CURSOR main_metrics_cursor IS
        WITH main_metrics AS (
            SELECT snap_id, end_time, ROUND(average/100,2) cpu_per_s
            FROM (
                SELECT snap_id, end_time, instance_number, metric_name, average, maxval
                FROM DBA_HIST_sysmetric_summary
                WHERE metric_name IN ('CPU Usage Per Sec')
            )
            GROUP BY snap_id, end_time
            ORDER BY snap_id, end_time
        )
        SELECT * FROM main_metrics;
    metric_row main_metrics_cursor%ROWTYPE;
BEGIN
    OPEN main_metrics_cursor;
    LOOP
        FETCH main_metrics_cursor INTO metric_row;
        EXIT WHEN main_metrics_cursor%NOTFOUND;
        -- 业务逻辑
        DBMS_OUTPUT.PUT_LINE('Snap ID: ' || metric_row.snap_id || ', CPU使用率/秒: ' || metric_row.cpu_per_s);
    END LOOP;
    CLOSE main_metrics_cursor;
END;
/

2. 创建未授权版存储过程：

CREATE OR REPLACE PROCEDURE process_metrics_simple IS
    CURSOR main_metrics_cursor IS
        WITH main_metrics AS (
            SELECT snap_id, end_time, ROUND(average/100,2) cpu_per_s
            FROM (
                SELECT snap_id, end_time, instance_number, metric_name, average, maxval
                FROM V$SYSMETRIC_SUMMARY
                WHERE metric_name IN ('CPU Usage Per Sec')
            )
            GROUP BY snap_id, end_time
            ORDER BY snap_id, end_time
        )
        SELECT * FROM main_metrics;
    metric_row main_metrics_cursor%ROWTYPE;
BEGIN
    OPEN main_metrics_cursor;
    LOOP
        FETCH main_metrics_cursor INTO metric_row;
        EXIT WHEN main_metrics_cursor%NOTFOUND;
        -- 业务逻辑
        DBMS_OUTPUT.PUT_LINE('Snap ID: ' || metric_row.snap_id || ', CPU使用率/秒: ' || metric_row.cpu_per_s);
    END LOOP;
    CLOSE main_metrics_cursor;
END;
/

3. 主程序调用逻辑：

DECLARE
    l_diagnostic_pack_enabled NUMBER;
BEGIN
    SELECT SUM(detected_usages)
    INTO l_diagnostic_pack_enabled
    FROM DBA_FEATURE_USAGE_STATISTICS 
    WHERE name IN ('ADDM','AWR Baseline','AWR Baseline Template','AWR Report','Automatic Workload Repository','Baseline Adaptive Thresholds','Baseline Static Computations','Diagnostic Pack','EM Performance Page');

    IF l_diagnostic_pack_enabled > 0 THEN
        process_metrics_diag_pack;
    ELSE
        process_metrics_simple;
    END IF;
END;
/

注意事项 #

• 原问题中提供的“简化版游标”仍依赖DBA_HIST_sysmetric_summary（属于Diagnostic Pack），未授权时必须替换为非依赖对象（如V$SYSMETRIC_SUMMARY），否则仍会触发检测。
• 动态SQL场景需注意绑定变量使用，避免SQL注入风险。

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