Как собрать динамический SQL с динамическими bind переменными?
Когда проект был маленький и стал понемногу разрастаться количеством фильтров, было принято решение переписать процедуры по отбору данных на динамический SQL. Выглядело это вполне себе нормально
PROCEDURE getData(
Filter1 IN VARCHAR2,
Filter2 IN VARCHAR2,
RES OUT SYS_REFCURSOR
) IS
tSQL: CLOB;
BEGIN
tSQL := 'SELECT * FROM MyTable WHERE 1 = 1';
IF (Filter1 IS NOT NULL) THEN
tSQL = TSQL || ' and product = ''' || Filter1 || ''' ';
END IF;
IF (Filter2 IS NOT NULL) THEN
tSQL = TSQL || ' and subProduct = ''' || Filter2 || ''' ';
END IF;
OPEN res FOR tSQL;
END;
Позже, проект рос и появилась потребность множественного выбора из одного поля. Создал тип nested table(вложенная таблица), переделал запрос. Так как данный тип нельзя указывать напрямую в запросе, приходится для каждого такого фильтра объявлять отдельную переменную.
create or replace type strings is table of varchar2(256);
-- -------------
PROCEDURE getData(
Filter1 IN VARCHAR2,
Filter2 IN VARCHAR2,
Filter3 IN strings,
RES OUT SYS_REFCURSOR
) IS
tSQL: CLOB;
b1 strings;
bindNum number := 0;
BEGIN
tSQL := 'SELECT * FROM MyTable WHERE 1 = 1 ';
IF (Filter1 IS NOT NULL) THEN
tSQL = TSQL || ' and product = ''' || Filter1 || ''' ';
END IF;
IF (Filter2 IS NOT NULL) THEN
tSQL = TSQL || ' and subProduct = ''' || Filter2 || ''' ';
END IF;
IF (Filter3 IS NOT NULL) THEN
tSQl := tSQL || ' AND categories IN (SELECT column_value FROM TABLE(:b1)) ) ';
b1 := Filter3;
bindNum := bindNum + 1;
END IF;
IF (bindNum = 1) then
OPEN res FOR tSQL USING b1;
ELSE
OPEN res FOR tSQL;
END IF;
END;
Далее мне необходимо вести "учет" количества задействованных переменных, что бы правильно вызывать SQL запрос. Так количество объявленных bind переменных должно строго равняться количеству передаваемых переменных, то это вылилось вот в такую, не очень красивую конструкцию
IF (bindIter = 0) THEN
OPEN res FOR tsql;
ELSIF (bindIter =1) THEN
OPEN res FOR tsql USING b1;
ELSIF (bindIter =2) THEN
OPEN res FOR tsql USING b1, b2;
... END etc
ELSE
OPEN res FOR tsql USING b1, b2, b3, b4, b5, b6, b7, b8, b9, b10;
END IF;
При этом, знаю, что у заказчика потребности будут расти и растягивать эту портянку будет сущим адом...
Попробовал переехать на пакет DBMS_SQL, все показалось красивым, НО... Там нельзя биндить переменные до того, как открыт курсор и передан SQL запрос.
DECLARE
cur NUMBER;
BEGIN
dbms_sql.open_cursor(cur); -- Открыть курсор
dbms_sql.parse(cur, 'select :x from dual', dbms_sql.native); -- Вставить запрос
dbms_sql.bind_variable(cur, ':x', '1'); -- Определять переменные
END;
Конечно, выглядеть это будет красивее, но я не уйду от безумного количества переменных и последующих проверок(сначала в IF проверяем, а есть ли данные для вписывания условия в WHERE, а потом в IF проверять, а надо ли биндить ту или иную переменную).
Смотрел в сторону JSON, но как мне кажется, это мою проблему не решит.
Oracle 19c, сервер реализован на nodeJS.
Как можно формировать динамический SQL более правильно, уходя от множественного определения переменных? Возможно есть другие способы сделать это?
Ответы (1 шт):
Если Вы собираете основной запрос tSQL динамически, то что мешает собрать скрипт открытия курсора OPEN res FOR tsql USING b1, b2, ... тоже динамически?
Ничего не мешает:
create type numbers as table of number;
create type named_list_of_numbers as object ( name varchar2(100), list numbers );
create type named_lists_of_numbers as table of named_list_of_numbers;
declare
c sys_refcursor;
a number;
a2 number;
a3 number;
procedure getData(
p_lists named_lists_of_numbers,
o_cur out sys_refcursor
) is
v_sql clob;
v_open_sql varchar2(1000);
begin
v_sql := '
select
x, square, cube
from
(
select
level x,
level*level square,
level*level*level cube
from dual
connect by level < 10
)
where 0=1';
v_open_sql := '
declare
args named_lists_of_numbers := :all_args;
begin
open :cur for :sql using ';
for j in 1 .. p_lists.count loop
v_sql := v_sql || '
or ' || p_lists(j).name || ' in (select column_value from table(:arr' || j || '))' ;
v_open_sql := v_open_sql || case when j > 1 then ', ' end || 'args(' || j || ').list' ;
end loop;
v_open_sql := v_open_sql || ';
end;' ;
dbms_output.put_line(v_sql);
dbms_output.put_line(v_open_sql);
execute immediate v_open_sql using p_lists, o_cur, v_sql;
end;
begin
getData(
named_lists_of_numbers(
named_list_of_numbers('x', numbers(2, 3)),
named_list_of_numbers('square', numbers(25)),
named_list_of_numbers('cube', numbers(343, 512))
),
c
);
loop
fetch c into a, a2, a3;
exit when c%notfound;
dbms_output.put_line(a||chr(9)||a2||chr(9)||a3);
end loop;
close c;
end;