Использование переменных привязки с динамическим предложением 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;
/

Мне было интересно, можете ли вы использовать переменные связывания в инструкции select, подобной этой

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, но у них нет примера выбора, подобного этому.

Спасибо

Ответы

Ответ 1

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

SELECT COUNT(*) INTO  FROM emp_...

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

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

Edit

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

Ответ 2

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

EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job;

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

Ответ 3

Поместите оператор 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;
/

Ответ 4

Переменная Bind может использоваться в запросе Oracle SQL с предложением "in".

Работает в 10г; Я не знаю о других версиях.

Переменная связывания - varchar длиной до 4000 символов.

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

: 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 в динамическом запросе с использованием переменной?)

Ответ 5

Выбор В функциональность работает только для PL/SQL Block, когда вы используете Execute немедленно, oracle интерпретирует v_query_str как строку SQL Query, поэтому вы не можете использовать в .will get keyword missing Exception. в примере 2 мы используем начало конца; поэтому он стал pl/sql-блоком и его законным.