Cursor with Update

Question

Cursor with Update

Navigation

#1 by (0 votes)

0

Greetings from Costa Rica,

I'm starting with Oracle PL / SQL (running the systems race) and I want to build a cursor as a practice to update the registry of names, emails, telephones and cards of the entire database.

The information of clients is in 2 tables only, I already managed to add a 1 to the variable each time the cursor finds a record first I want to try with names and then enter the rest of the fields in the cursor, but I want to differentiate it between people physical (natural) and legal (companies) but I can not do the update for what I would like to see if you can guide me.

  declare 
  v_acum   number;

  cursor c_actualiza
  is
  select a.cod_cia, a.cod_docum,a.tip_docum, a.nom_tercero,  a.nom2_tercero, a.ape1_tercero,a.ape2_tercero,a.tlf_movil,
    b.tlf_numero, b.email,b.tlf_numero_com,b.email_com,b.cta_cte,b.txt_email,b.cod_tarjeta,b.num_tarjeta,
    b.fec_vcto_tarjeta
  from a1001399 a, a1001331 b
  where a.cod_cia = 1
    and a.nom_tercero != 'null'
    and a.tip_docum = b.tip_docum
    and a.cod_docum = b.cod_docum
    and a.cod_docum 
  in ('111810464',
  '111610631',
  '800870150',
  '110550726',
  '111830715',                 
  '111120585',
  '110030332',
  '111460930',
  '3101397485',
  '3101294479',                  
  '01002312815',
  '011203096',
  '012180004480193988',
  '011203096',
  '07469542');
  begin  

  v_acum := 0;

  for reg in c_actualiza loop

  v_acum := v_acum + 1;

      begin        

   UPDATE  a1001399 i
         SET i.nom_tercero = 'PRUEBASCR' || ' - ' || v_acum 
          where i.cod_cia = reg.cod_cia
           and i.tip_docum = reg.tip_docum
           and i.cod_docum = reg.cod_docum;
                 EXCEPTION                   
          when i.tip_docum  IN ( 'CJU' and 'EEX') then
            UPDATE  a1001399 i
         SET i.nom_tercero = 'DESARROLLOCR' || ' - ' || v_acum 
          where i.cod_cia = reg.cod_cia
           and i.tip_docum = reg.tip_docum
           and i.cod_docum = reg.cod_docum;

             --dbms_output.put_line('ERROR');

             END;


     dbms_output.put_line (reg.nom_tercero);

       end loop;

       end;

oracle plsql

asked by Erickpa 09.12.2016 в 00:27

source

1 answer

Problems with combo-box that sends jsp of closing session Error printing a text type record in php using pdo and informix

score 0 · Answer 1

Thanks, jachguate - sstan

I have already managed to update a name and a surname with an IF in the client IDs that I selected from the sample, so as not to update the entire database until I successfully tested the code. I would like to try to achieve this same result with a "CASE"

declare

v_contador number;

cursor tests

is

select a.cod_cia,   ---compañía
       a.cod_docum, --identificacion del cliente
       a.tip_docum, --tipo físico o jurídico
       a.nom_tercero,
       a.nom2_tercero,
       a.ape1_tercero,
       a.ape2_tercero,
       a.tlf_movil,
       b.tlf_numero,
       b.email,
       b.tlf_numero_com,
       b.email_com,
       b.cta_cte,
       b.txt_email,
       b.cod_tarjeta,
       b.num_tarjeta,
       b.fec_vcto_tarjeta
  from a1001399 a, a1001331 b
 where a.cod_cia = b.cod_cia   -- índices de las 2 tablas
   and a.tip_docum = b.tip_docum --índices de las 2 tablas 
   and a.cod_docum = b.cod_docum  -- índices de las 2 tablas
   and a.cod_docum IN ('111810464',
                       '111610631',
                       '110550726',
                       '111830715',
                       '3101640433',
                       '3101124264',
                       '3101403163',
                       '0034300757',
                       '012180004480193988');

begin

v_contador: = 0;

for reg in loop tests

v_contador := v_contador + 1;

update a1001399 x
   set x.nom_tercero  = 'PRUEBASCR' || '_' || v_contador,
       x.nom2_tercero = 'REGISTROS'
 where x.cod_cia = reg.cod_cia
   and x.cod_docum = reg.cod_docum
   and x.tip_docum = reg.tip_docum;
if reg.tip_docum = 'CJU' or reg.tip_docum = 'EEX' then
  update a1001399 x
     set x.nom_tercero  = 'DESARROLLOCR' || '_' || v_contador,
         x.nom2_tercero = 'REGISTROS'
   where x.cod_cia = reg.cod_cia
     and x.cod_docum = reg.cod_docum
     and x.tip_docum = reg.tip_docum;

end if;

commit;

end loop;

end;

The result obtained.

Greetings.