Thank you very much for your replies. It was very helpfull for me. 2012/6/8 ddf <orat...@msn.com>
> > > On Jun 6, 1:03 pm, charly <augustoada...@gmail.com> 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 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 > -- 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
