在 PL/SQL 中优化代码.使其适当.代码正在运行但不正确 [英] Optimizing code in PL/SQL. Making it to proper. Code is running but not proper

问题描述

我有 3 套桌子.源表

I have 3 set of tables. Source table

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 中插入数据.DYNAMICENTITYGTT 中的操作将是更新"，其中 CREATION_DATE>max_last_update_dateDYNAMICENTITYGTT 中的操作将是 'add' where CREATION_DATE如果存在 p_entity_type，那么它将为该实体插入数据，否则它将为两个表插入.

We have to insert data in DYNAMICENTITYGTT from ORGDE and ITEMDE. Action in DYNAMICENTITYGTT will be 'update' where CREATION_DATE>max_last_update_date Action in DYNAMICENTITYGTT will be 'add' where CREATION_DATE<max_last_update_date if p_entity_type is present then it will insert data for that entity else it will insert for both tables.

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

I have written below code. i want to improve it and make it better.

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;  

您能否提出改进代码的建议.

Can you please suggest improvement on code.

推荐答案

这将类似于 plsql 过程重复的代码行.努力做得更好.

我们现在要做的是将 JOIN 添加到包含 batch_run_details 和 case 的表中，它将根据 creation_date 和 max_last_update_date.

What we do now is add JOIN to table containing batch_run_details and case that will determine for each row what action it has to insert based on creation_date and max_last_update_date.

CREATE OR REPLACE PROCEDURE update_dynamic_entity(p_entity_type VARCHAR2 DEFAULT NULL,
                                                  p_update_mode VARCHAR2) IS
BEGIN
  IF lower(p_update_mode) <> 'incremental'
  THEN
    RETURN; -- Do nothing if incorrect mode
  END IF;
  --
  INSERT INTO dynamicentitygtt
    (entity_type, entity_id, entity_code, synonyms, action)
    SELECT upper(NVL(p_entity_type, 'ITEM')),
           t.item_id,
           t.item_name,
           t.item_desc,
           CASE
             WHEN t.creation_date > b.max_last_update_date THEN
               'update'
             WHEN t.creation_date < b.max_last_update_date THEN
               'add'
           END
      FROM itemde t
      JOIN batch_run_details b
        ON b.entity_type = 'ITEM'
     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')),
           t.org_id,
           t.org_name,
           t.org_desc,
           CASE
             WHEN t.creation_date > b.max_last_update_date THEN
               'update'
             WHEN t.creation_date < b.max_last_update_date THEN
               'add'
           END
      FROM orgde t
      JOIN batch_run_details b
        ON b.entity_type = 'ORG'
     WHERE upper(p_entity_type) = 'ORG'
        OR p_entity_type IS NULL;
END update_dynamic_entity;

并且只是为了完成上一篇文章，单插入版本:

And just for completion from previous post, single insert version as well:

CREATE OR REPLACE PROCEDURE update_dynamic_entity(p_entity_type VARCHAR2 DEFAULT NULL,
                                                  p_update_mode VARCHAR2) IS
BEGIN
  IF lower(p_update_mode) <> 'incremental'
  THEN
    RETURN;
  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,
              creation_date
        FROM itemde
      UNION ALL
      -- ORG table
      SELECT 'ORG' entity_type, -- This separates inserted values
              org_id,
              org_name,
              org_desc,
              creation_date
        FROM orgde
      -- NEXT entity table
      )
    SELECT upper(t.entity_type),
           t.data_id,
           t.data_name,
           t.data_desc,
           CASE
             WHEN t.creation_date > b.max_last_update_date THEN
               'update'
             WHEN t.creation_date < b.max_last_update_date THEN
               'add'
           END
      FROM data_view t
      JOIN batch_run_details b
        ON b.entity_type = t.entity_type
     WHERE upper(p_entity_type) = t.entity_type
        OR p_entity_type IS NULL;
END update_dynamic_entity;

这篇关于在 PL/SQL 中优化代码.使其适当.代码正在运行但不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！