Almacene el resultado de la consulta negativa con varias columnas en una variable en Oracle PL / SQL

Aug 18 2020

Estoy usando el siguiente código para obtener las ID de proyecto adicionales presentes en TABLE_ONE en comparación con TABLE_TWO ( resultado de la tienda de origen de la consulta negativa (lista de varchars) en una variable en 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;

Ahora quiero agregar los nombres de proyectos correspondientes también a la segunda tabla. Cualquier project_ids adicional presente en TABLE_ONE en comparación con TABLE_TWO debe insertarse en TABLE_TWO junto con los nombres de proyecto correspondientes. ¿Cómo hacerlo? Tenga en cuenta que necesito almacenar el resultado de la consulta negativa en una variable, ya que realizaré varios pasos con el resultado.

Respuestas

1 MatthewMcPeak Aug 18 2020 at 21:11

SYS.ODCINUMBERLISTes solo una lista donde cada registro tiene un solo número. Necesita usar un nuevo tipo de registro para almacenar los múltiples campos en cada fila.

Puede definir un tipo de registro para usar en lugar de SYS.ODCINUMBERLIST. Pero me inclinaría a mover la SELECTdeclaración para que sea un cursor explícito, de modo que pueda definir un nuevo tipo como cursor %ROWTYPE. De esa manera, su tipo de registro y la declaración de selección siempre son consistentes, pero cambia un poco la estructura de su código.

Aquí está ese enfoque:

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;

Una nota sobre BULK COLLECT(tanto aquí como en su publicación original): los datos que está recopilando de forma masiva se almacenan en la memoria PGA. Por lo tanto, no use este código como está si hay un cambio, el número de registros nuevos no será razonable (tal vez más de unos pocos miles).