Oracle PL / SQLの変数に複数の列を持つマイナスクエリの結果を格納する

Aug 18 2020

以下のコードを使用して、TABLE_TWOと比較してTABLE_ONEに存在する追加のプロジェクトIDをフェッチしています(Oracle PL / SQLの変数のマイナスクエリ(varcharのリスト)のソースストア結果)

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;

次に、対応するプロジェクト名を2番目のテーブルにも追加します。TABLE_TWOと比較してTABLE_ONEに存在する余分なproject_idは、対応するプロジェクト名とともに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(ここと元の投稿の両方で)についての1つのメモ-一括収集しているデータはPGAメモリに保存されます。したがって、変更があった場合は、このコードをそのまま使用しないでください。新しいレコードの数は不合理になります(おそらく数千を超える)。