使用IN和OUT参数在Oracle中调用存储过程

时间:2021-12-06 16:38:58

I have this procedure:

我有这个程序:

CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
AS BEGIN
SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
END;

So when I run it like this it returns absolutely nothing:

所以,当我像这样运行它时,它绝对没有返回:

DECLARE
    amount NUMBER;
BEGIN
    PROC1(1000001, amount);
    dbms_output.put_line(amount);
END;

BTW I use DreamCoder for Oracle. Is there a problem with the procedure itself or with the way I call it? There is an entry in the INVOICE table with INVOICE_NR equal to 1000001.

顺便说一句,我使用DreamCoder for Oracle。程序本身或我称之为程序有问题吗? INVOICE表中有一个条目,INVOICE_NR等于1000001。

3 个解决方案

#1


35  

If you set the server output in ON mode before the entire code, it works, otherwise put_line() will not work. Try it!

如果在整个代码之前将服务器输出设置为ON模式,则它可以正常工作,否则put_line()将不起作用。尝试一下!

The code is,

代码是,

set serveroutput on;
CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
AS BEGIN
SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
END;

And then call the function as it is:

然后按原样调用该函数:

DECLARE
amount NUMBER;
BEGIN
PROC1(1000001, amount);
dbms_output.put_line(amount);
END;

#2


3  

I had the same problem.I used a trigger , and in that trigger I called a procedure which compute some values in 2 OUT variables .When I tried to print the result in trigger body ,nothing on screen, but then I solved this problem making 2 local variables in function , compute what I need with them and finally , copy those variables in your OUT procedure variables. I hope ,it'll be usefull , succes!

我有同样的问题。我使用了一个触发器,在那个触发器中我调用了一个程序来计算2个OUT变量中的一些值。当我试图在触发器体中打印结果时,屏幕上什么都没有,但后来我解决了这个问题函数中有2个局部变量,用它们计算我需要的东西,最后在OUT过程变量中复制这些变量。我希望,这将是有用的,成功的!

#3


2  

Go to Menu Tool -> SQL Output, Run the PL/SQL statement, the output will show on SQL Output panel.

转到菜单工具 - > SQL输出,运行PL / SQL语句,输出将显示在SQL输出面板上。

#1


35  

If you set the server output in ON mode before the entire code, it works, otherwise put_line() will not work. Try it!

如果在整个代码之前将服务器输出设置为ON模式,则它可以正常工作,否则put_line()将不起作用。尝试一下!

The code is,

代码是,

set serveroutput on;
CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
AS BEGIN
SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
END;

And then call the function as it is:

然后按原样调用该函数:

DECLARE
amount NUMBER;
BEGIN
PROC1(1000001, amount);
dbms_output.put_line(amount);
END;

#2


3  

I had the same problem.I used a trigger , and in that trigger I called a procedure which compute some values in 2 OUT variables .When I tried to print the result in trigger body ,nothing on screen, but then I solved this problem making 2 local variables in function , compute what I need with them and finally , copy those variables in your OUT procedure variables. I hope ,it'll be usefull , succes!

我有同样的问题。我使用了一个触发器,在那个触发器中我调用了一个程序来计算2个OUT变量中的一些值。当我试图在触发器体中打印结果时,屏幕上什么都没有,但后来我解决了这个问题函数中有2个局部变量,用它们计算我需要的东西,最后在OUT过程变量中复制这些变量。我希望,这将是有用的,成功的!

#3


2  

Go to Menu Tool -> SQL Output, Run the PL/SQL statement, the output will show on SQL Output panel.

转到菜单工具 - > SQL输出,运行PL / SQL语句,输出将显示在SQL输出面板上。