valores - tipos de subconsultas sql

¿Cómo se especifica la cláusula IN en una consulta dinámica utilizando una variable?

Question

En PL / SQL, puede especificar los valores para el operador IN que usa la concatenación:

v_sql := ''select field1
from table1
where field2 in ('' || v_list || '')'';

¿Es posible hacer lo mismo usando una variable?

v_sql := ''select field1
from table1
where field2 in (:v_list)''; 

¿Si es así, cómo?

EDITAR: Con referencia a la respuesta de Marcin, ¿cómo selecciono de la tabla resultante?

declare
cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = ''PERM'';
cursor c_get_food_list (v_food_table varchar2Table)is
select *
from v_food_table;
begin
for i in c_get_csv_as_tables loop
for j in c_get_food_list(i.food_list) loop
dbms_output.put_line(j.element);
end loop;
end loop;
end;

Obtuve el siguiente error:

ORA-06550: line 10, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 34:
PLS-00364: loop index variable ''J'' use is invalid
ORA-06550: line 15, column 13:
PL/SQL: Statement ignored

Answer 1

Al igual que en el enlace @Sathya, puedes vincular el varray (tomé @Codo example):

CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
/
DECLARE
l_str_tab str_tab_type;
l_count NUMBER;
v_sql varchar2(3000);
BEGIN
l_str_tab := str_tab_type();
l_str_tab.extend(2);
l_str_tab(1) := ''TABLE'';
l_str_tab(2) := ''INDEX'';
v_sql := ''SELECT COUNT(*) FROM all_objects WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(:v_list))'';
execute immediate v_sql into l_count using l_str_tab;
dbms_output.put_line(l_count);
END;
/

ACTUALIZACIÓN: el primer comando puede ser reemplazado por:

CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
/

luego llame:

l_str_tab.extend(1);

cada vez que agregas un valor

Answer 2

Lamentablemente, no puede enlazar una lista como esta, sin embargo, puede usar una función de tabla. Lea esto

Aquí hay un ejemplo de uso basado en su código:

declare
cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = ''PERM'';
cursor c_get_food_list (v_food_table varchar2Table)is
select column_value food
from TABLE(v_food_table);
begin
for i in c_get_csv_as_tables loop
for j in c_get_food_list(i.food_list) loop
dbms_output.put_line(j.food);
end loop;
end loop;
end;

Utilicé aquí una pseudocolumna column_value

Answer 3

Según la respuesta de @Marc no se puede hacer, sin embargo, hay mucho que añadir, ya que su consulta debería funcionar, es decir, ejecutar.

En pocas palabras, no puede usar una variable de vinculación para una tabla o columna. No solo eso, las variables de enlace se supone que son un carácter, por lo que si quieres un número debes usar to_number(:b1) etc.

Aquí es donde su consulta se cae. Cuando está pasando una cadena, Oracle asume que toda su lista es una sola cadena. Por lo tanto, estás ejecutando efectivamente:

select field1
from table1
where field2 = v_list

Sin embargo, no hay ninguna razón por la cual no puedas hacer esto de otra manera. v_list que está creando dinámicamente v_list , lo que significa que todo lo que necesita hacer es crear esta lista de manera diferente. Una serie de or condiciones es, supuestamente :-), no es diferente de usar un in

Supuestamente, quiero decir que nunca confíes en algo que no ha sido probado. Aunque Tom dice en el enlace que puede haber limitaciones de rendimiento, no hay garantía de que no sea más rápido que utilizarlo para comenzar. Lo mejor que puede hacer es ejecutar el seguimiento de su consulta y la suya y ver qué diferencia hay, si hay alguna.

SQL> set serveroutput on
SQL>
SQL> declare
2
3 l_string varchar2(32767);
4 l_count number;
5
6 begin
7
8 for xx in ( select rownum as rnum, a.*
9 from user_tables a
10 where rownum < 20 ) loop
11
12 if xx.rnum = 1 then
13 l_string := ''table_name = '''''' || xx.table_name || '''''''';
14 else
15 l_string := l_string || '' or table_name = '''''' || xx.table_name || ''
'''''';
16 end if;
17
18 end loop;
19
20 execute immediate ''select count(*)
21 from user_tables
22 where '' || l_string
23 into l_count
24 ;
25
26 dbms_output.put_line(''count is '' || l_count);
27
28 end;
29 /
count is 19
PL/SQL procedure successfully completed.

Answer 4

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