oracle在select里面调用存储过程

Question

我正在处理一个查询（SELECT），我需要将这个查询的结果插入到表中。 在执行插入之前，我需要进行一些检查，如果所有列都有效，我将执行插入。

检查是在存储过程中完成的。其他地方也使用相同的过程。 所以我正在考虑使用相同的程序来进行检查。

该过程进行检查并插入值一切正常。

我尝试在 SELECT 中调用该过程，但它不起作用。

SELECT field1, field2, myproc(field1, field2)

from MYTABLE.

这种代码不起作用。

我认为可以使用游标来完成，但我想避免使用游标。 我正在寻找最简单的解决方案。

有人有什么想法吗？

Answer 1

不能在 SELECT 语句中使用存储过程。
您可以为此使用函数。

据我了解，您正在 SP 中调用 insert，因此请考虑您可以在函数体中使用 INSERT/UPDATE。但是，如果您需要执行一些检查，您可以使用将执行检查的函数，并在 select 语句中使用该函数。

Answer 2

SQL 只能在投影中使用函数：它需要返回值的东西。所以你必须编写一些函数。这是坏消息。好消息是，您可以重复使用存储过程中的所有投资。

这是一个执行完全公正的商业规则的程序：只有经理才能获得高薪。

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>

因为它是一个过程，所以我们不能在 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>

该函数必须返回一个我们想要插入到表中的值。它不能返回一些无意义的短语，例如“薪水还可以”。此外，如果我们想要验证两列，我们需要为每一列提供一个单独的函数，即使它们之间存在关系，并且我们使用相同的存储过程来验证它们。 DETERMINISTIC 关键字很好用。

测试如下：水管工无法赚取 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>

...但经理可以（因为他们应得的）：

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>

请注意，抛出的异常对于这项工作至关重要。我们不能在 SQL 语句中编写一些奇怪的 IF SALARY IS VALID THEN INSERT 逻辑。因此，如果存储过程没有引发异常，而是返回一些微不足道的错误状态，则包装函数将必须解释输出并抛出自己的异常。

Answer 3

使用 PL/SQL 循环：

BEGIN
   FOR c IN (SELECT field1, field2 FROM mytable) LOOP
       my_proc(c.field1, c.field2);
   END LOOP;
END;