在Oracle中与另一个调用存储过程 [英] Call a stored procedure with another in Oracle
问题描述
有人知道一种方法,即使可能的话,也可以从另一个内部调用存储过程吗?如果是这样,您会怎么做?
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?
这是我的测试代码:
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;
推荐答案
您的存储过程按编码方式工作.问题出在最后一行,它无法调用任何一个存储过程.
Your stored procedures work as coded. The problem is with the last line, it is unable to invoke either of your stored procedures.
SQL * Plus中的三个选择是:call
,exec
和一个异常的PL/SQL块.
Three choices in SQL*Plus are: call
, exec
, and an anoymous PL/SQL block.
call
似乎是一个SQL关键字,并在《 SQL参考》中进行了说明. http://download.oracle.com/docs /cd/B19306_01/server.102/b14200/statements_4008.htm#BABDEHHG 语法图表明,即使没有参数传递给调用例程,也需要使用括号.
call
appears 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#BABDEHHG The syntax diagram indicates that parentesis are required, even when no arguments are passed to the call routine.
CALL test_sp_1();
匿名PL/SQL块是不在命名过程,函数,触发器等内部的PL/SQL.可用于调用您的过程.
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.
BEGIN
test_sp_1;
END;
/
Exec
是一个SQL * Plus命令,它是上述匿名块的快捷方式. EXEC <procedure_name>
将作为BEGIN <procedure_name>; END;
Exec
is 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;
完整示例:
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>
这篇关于在Oracle中与另一个调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!