SQL 在 Oracle 中使用 IN 和 OUT 参数调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10024771/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling a stored procedure in Oracle with IN and OUT parameters
提问by RegedUser00x
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 的条目。
回答by Keerthi
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;
回答by Elneny
I had the same problem. I used a trigger and in that trigger I called a procedure which computed some values into 2 OUT variables. When I tried to print the result in the trigger body, nothing showed on screen. But then I solved this problem by making 2 local variables in a function, computed what I need with them and finally, copied those variables in your OUT procedure variables. I hope it'll be useful and successful!
我有同样的问题。我使用了一个触发器，并在该触发器中调用了一个过程，该过程将一些值计算为 2 个 OUT 变量。当我尝试在触发器主体中打印结果时，屏幕上没有显示任何内容。但是后来我通过在函数中创建 2 个局部变量来解决这个问题，用它们计算我需要的东西，最后，将这些变量复制到你的 OUT 过程变量中。我希望它会是有用的和成功的！
回答by Alvin
Go to Menu Tool -> SQL Output, Run the PL/SQL statement, the output will show on SQL Output panel.
转到菜单工具 -> SQL 输出，运行 PL/SQL 语句，输出将显示在 SQL 输出面板上。
