如何将返回查询语句CLOB的PL/SQL函数结果转为游标数据

解决方案 #

静态游标无法直接使用动态生成的SQL语句，需改用**REF CURSOR（动态游标）**实现，具体步骤如下：

1. 替换静态游标定义，新增REF CURSOR类型变量和存储动态SQL的变量；
2. 调用函数获取CLOB格式的查询语句，若返回内容被单引号包裹，需先去除首尾单引号；
3. 使用OPEN ... FOR语句执行动态SQL并绑定到游标变量；
4. 遍历游标读取数据，赋值给记录类型后通过管道输出。

修改后的完整函数代码 #

FUNCTION fixMobOtherDayWeekMonth_pipe(
    p_display_resolution VARCHAR2,
    p_technology_group   VARCHAR2
    )return fixMobOtherDayWeekMonth_table PIPELINED
    
    AS
     output   fixMobOtherDayWeekMonth;
        v1       VARCHAR2 (200); -- 对应Category列
        v2       NUMBER;         -- 对应<5 min列
        v3       NUMBER;         -- 对应<15 min列
        v4       NUMBER;         -- 对应<60 min列
        v5       NUMBER;         -- 对应>60 min列
        v6       NUMBER;         -- 对应KPI列
        -- 定义REF CURSOR类型
        TYPE ref_cursor IS REF CURSOR;
        cursor_k ref_cursor;
        -- 存储动态SQL的变量
        v_dynamic_sql CLOB;
    BEGIN
        -- 1. 获取动态SQL，去除首尾的单引号（若函数返回的SQL带单引号包裹）
        SELECT TRIM(BOTH '''' FROM performance_9chartsDay(p_display_resolution, p_technology_group))
        INTO v_dynamic_sql
        FROM DUAL;
        
        -- 2. 动态打开游标，执行获取到的SQL语句
        OPEN cursor_k FOR v_dynamic_sql;
        
        -- 3. 遍历游标，处理数据并管道输出
        LOOP
            FETCH cursor_k INTO v1, v2, v3, v4, v5, v6;
            EXIT WHEN cursor_k%NOTFOUND;
            
            -- 将游标数据赋值给output记录类型
            output.Category := v1;
            output.less_than_5m := v2;
            output.less_than_15m := v3;
            output.less_than_60m := v4;
            output.more_than_60m := v5;
            output.KPI := v6;
            
            -- 管道输出当前行数据
            PIPE ROW(output);
        END LOOP;
        
        -- 关闭游标
        CLOSE cursor_k;
        
        RETURN;
    END fixMobOtherDayWeekMonth_pipe;

注意事项 #

• 确保fixMobOtherDayWeekMonth记录类型的字段顺序、数据类型与动态SQL返回的列完全匹配，否则会抛出类型不匹配错误；
• 如果performance_9chartsDay返回的SQL未被单引号包裹，直接删除TRIM(BOTH '''' FROM ...)处理逻辑即可；
• 需保证动态SQL语法正确，若参数涉及用户输入，需额外做安全校验以避免SQL注入风险。

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