Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE
- codigo4 VARCHAR2(4);
- codigo3 VARCHAR2(4);
- id_con_freguesia NUMBER;
- local_freguesia NUMBER;
- local_codigo4 VARCHAR2(4);
- local_codigo3 VARCHAR2(4);
- s_num NUMBER;
- BEGIN
- s_num :=0;
- FOR i IN (SELECT f2.id_contato, f2.cod_postal, f2.id_freguesia FROM gen_contato f2 WHERE id_freguesia IS NULL) LOOP
- BEGIN
- SELECT id_contato, SUBSTR(cod_postal,1,4), SUBSTR(cod_postal,6,3) INTO id_con_freguesia, codigo4, codigo3 FROM gen_contato WHERE id_contato = i.id_contato;
- FOR j IN (SELECT f1.codigo_postal_4, f1.codigo_postal_3, f1.id_freguesia INTO local_codigo4,local_codigo3,local_freguesia FROM gen_localidade f1 WHERE local_codigo4 = codigo4 AND local_codigo3 = codigo3) LOOP
- BEGIN
- s_num := s_num +1;
- EXCEPTION WHEN OTHERS
- THEN NULL;
- END;
- END LOOP;
- IF s_num = 1
- THEN
- UPDATE gen_contato SET id_freguesia = local_freguesia,
- updated_by = 'luis.macedo',
- update_date = SYSDATE
- WHERE id_contato = id_con_freguesia AND cod_postal = codigo4||'-'||codigo3;
- END IF;
- s_num := 0;
- EXCEPTION WHEN OTHERS
- THEN NULL;
- END;
- END LOOP;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement