背景:兴之所致,突然想了解oracle中exec与call的区别。
资料挖掘:
- CALL is 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 CALL statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN.
- EXEC could 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, but CALL requires empty parentheses: CALL SP();
分析:
1. 所有工具都支持call的调用,而exec 不是所有工具都支持。
2. call procedure(parameter1) 里的面参数必须是标准的sql语句数据类型,否则报错,如boolean型就不支持call的调用。但exec可以,因为exec 解释后,就是一个匿名plsql 块 begin .... end;
3. 调用方式不一样。当存储过程没有参数时,exec proc ; 不用带括号,call 就必须带上了,但标准做法,还是带上会好一点。
4. exec 不仅可以执行存储过程,还可以执行其它语句?