如何向Oracle存储过程输出游标写入失败ID并通过Sequelize调用

一、修正后的Oracle存储过程 #

原存储过程问题点 #

• 异常分支变量名拼写错误（userResult应为useResultIn）
• 打开游标语法非法，不能直接输出变量，需用合法查询语句
• 重复打开游标会触发报错，应先收集所有失败ID再统一返回
• 存在分号缺失等语法细节错误

步骤1：确保自定义类型已创建 #

如果ROLE_ARRAY类型未定义，先执行以下语句创建：

CREATE OR REPLACE TYPE ROLE_RECORD AS OBJECT (
    ROLE VARCHAR2(100),
    ROLECODE VARCHAR2(50),
    ROLEOD NUMBER
);
/
CREATE OR REPLACE TYPE ROLE_ARRAY AS TABLE OF ROLE_RECORD;
/

步骤2：修正后的存储过程 #

CREATE OR REPLACE PROCEDURE ROLESUPDATING(
    useResultIn IN ROLE_ARRAY,
    Output OUT SYS_REFCURSOR
)
IS
    -- 定义集合存储失败的ROLEOD
    v_failed_ids SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
BEGIN
    FOR i IN 1 .. useResultIn.COUNT LOOP
        BEGIN
            UPDATE AUTHUSERS 
            SET FIRSTNAME = useResultIn(i).ROLE 
            WHERE USERID = useResultIn(i).ROLEOD;
            
            -- 可选逻辑：如果更新未匹配到任何行，也视为失败
            IF SQL%ROWCOUNT = 0 THEN
                v_failed_ids.EXTEND;
                v_failed_ids(v_failed_ids.COUNT) := useResultIn(i).ROLEOD;
            END IF;
        EXCEPTION
            WHEN INVALID_NUMBER THEN
                v_failed_ids.EXTEND;
                v_failed_ids(v_failed_ids.COUNT) := useResultIn(i).ROLEOD;
            WHEN OTHERS THEN
                v_failed_ids.EXTEND;
                v_failed_ids(v_failed_ids.COUNT) := useResultIn(i).ROLEOD;
        END;
    END LOOP;
    
    -- 统一打开游标返回所有失败ID
    OPEN Output FOR
        SELECT COLUMN_VALUE AS FAILED_ROLEOD FROM TABLE(v_failed_ids);
END;
/

核心修改说明 #

• 新增v_failed_ids集合变量，集中收集所有更新失败的ROLEOD
• 异常分支不再直接打开游标，而是将失败ID加入集合
• 循环结束后通过TABLE()函数将集合转为查询结果，统一返回游标
• 可选逻辑：若更新未匹配到行（无数据变更），也将ID标记为失败，可根据业务需求删除该部分

二、Sequelize正确调用方式 #

修正后的调用代码 #

const oracledb = require('oracledb');
// 假设sequelize已完成初始化配置

async function updateRoles() {
    let useResultIn = [
        {"ROLE": "manager", "ROLECODE": "ADM", "ROLEOD": 1},
        {"ROLE": "admin", "ROLECODE": "MNG", "ROLEOD": 2}
    ];

    // 转换数组为Oracle对象类型要求的结构
    const bindArray = useResultIn.map(item => ({
        ROLE: item.ROLE,
        ROLECODE: item.ROLECODE,
        ROLEOD: item.ROLEOD
    }));

    const sequelquery = `BEGIN ROLESUPDATING(:useResultIn, :out); END;`;
    
    try {
        const [result] = await sequelize.query(sequelquery, {
            bind: {
                useResultIn: {
                    dir: oracledb.BIND_IN,
                    type: oracledb.ARRAY,
                    dbType: 'ROLE_ARRAY', // 对应Oracle中定义的数组类型名
                    val: bindArray
                },
                out: {
                    dir: oracledb.BIND_OUT,
                    type: oracledb.CURSOR
                }
            },
            type: sequelize.QueryTypes.RAW
        });

        const cursor = result.out;
        const failedIds = [];
        let row;

        // 逐行读取游标数据
        while ((row = await cursor.getRow())) {
            failedIds.push(row.FAILED_ROLEOD);
        }

        // 关闭游标释放资源
        await cursor.close();

        console.log('更新失败的ID列表:', failedIds);
        return failedIds;
    } catch (error) {
        console.error('存储过程调用失败:', error);
        throw error;
    }
}

// 执行调用
updateRoles();

关键修改说明 #

• 引入oracledb模块，确保绑定参数类型匹配Oracle要求
• 将输入数组转换为Oracle对象类型对应的结构
• 绑定useResultIn时指定dbType为Oracle中定义的ROLE_ARRAY类型
• 读取游标数据后手动关闭游标，避免数据库资源泄漏
• 明确获取游标返回的列名FAILED_ROLEOD（对应存储过程中的别名）

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