oracle 从另一个过程调用一个过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32936835/
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
Calling one procedure from another procedure
提问by Shweta Puchu
The code below is saved in a file named proc1.sql
下面的代码保存在名为 proc1.sql 的文件中
DECLARE
B VARCHAR2(25);
C NUMBER;
PROCEDURE Get_manager_detailS(NO IN NUMBER,NAME OUT VARCHAR2,SAL1 OUT NUMBER)
IS
BEGIN
SELECT ENAME, SAL
INTO NAME, SAL1
FROM EMP
WHERE EMPNO = NO;
END;
BEGIN
Get_manager_detailS(7900,B,C);
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE(C);
END;
/
This procedure is stored in another file proc3.sql
这个程序存储在另一个文件proc3.sql中
PROCEDURE Test_Procedure()
IS
BEGIN
b varchar2(25);
c number;
DBMS_OUTPUT.PUT_LINE('CALLING');
Get_manager_details(7900,b,c);
END;
When I am running it in sqlplus, it is showing an error
当我在 sqlplus 中运行它时，它显示一个错误
SP2-0734 UNKNOWN COMMAND BEGINING PROCEDURE.. REST OF THE LINES IGNORED. SP2-0042 UNKNOWN COMMAND" IS "..REST OF THE LINE IGNORED.
SP2-0734 未知命令开始程序.. 其余行被忽略。SP2-0042 未知命令“是”..其余行被忽略。
回答by brenners1302
Creating a PROCEDURE/FUNCTION vs. ANONYMOUS BLOCK
创建程序/函数与匿名块
Stored PROCEDURES/FUNCTIONS always starts with CREATE OR REPLACE ...and ends with END;
CREATE OR REPLACE serves as the implicit declare for stored functions and procedures, thus you dont have to write DECLARE anymore inside the block
An anonymous block starts with DECLAREand ends with END;
存储的过程/函数总是以CREATE OR REPLACE ...开头，以 END 结尾；
CREATE OR REPLACE 作为存储函数和过程的隐式声明，因此您不必再在块内编写 DECLARE
匿名块以DECLARE开始，以END 结束；
As for the code/block of codes saved in proc1.sql.
至于proc1.sql中保存的代码/代码块。
- Your declaration is misplaced.You should place it after the end of the procedure
- Start the procedure with CREATE OR REPLACE PROCEDURE
- 您的声明放错了位置。您应该在程序结束后放置
- 使用 CREATE OR REPLACE PROCEDURE 开始该过程
Try This Block:
试试这个块：
-- DECLARE
-- B VARCHAR2(25);
-- C NUMBER;
CREATE OR REPLACE PROCEDURE Get_manager_detailS(NO IN NUMBER,
NAME OUT VARCHAR2,
SAL1 OUT NUMBER)
IS
BEGIN
SELECT ENAME, SAL
INTO NAME, SAL1
FROM EMP
WHERE EMPNO = NO;
END; -- end of procedure
/
DECLARE -- start of anonymous block
B VARCHAR2(25);
C NUMBER;
BEGIN
Get_manager_detailS(7900,B,C);
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE(C);
END;
As for the procedure that will call the get_manager_details procedure.Its will be just the same as the anonymous block, the only difference will be is that it is stored
至于调用get_manager_details过程的过程，和匿名块是一样的，唯一的区别是存储
Base from what have you done already
从你已经做了什么的基础上
If you will not declare parameters in your procedure, parenthesis are not necessary so remove it.
If you dont have output parameters that will catch the result of your procedure, you can use dbms_output.put_line as you have used in the anonymous block above
variable declarations should be done after the IS keyword and before BEGIN statements, because as I have noted above CREATE OR REPLACE ... ISis the implicit declare for the stored functions and procedures
如果您不会在您的过程中声明参数，则不需要括号，因此将其删除。
如果您没有可以捕获过程结果的输出参数，您可以使用 dbms_output.put_line，就像您在上面的匿名块中使用的那样
变量声明应该在 IS 关键字之后和 BEGIN 语句之前完成，因为正如我上面提到的CREATE OR REPLACE ... IS是存储函数和过程的隐式声明
TRY THIS:
尝试这个：
CREATE OR REPLACE PROCEDURE Test_Procedure
IS -- always start with CREATE OR REPLACE
b varchar2(25);
c number;
BEGIN
-- b varchar2(25); misplaced declarations
-- c number;
DBMS_OUTPUT.PUT_LINE('CALLING');
Get_manager_details(7900,b,c);
DBMS_OUTPUT.PUT_LINE(B); -- displays the results b
DBMS_OUTPUT.PUT_LINE(C); -- and c
END;
Sorry for the long post.
抱歉，帖子太长了。
HOPE THIS HELPS.
希望这可以帮助。
CHEERS
干杯
回答by T.S.
Your first block is anonymous block in which you declare procedure - you can call procedure Get_manager_detailswithin anonymous block only. You can't call Get_manager_detailsfrom Test_Procedurebecause there is no such procedure. You need to create your procedure Get_manager_detailsfirst
您的第一个块是在其中声明过程的匿名块 - 您只能Get_manager_details在匿名块中调用过程。你不能打电话Get_manager_details，Test_Procedure因为没有这样的程序。您需要创建你的程序Get_manager_details第一
Create or replace procedure Get_manager_details ....
Then you can run
然后你可以运行
Create or replace procedure Test_Procedure ....
Or it will not compile.
否则将无法编译。
回答by sudip das
If you are trying to call the procedure get_manager_details inside test_procedure then you first need to create the test procedure. Add create or replace procedure test_procedure . Then after creating the test_procedure you can execute it in an anonymous block which will call the get_manager_details procedure.
如果您尝试在 test_procedure 中调用过程 get_manager_details，那么首先需要创建测试过程。添加创建或替换过程 test_procedure 。然后在创建 test_procedure 之后，您可以在一个匿名块中执行它，该块将调用 get_manager_details 过程。
