Oracle SQL 存储过程调用与执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20571647/
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
Oracle SQL Stored Procedures Call vs. Execute
提问by DanK
Problem
问题
I'm trying to understand the difference between Oracle SQL commands CALLand EXECUTE.
我试图了解 Oracle SQL 命令CALL和EXECUTE.
I've been using CALLto kick off stored procedures but in talking with another developer I found that he almost exclusively uses EXECUTE. I did some research online to see if I was doing something incorrectly but I'm not seeing the clear distinction between the two commands and people seem to use them interchangeably.
我一直在CALL使用EXECUTE. 我在网上做了一些研究，看看我是否做错了什么，但我没有看到这两个命令之间的明显区别，人们似乎可以互换使用它们。
Based on the documentation, they seem remarkably similar (at least in terms of interacting with stored procedures).
根据文档，它们看起来非常相似（至少在与存储过程的交互方面）。
- http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4008.htm
- http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12022.htm
- http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_app_dbms_aw026.htm
- http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4008.htm
- http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12022.htm
- http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_app_dbms_aw026.htm
It does look like CALLis a universal SQL command while EXECUTEseems to be proprietary so I would be inclined to use CALLover EXECUTEbut then again I don't know what that means in regards to performance.
它看起来确实CALL是一个通用的 SQL 命令，但EXECUTE似乎是专有的，所以我倾向于使用CALLoverEXECUTE但我不知道这对性能意味着什么。
Questions
问题
- Is one preferable over the other in terms of kicking off a stored procedure? Does it matter?
- If it does matter, what is a situation where either is appropriate?
- Are there any performance differences between the two? What's best practice?
- 在启动存储过程方面，一个比另一个更可取吗？有关系吗？
- 如果确实重要，那么在什么情况下两者都合适？
- 两者之间是否有任何性能差异？什么是最佳实践？
回答by DKroot
Both EXEC[ute] SP()and CALL SP()could be used in SQL*Plus to execute an SP. BTW, you can also use BEGIN SP(); END;
双方EXEC[ute] SP()并CALL SP()可以在SQL * Plus可用于执行SP。顺便说一句，你也可以使用BEGIN SP(); END;
But there are some differences.
但也有一些区别。
CALLis Oracle SQL and should work everywhere. Other DB clients that can talk to Oracle may or may not support SQL*Plus EXEC. Many do (for example, Oracle SQL Developer, SQLWorkbench/J), but some don't (Liquibase).The data types of the parameters passed by the
CALLstatement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN.EXECcould be used to execute not only an SP, but an arbitrary statement.If an SP does not have parameters, you can use
EXEC SP;syntax, butCALLrequires empty parentheses:CALL SP();
CALL是 Oracle SQL，应该可以在任何地方使用。可以与 Oracle 通信的其他数据库客户端可能支持也可能不支持 SQL*Plus EXEC。许多这样做（例如，Oracle SQL Developer、SQLWorkbench/J），但有些不这样做 (Liquibase)。CALL语句传递的参数的数据类型必须是SQL数据类型。它们不能是仅限 PL/SQL 的数据类型，例如 BOOLEAN。EXEC不仅可以用于执行 SP，还可以用于执行任意语句。如果 SP 没有参数，则可以使用
EXEC SP;语法，但CALL需要空括号：CALL SP();
回答by Jefferstone
If you are calling a proc that returns a sys_refcursor using Toad, there is a difference between CALL and EXEC.
如果您使用 Toad 调用返回 sys_refcursor 的 proc，则 CALL 和 EXEC 之间存在差异。
create procedure foo(i in number,o out sys_refcursor) as begin open o for select i from dual; end;
创建过程 foo(i in number,o out sys_refcursor) as begin open o for select i from dual; 结尾;
exec foo(1,:r); -- outputs 1 row
执行 foo(1,:r); -- 输出 1 行
call foo(1,:r); -- outputs 0 rows
调用 foo(1,:r); -- 输出 0 行
-- Note: when you prefix a parameter with a colon, Toad will prompt you for the type (which in this case is a cursor).
-- 注意：当您使用冒号作为参数前缀时，Toad 会提示您输入类型（在本例中为游标）。
回答by Hogan
EXECUTE takes a string as a parameter which allows you to "execute" dynamic sql. Execute is basically saying... with this input string run the SQL engine on the contents.
EXECUTE 将字符串作为参数，允许您“执行”动态 sql。执行基本上是说...用这个输入字符串在内容上运行 SQL 引擎。
CALL transfers control to a stored procedure or module.
CALL 将控制转移到存储过程或模块。
As you can see conceptually they are quite different. If you are just running a procedure however, in practice for that use case they are the same.
正如您在概念上所看到的，它们是完全不同的。但是，如果您只是运行一个过程，那么实际上对于该用例，它们是相同的。
I believe for clearest code unless you need execute you should use call.
我相信对于最清晰的代码，除非您需要执行，否则您应该使用 call。
