хранимая процедура вызова оракула в выборе
Я работаю над запросом (ВЫБОР), и я должен вставить результат этого в таблице. Прежде, чем сделать вставку у меня есть некоторая проверка, чтобы сделать, и если все столбцы будут действительны, то я сделаю вставку.
Проверка сделана в хранимой процедуре. Та же процедура используется где-то в другом месте также. Таким образом, я думаю с помощью той же процедуры, чтобы сделать мои проверки.
Процедура делает проверки и вставляет значения, весь OK.
Я tryied для вызова процедуры в моем ВЫБОРЕ, но это не делает работ.
SELECT field1, field2, myproc(field1, field2)
from MYTABLE.
Этот вид кода не делает работ.
Я думаю, что это может быть сделано с помощью курсора, но я хотел бы избежать курсоров. Я ищу самое легкое решение.
Кто-либо, какая-либо идея?
3 ответа
использовать цикл PL / SQL:
BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) LOOP
my_proc(c.field1, c.field2);
END LOOP;
END;
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>
Обратите внимание, что выброшенное исключение имеет решающее значение для этой работы. Мы не можем написать какую-то причудливую логику IF SALARY IS VALID THEN INSERT в нашем операторе SQL. Итак, если хранимая процедура не вызывает исключение, а вместо этого возвращает некоторую слабую ошибку, функция упаковки должна будет интерпретировать вывод и выбросить собственное исключение.
В операторе SELECT нельзя использовать хранимые процедуры. Для этого вы можете использовать функции.
Насколько я понимаю, вы вызываете insert в своем SP, поэтому примите во внимание, что вы можете использовать INSERT / UPDATE в теле функции. Но если вам нужно выполнить некоторые проверки, вы можете использовать функцию, которая будет выполнять эти проверки и использовать эту функцию в вашем операторе выбора.