Stocker le résultat de la requête moins avec plusieurs colonnes dans une variable dans Oracle PL / SQL

Aug 18 2020

J'utilise le code ci-dessous pour récupérer les ID de projet supplémentaires présents dans TABLE_ONE par rapport à TABLE_TWO ( résultat du magasin source de la requête moins (liste de varchars) dans une variable dans 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;

Maintenant, je veux ajouter les noms de projet correspondants également à la deuxième table. Tous les project_ids supplémentaires présents dans TABLE_ONE par rapport à TABLE_TWO doivent être insérés dans TABLE_TWO avec les noms de projet correspondants. Comment faire? Veuillez noter que je dois stocker le résultat de la requête moins dans une variable car je vais effectuer plusieurs étapes avec le résultat.

Réponses

1 MatthewMcPeak Aug 18 2020 at 21:11

SYS.ODCINUMBERLISTest juste une liste où chaque enregistrement a un seul numéro. Vous devez utiliser un nouveau type d'enregistrement pour stocker les multiples champs dans chaque ligne.

Vous pouvez définir un type d'enregistrement à utiliser à la place de SYS.ODCINUMBERLIST. Mais je serais enclin à déplacer l' SELECTinstruction pour qu'elle soit un curseur explicite, afin que vous puissiez définir un nouveau type comme curseur %ROWTYPE. De cette façon, votre type d'enregistrement et l'instruction select sont toujours cohérents, mais cela change un peu la structure de votre code.

Voici cette approche:

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;

Une note sur le BULK COLLECT(ici et dans votre message d'origine) - les données que vous collectez en masse sont stockées dans la mémoire PGA. N'utilisez donc pas ce code tel quel s'il y a un changement, le nombre de nouveaux enregistrements sera déraisonnable (peut-être plus de quelques milliers).