Passing of cpf as a parameter in an insert / select

0

I have a process of creating a table that is very slow and would like to improve it.

The view used in the process was created to use the CPF as a parameter, but when it is executed without the CPF or in join with another table it is extremely slow.

I'm trying to get the CPF's from a table and pass them as a parameter in insert / select, still unsuccessful. Can you help me?

declare

 v_doc  number(15);

 cursor c1 is

 select CPF from TABELA where cod = 1 and CPF is not null and CPF <> 0;

begin

 open  c1;

 fetch c1 into v_doc;

 close c1;

insert into teste_tab   (CPFCNPJCLIENTE,EMPRESA,CODAGENCIA,OPERACAO,NOMECLIENTE,TIPOPESSOA,TIPOBDV,DESCRICAOBDV,TIPOVINCULO,DATAINICIO,DATAFINAL)

SELECT

    CASE WHEN LENGTH(TO_CHAR(CPF_CNPJ_CLIENTE)) > 11 THEN 
            lpad(TO_CHAR(CPF_CNPJ_CLIENTE),14,'0') ELSE 
            lpad(TO_CHAR(CPF_CNPJ_CLIENTE),11,'0') END AS CPFCNPJCLIENTE,
    '11111111' AS EMPRESA,
    '00010' AS CODAGENCIA,
        CASE WHEN LENGTH(TO_CHAR(CPF_CNPJ_CLIENTE)) > 11 THEN 
                lpad(TO_CHAR(CPF_CNPJ_CLIENTE),14,'0') ELSE 
                lpad(TO_CHAR(CPF_CNPJ_CLIENTE),11,'0') END || '_' || TO_CHAR(DATA_CADASTRO, 'yyyymmdd') || 'CAD' || TO_CHAR(DATA_ATIVACAO, 'yyyymmdd') AS OPERACAO,
    SUBSTR(NOME_CLIENTE, 0, 50) AS NOMECLIENTE,
    CASE WHEN LENGTH(TO_CHAR(CPF_CNPJ_CLIENTE)) <= 11 THEN 
            'F' ELSE 
            'J' END AS TIPOPESSOA,
    '00' AS TIPOBDV,
    'Sem BDV' AS DESCRICAOBDV,
    'T' AS TIPOVINCULO,
    CASE WHEN DATA_ATIVACAO IS NULL then 
            DATA_CADASTRO           ELSE 
            DATA_ATIVACAO END AS DATAINICIO,
    DATA_INATIVACAO AS DATAFINAL
FROM
    VIEW
where VIEW.CPF_CNPJ_CLIENTE = v_doc;

commit;  

end;
    
asked by anonymous 26.03.2018 / 23:42

0 answers