如何从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 */
相关文章