oracle - 优化 PL/SQL 中的代码。使其正确。代码正在运行但不正确

Question

我有 3 组桌子。源表

ORGDE(ORG_ID,ORG_NAME,ORG_DESC,CREATION_DATE,LAST_UPDATE_DATE)       
ITEMDE(ITEM_ID,ITEM_NAME,ITEM_DESC,CREATION_DATE,LAST_UPDATE_DATE)   

目标表

DYNAMICENTITYGTT(ENTITY_TYPE,ENTITY_ID,ENTITY_CODE,SYNONYMS,ACTION)

条件表

BATCH_RUN_DETAILS(ENTITY_TYPE,LAST_RUN_DATE,MAX_LAST_UPDATE_DATE)

我们必须从 ORGDE 和 ITEMDE 向 DYNAMICENTITYGTT 中插入数据。如果p_entity_typeDYNAMICENTITYGTT will be 'update' where CREATION_DATE>max_last_update_date 存在DYNAMICENTITYGTT will be 'add' where CREATION_DATE<max_last_update_date ，那么它将为该实体插入数据，否则它将为两个表插入。

我写了下面的代码。我想改进它，让它变得更好。

CREATE OR REPLACE procedure UPDATE_DYNAMIC_ENTITY(P_ENTITY_TYPE varchar2 default null,P_UPDATE_MODE varchar2)
IS
BEGIN
IF UPPER(P_UPDATE_MODE)='INCREMENTAL'
THEN
 IF UPPER(p_entity_type)='ORG' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select P_Entity_type,ORG_id,org_name,org_desc,'add' from ORGDE where creation_date>(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
     INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select P_Entity_type,ORG_id,org_name,org_desc,'update' from ORGDE where creation_date<(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
 ELSIF UPPER(p_entity_type)='ITEM' then
      INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select P_Entity_type,item_id,item_name,item_desc,'add' from ITEMDE where creation_date>(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
     INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select P_Entity_type,item_id,item_name,item_desc,'update' from ITEMDE where creation_date<(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
 ELSIF P_ENTITY_TYPE=NULL THEN
     --Reading from org
     INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select p_Entity_type,ORG_id,org_name,org_desc,'add' from ORGDE where creation_date>(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
     INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select p_Entity_type,ORG_id,org_name,org_desc,'update' from ORGDE where creation_date<(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
     --reading from item
     INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select p_Entity_type,item_id,item_name,item_desc,'add' from ITEMDE where creation_date>(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
     INSERT INTO DYNAMICENTITYGTT(Entity_type,Entity_id,Entity_code,Synonyms,Action) select p_Entity_type,item_id,item_name,item_desc,'update' from ITEMDE where creation_date<(select max_last_update_date from BATCH_RUN_DETAILS where ENTITY_TYPE=P_ENTITY_TYPE);
  END IF;
END IF;
END UPDATE_DYNAMIC_ENTITY;  

您能否建议改进代码。

标签： oracleplsqloracle11goracle-sqldeveloperplsqldeveloper

Answer 1

我要改进它的第一件事是格式化它，使其可读。我花的时间比写这句话的时间少：

CREATE OR replace PROCEDURE Update_dynamic_entity(
p_entity_type VARCHAR2 DEFAULT NULL,
p_update_mode VARCHAR2)
IS
BEGIN
    IF Upper(p_update_mode) = 'INCREMENTAL' THEN
      IF Upper(p_entity_type) = 'ORG' THEN
        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT p_entity_type,
               org_id,
               org_name,
               org_desc,
               'add'
        FROM   orgde
        WHERE  creation_date > (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);

        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT p_entity_type,
               org_id,
               org_name,
               org_desc,
               'update'
        FROM   orgde
        WHERE  creation_date < (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);
      ELSIF Upper(p_entity_type) = 'ITEM' THEN
        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT p_entity_type,
               item_id,
               item_name,
               item_desc,
               'add'
        FROM   itemde
        WHERE  creation_date > (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);

        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT p_entity_type,
               item_id,
               item_name,
               item_desc,
               'update'
        FROM   itemde
        WHERE  creation_date < (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);
      ELSIF p_entity_type = NULL THEN
        --Reading from org
        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT entity_type,
               org_id,
               org_name,
               org_desc,
               'add'
        FROM   orgde
        WHERE  creation_date > (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);

        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT entity_type,
               org_id,
               org_name,
               org_desc,
               'update'
        FROM   orgde
        WHERE  creation_date < (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);

        --reading from item
        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT entity_type,
               item_id,
               item_name,
               item_desc,
               'add'
        FROM   itemde
        WHERE  creation_date > (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);

        INSERT INTO dynamicentitygtt
                    (entity_type,
                     entity_id,
                     entity_code,
                     synonyms,
                     action)
        SELECT entity_type,
               item_id,
               item_name,
               item_desc,
               'update'
        FROM   itemde
        WHERE  creation_date < (SELECT max_last_update_date
                                FROM   batch_run_details
                                WHERE  entity_type = p_entity_type);
      END IF;
    END IF;
END update_dynamic_entity;  

我要做的第二件事是将 DYNAMICENTITYGTT 的名称更改为可读的名称 DYNAMIC_ENTITY_GTT。（实际上是用小写的。我用大写显示它，因为它在数据字典中就是这样。我实际上用小写写了我的所有代码。）

为什么要将两个几乎相同的行插入 DYNAMICENTITYGTT（'add' 和 'update'）？

该表的名称带有“GTT”表明它是一个全局临时表，所以我希望您在同一个会话中实际使用它。