plsqlプロシージャの繰り返しコード行。より良い方法で作ろうとしています

Aug 21 2020

シナリオがあります。ユーザーがパラメーターを渡す場合はEntity_type、そのentitytable)のデータのみを挿入する必要があります。彼がパラメータを渡さない場合は、必要なすべてのテーブルのデータを1つのテーブルに挿入する必要があります。

したがって、渡された場合はテーブルDYNAMICENTITYGTTからデータを取得Itemし、渡された場合はテーブルからデータを取得するテーブルがありorgます。ただしEntity_type、proc。でパラメータがnullの場合は、両方のテーブルからデータを取得します。

また、UPDATE_MODE追加または削除する手段のタイプに応じて、別の列を格納します。ターゲットテーブルは同じです。ソーステーブルとその列名は異なりますが、タイプは同じです。

私は同じための手順を以下に書きました。

私はこのコードをより良くするためにとにかくそこにあることを要求しているだけです。つまり、これをもっと賢く書くことができるのでしょうか?複数行繰り返しているからです。2つのエンティティの例を示しましたが、7つあるため、コードは巨大になります。

CREATE OR REPLACE procedure UPDATE_DYNAMIC_ENTITY(ENTITY_TYPE varchar2 default null,UPDATE_MODE varchar2)
Is
x number;
BEGIN
IF UPPER(entity_type)='ITEM' then
    if upper(UPDATE_MODE)='DELETE' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,Item_id,item_name,item_desc,'delete' from ITEMDE;
    ELSIF lower(UPDATE_MODE)='add' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,Item_id,item_name,item_desc,'add' from ITEMDE;
    END IF;
ELSIF UPPER(entity_type)='ORG' then
    if upper(UPDATE_MODE)='DELETE' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,ORG_id,org_name,org_desc,'delete' from ORGDE;
    ELSIF lower(UPDATE_MODE)='add' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,ORG_id,org_name,org_desc,'add' from ORGDE;
    END IF;
ELSE
   if upper(UPDATE_MODE)='DELETE' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,Item_id,item_name,item_desc,'delete' from ITEMDE;
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,ORG_id,org_name,org_desc,'delete' from ORGDE;
    ELSIF lower(UPDATE_MODE)='add' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,Item_id,item_name,item_desc,'add' from ITEMDE;
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select Entity_type,ORG_id,org_name,org_desc,'add' from ORGDE;
   END IF;
   
END IF;
END UPDATE_DYNAMIC_ENTITY;

回答

1 KayaNatsumi Aug 20 2020 at 22:10

基本的に、コンテンツを決定する2つの変数を持つ2つの挿入が表示されます。selectステートメントから挿入しているため、これらのselectを操作して、条件が期待どおりでない場合に値を返さないようにすることができます。

パラメータのp_update_mode場合、値「delete」が含まれている場合は「delete」を挿入し、値「add」が含まれている場合は「add」を挿入するのは簡単です。

パラメータp_entity_typeについては、値が「NULL」の場合は両方の選択から挿入し、itemde値が「ITEM」のorgde場合はテーブルからのみ、値が「ORG」の場合はテーブルからのみ挿入します。

p_entity_type「NULL」、「ITEM」、「ORG」のみを認識するため、いずれの選択にも無効な値が含まれている場合、データは生成されません。ただし、パラメータp_update_modeについては、値を直接変更して挿入に使用するため、入力値が有効かどうかを確認することをお勧めします。

CREATE OR REPLACE PROCEDURE update_dynamic_entity(p_entity_type VARCHAR2 DEFAULT NULL,
                                                  p_update_mode VARCHAR2) IS
BEGIN
  IF lower(p_update_mode) NOT IN ('add', 'delete')
  THEN
    RAISE VALUE_ERROR; -- maybe use raise_application_error for more details about problem
  END IF;
  --
  INSERT INTO dynamicentitygtt
    (entity_type, entity_id, entity_code, synonyms, action)
    SELECT upper(NVL(p_entity_type, 'ITEM')), item_id, item_name, item_desc, lower(p_update_mode)
      FROM itemde
     WHERE upper(p_entity_type) = 'ITEM'
        OR p_entity_type IS NULL;
  --
  INSERT INTO dynamicentitygtt
    (entity_type, entity_id, entity_code, synonyms, action)
    SELECT upper(NVL(p_entity_type, 'ORG')), org_id, org_name, org_desc, lower(p_update_mode)
      FROM orgde
     WHERE upper(p_entity_type) = 'ORG'
        OR p_entity_type IS NULL;
END update_dynamic_entity;

あなたが書くとき、あなたは7つのエンティティを持っているので、私が信じているように(そして私が間違っているかどうか私に知らせて)、このアプローチは7つの挿入を持つことになります。各エンティティは異なるテーブルに独自のデータセットを持っています。

以下の例のように、これらすべてのテーブルを結合して単一の挿入にすることもできます。新しいエンティティはすべてWITH、ステートメントの一部に新しいselectを追加するだけです。しかし、この場合のパフォーマンスについてはよくわかりません。それはあなたのテーブルがどれだけいっぱいかによるでしょう。

CREATE OR REPLACE PROCEDURE update_dynamic_entity(p_entity_type VARCHAR2 DEFAULT NULL,
                                                  p_update_mode VARCHAR2) IS
BEGIN
  IF lower(p_update_mode) NOT IN ('add', 'delete')
  THEN
    RAISE VALUE_ERROR; -- maybe use raise_application_error for more details about problem
  END IF;
  --
  INSERT INTO dynamicentitygtt
    (entity_type, entity_id, entity_code, synonyms, action)
    WITH data_view AS
     ( -- ITEM table
      SELECT 'ITEM' entity_type, -- This separates inserted values
              item_id data_id,
              item_name data_name,
              item_desc data_desc
        FROM itemde
      UNION ALL
      -- ORG table
      SELECT 'ORG' entity_type, -- This separates inserted values
              org_id,
              org_name,
              org_desc
        FROM orgde
      -- NEXT entity table
      )
    SELECT upper(entity_type), data_id, data_name, data_desc, lower(p_update_mode)
      FROM data_view
     WHERE upper(p_entity_type) = entity_type
        OR p_entity_type IS NULL;
END update_dynamic_entity;

そして、これが面倒になったとしても、sVIEWを実行する場所を作成し、それUNIONを削除しWITHPROCEDURE、新しいエンティティVIEWPROCEDURE。の代わりにselectを追加することができます。