Использование переменных связывания с динамическим предложением SELECT INTO в PL / SQL

У меня вопрос относительно того, где переменные связывания могут использоваться в динамическом операторе SQL в PL / SQL.

Например, я знаю, что это действительно так:

CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) 
RETURN NUMBER
IS
  v_query_str VARCHAR2(1000);
  v_num_of_employees NUMBER;
BEGIN
  v_query_str := 'SELECT COUNT(*) FROM emp_' 
                 || p_loc
                 || ' WHERE job = :bind_job';                           
  EXECUTE IMMEDIATE v_query_str
    INTO v_num_of_employees
    USING p_job;
  RETURN v_num_of_employees;
END;
/

Мне было интересно, можно ли использовать переменные привязки в таком операторе выбора

CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) 
RETURN NUMBER
IS
  v_query_str VARCHAR2(1000);
  v_num_of_employees NUMBER;
BEGIN
  v_query_str := 'SELECT COUNT(*) INTO :into_bind FROM emp_' 
                 || p_loc
                 || ' WHERE job = :bind_job';                           
  EXECUTE IMMEDIATE v_query_str
    USING out v_num_of_employees, p_job;
  RETURN v_num_of_employees;
END;
/

Примечание. Я использовал оператор SELECT INTO в качестве динамической строки и использовал переменную связывания в предложении INTO.

В настоящее время я путешествую, и несколько дней у меня не будет доступа к компьютеру дома, но это меня немного беспокоит. Пытался прочитать справочник PL / SQL, но у них нет такого примера выбора.

Спасибо


sql oracle plsql oracle11g execute-immediate
person BYS2    schedule 19.10.2011    source источник
comment
Связанный: stackoverflow.com/q/25489002/1461424   -  person sampathsris    schedule 26.08.2014

Ответы (5)


arrow_upward
28
arrow_downward

Нет, вы не можете использовать переменные связывания таким образом. Во втором примере :into_bind в v_query_str - это просто заполнитель для значения переменной v_num_of_employees. Ваш оператор select into превратится во что-то вроде:

SELECT COUNT(*) INTO  FROM emp_...

потому что значение v_num_of_employees равно null в EXECUTE IMMEDIATE.

В вашем первом примере представлен правильный способ привязки возвращаемого значения к переменной.

Изменить

Исходный постер отредактировал второй блок кода, на который я ссылаюсь в своем ответе, чтобы использовать режим параметров OUT для v_num_of_employees вместо режима IN по умолчанию. Эта модификация делает оба примера функционально эквивалентными.

person user272735    schedule 19.10.2011
comment
О, это имеет смысл, ': into_bind' будет заменен, когда он действительно будет запущен. В первом примере, который я написал, все же будет использоваться переменная связывания в предложении INTO? Причина, по которой я хотел попробовать второй способ, заключалась в том, что он использует переменные связывания. - person BYS2; 19.10.2011
comment
@ BYS2: Да, вы можете использовать режим параметров OUT. Однако я предпочитаю EXECUTE IMMEDIATE INTO для ясности. - person user272735; 20.10.2011
comment
java2s.com/Tutorial/Oracle/0440__PL-SQL-Statements/ - person shareef; 12.11.2012

arrow_upward
30
arrow_downward

На мой взгляд, динамический блок PL / SQL несколько непонятен. Хотя он очень гибкий, его также сложно настроить, сложно отладить и сложно понять, что происходит. Мой голос идет за ваш первый вариант,

EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job;

Оба используют переменные связывания, но, во-первых, для меня это более изменяемое и настраиваемое, чем опция @jonearles.

person Aitor    schedule 19.10.2011
comment
Хорошо, спасибо за эту информацию, поскольку оба используют bind varialbes, первый вариант кажется лучшим! - person BYS2; 19.10.2011
comment
@ BYS2: Мне также больше всего нравится этот синтаксис. Понятия не имею о производительности. - person user272735; 20.10.2011

arrow_upward
19
arrow_downward

Поместите оператор select в динамический блок PL / SQL.

CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) 
RETURN NUMBER
IS
  v_query_str VARCHAR2(1000);
  v_num_of_employees NUMBER;
BEGIN
  v_query_str := 'begin SELECT COUNT(*) INTO :into_bind FROM emp_' 
                 || p_loc
                 || ' WHERE job = :bind_job; end;';
  EXECUTE IMMEDIATE v_query_str
    USING out v_num_of_employees, p_job;
  RETURN v_num_of_employees;
END;
/
person Jon Heller    schedule 19.10.2011
comment
Ага, понятно, спасибо! Кстати, знаете ли вы, какой путь лучше? инкапсулируя SELECT INTO в блок, как вы, или используя предложение INTO оператора EXECUTE IMMEDIATE, как я сделал в моем первом примере. Используют ли оба переменные связывания? какая разница в эффективности? - person BYS2; 19.10.2011
comment
Я согласен с другими, что первый метод (динамический SQL) намного лучше, чем второй (динамический PL / SQL). Динамический PL / SQL встречается очень редко и почти никогда не нужен. Основываясь на простом тестировании, я не заметил разницы в производительности между ними, но я ожидал, что в некоторых случаях динамический метод PL / SQL будет немного медленнее. Динамический метод PL / SQL фактически генерирует 3 переменных связывания: 2 для блока PL / SQL (хотя ни одна из них не фиксируется в V $ SQL_BIND_CAPTURE) и 1 для запроса SQL. Подобные причуды могут очень затруднить настройку и отладку. - person Jon Heller; 20.10.2011
comment
О, хорошо, спасибо за все ваши советы! Не могу записать ваш как ответ, потому что технически это был не мой вопрос, но я за обучение, так что это полезно знать: D - person BYS2; 20.10.2011
comment
В этом есть смысл. Имейте в виду, что он выполняется вне PLSQL (правильный термин?), Как вы видели бы в командной строке SQLPlus, поэтому вы можете выполнять DDL из exec immed. - person Pecos Bill; 17.10.2014

arrow_upward
0
arrow_downward

Переменная связывания может использоваться в запросе Oracle SQL с условием «in».

Работает в 10g; Насчет других версий не знаю.

Переменная привязки - это varchar до 4000 символов.

Пример: привязать переменную, содержащую список значений, разделенных запятыми, например

: bindvar = 1,2,3,4,5

select * from mytable
  where myfield in
    (
      SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
      FROM dual
      CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
    );

(Та же информация, что я разместил здесь: Как указать предложение IN в динамическом запросе с помощью переменной?)

person Kat    schedule 11.06.2016

arrow_upward
-2
arrow_downward

Функциональность Select Into работает только для блока PL / SQL, когда вы используете Execute немедленно, oracle интерпретирует v_query_str как строку SQL-запроса, поэтому вы не можете использовать в .will get ключевое слово missing Exception. в примере 2 мы используем begin end; так что он стал блоком pl / sql и его законным.

person Khilan Shah    schedule 27.12.2016