Блог

Главная - Вопросы по программированию - Динамический sql с циклом for PL / SQL

Динамический sql с циклом for PL / SQL

#sql #oracle #plsql #dynamic-sql

#sql #Oracle #plsql #dynamic-sql

Вопрос:

Следующий запрос необходимо преобразовать в динамический SQL без жесткого кода cursor SQL, используя l_query, я не знаю, какой l_query он будет использовать в качестве параметра. Внутри цикла мне нужно выполнить другой запрос на вставку ( l_insert_query), который также предоставляется в качестве параметра.

Мы были бы очень признательны за ваш совет

 DECLARE
    CURSOR cust
    IS
        SELECT *
          FROM customer
         WHERE id < 500;
BEGIN
    l_query := 'SELECT * FROM customer  WHERE id < 5';
    l_insert_query :=
        'insert into data ( name, mobile) values ( cust.name,cust.mobile)';

    FOR r_cust IN cust
    LOOP
        EXECUTE IMMEDIATE l_insert_query;
    END LOOP;
END;

Комментарии:

1. Что вы знаете о запросах? Как вы должны программно определить, что, предположительно, «cust.name » in l_insert_query должен ссылаться на name столбец из customer таблицы в l_query ? Если вы ничего не знаете о l_query , вы понятия не имеете, из скольких таблиц он запрашивает или какие столбцы он возвращает, не говоря уже о том, какой псевдоним человек мог бы выбрать для имени таблицы.

2. Если оба l_query и l_insert_query являются параметрами, похоже, что ваша проблема связана с семантическим анализом этих двух параметров. Сначала вы должны создать алгоритм для чтения этих двух параметров, проверить, является ли оба они допустимыми SQL-запросами и l_query пригодны для запуска l_insert_query . С помощью этого алгоритма вы могли бы делать все, что l_insert_query захотите, используя динамический SQL или, возможно, достаточно только SQL.

3. Итак, l_insert_query всегда ли столбцы будут ссылаться как cust.<<column name>> ? Зачем вам проектировать систему таким образом? Если бы l_insert_query было просто insert into data( name, mobile ) и l_query было select name, mobile from customer where id < 5 , вы могли бы, по крайней мере, объединить их, чтобы динамически генерировать insert инструкцию.

4. Как она стоит, вы, вероятно, должны использовать dbms_sql для выполнения l_query , динамически опрашивать курсор ручки, чтобы получить список имен столбцов, поиск l_insert_query любых cust. ссылок, замены связывать переменные, выполнить итерацию dbms_sql курсора ручку вызова l_insert_query и передавая переменные связывания. Это целая куча довольно бессмысленных манипуляций со строками, и dbms_sql это не тривиальный пакет для работы (и отладка и обслуживание будут намного сложнее).

5. @JustinCave — «Зачем вам проектировать систему таким образом? » Некоторые архитекторы решений рассматривают базу данных как препятствие, которое необходимо преодолеть и сломать, когда его нельзя игнорировать.

Ответ №1:

Вы могли бы сделать это с помощью динамического блока PL / SQL:

 declare
  l_query varchar2(100) := 'SELECT * FROM customer  WHERE id < 5';
  l_insert varchar2(100) := 'insert into data ( name, mobile) values ( cust.name,cust.mobile)';
  l_plsql varchar2(4000);
begin
  l_plsql := '
begin
  for cust in (' || l_query || ') loop
    ' || l_insert || ';
  end loop;
end;
';

  dbms_output.put_line(l_plsql);
  execute immediate l_plsql;
end;
/

l_plsql Инструкция завершается как сгенерированный блок PL / SQL с использованием запроса курсора и инструкции insert:

 begin
  for cust in (SELECT * FROM customer  WHERE id < 5) loop
    insert into data ( name, mobile) values ( cust.name,cust.mobile);
  end loop;
end;

db<>скрипта

Но то, что вы можете это сделать, не означает, что вы должны. Это уязвимо для внедрения SQL и не кажется очень безопасным, разумным или эффективным способом обработки данных в вашей системе.