Fazer SELECT em Procedure e colocar resultado em variaveis

[mtsys]

Pessoal tenho a SP abaixo, que tem um update fudido.
Queria saber como eu faço para pegar um select e jogar em variáveis para conseguir utilizar no update.

Selecionar tudo

CREATE OR REPLACE PROCEDURE bdautolab.SP_Fornecedor (vCofFornecedor integer)
IS  
   vVinculo varchar2(10);
BEGIN

  
  insert into addesenv.fornecedor 
     ( RAZAOSOCIAL, TIPOPESSOA, CPFCGC, ENDERECO, CEP, 
       TIPOFORNECEDOR, FANTASIA, RGIE, EMAIL, SITE, VINCULO
     )
  select 
     a.cli_razaosocial_a RAZAOSOCIAL, 
     case when a.Tipocliente_i = 0 then 'J' else 'F' end TIPOPESSOA,
     replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', '') CPFCGC,
     a.cli_endereco_a ENDERECO,
     a.cli_cep_a CEP, 
     'C' TIPOFORNECEDOR,
     a.cli_nomecliente_a FANTASIA,
     SUBSTR(a.cli_inscricaoestadual_a, 1, 20)  RGIE,
     a.cli_email_a EMAIL,
     a.cli_homepage_a  SITE,
     'G' VINCULO
  from 
     bdautolab.cliente a 
     left join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
  where 
     b.CodFornecedor is null
     and a.cli_codcliente = vCofFornecedor;

        
  update 
     addesenv.fornecedor 
  set 
     RAZAOSOCIAL = (select cli_razaosocial_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     TIPOPESSOA = (select case when Tipocliente_i = 0 then 'J' else 'F' end from bdautolab.cliente where  cli_codcliente = vCofFornecedor), 
     ENDERECO = (select cli_endereco_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor), 
     CEP = (select cli_cep_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     FANTASIA = (select cli_nomecliente_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     RGIE = (select SUBSTR(cli_inscricaoestadual_a, 1, 20) from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     EMAIL = (select cli_email_a  from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     SITE = (select cli_homepage_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     VINCULO = 
       (
          select 
            case when 
               ( select count(*) from bdautolab.cliente a 
                 inner join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
                 where a.cli_codcliente = vCofFornecedor and b.vinculo like '%G%') = 0 then 'G' 
             else nvl(Vinculo, '') || 'G' end 
          from 
             bdautolab.cliente a 
             inner join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
          where
             a.cli_codcliente = vCofFornecedor     
       )           
  where
     CPFCGC = 
        ( select 
             replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', '')        
          from
             bdautolab.cliente a
          where 
             a.cli_codcliente = vCofFornecedor
        );             
         
  
END SP_Fornecedor;

[]s

Marlon

[diegolenhardt]

Selecionar tudo

vTeste   varchar2(10);
      
BEGIN

  select 'TESTE'
    into vTeste
    from Dual;

depois você poderia usar essa variavel vTeste no insert/update,

Selecionar tudo

CREATE OR REPLACE PROCEDURE bdautolab.SP_Fornecedor (vCofFornecedor integer)
IS 
   vVinculo varchar2(10);
   
   vTeste   varchar2(10);
      
BEGIN

  select 'TESTE'
    into vTeste
    from Dual;
 
  insert into addesenv.fornecedor
     ( RAZAOSOCIAL, TIPOPESSOA, CPFCGC, ENDERECO, CEP,
       TIPOFORNECEDOR, FANTASIA, RGIE, EMAIL, SITE, VINCULO
     )
  select
     a.cli_razaosocial_a RAZAOSOCIAL,
     case when a.Tipocliente_i = 0 then 'J' else 'F' end TIPOPESSOA,
     replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', '') CPFCGC,
     a.cli_endereco_a ENDERECO,
     a.cli_cep_a CEP,
     'C' TIPOFORNECEDOR,
     a.cli_nomecliente_a FANTASIA,
     SUBSTR(a.cli_inscricaoestadual_a, 1, 20)  RGIE,
     a.cli_email_a EMAIL,
     a.cli_homepage_a  SITE,
     'G' VINCULO
  from
     bdautolab.cliente a
     left join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
  where
     b.CodFornecedor is null
     and a.cli_codcliente = vCofFornecedor;

       
  update
     addesenv.fornecedor
  set
     RAZAOSOCIAL = (select cli_razaosocial_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     TIPOPESSOA = (select case when Tipocliente_i = 0 then 'J' else 'F' end from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     ENDERECO = (select cli_endereco_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     CEP = (select cli_cep_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     FANTASIA = (select cli_nomecliente_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     RGIE = (select SUBSTR(cli_inscricaoestadual_a, 1, 20) from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     EMAIL = (select cli_email_a  from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     SITE = (select cli_homepage_a from bdautolab.cliente where  cli_codcliente = vCofFornecedor),
     VINCULO =
       (
          select
            case when
               ( select count(*) from bdautolab.cliente a
                 inner join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
                 where a.cli_codcliente = vCofFornecedor and b.vinculo like '%G%') = 0 then 'G'
             else nvl(Vinculo, '') || 'G' end
          from
             bdautolab.cliente a
             inner join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
          where
             a.cli_codcliente = vCofFornecedor     
       )           
  where
     CPFCGC =
        ( select
             replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', '')       
          from
             bdautolab.cliente a
          where
             a.cli_codcliente = vCofFornecedor
        );             
         
 
END SP_Fornecedor; 

[mtsys]

Por que ao tentar colocar este código

Selecionar tudo

  select a.cli_razaosocial_a
  into vTeste
  from  bdautolab.cliente a where  a.cli_codcliente = vCofFornecedor; 

Deu erro na SP?

[mtsys]

Descobri o motivo.

Tem que retornar algo o SQL. Mas se não retornar o que devo fazer para funcionar?

[diegolenhardt]

Selecionar tudo

begin
  select a.cli_razaosocial_a
  into vTeste
  from  bdautolab.cliente a where  a.cli_codcliente = vCofFornecedor; 
exception
  when no_data_found then
    null;
end;

[mtsys]

Estou ficando com mais cabelo brando do que eu já tenho.

Agora está dando o erro:

Selecionar tudo

ORA-06550: linha 2, coluna 7:
PLS-00905: o objeto BDAUTOLAB.SP_FORNECEDOR é inválido
ORA-06550: linha 2, coluna 7:
PL/SQL: Statement ignored

A sp ficou assim.

Selecionar tudo

CREATE OR REPLACE PROCEDURE bdautolab.SP_Fornecedor (vCofFornecedor integer)
IS 
   vCODFORNECEDOR_UPF number(5);      
   vRAZAOSOCIAL varchar2(100);
   vTIPOPESSOA varchar2(1);
   vENDERECO varchar2(100);
   vCEP varchar2(15);
   vFANTASIA varchar2(100);
   vRGIE varchar2(20);
   vEMAIL varchar2(100);
   vSITE varchar2(500);
   vVINCULO varchar2(10);  
   vCPFCGC varchar2(14);   
   vFone varchar2(25);
   vFax varchar2(25);    
BEGIN
  
  insert into addesenv.fornecedor 
     ( RAZAOSOCIAL, TIPOPESSOA, CPFCGC, ENDERECO, CEP, 
       TIPOFORNECEDOR, FANTASIA, RGIE, EMAIL, SITE, VINCULO
     )
  select 
     a.cli_razaosocial_a RAZAOSOCIAL, 
     case when a.Tipocliente_i = 0 then 'J' else 'F' end TIPOPESSOA,
     replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', '') CPFCGC,
     a.cli_endereco_a ENDERECO,
     a.cli_cep_a CEP, 
     'C' TIPOFORNECEDOR,
     a.cli_nomecliente_a FANTASIA,
     SUBSTR(a.cli_inscricaoestadual_a, 1, 20)  RGIE,
     a.cli_email_a EMAIL,
     a.cli_homepage_a  SITE,
     'G' VINCULO
  from 
     bdautolab.cliente a 
     left join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
  where 
     b.razaosocial is null
     and a.cli_codcliente = vCofFornecedor;
  
  select   
      a.cli_razaosocial_a, 
      case when a.Tipocliente_i = 0 then 'J' else 'F' end,
      a.cli_endereco_a, 
      a.cli_cep_a,
      a.cli_nomecliente_a,
      SUBSTR(a.cli_inscricaoestadual_a, 1, 20),
      nvl(a.cli_email_a, '') EMAIL,
      nvl(a.cli_homepage_a, '')  SITE,
      case 
         when 
            (SUBSTR(b.vinculo, 1, 1) = 'G') or (SUBSTR(b.vinculo, 6, 1) = 'G') or
            (SUBSTR(b.vinculo, 2, 1) = 'G') or (SUBSTR(b.vinculo, 7, 1) = 'G') or
            (SUBSTR(b.vinculo, 3, 1) = 'G') or (SUBSTR(b.vinculo, 8, 1) = 'G') or
            (SUBSTR(b.vinculo, 4, 1) = 'G') or (SUBSTR(b.vinculo, 9, 1) = 'G') or
            (SUBSTR(b.vinculo, 5, 1) = 'G') or (SUBSTR(b.vinculo, 10, 1) = 'G') then b.vinculo
         else  b.vinculo || 'G' end,
      replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', '')
   into      
      vRAZAOSOCIAL, 
      vTIPOPESSOA, 
      vENDERECO, 
      vCEP,
      vFANTASIA,
      vRGIE,
      vEMAIL,
      vSITE,
      vVINCULO,
      vCPFCGC      
   from  
      bdautolab.cliente a 
      inner join addesenv.fornecedor b on (b.CPFCGC = replace(replace(replace(a.cli_cadastronacional_a, '.', ''), '/', ''), '-', ''))
   where  
      a.cli_codcliente = vCofFornecedor;            
  
  update 
     addesenv.fornecedor 
  set 
     RAZAOSOCIAL = vRAZAOSOCIAL,
     TIPOPESSOA = vTIPOPESSOA, 
     ENDERECO = vENDERECO, 
     CEP = vCEP,
     FANTASIA = vFANTASIA,
     RGIE = vRGIE,
     EMAIL = vEMAIL,
     SITE = vSITE,
     VINCULO = vVINCULO 
  where
     CPFCGC = vCPFCGC;   
           
     
END SP_Fornecedor;

[]s

Marlon

[fsitja]

Camarada, não tem porque gravar em variável antes, só precisa otimizar a forma como está sendo feito o update.

Ele acessa a tabela Cliente 12 vezes (são 12 subqueries no update), e só precisaria acessar duas. Isso deixa a execução potencialmente muito mais lenta além de torná-la muito difícil para dar manutenção, como você já está percebendo.

O comando abaixo faz a mesma coisa (não testei, pois não tenho suas tabelas):

Selecionar tudo

UPDATE addesenv.fornecedor
   SET (razaosocial, tipopessoa, endereco, cep, fantasia, rgie, email, site) = 
       (SELECT cli_razaosocial_a,
               CASE WHEN tipocliente_i = 0 THEN 'J' ELSE 'F' END,
               cli_endereco_a,
               cli_cep_a,
               cli_nomecliente_a,
               substr(cli_inscricaoestadual_a, 1, 20),
               cli_email_a,
               cli_homepage_a
          FROM bdautolab.cliente
         WHERE cli_codcliente = vcoffornecedor),
        (vinculo) = (SELECT case when (COUNT(case when b.vinculo LIKE '%G%' then 1 end) over ()) = 0
                                 then 'G' else vinculo || 'G' end
                       FROM bdautolab.cliente a
                      INNER JOIN addesenv.fornecedor b
                            ON (b.cpfcgc = regexp_replace(a.cli_cadastronacional_a, '[./-]'))
                      WHERE a.cli_codcliente = vcoffornecedor)
 WHERE cpfcgc = (SELECT regexp_replace(a.cli_cadastronacional_a, '[./-]')
                   FROM bdautolab.cliente a
                  WHERE a.cli_codcliente = vcoffornecedor);

Muito mais fácil de ler e entender.

Outra coisa, reparei que a procedure faz envio de parâmetro de código.

Se isso for um procedimento batch, assumo que exista um loop que chama a procedure "sp_fornecedor" centenas/milhares de vezes.

Sendo assim, dá para melhorar o desempenho muito mesmo, simplesmente removendo o cursor loop que chama a procedure e envia os milhares de comandos e possivelmente fazendo tudo num comando só, inclusive o insert, através de um Merge.

Se ficar interessado, poste um pouco mais da lógica do procedimento que chama a procedure e do que ele tenta fazer, e podemos ajudar da melhor forma.

Abraço,
Francisco.

[mtsys]

Funcionou de primeira.

fsitja estou acostumado com a sintaxe do SQL Server.
Por isto estou apanhando pacas para o Oracle.

Mas como você passou ficou perfeito e funcionou de primeira.

Valeu