如何测试以VARRAY为输入、SYS_REFCURSOR为输出的Oracle存储过程？

问题根源 #

你的存储过程存在两个关键问题：

1. 游标被重复覆盖：在FOR循环中每次执行OPEN c_configurations FOR ...会关闭之前打开的游标，最终输出游标仅指向最后一次循环的查询结果。如果最后一个configcategory无匹配记录，游标就为空，导致测试脚本无数据输出。
2. 无效元素遍历：测试脚本中extend(10)会创建10个元素，但仅赋值前2个，循环会遍历所有10个元素，其中8个为NULL，导致多次查询无意义的NULL类别。

修复后的存储过程 #

修改存储过程，一次性查询所有传入的有效configcategory，避免重复打开游标：

CREATE OR REPLACE TYPE configcategoryarr IS
    VARRAY(256) OF VARCHAR2(256);
/

CREATE OR REPLACE PROCEDURE get_configurations (
    t_configcategory  IN   configcategoryarr,
    c_configurations  OUT  SYS_REFCURSOR
) IS
BEGIN
    IF t_configcategory.count > 0 THEN
        -- 一次性查询所有非空的configcategory记录
        OPEN c_configurations FOR 
            SELECT
                configcategory,
                configid,
                configlabel,
                configvalue,
                configenabled
            FROM
                configurations
            WHERE
                configcategory MEMBER OF t_configcategory
                -- 过滤VARRAY中的空元素
                AND configcategory IS NOT NULL;
    ELSE
        -- 传入空数组时返回空游标
        OPEN c_configurations FOR SELECT * FROM dual WHERE 1=0;
    END IF;
END get_configurations;
/

修正后的测试脚本 #

简化VARRAY初始化，避免无效元素，同时正确读取游标数据：

SET SERVEROUTPUT ON;

DECLARE
    t_cca             configcategoryarr;
    l_cursor          SYS_REFCURSOR;
    l_configcategory  configurations.configcategory%TYPE;
    l_configid        configurations.configid%TYPE;
    l_configlabel     configurations.configlabel%TYPE;
    l_configvalue     configurations.configvalue%TYPE;
    l_configenabled   configurations.configenabled%TYPE;
BEGIN
    -- 直接初始化需要的元素，无需额外extend
    t_cca := configcategoryarr('Department', 'OU');
    get_configurations(t_configcategory => t_cca, c_configurations => l_cursor);
    
    -- 遍历游标输出所有记录
    LOOP
        FETCH l_cursor INTO
            l_configcategory,
            l_configid,
            l_configlabel,
            l_configvalue,
            l_configenabled;
        EXIT WHEN l_cursor%notfound;
        
        dbms_output.put_line(
            l_configcategory || '_' ||
            l_configid || '_' ||
            l_configlabel || '_' ||
            l_configvalue || '_' ||
            l_configenabled
        );
    END LOOP;

    CLOSE l_cursor;
END;
/

针对NodeJS调用的说明 #

修正后的存储过程返回一个包含所有匹配记录的单一游标，NodeJS应用可以通过Oracle驱动（如oracledb）直接读取游标中的所有数据，无需额外处理。驱动会自动遍历游标并返回结果集，符合批量获取数据的需求。

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