Function

[zenfra]

Bom dia...

Gostaria de tirar uma dúvida em relação a criação de função com o retorno de tabelas...
Como o numero de registros é muito grande e esse relatórios precisam ser gerados em planilhas excel sendo formatados da forma como o cliente solicitou, preciso criar uma função que retorne uma tabela com os valores em posições estratégicos para que isso facilite na exibição e formatação dos registros na planilha excel e evite que eu precise ficar abrindo e fechando conexao com o banco para preencher a planilha...

É possível retorna uma tabela através de uma função?

Se sim, poderiam me daar um exemplo?

Obrigado!

[Trevisolli]

Brother, uma função pode retornar um OBJETO, que no teu caso, pode ser uma PL/SQL TABLE.

Procure aqui no fórum que irá conseguir alguns exemplos.
qualquer coisa manda ai.

[LoadingXp]

Trevis mas esse PL/SQL Table mencionado por exemplo pode fazer com que eu tenha um Select * from tabela e ele retornar os resultados para mim...

No SQL Server eu tinha costume de fazer tudo em procedure por exemplo

Selecionar tudo

CREATE PROCEDURE TESTE
AS
BEGIN
  SELECT * FROM TREVIS
END

Quando eu executava essa procedure ele me retornava todas as linhas da consulta. Como eu posso fazer isso no Oracle?

[Trevisolli]

Brother, isso mesmo...
Retornando uma PL/SQL TABLE, posso retornar um objeto, ou seja, N linhas e N colunas...

Vou montar um exemplo pra vocês e já mando.

[Trevisolli]

Pessoal,seguinte: Fiz tudo em um bloco anônimo mas, pode ser criada package de banco para tal.

Qualquer coisa é só falar.

Selecionar tudo

DECLARE 
  
  -- criar o registro
  TYPE r_emp IS RECORD (vename    VARCHAR2(100),
                        vhiredate DATE,
								vdname    VARCHAR2(100),
								vsal      NUMBER);
								
  -- criar a tabela dos registros
  TYPE t_emp IS TABLE OF r_emp INDEX BY BINARY_INTEGER;
  
  V_RETORNO T_EMP;
  
  FUNCTION FUN_EMPREGADO (P_EMPNO IN NUMBER DEFAULT NULL)
  RETURN t_emp
  IS 
    -- CRIA variável do tipo Tabela para retorno
    tab_emp t_emp;
    
    CURSOR cur_empregado
      IS 
        SELECT e.ename,
               e.hiredate,
               d.dname,
               e.sal
          FROM emp e,
               dept d
         WHERE d.deptno = e.deptno
           AND e.empno  = P_EMPNO;

    CURSOR cur_empregados
      IS 
        SELECT e.ename,
               e.hiredate,
               d.dname,
               e.sal
          FROM emp e,
               dept d
         WHERE d.deptno = e.deptno;
           
  BEGIN
    
    IF P_EMPNO IS NOT NULL
    THEN 
	    -- Popula PL/SQL TABLE
	    FOR x IN cur_empregado
	    LOOP
	      tab_emp(cur_empregado%ROWCOUNT).vename    := x.ename;
	      tab_emp(cur_empregado%ROWCOUNT).vhiredate := x.hiredate;
	      tab_emp(cur_empregado%ROWCOUNT).vdname    := x.dname;
	      tab_emp(cur_empregado%ROWCOUNT).vsal      := x.sal;
	    END LOOP;
    ELSE
    -- Popula PL/SQL TABLE
	    FOR x IN cur_empregados
	    LOOP
	      tab_emp(cur_empregados%ROWCOUNT).vename    := x.ename;
	      tab_emp(cur_empregados%ROWCOUNT).vhiredate := x.hiredate;
	      tab_emp(cur_empregados%ROWCOUNT).vdname    := x.dname;
	      tab_emp(cur_empregados%ROWCOUNT).vsal      := x.sal;
	    END LOOP;
    END IF;
    RETURN tab_emp;
    
    EXCEPTION 
      WHEN OTHERS THEN
		  raise_application_error(-20001, 'Erro ao popular PL/SQL TABLE. ' || SQLERRM); 
  END;

  BEGIN
  -- UTILIZAÇÃO DA FUNÇÃO ( RETORNANDO UMA PL/SQL TABLE )
  V_RETORNO := FUN_EMPREGADO;

  -- TESTANDO RETORNO (cursor todos os empregados)
  FOR X IN V_RETORNO.FIRST .. V_RETORNO.LAST
  LOOP
    dbms_output.put_line('Nome do Empregado: ' || V_RETORNO(X).VENAME);
  END LOOP;
  
  	 

END;    	

[LoadingXp]

Obrigadão Trevis!!!!!
Você é show!!!