Сохранить результат отрицательного запроса с несколькими столбцами в переменной в Oracle PL / SQL

Aug 18 2020

Я использую приведенный ниже код для извлечения дополнительных идентификаторов проектов, представленных в TABLE_ONE, по сравнению с TABLE_TWO (исходный результат Store минус-запроса (список varchars) в переменной в Oracle PL / SQL )

DECLARE 
  l_missing_id_list SYS.ODCINUMBERLIST;
BEGIN
  SELECT project_id
  BULK COLLECT INTO l_missing_id_list
  FROM 
    (
    SELECT t1.project_id FROM table_one t1
    MINUS
    SELECT t2.project_id FROM table_two t2 );
    
  FORALL i IN l_missing_id_list.FIRST..l_missing_id_list.LAST
    INSERT INTO table_two VALUES ( l_missing_id_list(i) );
    
  COMMIT;
  
  -- Values are now inserted and you have the list of IDs in l_missing_id_list to add to your email.
END;

Теперь я хочу добавить соответствующие имена проектов также во вторую таблицу. Какие бы дополнительные project_id ни присутствовали в TABLE_ONE по сравнению с TABLE_TWO, следует вставить в TABLE_TWO вместе с соответствующими именами проектов. Как это сделать? Обратите внимание, что мне нужно сохранить результат минус-запроса в переменной, так как я буду выполнять несколько шагов с результатом.

Ответы

1 MatthewMcPeak Aug 18 2020 at 21:11

SYS.ODCINUMBERLISTэто просто список, в котором каждая запись имеет единственный номер. Вам необходимо использовать новый тип записи для хранения нескольких полей в каждой строке.

Вы можете определить тип записи, который будет использоваться вместо SYS.ODCINUMBERLIST. Но я был бы склонен сделать этот SELECTоператор явным курсором, чтобы вы могли определить новый тип как курсор %ROWTYPE. Таким образом, ваш тип записи и оператор select всегда согласованы, но это немного меняет структуру вашего кода.

Вот такой подход:

DECLARE 

  CURSOR c_select IS
      SELECT project_id, project_name
      FROM 
        (
          SELECT t1.project_id, t1.project_name FROM table_one t1
          MINUS
          SELECT t2.project_id, t2.project_name FROM table_two t2 );

  TYPE l_missing_row_list_typ IS TABLE OF c_select%ROWTYPE;
  l_missing_row_list l_missing_row_list_typ;
  
BEGIN
  OPEN c_select;
  FETCH c_select BULK COLLECT INTO l_missing_row_list;
  CLOSE c_select;
  
  FORALL i IN l_missing_row_list.FIRST..l_missing_row_list.LAST
    INSERT INTO table_two VALUES ( l_missing_row_list(i).project_id, l_missing_row_list(i).project_name );
    
  COMMIT;
  
  -- Values are now inserted and you have the list of IDs in l_missing_row_list to add to your email.
END;

Одно замечание по поводу BULK COLLECT(как здесь, так и в вашем исходном сообщении) - данные, которые вы собираете массово, хранятся в памяти PGA. Так что не используйте этот код как есть, если произойдет изменение, количество новых записей будет необоснованным (может быть, более нескольких тысяч).