如何从oracle中的存储过程中提取输出变量? [英] How to extract the out variables from a stored procedure in oracle?

问题描述

我有存储过程，其中有很多输出变量.所以我像这样调用存储过程:

I have stored procedure and there are many out variables in it. So I am calling the stored procedure like this:

export const infoHR3 = async () => {
  try {
    const sql =
      ` 
      Declare
        ln_order_qty  NUMBER;
        ln_in_proc_qty_hr  NUMBER;
        ln_procd_hr_mass  NUMBER;
        ln_in_proc_qty  NUMBER;
        ln_wip  NUMBER;
        ln_qa  NUMBER;
        ln_packing  NUMBER;
        ln_dispatchable_qty  NUMBER;
        ln_despatched_qty  NUMBER;
        ln_finished_qty  NUMBER;
        ln_balance_qty  NUMBER;
        ln_bal_disp_qty  NUMBER;
      BEGIN
        CRMDBA.C1DVX007(
          '9514593782',
          '1',
          1,
          ln_order_qty,
          ln_in_proc_qty_hr,
          ln_procd_hr_mass,
          ln_in_proc_qty,
          ln_wip,
          ln_qa,
          ln_packing,
          ln_dispatchable_qty,
          ln_despatched_qty,
          ln_finished_qty,
          ln_balance_qty,
          ln_bal_disp_qty
        );
        dbms_output.put_line(ln_order_qty);
      END;  `;
    return await query(sql);
  } catch (error) {
    console.log(error);
    throw new Error.InternalServerError("Error!");
  }
};

这是模型:

function getinfoHR3Table() {
  return infoHR3();
}
    
export const ProcessModel = {
      getProcess,
      getReason,
      getinfoHR1Table,
      getinfoHR2Table,
      getinfoCR1Table,
      getinfoCR2Table,
      getinfoHR3Table
};

这是控制器:

export const getinfoHR3Table = async (req: Request, res: Response) => {
  try {
    const results: any = await ProcessModel.getinfoHR3Table();
    return res.status(200).json(results);
  } catch (error) {
    return res.status(400).json(error);
  }
};

问题是我在结果中得到了空白值.如何在调用存储过程时提取变量并返回它们??

The problem is I am getting blank value in the result. How do I extract out variables and return them when calling a stored procedure??

问题在于 DBMS_OUTPUT.PUT_LINE 只是在数据库中打印行.该过程不返回任何可在后端使用的值.然而，它为 outvariables 提供了值.

The problem with this is that DBMS_OUTPUT.PUT_LINE just prints line in the database. The procedure does not return any values which can be used in the backend. However it gives values to outvariables.

如何从过程中提取这些外部变量，以便在后端使用它们?

How can I extract these outvariables from the procedure so that I can use them in the backend?

推荐答案

/如果 ln_order_qty 是过程的输出变量，您可以将该变量的值捕获到另一个变量中并从那里开始使用它.下面是一个例子:-/

ln_order_qty_output := ln_order_qty;

/* 上面的变量 ln_order_qty_output 现在将包含来自过程的输出变量值，您可以将其用作后端 PLSQL 过程中的值 */

/* The above variable ln_order_qty_output will now contain the output variable value from the procedure and you can use it as a value in the backend PLSQL procedure */

    Declare
      ln_order_qty  NUMBER;
      ln_in_proc_qty_hr  NUMBER;
      ln_procd_hr_mass  NUMBER;
      ln_in_proc_qty  NUMBER;
      ln_wip  NUMBER;
      ln_qa  NUMBER;
      ln_packing  NUMBER;
      ln_dispatchable_qty  NUMBER;
      ln_despatched_qty  NUMBER ;
      ln_finished_qty  NUMBER;
      ln_balance_qty  NUMBER;
      ln_bal_disp_qty  NUMBER;
 BEGIN
    

CRMDBA.C1DVX007('9514593782','1',1,ln_order_qty,ln_in_proc_qty_hr,ln_procd_hr_mass,

CRMDBA.C1DVX007('9514593782','1',1,ln_order_qty,ln_in_proc_qty_hr,ln_procd_hr_mass,

 ln_in_proc_qty,ln_wip,ln_qa,ln_packing,ln_dispatchable_qty,ln_despatched_qty,
 ln_finished_qty,ln_balance_qty,ln_bal_disp_qty);
 ln_order_qty_output := ln_order_qty;    
 dbms_output.put_line(ln_order_qty);    
 dbms_output.put_line(ln_order_qty_output);    
END;     
  

/*dbms_output.put_line上显示的两个值应该是一样的您现在可以使用变量 ln_order_qty_output 的值后端*/

/*Both the values displayed on the dbms_output.put_line should be the same You can now use the value of the variable ln_order_qty_output in the backend */

这篇关于如何从oracle中的存储过程中提取输出变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！