#oracle #plsql
#Oracle #plsql
Вопрос:
Возможно ли вызвать подпрограмму из инструкции SQL из хранимой процедуры Oracle? Возможно, пример был бы лучшим способом объяснить это. Вот простой пример:
DECLARE
PROCEDURE p_test(in_text VARCHAR2) IS
BEGIN
dbms_output.put_line(in_text);
END;
BEGIN
SELECT p_test('Test') FROM dual;
END;
/
Однако это приводит к ошибке ORA-00904. Итак, я не уверен, что ответ на мой вопрос будет утвердительным. Тем не менее, я все еще хотел спросить.
Что моя настоящая программа пытается сделать, так это сравнить более сотни пар полей. Итак, что-то вроде этого:
DECLARE
PROCEDURE p_compare
(in_old VARCHAR2, in_new VARCHAR2) IS
BEGIN
IF in_old <> in_new THEN
dbms_output.put_line('Mismatch');
INSERT INTO tbl_mismatch VALUES (in_old, in_new);
END IF;
END;
v_old_value_a VARCHAR2(30);
v_new_value_a VARCHAR2(30);
v_old_value_b VARCHAR2(30);
v_new_value_b VARCHAR2(30);
BEGIN
--What I would like to do
SELECT p_compare(old_value_a, new_value_a), p_compare(old_value_b, new_value_b)
FROM (SELECT 'ALPHA' old_value_a, 'ALPHA' new_value_a, 'BETA' old_value_b, 'DELTA' new_value_b FROM dual);
--What I am currently doing
--I have over 100 pairs of fields that I am comparing
--Declaring two variables for each pair becomes cumbersome
SELECT old_value_a, new_value_a, old_value_b, new_value_b
INTO v_old_value_a, v_new_value_a, v_old_value_b, v_new_value_b
FROM (SELECT 'ALPHA' old_value_a, 'ALPHA' new_value_a, 'BETA' old_value_b, 'DELTA' new_value_b FROM dual);
p_compare(v_old_value_a, v_new_value_a);
p_compare(v_old_value_b, v_new_value_b);
END;
/
Итак, если, возможно, то, что я пытаюсь, невозможно, есть ли лучший способ достичь моей конечной цели?
Комментарии:
1. Таким образом можно вызвать функцию, а не процедуру. Чего вы ожидаете от
SELECT p_test('Test') FROM dual?2. Алексей, для целей примера я бы ожидал, что
SELECT p_test('Test') FROM dualвернет то же самое, что иp_test('Test'). В реальном коде подпрограмма выполняет сравнение и несколько дополнительных операций и вставляет отличающиеся значения в таблицу.3. Если p_test является процедурой, она вообще ничего не возвращает; если вам нужно получить какой-то результат, вам нужно создать функцию, которая возвращает значение
4. Вы не можете вызвать процедуру из SQL, потому что процедуры не возвращают значение, и, если подумать, единственные места, где вы могли бы вызвать подпрограмму из инструкции SQL, — это места, где требуется значение. Таким образом, вы можете вызывать ФУНКЦИИ из инструкций SQL, но не ПРОЦЕДУРЫ. Желаю удачи.
5. Итак, зачем вам нужен запрос? Определите процедуру и вызовите ее, не используя select
Ответ №1:
Вы можете просто вызвать процедуру таким образом:
SQL> DECLARE
2 PROCEDURE p_compare(in_old VARCHAR2, in_new VARCHAR2) IS
3 BEGIN
4 IF in_old <> in_new
5 THEN
6 DBMS_OUTPUT.put_line('Mismatch');
7
8 INSERT INTO tbl_mismatch
9 VALUES (in_old, in_new);
10 END IF;
11 END;
12 BEGIN
13 p_compare('AA', 'BB');
14 p_compare('XX', 'XX');
15 END;
16 /
Mismatch
PL/SQL procedure successfully completed.
SQL> select * from tbl_mismatch;
IN_OLD IN_NEW
------------------------------ ------------------------------
AA BB
Если вы хотите использовать функцию для вызова внутри инструкции SQL, это способ:
SQL> CREATE OR REPLACE FUNCTION f_compare(in_old VARCHAR2, in_new VARCHAR2)
2 RETURN VARCHAR2 IS
3 BEGIN
4 IF in_old <> in_new
5 THEN
6 RETURN 'Mismatch';
7 ELSE
8 RETURN 'Match';
9 END IF;
10 END;
11 /
Function created.
SQL> DECLARE
2 v_compare_result1 VARCHAR2(30);
3 v_compare_result2 VARCHAR2(30);
4 BEGIN
5 SELECT f_compare(old_value_a, new_value_a), f_compare(old_value_b, new_value_b)
6 INTO v_compare_result1, v_compare_result2
7 FROM (SELECT 'ALPHA' old_value_a,
8 'ALPHA' new_value_a,
9 'BETA' old_value_b,
10 'DELTA' new_value_b
11 FROM DUAL);
12
13 DBMS_OUTPUT.put_line('Compare 1: ' || v_compare_result1);
14 DBMS_OUTPUT.put_line('Compare 2: ' || v_compare_result2);
15 END;
16 /
Compare 1: Match
Compare 2: Mismatch
PL/SQL procedure successfully completed.
SQL>
Комментарии:
1. Но я извлекаю значения, которые необходимо сравнить, из таблицы. Я бы предпочел не объявлять две переменные для каждого сравниваемого поля. В моем примере у меня есть v_old_value_a / b. Ну, представьте v_[старый / новый]_value_[01-99].
2. Возможно, функция — лучший способ. Похоже, мне понадобится только одна переменная для сравнения, а не две.
3. Функция Guess не работает, когда она встроена в процедуру, как я надеялся с подпрограммой. Я работаю с приложением поставщика, поэтому я немного ограничен.
4. В SQL вы можете вызывать только хранимую функцию, а не ту, которая определена в вашем блоке
5. Также функция может находиться в том же пакете PL / SQL, если она общедоступна (т. Е. объявлена в заголовке пакета). В качестве альтернативы, если строк для обработки не слишком много, вы можете перебирать их и использовать локальную функцию внутри цикла.
Ответ №2:
Если вы хотите использовать переменные, вы должны объявить их перед объявлением любой встроенной функции / процедуры.
Это работает:
DECLARE
x NUMBER;
PROCEDURE p_test(in_text VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(in_text);
END;
FUNCTION give_me_five RETURN NUMBER IS
BEGIN
RETURN 5;
END;
BEGIN
x := give_me_five;
DBMS_OUTPUT.PUT_LINE( x );
p_test('Hello World');
END;
но эта не работает:
DECLARE
FUNCTION give_me_five RETURN NUMBER IS
BEGIN
RETURN 5;
END;
x NUMBER;
BEGIN
x := give_me_five;
DBMS_OUTPUT.PUT_LINE( x );
END;
ORA-06550: line 7, column 2:
PLS-00103: Encountered the symbol "X" when expecting one of the following:
begin function pragma procedure
ORA-06550: line 13, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map