immediate - oracle pl sql execute dynamic query

Usar variables de vinculación con la cláusula dinámica SELECT INTO en PL/SQL

Question

Tengo una pregunta sobre dónde se pueden usar las variables de vinculación en una declaración SQL dinámica en PL / SQL.

Por ejemplo, sé que esto es válido:

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;
/

Me preguntaba si podría usar una variable de vinculación en una declaración de selección como esta

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;
/

Nota: utilicé una instrucción SELECT INTO como mi cadena dyamic y usé una variable bind en la cláusula INTO.

Actualmente estoy de viaje y no tendré acceso a mi computadora en mi casa por unos días, pero esto me ha estado molestando un poco. Intenté leer la referencia PL / SQL, pero no tienen un ejemplo de una selección como esta.

Gracias

Answer 1

Coloque la instrucción de selección en un bloque dinámico 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;
/

Answer 2

En mi opinión, un bloque dinámico PL / SQL es algo oscuro. Si bien es muy flexible, también es difícil de sintonizar, difícil de depurar y difícil de descifrar qué ocurre. Mi voto va a su primera opción,

EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job;

Ambos usos vinculan variables, pero primero, para mí, es más modificable y ajustable que la opción @jonearles.

Answer 3

La variable de vinculación se puede utilizar en la consulta SQL de Oracle con la cláusula "in".

Funciona en 10g; No sé sobre otras versiones.

La variable de enlace es varchar hasta 4000 caracteres.

Ejemplo: variable de vinculación que contiene una lista de valores separados por comas, por ejemplo

: 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
    );

(La misma información que publiqué aquí: ¿cómo se especifica cláusula IN en una consulta dinámica usando una variable? )

Answer 4

No, no puedes usar las variables de vinculación de esa manera. En su segundo ejemplo :into_bind en v_query_str es solo un marcador de posición para el valor de la variable v_num_of_employees . Su declaración select into se convertirá en algo así como:

SELECT COUNT(*) INTO  FROM emp_...

porque el valor de v_num_of_employees es null en EXECUTE IMMEDIATE .

Su primer ejemplo presenta la forma correcta de enlazar el valor de retorno a una variable.

Editar

El póster original ha editado el segundo bloque de código al que me refiero en mi respuesta para usar el modo de parámetro OUT para v_num_of_employees lugar del modo IN predeterminado. Esta modificación hace que los dos ejemplos sean funcionalmente equivalentes.

Answer 5

Seleccionar en funcionalidad solo funciona para PL / SQL Block, cuando utiliza Execute immediate, oracle interpreta v_query_str como una cadena de consulta SQL para que no pueda usar .will get keyword missing Exception. en el ejemplo 2, estamos utilizando begin end; por lo que se convirtió en bloque pl / sql y es legal.