Optimización de código en PL / SQL. Haciéndolo adecuado. El código se está ejecutando pero no es adecuado
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_date
acción DYNAMICENTITYGTT will be 'add' where CREATION_DATE<max_last_update_date
si 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
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 JOIN
a la tabla que contiene un batch_run_details
caso que determinará para cada fila qué acción tiene que insertar en función de creation_date
y 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;
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.
Puede hacer esto en una declaración de inserción, si lo desea. Utilice UNION ALL
para unir los resultados de la consulta. Con CASE WHEN
puedes decidir si escribir 'add'
o 'update'
.
También estoy haciendo algunas suposiciones aquí:
- No solo desea escribir filas para
creation_date
menor o mayor quemax_last_update_date
, sino también cuando los dos son iguales . - Las filas copiadas
orgde
siempre tendrán el entity_type'ORG'
(no nulo cuando p_entity_type es nulo). Lo mismo paraitemde
y'ITEM'
. - Las filas copiadas de
orgde
obtendrán el indicador'update'
/'add'
dependiendo de batch_run_details donde entity_type ='ORG'
(no nulo cuando p_entity_type es nulo). Lo mismo paraitemde
y'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 WHERE
condiciones 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;