Как вы определяете предложение IN в динамическом запросе с использованием переменной?
В PL / SQL вы можете указать значения для оператора IN, используя конкатенацию:
v_sql := 'select field1
from table1
where field2 in (' || v_list || ')';
Можно ли сделать то же самое с помощью переменной?
v_sql := 'select field1
from table1
where field2 in (:v_list)';
Если так, то как?
РЕДАКТИРОВАТЬ: со ссылкой на ответ Марцина, как выбрать из таблицы результатов?
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;
Я получаю следующую ошибку:
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
Решение
Как и в ссылке @Sathya, вы можете связать varray (я взял пример @Codo):
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;
/
ОБНОВИТЬ: первая команда может быть заменена на:
CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
/
затем позвоните:
l_str_tab.extend(1);
когда вы добавляете значение
Другие решения
К сожалению, вы не можете связать список таким образом, однако вы можете использовать табличную функцию. Читать этот
Вот пример использования на основе вашего кода:
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;
Я использовал здесь column_value псевдостолбец
Переменная связывания может использоваться в запросе 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
);
Согласно ответу @ Marcin, вы не можете сделать это, однако, есть немало, чтобы добавить к этому, поскольку ваш запрос должен действительно работать, т.е.
Проще говоря, вы не можете использовать переменную связывания для таблицы или столбца. Мало того, что связывать переменные они предполагаются как символ, поэтому, если вы хотите число, вы должны использовать to_number(:b1) и т.п.
Это где ваш запрос падает. Когда вы передаете строку, Oracle предполагает, что весь ваш список представляет собой одну строку. Таким образом, вы эффективно работаете:
select field1
from table1
where field2 = v_list
Нет никаких причин, почему вы не можете сделать это иначе. Я собираюсь предположить, что вы динамически создаете v_listЭто означает, что все, что вам нужно сделать, это создать этот список по-другому. Серия or условия, якобы :-), ничем не отличается от использования in,
Якобы я имею в виду никогда не полагаться на то, что не проверено. Хотя Том говорит в ссылке, что могут быть ограничения производительности, нет гарантии, что это не было быстрее, чем использование in начать с. Лучше всего выполнить трассировку по вашему запросу и его и посмотреть, какая разница, если таковые имеются.
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.
detector