On Jun 6, 1:03 pm, charly <[email protected]> wrote:
> 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!!
It appears you are trying to re-invent the wheel; look at the
following straight SQL example that does what you are asking:
SQL> create table empm as select * from emp where deptno = 10;
Table created.
SQL>
SQL> merge into empm m
2 using emp e
3 on ( e.EMPNO = m.EMPNO )
4 when matched then
5 update set job='janitor' where ename='KING' ;
1 row merged.
SQL>
SQL> select * From empm;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7839 KING janitor 17-NOV-81
5000 10
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> merge into empm m
2 using emp e
3 on ( e.EMPNO = m.EMPNO )
4 when matched then
5 update set job='janitor' where ename='KING'
6 when not matched then
7 insert (m.empno, m.ename, m.job)
8 values (e.empno, e.ename, e.job)
9 where e.deptno=20;
6 rows merged.
SQL>
SQL> select * From empm;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7839 KING janitor 17-NOV-81
5000 10
7934 MILLER CLERK 7782 23-JAN-82
1300 10
7788 SCOTT ANALYST
7566 JONES MANAGER
7902 FORD ANALYST
7369 SMITH CLERK
7876 ADAMS CLERK
8 rows selected.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> merge into empm m
2 using emp e
3 on ( e.EMPNO = m.EMPNO )
4 when not matched then
5 insert (m.empno, m.ename, m.job)
6 values (e.empno, e.ename, e.job)
7 where e.deptno=20
8 when matched then
9 update set job='janitor' where ename='KING' ;
6 rows merged.
SQL>
SQL> select * From empm;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7839 KING janitor 17-NOV-81
5000 10
7934 MILLER CLERK 7782 23-JAN-82
1300 10
7788 SCOTT ANALYST
7566 JONES MANAGER
7902 FORD ANALYST
7369 SMITH CLERK
7876 ADAMS CLERK
8 rows selected.
SQL>
As you can see you can write the merge to process matched or unmatched
records 'first'.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
