Retornando uma TABELA através de uma função: Demonstrando a capacidade do PL/SQL

Retornando uma TABELA através de uma função

Depois de algum tempo afastado, aqui estou de volta com um novo artigo. Hoje eu vou demonstrar para vocês como retornar uma tabela através de uma função.

O objetivo aqui não será a discussão de viabilidade desse procedimento, e sim mostrar a capacidade do PL/SQL e suas possibilidades.

Primeiro vamos criar uma estrutura para a nossa tabela através de TYPEs. Copie e cole o código abaixo:

CREATE OR REPLACE TYPE t_value AS OBJECT
(
 codigo NUMBER,
 descricao VARCHAR2(500)
) 
/

CREATE OR REPLACE TYPE t_value_table AS TABLE OF t_value
/

Agora vamos criar uma tabela com alguns dados para nosso exemplo:

CREATE TABLE teste_empresa
(
 codigo NUMBER,
 descricao VARCHAR2(500)
)
/

INSERT INTO teste_empresa VALUES(01, 'TESTE')
/

INSERT INTO teste_empresa VALUES(02, 'TESTE')
/

Agora vamos criar a função que irá retornar a tabela:

CREATE OR REPLACE FUNCTION f_list_value
RETURN t_value_table IS

 -- Definir as variáveis

 TYPE tList IS REF CURSOR;
 sList tList;
 vList t_value := t_value(0,0);
 ttList  t_value_table := t_value_table();

BEGIN
 BEGIN
 DBMS_OUTPUT.PUT_LINE('[ INICIO ]');

 OPEN sList FOR 
  SELECT * FROM teste_empresa; 
 LOOP
 FETCH sList INTO vList.codigo, vList.descricao;

 EXIT WHEN sList%NOTFOUND;
 -- Vamos manipular os dados

 vList.descricao := vList.descricao || ' - ' || vList.codigo;
 ttList.extend;
 ttList(vList.codigo) := vList; 

 END LOOP;

 CLOSE sList;

 DBMS_OUTPUT.PUT_LINE('[ FIM ]');

 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('[ ERRO ] ' || TO_CHAR(SQLCODE) || ' - ' || SQLERRM); 
 vList := t_value(0,'ERRO');
 ttList.extend;
 ttList(1) :=  vList;

 -- Não esqueça de sempre fechar o seu cursor 🙂

 IF sList%ISOPEN THEN
 CLOSE sList;

 END IF;

 END;

RETURN(ttList);

END f_list_value;
/

Agora vamos ao nosso SELECT:

SELECT * FROM TABLE(f_list_value())
/

codigo descricao
------ ---------
1 TESTE - 1
2 TESTE - 2

Como podem observar, o resultado são os registros de uma tabela.

Você também pode utilizar restrições na cláusula WHERE:

SELECT * FROM TABLE(f_list_value())
WHERE codigo > 1
/

codigo descricao
------ ---------
1 TESTE - 2

Ou mesmo fazer um UNION com uma tabela do banco de dados:

SELECT codigo, descricao FROM teste_empresa
UNION
SELECT codigo,descricao FROM TABLE(f_list_value())
/

codigo descricao
------ ---------
1 TESTE 
1 TESTE - 1
2 TESTE 
2 TESTE - 2

Se por alguma causa desconhecida, você precisar usar o ROWNUM, não é aconselhável:

SELECT codigo,descricao FROM TABLE(f_list_value())
WHERE rownum > 1
/

codigo descricao
------ ---------

É isso aí pessoal ! Espero que essa dica amplie seus conhecimentos sobre a linguagem PL/SQL e que seja mais uma opção a considerar.

Um grande abraço