Ответ 1
используйте цикл PL/SQL:
BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) LOOP
my_proc(c.field1, c.field2);
END LOOP;
END;
Хранимая процедура вызова оракула внутри select
Я работаю над запросом (SELECT), и мне нужно вставить результат этого в таблицу. Перед выполнением вставки у меня есть проверка, и если все столбцы действительны, я сделаю вставку.
Проверка выполняется в хранимой процедуре. Такая же процедура используется и в другом месте. Поэтому я пытаюсь использовать ту же процедуру для выполнения своих проверок.
Процедура проверок и вставка значений все в порядке.
Я попытался вызвать процедуру внутри своего SELECT, но она не работает.
SELECT field1, field2, myproc(field1, field2)
from MYTABLE.
Этот тип кода не работает.
Я думаю, что это можно сделать с помощью курсора, но я бы хотел избежать курсоров. Я ищу самое простое решение.
Кто-нибудь, любая идея?
Ответы
Ответ 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.