Optimización de código en PL / SQL. Haciéndolo adecuado. El código se está ejecutando pero no es adecuado

Aug 20 2020

Tengo 3 juegos de mesas. Tabla de origen

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

Tabla de destino

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

Tabla de condiciones

BATCH_RUN_DETAILS(ENTITY_TYPE,LAST_RUN_DATE,MAX_LAST_UPDATE_DATE)

Tenemos que insertar datos en DYNAMICENTITYGTT de ORGDE y ITEMDE. Acción en DYNAMICENTITYGTT will be 'update' where CREATION_DATE>max_last_update_dateacción DYNAMICENTITYGTT will be 'add' where CREATION_DATE<max_last_update_datesi p_entity_type está presente, insertará datos para esa entidad, de lo contrario, insertará para ambas tablas.

He escrito el siguiente código. quiero mejorarlo y hacerlo mejor.

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;  

¿Puede sugerir una mejora en el código?

Respuestas

2 KayaNatsumi Aug 20 2020 at 20:59

Esto sería similar a la respuesta anterior en la línea repetitiva de código del procedimiento plsql. tratando de hacer de una mejor manera .

Lo que hacemos ahora es agregar JOINa la tabla que contiene un batch_run_detailscaso que determinará para cada fila qué acción tiene que insertar en función de creation_datey 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;

Y solo para completar la publicación anterior, también la versión de inserción única:

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

Lo primero que haría para mejorarlo es formatearlo para que sea legible. Me tomó menos tiempo para formatearlo que para escribir esta oración:

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;  

Lo segundo que haría es cambiar el nombre de DYNAMICENTITYGTT a un nombre legible, DYNAMIC_ENTITY_GTT. (De hecho, lo codifico en minúsculas. Lo muestro en mayúsculas porque así es en el diccionario de datos. De hecho, escribo todo mi código en minúsculas).

¿Por qué está insertando dos filas casi idénticas en DYNAMICENTITYGTT ('agregar' y 'actualizar')?

El nombre de esa tabla, con 'GTT' sugiere que es una tabla temporal global, por lo que espero que realmente haga algo con ella, en la misma sesión.

1 ThorstenKettner Aug 20 2020 at 21:30

Puede hacer esto en una declaración de inserción, si lo desea. Utilice UNION ALLpara unir los resultados de la consulta. Con CASE WHENpuedes decidir si escribir 'add'o 'update'.

También estoy haciendo algunas suposiciones aquí:

  1. No solo desea escribir filas para creation_date menor o mayor que max_last_update_date, sino también cuando los dos son iguales .
  2. Las filas copiadas orgdesiempre tendrán el entity_type 'ORG'(no nulo cuando p_entity_type es nulo). Lo mismo para itemdey 'ITEM'.
  3. Las filas copiadas de orgdeobtendrán el indicador 'update'/ 'add'dependiendo de batch_run_details donde entity_type = 'ORG'(no nulo cuando p_entity_type es nulo). Lo mismo para itemdey 'ITEM'.

El procedimiento:

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;

Si desea esto mejor con declaraciones separadas (es decir, no UNION ALL), movería las WHEREcondiciones fuera de las consultas nuevamente:

  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;