SQL 在 Oracle 中执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6154425/
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
Executing a Stored Procedure in Oracle
提问by kalls
I have a stored procedure, on Toad for Oracle I am calling the procedure using
我有一个存储过程，在 Toad for Oracle 上我正在使用
SELECT FROM PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7)
FROM DUAL
I have 3 output parameter on this procedure as well I am getting an
我在这个过程中也有 3 个输出参数，我得到了一个
ORA-00904: PKGName.ProcedureName : Invalid Identifier
ORA-00904: PKGName.ProcedureName : 无效标识符
Do have to mention the output parameter on the procedure call as well? If yes how can I use it?
是否也必须在过程调用中提及输出参数？如果是，我该如何使用它？
回答by DCookie
You cannot use a procedure in a SELECT statement. Functions yes (with appropriate return types), procedures no. Items in a SELECT list must be expressions, which must resolve to a value. A procedure does not meet this criteria.
不能在 SELECT 语句中使用过程。函数是（具有适当的返回类型），过程不是。SELECT 列表中的项目必须是表达式，它必须解析为一个值。程序不符合此标准。
And yes, you do need to mention the output variables in your parameter list. The procedure is going to set those parameters to some values, there needs to be a output parameter specified for each to receive them. @schurik shows you how it is usually done in PL/SQL. @Datajam is close to how you'd do it in SQL*Plus, but leaves out the output parameters:
是的，您确实需要在参数列表中提及输出变量。该过程将把这些参数设置为一些值，需要为每个参数指定一个输出参数来接收它们。@schurik 向您展示了它通常是如何在 PL/SQL 中完成的。@Datajam 与您在 SQL*Plus 中的做法很接近，但省略了输出参数：
SQL> var num_var number
SQL> var txt_var varchar2(15)
SQL> var txt_var2 varchar2(20)
SQL> exec PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7, :num_var, :txt_var, :txt_var2);
PL/SQL procedure successfully completed
num_var
---------------
42
txt_var
-----------------
some text
txt_var2
-------------------
some other text
SQL>
回答by schurik
declare
-- declare variables to keep output values
output_par_1 varchar2(100);
output_par_2 number(10);
...
begin
PKGName.ProcedureName(1,'10/10/2010','10/23/2010',output_par_1,output_par_2);
-- display output values
dbms_output.put_line('output_par_1: ' || output_par_1);
dbms_output.put_line('output_par_2: ' || output_par_2);
end;
/
回答by Anthony Accioly
If you want to be able to call procedures from select, wrap it with a function or table function. See here for more details: http://technology.amis.nl/blog/1017/calling-stored-procedures-using-plain-sql-for-when-sql-is-allowed-but-calls-to-stored-procedures-are-not(heck the title is almost an article hehehe).
如果您希望能够从 select 中调用过程，请使用函数或表函数将其包装起来。有关更多详细信息，请参见此处：http: //technology.amis.nl/blog/1017/calling-stored-procedures-using-plain-sql-for-when-sql-is-allowed-but-calls-to-stored-程序都不是（见鬼的标题几乎是一篇文章呵呵）。
回答by maple_shaft
Yes, you must provide all arguments. Declare a variable of the appropriate type and pass it as an output argument.
是的，您必须提供所有参数。声明适当类型的变量并将其作为输出参数传递。
回答by Datajam
You shouldn't really call a procedure using a SELECT statement (and even if you did, the call would be before the FROM part).
您不应该真正使用 SELECT 语句调用过程（即使您这样做了，调用也会在 FROM 部分之前）。
Instead, use a SQL*Plus prompt (I think Toad has a built-in SQL*Plus interface):
相反，使用 SQL*Plus 提示（我认为 Toad 有一个内置的 SQL*Plus 接口）：
exec PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7);
回答by HAL 9000
The code in the question is syntactically wrong, it should be
问题中的代码在语法上是错误的，应该是
SELECT PKGName.ProcedureName(1,'10/10/2010','10/23/2010',7,7) FROM DUAL
However, this would only work for functions. But as it's obviously working in Toad I assume the poster actually did have a function. I also made the assumption that using SQL was a prerequisite.
但是，这仅适用于函数。但由于它显然在 Toad 中工作，我认为海报确实具有功能。我还假设使用 SQL 是先决条件。
If the point of the question was how to make use of multiple output parameters - try creating a user defined type. Maybe the question should then be renamed to "Calling a procedure with output parameters from SQL in Oracle".
如果问题的重点是如何使用多个输出参数 - 尝试创建用户定义的类型。也许应该将问题重命名为“使用 Oracle 中的 SQL 的输出参数调用过程”。
Otherwise simple wrapper function without output parameters would do the job.
否则没有输出参数的简单包装函数就可以完成这项工作。
