Procedure - Oracle

Oracle

26/06/2014

Bom dia,

Eu sempre executo uma SQL em meu banco de dados:

Select empresa, revenda, situacao_os, chassi, dta_encerramento from
(
select ofi_atendimento.empresa, ofi_atendimento.revenda, ofi_ordem_servico.situacao_os, ofi_atendimento.chassi, ofi_ordem_servico.dta_encerramento
from ofi_servico_os, ofi_servico, ofi_atendimento, ofi_ordem_servico
where ofi_atendimento.contato = ofi_servico_os.contato
and ofi_atendimento.empresa = ofi_servico_os.empresa
and ofi_atendimento.revenda = ofi_servico_os.revenda
and ofi_ordem_servico.empresa = ofi_atendimento.empresa
and ofi_ordem_servico.revenda = ofi_atendimento.revenda
and ofi_ordem_servico.contato = ofi_atendimento.contato
and ofi_servico_os.empresa = ofi_servico.empresa
and ofi_servico_os.servico = ofi_servico.servico
and ofi_servico.maodeobra like '%M0011%'
)
group by empresa, revenda, situacao_os, chassi, dta_encerramento;

Porém eu gostaria de criar uma procedure para isso.
Na penultima linha final, onde está escrito: and ofi_servico.maodeobra like '%M0011%'
esse M0011, eu gostaria que fosse dinamico, ou seja, repassado via parametro, pois ele é variavel.

Queria alguma coisa que fosse do tipo assim:
select chassi(M0011);

E ele retornasse tudo o que está ai.

Já tentei alguns códigos, como este por exemplo:

create or replace
procedure retorna_chassi_recall_certo (maodeobra VARCHAR2)
is
Begin

Select into empresa, revenda, situacao_os, chassi, dta_encerramento from
(
select ofi_atendimento.empresa, ofi_atendimento.revenda, ofi_ordem_servico.situacao_os, ofi_atendimento.chassi, ofi_ordem_servico.dta_encerramento
from ofi_servico_os, ofi_servico, ofi_atendimento, ofi_ordem_servico
where ofi_atendimento.contato = ofi_servico_os.contato
and ofi_atendimento.empresa = ofi_servico_os.empresa
and ofi_atendimento.revenda = ofi_servico_os.revenda
and ofi_ordem_servico.empresa = ofi_atendimento.empresa
and ofi_ordem_servico.revenda = ofi_atendimento.revenda
and ofi_ordem_servico.contato = ofi_atendimento.contato
and ofi_servico_os.empresa = ofi_servico.empresa
and ofi_servico_os.servico = ofi_servico.servico
and ofi_servico.maodeobra like ('%' || maodeobra || '%')
)
group by empresa, revenda, situacao_os, chassi, dta_encerramento;

END;

Mas dá erro!
E nunca retorna..

Alguem tem idéia?
Gustavo Clepardi

Gustavo Clepardi

Curtidas 0

Respostas

Marisiana Battistella

Marisiana Battistella

26/06/2014

Olá Gustavo!
Tenta remover os parenteses que vc colocou no like..
and ofi_servico.maodeobra like '%' || maodeobra || '%'

Eu acho que o erro que está ocorrendo é por causa disso...
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Continua dando erro!

[img]http://arquivo.devmedia.com.br/forum/imagem/375426-20140626-141141.png[/img]
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Teu problema é outro....
Vc não definiu as colunas que o select tem que pegar...

SELECT res.empresa, 
              res.revenda,
              res.situacao_os,
              res.chassi,
              res.dta_encerramento
INTO empresa,
         revenda,
         situacao_os,
         chassi,
         dta_encerramento
FROM ( 
           SELECT ofi_atendimento.empresa,
                           ofi_atendimento.revenda,
                           ofi_ordem_servico.situacao_os,
                           ofi_atendimento.chassi,
                           ofi_ordem_servico.dta_encerramento
            FROM ofi_servico_os, 
                        ofi_servico,
                        ofi_atendimento,
                       ofi_ordem_servico
           WHERE ofi_atendimento.contato = ofi_servico_os.contato
           AND ofi_atendimento.empresa = ofi_servico_os.empresa
           AND ofi_atendimento.revenda = ofi_servico_os.revenda
           AND ofi_ordem_servico.empresa = ofi_atendimento.empresa
           AND ofi_ordem_servico.revenda = ofi_atendimento.revenda 
          AND ofi_ordem_servico.contato = ofi_atendimento.contato
          AND ofi_servico_os.empresa = ofi_servico.empresa
          AND ofi_servico_os.servico = ofi_servico.servico 
          AND ofi_servico.maodeobra like '%' || maodeobra || '%' 
         ) res
GROUP BY res.empresa, 
                   res.revenda,
                   res.situacao_os,
                   res.chassi,
                   res.dta_encerramento;
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Acredito que seja preciso fazer alguma outra coisa... ainda continua dando o problema
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Qual é o erro?
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Segue!

[img]http://arquivo.devmedia.com.br/forum/imagem/375426-20140626-153959.png[/img]
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Você não está retornando os dados de forma correta...
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Nesta documentação tem uma explicação detalhada de como é a estrutura de uma procedure...
[url]http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm[/url]
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Olha a estrutura:

create or replace procedure retorna_chassi_recall_certo
(maodeobra IN VARCHAR2)
is
-- Declaração de variáveis aqui
Begin

end;
/
não existe a declaração "Declare" no corpo de uma procedure

Caso queira declarar variáveis de saida só acrescentar "," após a variável de "IN" veja o seguinte exemplo: (maodeobra IN VARCHAR2, Chave OUT VARCHAR2, Chave1 OUT NUMBER)

mas sempre aconselho a todos utilizar o json no oracle pois minimiza o conteudo das procedures além de torna-las mais eficientes.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Lourival, se o retorno da procedure for uma lista de dados, não tem q utilizar um cursor?
As variáveis de saída (OUT) não são utilizadas apenas quando retorna um valor único?
Fiquei com essas dúvidas, poderia me explicar?
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Marisiana, vc pode utilizar um cursor como retorno, mas nem sempre é prático, eu utilizo o pl/json no oracle pois me evita muitos problemas e fico com um padrão para todas as linguagens que trabalho (Java/Delphi) o que facilita muito as manutenções.

Exemplo:

Cursor : olha o exemplo nessa página: http://glufke.net/oracle/viewtopic.php?t=3825

Exemplo usando o PL/JSON (versão 1.0.4): 

create or replace procedure Monta_Json (Campo OUT VARCHAR2)
is

ObjJson  json;
Num1     NUMBER:=1;
Num       VARCHAR2(10);
Ip           VARCHAR2(100);
Campo   VARCHAR2(30000);

begin

-- Acha o Ip
 select TRIM(substr(SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),1,100))
   into Ip
  from dual;

num1:=Num1 +1;
Num:=null;
-- Monta Objeto Json
for r in (select produto from tabela)
 loop
  ObjJson:=json();
  ObjJson.put('FAT50_DEPOSITO',       'VEN');                      - Montando variavel fixa
  ObjJson.put('USUARIO',                     TO_CHAR(Num1));   - variavel acumulada
  ObjJson.put('FAT50_PRODUTO_IE',  r.Produto);                - Variavel do cursor
  ObjJson.Put('IP',                                 IP);                            - Variavel de um select
  ObjJson.put('FAT50_NARRATIVA',     Num);                        - Variavel fixa
  Campo:=json.to_char(ObjJson);                                          - Criando Json
 end loop;
end;/


Então como pode ver eu posso sair com vários campos em uma só variável de saída o qual me facilita bastante, esse exemplo acima é sobre uma única tabela, mas pode ser utilizando diversas tabelas, as possibilidade são muitas. Eu pelo menos nunca precisei utilizar retorno de cursor em procedures o pl/json resolveu todos os meus problemas.
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Não considerar a declaração "Campo VARCHAR2(30000); " nas variaveis. pois a coloquei no OUT da procedure,
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Bem interessante Lourival!
Eu ainda não conhecia o PL/JSON, por isso fiquei com a dúvida...
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Eu trabalhei com a ferramenta PL/SQL Developer onde eu utilizava cursores para obter os retornos dor procedimentos.
Por exemplo:
Quando eu estava trabalhando em uma determinada aplicação, eu criava um Package pra armazenar todas as procedures e functions que aquela aplicação iria utilizar.
Assim, eu declarava um tipo de cursor pra ser utilizado na package...

 -- Declaração de tipos públicos
    TYPE t_refcur IS REF CURSOR;


E depois utilizava esse cursor nos procedimentos:

 
PROCEDURE prc_gettipobolsa(in_codtipobolsa IN acdesenv.pestipobolsa.codtipobolsa%TYPE,
                                                  out_result      OUT t_refcur) IS
    BEGIN
        IF (in_codtipobolsa IS NULL) THEN
            OPEN out_result FOR
                SELECT tb.codtipobolsa,
                       tb.descricao,
                       tb.abreviatura
                FROM   acdesenv.pestipobolsa tb
                ORDER  BY tb.codtipobolsa;
        ELSE
            OPEN out_result FOR
                SELECT tb.codtipobolsa,
                       tb.descricao,
                       tb.abreviatura
                FROM   acdesenv.pestipobolsa tb
                WHERE  tb.codtipobolsa = in_codtipobolsa
                ORDER  BY tb.codtipobolsa;
        END IF;
    END prc_gettipobolsa;


O retorno de um procedimento assim eu utilizava em aplicações desenvolvidas em Delphi ou PHP, conforme a necessidade.
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Já existe uma aplicacao rodando em cima do banco.
Eu na verdade executo consultas diretamente no banco de dados, então nao uso nenhum programa ou codifico nada, isso é puro e simplesmente PL/SQL direto pelo SqlDeveloper.
Ainda está dando problema!
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Mas, pelo que entendi, o teu problema é o retorno dos dados, vc tem utilizar um dos exemplos que apresentamos...
Esse exemplo que eu apresentei pode ser criado no SQLDeveloper...
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Gustavo olha a procedure:

create or replace procedure retorna_chassi_recall_certo
          (maodeobra IN VARCHAR2)
is
-- Considerando que as variaveis de "INTO" estejam declaradas
Begin

Select a.Empresa, a.Revenda, a.Situacao, a.Chassi, a.DtEncerra
  into empresa, revenda, situacao_os, chassi, dta_encerramento
  from (select ofi_atendimento.empresa Empresa, ofi_atendimento.revenda Revenda,
               ofi_ordem_servico.situacao_os Situacao, ofi_atendimento.chassi Chassi,
               ofi_ordem_servico.dta_encerramento DtEncerra
          from ofi_servico_os,
               ofi_servico,
               ofi_atendimento,
               ofi_ordem_servico
         where ofi_atendimento.contato = ofi_servico_os.contato
           and ofi_atendimento.empresa = ofi_servico_os.empresa
           and ofi_atendimento.revenda = ofi_servico_os.revenda
           and ofi_ordem_servico.empresa = ofi_atendimento.empresa
           and ofi_ordem_servico.revenda = ofi_atendimento.revenda
           and ofi_ordem_servico.contato = ofi_atendimento.contato
           and ofi_servico_os.empresa = ofi_servico.empresa
           and ofi_servico_os.servico = ofi_servico.servico
           and ofi_servico.maodeobra like  '%' || maodeobra || '%') a
 group by empresa, revenda, situacao_os, chassi, dta_encerramento;

END;
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Outro exemplo:


CREATE OR REPLACE PACKAGE types AS 
  TYPE cursor_type IS REF CURSOR;
END Types;
/

CREATE OR REPLACE PROCEDURE get_emp_rs 
            (p_deptno    IN  emp.deptno%TYPE,
             p_recordset OUT SYS_REFCURSOR)
AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename, empno, deptno
       FROM  emp
     WHERE deptno = p_deptno
     ORDER BY ename;
END GetEmpRS;
/


Esses exemplos estão no site da Oracle.
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Tentei fazer isso, o problema agora é que o LIKE não tá pegando a variável de entrada...
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Tem alguma coisa diferente na tua procedure fiz um teste e funcionou certinho:

Teste:

declare
sao varchar2(2000);
Codigo number(6);
Nome varchar2(60);
Endereco varchar2(60);
begin
sao:='SAO';
select t.bas28_emitente_iu, t.bas28_nome, t.bas28_endereco
  into Codigo, Nome, Endereco
  from bas28_emitente t
 where t.bas28_cidade like '%'||sao||'%'
   and rownum = 1;
end;
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Eu fiz... 


create or replace
procedure retorna_chassi_devmedia(maodeobralikein IN VARCHAR2)
is
empresa NUMBER(2);
revenda NUMBER(2); 
situacao_os NUMBER(1);
chassi VARCHAR(17);
maodeobra VARCHAR(15);
dta_encerramento Date;

Begin
maodeobralike := maodeobralikein;

Select a.Empresa, a.Revenda, a.Situacao, a.Chassi, a.MO, a.DtEncerra
  into empresa, revenda, situacao_os, chassi, maodeobra, dta_encerramento
  from (select ofi_atendimento.empresa Empresa, ofi_atendimento.revenda Revenda,
               ofi_ordem_servico.situacao_os Situacao, ofi_atendimento.chassi Chassi,
               ofi_servico.maodeobra MO,
               ofi_ordem_servico.dta_encerramento DtEncerra
          from ofi_servico_os,
               ofi_servico,
               ofi_atendimento,
               ofi_ordem_servico
         where ofi_atendimento.contato = ofi_servico_os.contato
           and ofi_atendimento.empresa = ofi_servico_os.empresa
           and ofi_atendimento.revenda = ofi_servico_os.revenda
           and ofi_ordem_servico.empresa = ofi_atendimento.empresa
           and ofi_ordem_servico.revenda = ofi_atendimento.revenda
           and ofi_ordem_servico.contato = ofi_atendimento.contato
           and ofi_servico_os.empresa = ofi_servico.empresa
           and ofi_servico_os.servico = ofi_servico.servico
           and ofi_servico.maodeobra like  '%' || maodeobralike || '%'
 group by empresa, revenda, situacao_os, chassi, MaoDeObra, dta_encerramento) a;
 
END retorna_chassi_devmedia;


Olha o que acontece:

nada!

[img]http://arquivo.devmedia.com.br/forum/imagem/375426-20140627-172713.png[/img]
GOSTEI 0
Gustavo Clepardi

Gustavo Clepardi

26/06/2014

Eu fiz... 


create or replace
procedure retorna_chassi_devmedia(maodeobralikein IN VARCHAR2)
is
empresa NUMBER(2);
revenda NUMBER(2); 
situacao_os NUMBER(1);
chassi VARCHAR(17);
maodeobra VARCHAR(15);
dta_encerramento Date;

Begin
maodeobralike := maodeobralikein;

Select a.Empresa, a.Revenda, a.Situacao, a.Chassi, a.MO, a.DtEncerra
  into empresa, revenda, situacao_os, chassi, maodeobra, dta_encerramento
  from (select ofi_atendimento.empresa Empresa, ofi_atendimento.revenda Revenda,
               ofi_ordem_servico.situacao_os Situacao, ofi_atendimento.chassi Chassi,
               ofi_servico.maodeobra MO,
               ofi_ordem_servico.dta_encerramento DtEncerra
          from ofi_servico_os,
               ofi_servico,
               ofi_atendimento,
               ofi_ordem_servico
         where ofi_atendimento.contato = ofi_servico_os.contato
           and ofi_atendimento.empresa = ofi_servico_os.empresa
           and ofi_atendimento.revenda = ofi_servico_os.revenda
           and ofi_ordem_servico.empresa = ofi_atendimento.empresa
           and ofi_ordem_servico.revenda = ofi_atendimento.revenda
           and ofi_ordem_servico.contato = ofi_atendimento.contato
           and ofi_servico_os.empresa = ofi_servico.empresa
           and ofi_servico_os.servico = ofi_servico.servico
           and ofi_servico.maodeobra like  '%' || maodeobralike || '%'
 group by empresa, revenda, situacao_os, chassi, MaoDeObra, dta_encerramento) a;
 
END retorna_chassi_devmedia;


Olha o que acontece:

nada!

[img]http://arquivo.devmedia.com.br/forum/imagem/375426-20140627-172713.png[/img]
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Gustavo, o problema é que vc não está tratando o retorno, no caso vc executa o bloco, como ele não tem "OUT" ele simplesmente só executa mais nada, veja o exemplo acima que tem no site da Oracle e implemente o seu cursor de retorno.
GOSTEI 0
Marisiana Battistella

Marisiana Battistella

26/06/2014

Conseguiu resolver, Gustavo?
GOSTEI 0
Lourival Queiroz

Lourival Queiroz

26/06/2014

Gustavo um exemplo funcional da utilização de cursor:

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_row     t009_cliente%ROWTYPE;
BEGIN
  IF NOT l_cursor%ISOPEN THEN
    OPEN l_cursor FOR
      SELECT *
        FROM t009_cliente
       WHERE t009_nome like '%MARIA%'
       ORDER BY t009_nome DESC;
  END IF; 

  LOOP
    FETCH l_cursor
    INTO  l_row;   

    -- Use both checks to prove a point.
    EXIT WHEN NOT l_cursor%FOUND;
    EXIT WHEN l_cursor%NOTFOUND;   

    DBMS_OUTPUT.put_line(l_cursor%ROWCOUNT || ' : ' || l_row.t009_cliente_iu || ' : ' || l_row.t009_nome);
  END LOOP; 

  CLOSE l_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF l_cursor%ISOPEN THEN
      CLOSE l_cursor;
    END IF;
END;
/
GOSTEI 0
POSTAR