การเพิ่มประสิทธิภาพโค้ดใน PL / SQL ทำให้เหมาะสม รหัสกำลังทำงาน แต่ไม่ถูกต้อง

Aug 20 2020

ฉันมีโต๊ะ 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)

เราต้องแทรกข้อมูลใน DYNAMICENTITYGTT จาก ORGDE และ ITEMDE Action in DYNAMICENTITYGTT will be 'update' where CREATION_DATE>max_last_update_dateAction DYNAMICENTITYGTT will be 'add' where CREATION_DATE<max_last_update_dateถ้ามี p_entity_type ก็จะแทรกข้อมูลสำหรับเอนทิตีนั้นมิฉะนั้นจะแทรกสำหรับทั้งสองตาราง

ฉันได้เขียนโค้ดด้านล่าง ฉันต้องการปรับปรุงและทำให้ดีขึ้น

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;  

คุณช่วยแนะนำการปรับปรุงโค้ดได้ไหม

คำตอบ

2 KayaNatsumi Aug 20 2020 at 20:59

สิ่งนี้จะคล้ายกับคำตอบก่อนหน้านี้ที่บรรทัดรหัสซ้ำ ๆ ของกระบวนการ plsql พยายามที่จะทำในทางที่ดีขึ้น

สิ่งที่เราทำตอนนี้คือการเพิ่มJOINไปที่โต๊ะที่มีbatch_run_detailsและกรณีที่จะกำหนดสำหรับแต่ละแถวว่าการกระทำที่จะมีการแทรกอยู่บนพื้นฐานและcreation_datemax_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;

และเพื่อให้เสร็จสิ้นจากโพสต์ก่อนหน้านี้ให้ใส่เวอร์ชันแทรกเดียวด้วย:

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;
2 EdStevens Aug 20 2020 at 20:51

สิ่งแรกที่ฉันจะปรับปรุงคือฟอร์แมตเพื่อให้อ่านได้ ฉันใช้เวลาในการจัดรูปแบบน้อยกว่าที่ใช้ในการเขียนประโยคนี้:

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' แสดงว่าเป็นตารางชั่วคราวส่วนกลางดังนั้นฉันคาดหวังให้คุณทำบางอย่างกับตารางนั้นในเซสชันเดียวกัน

1 ThorstenKettner Aug 20 2020 at 21:30

คุณสามารถทำได้ในคำสั่งแทรกเดียวถ้าคุณต้องการ เพียงใช้UNION ALLเพื่อรวมผลการค้นหาเข้าด้วยกัน ด้วยCASE WHENคุณสามารถตัดสินใจว่าจะเขียนหรือ'add''update'

ฉันยังตั้งสมมติฐานที่นี่:

  1. คุณไม่เพียง แต่ต้องการที่จะเขียนแถวสำหรับcreation_date น้อยหรือมากขึ้นกว่าmax_last_update_dateแต่ยังเมื่อทั้งสองมีความเท่าเทียมกัน
  2. แถวที่คัดลอกมาorgdeจะต้องมี entity_type เสมอ'ORG'(ไม่ใช่ null เมื่อ p_entity_type เป็นโมฆะ) เหมือนกันสำหรับitemdeและ'ITEM'.
  3. แถวที่คัดลอกมาorgdeจะได้รับแฟล็ก'update'/ 'add'ขึ้นอยู่กับ batch_run_details โดยที่ entity_type = 'ORG'(ไม่ใช่ null เมื่อ p_entity_type เป็นโมฆะ) เหมือนกันสำหรับitemdeและ'ITEM'.

ขั้นตอน:

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
    insert into dynamicentitygtt (entity_type, entity_id, entity_code, synonyms, action)
    select 
      'ORG', org_id, org_name, org_desc,
      case when creation_date > 
            (select max_last_update_date from batch_run_details where entity_type = 'ORG')
           then 'add' 
           else 'update'
      end
    from orgde
    where upper(p_entity_type) = 'ORG' or p_entity_type is null
    union all
    select 
      'ITEM', item_id, item_name, item_desc,
      case when creation_date >
            (select max_last_update_date from batch_run_details where entity_type = 'ITEM')
           then 'add' 
           else 'update'
      end
    from itemde
    where upper(p_entity_type) = 'ITEM' or p_entity_type is null;
  end if;
end update_dynamic_entity;

หากคุณต้องการสิ่งนี้ดีกว่าโดยใช้คำสั่งแยกต่างหาก (เช่นไม่UNION ALL) ฉันจะย้ายWHEREเงื่อนไขออกนอกแบบสอบถามอีกครั้ง

  if upper(p_update_mode) = 'INCREMENTAL' then
    if upper(p_entity_type) = 'ORG' or p_entity_type is null then
      insert into dynamicentitygtt (entity_type, entity_id, entity_code, synonyms, action)
      ...
      from orgde;
    end if;
    
    if upper(p_entity_type) = 'ITEM' or p_entity_type is null then
      insert into dynamicentitygtt (entity_type, entity_id, entity_code, synonyms, action)
      ...
      from itemde;
    end if;
  end if;