如何将自定义数据写入Oracle过程的输出参数游标

dm7nw8vv 于 2023-03-01 发布在 Oracle

关注(0)|答案(1)|浏览(140)

我在for循环中执行更新操作，如果更新失败，我需要返回更新失败的id。
这是我的存储过程：

create or replace PROCEDURE  ROLESUPDATING(useResultIn IN ROLE_ARRAY,Output OUT SYS_REFCURSOR)
IS
BEGIN
  FOR i IN 1 .. useResultIn.count
  LOOP
  BEGIN
  UPDATE AUTHUSERS SET FIRSTNAME = useResultIn(i).role  where USERID = useResultIn(i).ROLEOD;
  EXCEPTION
  WHEN INVALID_NUMBER THEN
        open output for userResult(i).ROLEOD
  WHEN OTHERS THEN
        open output for userResult(i).ROLEOD
  END;
  END LOOP
;
END;

我试图执行它是给错误。你能建议我如何才能调用该过程从Sequelize和看到的身份证为那些更新失败？
过程正在按如下顺序调用：

let useResultIn=[{"ROLE":"manager","ROLECODE":"ADM","ROLEOD":1},{"ROLE":"admin","ROLECODE":"MNG","ROLEOD":2,}]
      let sequelquery = `BEGIN ROLESUPDATING(:useResultIn,:out);END;`;
          let users = await sequelize.query(sequelquery, {
            bind: {
              useResultIn: {type:"ROLE_ARRAY",val:useResultIn},
              out :{dir: oracledb.BIND_OUT, type:oracledb.CURSOR}
            }, type: sequelize.QueryTypes.RAW
          })
          var userIds = [];
          let row;
          while ((row = await users[0].getRow())) {
            userIds.push(row)
          }

oracle

来源：https://stackoverflow.com/questions/75545467/how-to-write-custom-data-into-a-cursor-which-is-out-parameter-of-an-oracle-proce

1条答案

按热度按时间

hkmswyz61#

不要使用REF CURSOR ...尝试简单的集合类型：

CREATE TYPE roleod_list as TABLE OF integer;

create or replace PROCEDURE  ROLESUPDATING(useResultIn IN ROLE_ARRAY,Output OUT roleod_list)
IS
  tab_bad_ids roleod_list := roleod_list();
BEGIN
  FOR i IN 1 .. useResultIn.count
  LOOP
    BEGIN
      UPDATE AUTHUSERS SET FIRSTNAME = useResultIn(i).role  where USERID = useResultIn(i).ROLEOD;
    EXCEPTION
      WHEN OTHERS THEN
        tab_bad_ids.EXTEND;
        tab_bad_ids(tab_bad_ids.LAST) := useResultIn(i).ROLEOD;
    END;
  END LOOP;
  
  Output := tab_bad_ids;
END;

然后调用程序可以接收该消息，并出于任何原因对其进行循环：

declare
  my_errors roleod_list;
begin
  ROLESUPDATING(useResultIn,my_errors);
  FOR i IN my_errors.FIRST ..my_errors.LAST
  LOOP
    dbms_output.put_line(my_errors(i));
  END LOOP;
end;

赞(0）回复(0）举报 2023-03-01

我来回答

如何将自定义数据写入Oracle过程的输出参数游标

1条答案

相关问题

热门标签

最新问答