Хранимая процедура вызова оракула внутри select

Я работаю над запросом (SELECT), и мне нужно вставить результат этого в таблицу. Перед выполнением вставки у меня есть проверка, и если все столбцы действительны, я сделаю вставку.

Проверка выполняется в хранимой процедуре. Такая же процедура используется и в другом месте. Поэтому я пытаюсь использовать ту же процедуру для выполнения своих проверок.

Процедура проверок и вставка значений все в порядке.

Я попытался вызвать процедуру внутри своего SELECT, но она не работает.

SELECT field1, field2, myproc(field1, field2)

from MYTABLE.

Этот тип кода не работает.

Я думаю, что это можно сделать с помощью курсора, но я бы хотел избежать курсоров. Я ищу самое простое решение.

Кто-нибудь, любая идея?

Ответ 1

используйте цикл PL/SQL:

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

Ответ 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 спондуликов....

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>

Обратите внимание на то, что исключение для броска имеет решающее значение для этой работы. Мы не можем написать какую-то странную ошибку, если SALARY является логичной логикой INSERT в нашем SQL-заявлении. Таким образом, если хранимая процедура не вызывает исключение, но вместо этого возвращает некоторый wimpy-статус ошибки, то функция-оболочка должна будет интерпретировать вывод и выдавать собственное исключение.

Ответ 3

Вы не можете использовать хранимые процедуры в инструкции SELECT. Вы можете использовать для этого функции.

Как я понимаю, вы вызываете вставку в свой SP, поэтому учтите, что вы можете использовать INSERT/UPDATE в теле функции. Но если вам нужно сделать некоторые проверки, вы можете использовать функцию, которая будет выполнять это, и использовать эту функцию в вашем заявлении select.