Oracle存储过程中动态SQL写入SYS_REFCURSOR报错求助

先直接点明核心问题：你遇到的ORA-20000: ORU-10027错误，根源并非存储过程的动态SQL逻辑，而是调用代码里的游标循环写错了，导致无限输出同一行数据，撑爆了dbms_output的缓冲区。

错误原因拆解 #

你的调用块中，仅在进入循环前执行了一次fetch rc into row;，之后的while (rc%found)循环里没有再次执行fetch操作。这意味着游标始终处于“找到数据”的状态，会无限重复输出第一次fetch到的内容，直到dbms_output的缓冲区达到1MB上限，触发溢出错误。

另外，存储过程里还有一个隐性问题：初始SQL中的WHERE LINIA_PROD=NULL是无效的——在Oracle中判断空值必须用IS NULL，=NULL永远不会匹配任何行，这部分SQL只会返回空结果，平白增加不必要的UNION ALL开销。

分步修正方案 #

1. 修复调用块的循环逻辑 #

把fetch操作放到循环内部，确保每次循环都获取新的行，直到游标无数据可返回：

set serveroutput on
declare
type1 prodTypeList := prodTypeList(prodType('test1',1), prodType('test2', 20));
rc SYS_REFCURSOR;
row_val varchar2(200); -- 避免用row这种关键字作为变量名
BEGIN
MY_PROC(type1, rc);
LOOP
    fetch rc into row_val;
    EXIT WHEN rc%notfound; -- 无数据时直接退出循环
    dbms_output.put_line(row_val);
END LOOP;
close rc;
end;
/

2. 优化存储过程的动态SQL #

去掉无效的初始WHERE LINIA_PROD=NULL部分，直接从第一个元素开始构建SQL，避免多余的空结果拼接：

CREATE OR REPLACE PROCEDURE my_proc (prodLines IN prodTypeList ,rekom OUT SYS_REFCURSOR) IS
v_query VARCHAR2(4000);
BEGIN
    IF prodLines.COUNT > 0 THEN
        -- 从第一个元素开始构建基础SQL
        v_query := 'SELECT ID_REKOM_OFERTA FROM REKOM_CROSS_PROM WHERE LINIA_PROD=''' || prodLines(1).p_line || '''';
        
        -- 从第二个元素开始追加UNION ALL语句
        FOR i IN 2 .. prodLines.COUNT LOOP
            v_query := v_query || ' UNION ALL SELECT ID_REKOM_OFERTA FROM REKOM_CROSS_PROM WHERE LINIA_PROD=''' || prodLines(i).p_line || '''';
        END LOOP;
        
        OPEN rekom FOR v_query;
    ELSE
        -- 传入空列表时返回空游标
        OPEN rekom FOR SELECT NULL FROM DUAL WHERE 1=0;
    END IF;
END my_proc;
/

3. 进阶优化：用绑定变量替代字符串拼接 #

为了避免SQL注入风险，同时提升查询性能，建议用绑定变量构建动态SQL，而非直接拼接字符串：

CREATE OR REPLACE PROCEDURE my_proc (prodLines IN prodTypeList ,rekom OUT SYS_REFCURSOR) IS
v_query VARCHAR2(4000);
BEGIN
    IF prodLines.COUNT > 0 THEN
        v_query := 'SELECT ID_REKOM_OFERTA FROM REKOM_CROSS_PROM WHERE LINIA_PROD IN (';
        
        -- 构建绑定变量占位符
        FOR i IN 1 .. prodLines.COUNT LOOP
            IF i > 1 THEN
                v_query := v_query || ',';
            END IF;
            v_query := v_query || ':p' || i;
        END LOOP;
        v_query := v_query || ')';
        
        -- 通过USING子句传递绑定变量
        OPEN rekom FOR v_query USING prodLines(1).p_line, prodLines(2).p_line;
        
        -- 如果你用的是Oracle 12c+，可以直接用TABLE函数完全避免动态SQL：
        -- OPEN rekom FOR SELECT ID_REKOM_OFERTA FROM REKOM_CROSS_PROM WHERE LINIA_PROD IN (SELECT p_line FROM TABLE(prodLines));
    ELSE
        OPEN rekom FOR SELECT NULL FROM DUAL WHERE 1=0;
    END IF;
END my_proc;
/

如果你的Oracle版本是12c及以上，甚至可以完全抛弃动态SQL，直接用TABLE()函数把传入的集合转换为表进行查询，代码会更简洁安全。

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