SQL oracle在select里面调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2865149/
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
oracle call stored procedure inside select
提问by CC.
I'm working on a query (a SELECT) and I need to insert the result of this one in a table. Before doing the insert I have some checking to do, and if all columns are valid, I will do the insert.
我正在处理一个查询（一个 SELECT），我需要将这个查询的结果插入到一个表中。在进行插入之前，我有一些检查要做，如果所有列都有效，我将进行插入。
The checking is done in a stored procedure. The same procedure is used somewhere else too. So I'm thinking using the same procedure to do my checks.
检查是在存储过程中完成的。其他地方也使用相同的程序。所以我正在考虑使用相同的程序来进行检查。
The procedure does the checkings and insert the values is all OK.
该程序进行检查并插入值一切正常。
I tryied to call the procedure inside my SELECT but it does not works.
我尝试在我的 SELECT 中调用该过程，但它不起作用。
SELECT field1, field2, myproc(field1, field2)
from MYTABLE.
This kind of code does not works.
这种代码不起作用。
I think it can be done using a cursor, but I would like to avoid the cursors. I'm looking for the easiest solution.
我认为可以使用游标来完成，但我想避免使用游标。我正在寻找最简单的解决方案。
Anybody, any idea ?
任何人，任何想法？
回答by Vincent Malgrat
use a PL/SQL loop:
使用 PL/SQL 循环：
BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) LOOP
my_proc(c.field1, c.field2);
END LOOP;
END;
回答by APC
SQL can only use functions in the projection: it needs something which returns a value. So you are going to have to write some functions. That's the bad news. The good news is, you can re-use all the investement in your stored procedures.
SQL 只能在投影中使用函数：它需要返回值的东西。所以你将不得不编写一些函数。这就是坏消息。好消息是，您可以重用存储过程中的所有投资。
Here is a procedure which enforces a completely just business rule: only managers can have a high salary.
这是一个执行完全公正的商业规则的程序：只有经理才能获得高薪。
SQL> create or replace procedure salary_rule
2 ( p_sal in emp.sal%type
3 , p_job in emp.job%type)
4 is
5 x_sal exception;
6 begin
7 if p_sal > 4999 and p_job != 'MANAGER' then
8 raise x_sal;
9 end if;
10 exception
11 when x_sal then
12 raise_application_error(-20000, 'Only managers can earn that much!');
13 end salary_rule;
14 /
Procedure created.
SQL>
Because it is a procedure we cannot use it in a SELECT statement; we need to wrap it in a function. This function just calls the stored procedure. It returns the input parameter P_SAL. In other words, if the salary is valid (according to the rules) it will be returned. Otherwise the function will re-hurl the stored procedure's exception.
因为它是一个过程，我们不能在 SELECT 语句中使用它；我们需要将它包装在一个函数中。这个函数只是调用存储过程。它返回输入参数 P_SAL。换句话说，如果工资有效（根据规则），它将被退回。否则该函数将重新抛出存储过程的异常。
SQL> create or replace function validate_salary
2 ( p_sal in emp.sal%type
3 , p_job in emp.job%type)
4 return emp.sal%type
5 is
6 begin
7 salary_rule(p_sal, p_job);
8 return p_sal;
9 end validate_salary;
10 /
Function created.
SQL>
The function has to return a value which we want to insert into our table. It cannot return some meaningless phrase like "salary okay". Also, if we want to validate two columns we need a separate function for each, even if there is a relationship between them and we use the same stored procedure to validate them both. Good use for the DETERMINISTIC keyword.
该函数必须返回一个我们想要插入到表中的值。它不能返回一些无意义的短语，如“薪水还可以”。此外，如果我们想验证两列，我们需要为每一列单独的函数，即使它们之间存在关系并且我们使用相同的存储过程来验证它们。很好地使用 DETERMINISTIC 关键字。
Here's the test: plumbers cannot earn 5000 spondulicks ....
这是测试：水管工不能赚 5000 spondulicks ....
SQL> insert into emp
2 (empno
3 , ename
4 , job
5 , deptno
6 , sal )
7 select
8 emp_seq.nextval
9 , 'HALL'
10 , 'PLUMBER'
11 , 60
12 , validate_salary(5000, 'PLUMBER')
13 from dual
14 /
, validate_salary(5000, 'PLUMBER')
*
ERROR at line 12:
ORA-20000: Only managers can earn that much!
ORA-06512: at "APC.SALARY_RULE", line 12
ORA-06512: at "APC.VALIDATE_SALARY", line 7
SQL>
... but managers can (because they deserve it):
...但经理可以（因为他们应得的）：
SQL> insert into emp
2 (empno
3 , ename
4 , job
5 , deptno
6 , sal )
7 select
8 emp_seq.nextval
9 , 'HALL'
10 , 'MANAGER'
11 , 60
12 , validate_salary(5000, 'MANAGER')
13 from dual
14 /
1 row created.
SQL>
Note that the hurled exception is crucial to this working. We cannot write some bizarre IF SALARY IS VALID THEN INSERT logic in our SQL statement. So, if the stored procedure doesn't raise an exception but instead returns some wimpy error status the wrapping function will have to interpret the output and hurl its own exception.
请注意，抛出的异常对于这项工作至关重要。我们不能在我们的 SQL 语句中编写一些奇怪的 IF SALARY IS VALID THEN INSERT 逻辑。因此，如果存储过程没有引发异常而是返回一些微不足道的错误状态，则包装函数将不得不解释输出并抛出自己的异常。
回答by Incognito
You can't use stored procedures in SELECT statement. You can use functions for that.
您不能在 SELECT 语句中使用存储过程。您可以为此使用函数。
As I understand you are calling insert in your SP, so take into consideration that you can's use INSERT/UPDATE in function body. But if you need to do some checks you can use function which will do that checks and use that function in your select statement.
据我了解，您正在 SP 中调用 insert，因此请考虑到您可以在函数体中使用 INSERT/UPDATE。但是如果你需要做一些检查，你可以使用函数来做检查并在你的选择语句中使用该函数。
