Hello friends, I am currently starting in the area of pl / sql. I wonder
if anyone could give me a hand with a situation. I' am creating a procedure
that updates TARGET TABLE. In case it finds new records in the BASE TABLE.
If there is something new in the BASE TABLE, i have to insert the new
records in the TARGET TABLE.
To acomplish this I have done the following:
PROCEDURE automatizacion_celdas
(
)
IS
-----VARIABLES DEL PROCEDIMIENTO-----
v_id it_bi.cnf_cellsite_optima.id%type;
v_nombre it_bi.cnf_cellsite_optima.nombre%type;
v_latitude it_bi.cnf_cellsite_optima.latitude%type;
v_longitud it_bi.cnf_cellsite_optima.longitude%type;
v_opid it_bi.cnf_cellsite_optima.opid%type;
v_opnombre it_bi.cnf_cellsite_optima.opnombre%type;
v_tecnologia it_bi.cnf_tr_celdas_optima.tecnologia%type;
v_provincia it_bi.cnf_tr_celdas_optima.cod_provincia%type;
v_ciudad it_bi.cnf_tr_celdas_optima.cod_ciudad%type;
v_descripcion it_bi.cnf_tr_celdas_optima.cell_description%type;
v_count number;
v_etapa it_brain.flw_proceso_log.etapa%TYPE;
v_cod_etapa it_brain.flw_proceso_log.cod_etapa%TYPE;
BEGIN
po_cod_err := 'DWH-00000'; --.AUTOMATIZACION_CELDAS: Error
al actualizar datos
UPDATE celdas c
Set
nombre = (SELECT nombre From
Tabla_base ct where ct.id = c.id AND ct.nombre <> c.nombre ),
latitude = (SELECT latitude From
Tabla_base ct where ct.id = c.id AND ct.latitude <> c.latitude ),
longitude = (SELECT longitude From
Tabla_base ct where ct.id = c.id AND ct.nombre <> c.nombre ),
opid = (SELECT opid From Tabla_base
ct where ct.id = c.id AND ct.nombre <> c.nombre ),
opnombre = (SELECT opnombre From
Tabla_base ct where ct.id = c.id AND ct.opid <> c.opid ),
tecnologia = (SELECT tecnologia From
Tabla_base ct where ct.id = c.id AND ct.tecnologia <> c.tecnologia ),
cod_provincia = (SELECT cod_provincia
>From Tabla_base ct where ct.id = c.id AND ct.cod_provincia <>
c.cod_provincia ),
cod_ciudad = (SELECT cod_ciudad From
Tabla_base ct where ct.id = c.id AND ct.cod_ciudad <> c.cod_ciudad ),
cell_description = (SELECT cell_description
>From Tabla_base ct where ct.id = c.id AND ct.cell_description <>
c.cell_description );
po_cod_err := 'DWH-00000'; --.AUTOMATIZACION_CELDAS:
Error al iniciar iteración
FOR x in (select id from it_bi.cnf_tr_celdas_optima)
LOOP
---------- Inicializacion de Variables------------
v_id := 0;
v_nombre := 0;
v_latitude := 0;
v_longitud := 0;
v_opid := 0;
v_opnombre := 0;
v_tecnologia := 0;
v_provincia := 0;
v_ciudad := 0;
v_descripcion := 0;
v_count := 0;
Begin
-----------Busco nuevos registro en la tabla-----------------
select id, nombre, latitude, longitude, opid,
opnombre,'Cell '|| opid ||' - '||substr(nombre, 1,11)||' -'||' GSM '
into v_id,
v_nombre,v_latitude,v_longitud,v_opid,v_opnombre, v_descripcion
from Tabla_base o
where not EXISTS (select 1 from celdas t where t.id = x.id
);
po_cod_err := 'DWH-00000'; --.AUTOMATIZACION_CELDAS:
Error al insertar datos
-----------inserto registros en caso de haber
nuevos-----------------
v_count := sql%rowcount;
IF v_count >= 1 then
insert into celdas
(id,
nombre,
latitude,
longitude,
opid,
opnombre,
tecnologia,
cod_provincia,
cod_ciudad,
cell_description,
fch_inicio,
fch_fin,
estado_actual
)
VALUES (v_id,
v_nombre,
v_latitude,
v_longitud,
v_opid,
v_opnombre,
'GSM',
null,
null,
v_descripcion,
null,
null,
null
);
commit;
END IF;
end;
end loop;
END;
What I want is to find some function to tell me if the select bring records
(if there are records), and if it is >= 1 then to insert the records in the
TARGET TABLE. I'm using the sql%rowcount and does not do what I seek . I
will appreciate if someone can give me a guide or suggest me something.
Thank you very much! I'll wait for your answers.
Greetings!!
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
