如何用Node.js的Sequelize ORM调用带输出参数的Oracle存储过程？

问题重现 #

Oracle存储过程（存在语法错误） #

create or relace PROCEDURE sp_user( userStatusCondition IN varchar2, useResult OUT SYS_REFCURSOR)
IS
BEGIN
OPEN useResult FOR SELECT a.USERID,
a.FIRSTNAME,
a.LASTNAME,
b.FIRSTNAME as reportingUserFirstName, 
FROM USERS a
left join employee b on  a.REPORUSERID=b.EXTERNALUSERID 
where  a.STATUS = userStatusCondition
ORDER BY a.CREATEDAT asc;
END ;

Node.js调用代码（参数传递错误） #

let userStatusCondition="ACT"     
sql = 'BEGIN  sp_user(:userStatusCondition,:result); END;';
let options={}
const data = {userStatusCondition:userStatusCondition,result:{dir: oracledb.BIND_OUT, type: oracledb.CURSOR}};
let output = await sequelize.query(sql, data, options);

错误信息 #

ORA-01008: not all variables bound

错误原因 #

1. 存储过程语法错误：
   • create or relace拼写错误，应为create or replace
   • SELECT语句中b.FIRSTNAME as reportingUserFirstName,末尾多了逗号，导致SQL语法不合法
2. Sequelize调用参数格式错误：
   Sequelize的query方法第二个参数是配置对象，绑定变量必须放在options.bind中，而非单独作为第二个参数传递。

修正方案 #

步骤1：修复Oracle存储过程语法 #

create or replace PROCEDURE sp_user( userStatusCondition IN varchar2, useResult OUT SYS_REFCURSOR)
IS
BEGIN
OPEN useResult FOR SELECT a.USERID,
a.FIRSTNAME,
a.LASTNAME,
b.FIRSTNAME as reportingUserFirstName
FROM USERS a
left join employee b on a.REPORUSERID = b.EXTERNALUSERID 
where a.STATUS = userStatusCondition
ORDER BY a.CREATEDAT asc;
END ;

步骤2：修正Sequelize调用代码 #

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

async function queryUserList() {
  const userStatusCondition = "ACT";     
  const sql = 'BEGIN sp_user(:userStatusCondition, :result); END;';
  
  const options = {
    bind: {
      userStatusCondition: userStatusCondition,
      result: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR }
    },
    type: sequelize.QueryTypes.RAW
  };

  const [output] = await sequelize.query(sql, options);
  
  // 解析输出游标中的数据
  const resultSet = output.result;
  let userList;
  try {
    userList = await resultSet.getRows(); // 获取所有行数据
  } finally {
    await resultSet.close(); // 关闭游标释放资源
  }
  
  return userList;
}

关键说明 #

• 绑定变量必须放在options.bind中，这是Sequelize识别变量绑定的标准方式
• 配置type: sequelize.QueryTypes.RAW确保返回原始Oracle结果对象，便于解析游标
• 游标使用完毕后必须主动关闭，避免数据库连接资源泄漏

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