oracle 我们可以在 PL/SQL 中的函数内部调用过程吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9720376/
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
can we call procedure inside a function in PL/SQL?
提问by user1252398
we can call the function inside the procedure , but is it possible to call procedure inside the function ? I tried but I can't call procedure inside the function. could you tell me why we can not call the procedure inside the function?
我们可以在过程内部调用函数，但是可以在函数内部调用过程吗？我试过了，但我无法在函数内部调用过程。你能告诉我为什么我们不能在函数内部调用过程吗？
回答by APC
" I tried but I can't call procedure inside the function."
“我试过了，但我无法在函数内部调用过程。”
How did you try? What did you try? In what way did you fail?
你是怎么尝试的？你尝试了什么？你以什么方式失败了？
Because it is permitted to call procedure inside the function. So if it isn't working for you, then the cause is something wrong in your code. We cannot possibly diagnose that without you providing a lot more information than you currently have.
因为允许在函数内部调用过程。所以如果它对你不起作用，那么原因是你的代码有问题。如果您不提供比您目前拥有的信息多得多的信息，我们就无法做出诊断。
The two most likely reasons are:
两个最可能的原因是：
You have a syntax error in your code which is preventing it from compiling, or some other bug which is hurling a runtime exception.
The function might be in scope of the procedure but not vice versa.
Your procedure is doing something which is not allowed when we call a function in a query (such as issuing DML) and you are calling your function in a SELECT statement.
您的代码中存在语法错误，导致其无法编译，或其他一些引发运行时异常的错误。
函数可能在过程的范围内，但反之则不然。
当我们在查询中调用函数（例如发出 DML）并且您在 SELECT 语句中调用函数时，您的过程正在做一些不允许的事情。
回答by Bob Jarvis - Reinstate Monica
I'm going to take a guess here that you have the function declared first, with the procedure following, similar to:
我将在这里猜测您首先声明了该函数，其过程如下，类似于：
DECLARE
FUNCTION my_func RETURN NUMBER IS
BEGIN
RETURN 2;
END my_func;
PROCEDURE my_proc IS
BEGIN
DBMS_OUTPUT.PUT_LINE(my_func + 1);
END my_proc;
BEGIN -- main
my_proc;
END; -- main
As shown above, with the function declared first you can call the function from the procedure. However, if you try something like the following (function declared before procedure, and function calls procedure):
如上所示，首先声明函数后，您可以从过程中调用该函数。但是，如果您尝试以下操作（在程序之前声明的函数，以及函数调用程序）：
DECLARE
FUNCTION my_func RETURN NUMBER IS
BEGIN
my_proc;
RETURN 2;
END my_func;
PROCEDURE my_proc IS
BEGIN
DBMS_OUTPUT.PUT_LINE('22');
END my_proc;
BEGIN -- main
DBMS_OUTPUT.PUT_LINE(my_func);
END; -- main
the compile will fail, because my_func cannot 'see' my_proc. To make it work you need to put in a 'prototype' declaration of my_proc, as follows:
编译将失败，因为 my_func 无法“看到”my_proc。要使其工作，您需要放入 my_proc 的“原型”声明，如下所示：
DECLARE
PROCEDURE my_proc;
FUNCTION my_func RETURN NUMBER IS
BEGIN
my_proc;
RETURN 2;
END my_func;
PROCEDURE my_proc IS
BEGIN
DBMS_OUTPUT.PUT_LINE('22');
END my_proc;
BEGIN -- main
DBMS_OUTPUT.PUT_LINE(my_func);
END; -- main
Share and enjoy.
分享和享受。
回答by Florin Ghita
My guess is that you are using call procor exec proc. See below an example how to call the procedure.
我的猜测是您正在使用call procor exec proc。请参阅下面的示例如何调用该过程。
CREATE OR REPLACE function f() return number as
BEGIN
your_proc;
another_proc_with_param(2, 'John');
return 0;
EXCEPTION when others then return -1;
END f;
However, if your function(or procedures called by your function) does DML, your function can't be used in sql statements.(Can be used only in PLSQL blocks).
但是，如果您的函数（或您的函数调用的过程）执行 DML，则您的函数不能在 sql 语句中使用。（只能在 PLSQL 块中使用）。
回答by Harish
create or replace function test_fun(id in number) return number
as
val number;
begin
get_data(id,val);
return val;
end;
create or replace procedure get_data(a in number ,b out number)
as
id number;
begin
b:=a*a;
end;
回答by Nitesh Raghuwanshi
--------------------------procedure inside function-----------------------------
--------------------------函数内的过程--------------------- --------
create or replace function f_2 return date as
begin
declare
today_date date;
x number;
-------procedure declaration-----------------
-------程序声明-------
procedure pro_3(d_date out date )
is
begin
d_date:=sysdate;
end pro_3;
BEGIN
---------procedure called--------------------
---------调用程序--------------------
pro_3(today_date);
for x in 1..7 LOOP
IF TO_CHAR(today_date,'FMDAY')='SUNDAY' THEN
GOTO label_name;
END IF;
today_date:=today_date+1;
END LOOP;
<<label_name>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(today_date,'DAY')||today_date);
end;
DBMS_OUTPUT.PUT_LINE('today is ' ||TO_CHAR(sysdate,'DAY'));
return sysdate;
end;
----------------------------------execution---------------------------------
- - - - - - - - - - - - - - - - - 执行 - - - - - - - - ------------------
exec dbms_output.put_line(f_2);
