plsql过程重复的代码行.试图做得更好 [英] plsql procedure repetitive line of code. trying to make in better way

问题描述

我有一个场景.如果用户传递参数 Entity_type ，则仅需要为该实体(表)插入数据.如果他没有传递任何参数，那么您需要将所有必需表的数据插入到一个表中.

I have a scenario. If user pass the parameter Entity_type then you need to insert data for that entity (table) only. If he doesn't pass any parameter then you need to insert data for all the required tables into one table.

所以我们有表 DYNAMICENTITYGTT ，如果通过了，它将从 Item 表中获取数据；如果通过了，则从 org 表中获取数据.但是，如果proc中的 Entity_type 参数为null，它将从两个表中获取数据.

so We have table DYNAMICENTITYGTT which will take data from Item table if it is passed, org table if it is passed. But it will take data from both the table if Entity_type parameter is null in proc..

它还将存储另一列，具体取决于 UPDATE_MODE 的类型，即添加或删除.目标表是相同的.源表及其列名不同，但类型相同.

Also it will store another column depending on the type of UPDATE_MODE means add or delete. Target table is same. source table and its column name are different but type are same.

我已经按照以下步骤编写了相同的程序.

I have written below procedure for the same.

无论如何，我只是要求那里有更好的代码.我的意思是，这可以用更聪明的方式写吗?因为我要重复多行.我给出了2个实体的示例，但有7个实体，因此代码将非常庞大.

I am only requesting is there anyway to make this code better. I mean can this be written in more smarter way? Because I am repeating multiple lines. I have given example of 2 entities but there are 7 so code would be huge.

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;

推荐答案

基本上，我看到两个带有两个确定其内容的变量的插入.您是从select语句插入的，因此可以在条件不符合预期的情况下操纵这些选择以不返回任何值.

Basically I see two inserts with two variables determining its content. You are inserting from select statement, therefore you can manipulate these selects to return no values when conditions are not as expected.

对于参数 p_update_mode ，如果它包含值"delete"，则很容易.如果它包含值"add"，则插入"delete".插入添加".

For parameter p_update_mode it is easy, if it contains value "delete" insert "delete", if it contains value "add" insert "add".

对于参数 p_entity_type ，当值是"NULL"时，我们从两个选择中插入；如果值是"ITEM"，则仅从 itemde 表中插入.并且仅在 orgde 表中显示(如果值是"ORG").

For parameter p_entity_type we insert from both selects when its value is "NULL", only from itemde table if value is "ITEM" and only from orgde table if value is "ORG".

现在，如果 p_entity_type 中存在一些无效值，则所有选择都不会产生数据，因为我们只能识别"NULL"，"ITEM".和"ORG".但是对于参数 p_update_mode ，我们直接修改该值并在插入中使用它，因此最好检查一下输入值是否对我们有效.

Now in case there is some invalid value in p_entity_type none of the selects will produce data as we only recognise "NULL", "ITEM" and "ORG". But for parameter p_update_mode we directly modify the value and use it in insert, therefore it would be better to perform some check if the input value is valid for us.

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个插入，正如我认为的(如果我错了，请告诉我)，每个实体在不同的表中都有自己的数据集.

As you write you have 7 entities therefore this approach would lead to have 7 inserts, as I believe (and let me know if I am wrong) each entity has its own set of data in different tables.

也可以将所有这些表连接起来，并使其成为单个插入，如下例所示，每个新实体都意味着仅将新选择添加到语句的 WITH 部分中.但是我不确定这种情况下的性能.那将取决于您的桌子有多满.

There is also possibility to join all these tables and make it into single insert as example below, and every new entity would mean only adding new select into WITH part of the statement. But I am not sure about performance in this case. That would depend on how full your tables are.

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;

即使这对您造成麻烦，您也可以创建一个 VIEW ，在其中执行 UNION s并将该 WITH 从 PROCEDURE ，并在新实体中将选择添加到 VIEW 中，而不是 PROCEDURE .

And even if this gets messy for you, you can create a VIEW where you do the UNIONs and remove that WITH from PROCEDURE and with new entities adding selects into VIEW instead of PROCEDURE.

这篇关于plsql过程重复的代码行.试图做得更好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！