SQL 在 Oracle 中调用另一个存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3415232/
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
Call a stored procedure with another in Oracle
提问by electricsheep
Does anyone know of a way, or even if its possible, to call a stored procedure from within another? If so, how would you do it?
有没有人知道从另一个内部调用存储过程的方法，或者即使有可能？如果是这样，你会怎么做？
Here is my test code:
这是我的测试代码：
SET SERVEROUTPUT ON;
DROP PROCEDURE test_sp_1;
DROP PROCEDURE test_sp;
CREATE PROCEDURE test_sp
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Test works');
END;
/
CREATE PROCEDURE test_sp_1
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Testing');
test_sp;
END;
/
CALL test_sp_1;
回答by Shannon Severance
Your stored procedures work as coded. The problem is with the last line, it is unable to invoke either of your stored procedures.
您的存储过程按编码工作。问题出在最后一行，它无法调用您的任何一个存储过程。
Three choices in SQL*Plus are: call, exec, and an anoymous PL/SQL block.
在SQL * Plus三种选择是：call，exec，和anoymous PL / SQL块。
callappears to be a SQL keyword, and is documented in the SQL Reference. http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHGThe syntax diagram indicates that parentesis are required, even when no arguments are passed to the call routine.
call似乎是一个 SQL 关键字，并记录在 SQL 参考中。http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHG语法图表明需要括号，即使没有参数传递给调用例程也是如此。
CALL test_sp_1();
An anonymous PL/SQL block is PL/SQL that is not inside a named procedure, function, trigger, etc. It can be used to call your procedure.
匿名 PL/SQL 块是不在命名过程、函数、触发器等内部的 PL/SQL。它可用于调用您的过程。
BEGIN
test_sp_1;
END;
/
Execis a SQL*Plus command that is a shortcut for the above anonymous block. EXEC <procedure_name>will be passed to the DB server as BEGIN <procedure_name>; END;
Exec是一个 SQL*Plus 命令，它是上述匿名块的快捷方式。EXEC <procedure_name>将作为BEGIN <procedure_name>; END;
Full example:
完整示例：
SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PROCEDURE test_sp
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Test works');
5 END;
6 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE test_sp_1
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Testing');
5 test_sp;
6 END;
7 /
Procedure created.
SQL> CALL test_sp_1();
Testing
Test works
Call completed.
SQL> exec test_sp_1
Testing
Test works
PL/SQL procedure successfully completed.
SQL> begin
2 test_sp_1;
3 end;
4 /
Testing
Test works
PL/SQL procedure successfully completed.
SQL>
回答by dcp
Sure, you just call it from within the SP, there's no special syntax.
当然，你只是从 SP 内部调用它，没有特殊的语法。
Ex:
前任：
PROCEDURE some_sp
AS
BEGIN
some_other_sp('parm1', 10, 20.42);
END;
If the procedure is in a different schema than the one the executing procedure is in, you need to prefix it with schema name.
如果该过程与执行过程所在的模式不同，则需要在其前面加上模式名称。
PROCEDURE some_sp
AS
BEGIN
other_schema.some_other_sp('parm1', 10, 20.42);
END;
回答by Rajesh Chamarthi
@Michael Lockwood - you don't need to use the keyword "CALL" anywhere. You just need to mention the procedure call directly.
@Michael Lockwood - 您无需在任何地方使用关键字“CALL”。您只需要直接提及过程调用。
That is
那是
Begin
proc1(input1, input2);
end;
/
instead of
代替
Begin
call proc1(input1, input2);
end;
/
回答by Dave Costa
To invoke the procedure from the SQLPlus command line, try one of these:
要从 SQLPlus 命令行调用该过程，请尝试以下操作之一：
CALL test_sp_1();
EXEC test_sp_1
回答by Nirav savla
Calling one procedure from another procedure:
从另一个过程调用一个过程：
One for a normal procedure:
一种用于正常程序：
CREATE OR REPLACE SP_1() AS
BEGIN
/* BODY */
END SP_1;
Calling procedure SP_1 from SP_2:
从 SP_2 调用过程 SP_1：
CREATE OR REPLACE SP_2() AS
BEGIN
/* CALL PROCEDURE SP_1 */
SP_1();
END SP_2;
Call a procedure with REFCURSOR or output cursor:
使用 REFCURSOR 或输出游标调用过程：
CREATE OR REPLACE SP_1
(
oCurSp1 OUT SYS_REFCURSOR
) AS
BEGIN
/*BODY */
END SP_1;
Call the procedure SP_1 which will return the REFCURSOR as an output parameter
调用过程 SP_1，它将返回 REFCURSOR 作为输出参数
CREATE OR REPLACE SP_2
(
oCurSp2 OUT SYS_REFCURSOR
) AS `enter code here`
BEGIN
/* CALL PROCEDURE SP_1 WITH REF CURSOR AS OUTPUT PARAMETER */
SP_1(oCurSp2);
END SP_2;
