Armazene o resultado da consulta negativa com várias colunas em uma variável no Oracle PL / SQL

Question

Estou usando o código abaixo para buscar os IDs de projeto extras presentes em TABLE_ONE em comparação com TABLE_TWO ( resultado do armazenamento de fonte da consulta negativa (lista de varchars) em uma variável no 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;

Agora, quero adicionar nomes de projetos correspondentes também à segunda tabela. Quaisquer project_ids extras presentes em TABLE_ONE em comparação com TABLE_TWO devem ser inseridos em TABLE_TWO junto com os nomes de projeto correspondentes. Como fazer isso? Observe que preciso armazenar o resultado negativo da consulta em uma variável, pois executarei várias etapas com o resultado.

Answer 1

SYS.ODCINUMBERLISTé apenas uma lista onde cada registro possui um único número. Você precisa usar um novo tipo de registro para armazenar os vários campos em cada linha.

Você pode definir um tipo de registro para usar no lugar de SYS.ODCINUMBERLIST. Mas estou inclinado a mover a SELECTinstrução para ser um cursor explícito, para que você possa definir um novo tipo como cursor %ROWTYPE. Dessa forma, seu tipo de registro e a instrução select são sempre consistentes, mas muda um pouco a estrutura do seu código.

Esta é a abordagem:

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;

Uma observação sobre BULK COLLECT(aqui e em sua postagem original) - os dados que você está coletando em massa são armazenados na memória PGA. Portanto, não use este código porque, se houver uma alteração, o número de novos registros não será razoável (talvez mais do que alguns milhares).