Menu de navegação

[Marco BorgesParticipante]

Senhores, preciso realizar a atualização com base num cursor.

Preciso encontrar o CPF porém ele vem sem DAC e inclui-lo numa coluna de uma tabela qualquer.

A lógica:

faz o primeiro cursor neste select.
select cpf_icg,id
into vCPF,vID
from tkt_261064;

Após isto

ele tem que fazer o segundo cursor, com base no resultado do primeiro:

select max (dev_cpf)
into vCPF_DAC
from devedor
where dev_pro_cod = ‘ICG’
and dev_cpf like vCPF || ‘%’
group by dev_cpf;

E por fim, ele faz um update na minha tabela:

update tkt_261064 i set i.cpf_com_dac=vCPF_DAC where i.cpf_icg= substr (‘vCPF_DAC’,1,12);

Coloco esse where, porque como eu nao tenho o controle ,coloquei igual ao cpf original.

————————————————

create or replace procedure teste30 is
vCPF varchar2(20);
vCPF_DAC varchar2(20);
vID number(19);

CURSOR c1
IS
select cpf_icg,id
into vCPF,vID
from tkt_261064;
CURSOR c2
IS
select max (dev_cpf)
into vCPF_DAC
from devedor
where dev_pro_cod = ‘ICG’
and dev_cpf like vCPF || ‘%’
group by dev_cpf;
BEGIN
open c1;
LOOP
fetch c1 into vCPF,vID;
end loop;
FOR x IN c2;

loop
update tkt_261064 i set i.cpf_com_dac=vCPF_DAC where i.cpf_icg= substr (‘vCPF_DAC’,1,12);

close c2;
commit;
end loop;
END;

+————————

Erro
Compilation errors for PROCEDURE HOEPERSITN.TESTE30

Error: PLS-00103: Encountered the symbol “;” when expecting one of the following:

. ( * @ % & – + / at loop mod lembrete rem ..
|| multiset
Line: 24
Text: FOR x IN c2;

Error: PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:

end not pragma final instantiable ordem overriding estático
membro constructor mapa
Line: 31
Text: END;

Compilation errors for PROCEDURE HOEPERSITN.TESTE30

Error: PLS-00103: Encountered the symbol “;” when expecting one of the following:

. ( * @ % & – + / at loop mod lembrete rem ..
|| multiset
Line: 24
Text: FOR x IN c2;

Error: PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:

end not pragma final instantiable ordem overriding estático
membro constructor mapa
Line: 31
Text: END;

[Renato José Correia LimaParticipante]

Verifica se o código compila.

CREATE OR REPLACE PROCEDURE teste30
IS
vCPF VARCHAR2 (20);
vCPF_DAC VARCHAR2 (20);
vID NUMBER (19);

CURSOR c1
IS
SELECT cpf_icg, id
INTO vCPF, vID
FROM tkt_261064;

CURSOR c2
IS
SELECT MAX (dev_cpf)
INTO vCPF_DAC
FROM devedor
WHERE dev_pro_cod = 'ICG' AND dev_cpf LIKE vCPF || '%'
GROUP BY dev_cpf;
BEGIN
OPEN c1;

LOOP
FETCH c1
INTO vCPF, vID;

EXIT WHEN c1%NOTFOUND;

FOR x IN c2
LOOP
UPDATE tkt_261064 i
SET i.cpf_com_dac = x.vCPF_DAC
WHERE i.cpf_icg = SUBSTR (x.vCPF_DAC, 1, 12);

CLOSE c2;

COMMIT;
END LOOP;
END LOOP;

CLOSE c1;
END;

Att.

[Autor]

Posts