segunda-feira, 7 de dezembro de 2009

Usando listas como tabelas em PL/SQL

Um problema recorrente em sistemas web é o de usar listas em consultas. Isto é, o usuário seleciona vários itens de uma lista e isso precisa ser transformado em um SELECT, UPDATE, INSERT, ou mesmo DELETE.

A solução mais comum é a de gerar o SQL dinamicamente. Mas isso cria mais dois problemas: não é seguro (facilita a injeção de SQL) e obriga o Oracle a compilar um novo comando.

É possível passar um array de elementos como parâmetro de uma funcão ou procedure, mas isso não facilita a vida nem do Java (estou presumindo que a interface com o banco seja JDBC) nem do PL/SQL.

Uma solução que surgiu com o Oracle 9i são as funções PIPELINED. Elas permitem escrever uma função cujos resultados podem ser convertidos em linhas de uma tabela.

Vou descrever isso melhor com código. A declaração abaixo é de uma package que contém funções que vamos usar adiante, diretamente em comandos DML:


CREATE OR REPLACE PACKAGE LIST_PKG AS

TYPE ID_TABLE IS TABLE OF INTEGER;
TYPE NAME_TABLE IS TABLE OF VARCHAR2(32767);

FUNCTION IDS(LIST IN VARCHAR2) RETURN ID_TABLE PIPELINED;
FUNCTION NAMES(LIST IN VARCHAR2) RETURN NAME_TABLE PIPELINED;

END;

Temos aqui dois tipos, uma tabela de inteiros e uma tabela de strings. A função IDS() vai transformar um string com inteiros em uma tabela de inteiros. A função NAMES() fará o mesmo por strings.

Então, se eu quiser pesquisar um grupo de pessoas com determinados cargos, posso fazer o seguinte:


SELECT NOME, CARGO, SALARIO
FROM
PESSOAS P
INNER JOIN (TABLE(LIST_PKG.NAMES('Chefão,Chefe,Chefinho')) T
ON T.COLUMN_VALUE=P.CARGO)


A função TABLE transforma os valores de LIST_PKG.NAMES() em uma tabela.

Se eu quiser encontrar um grupo específico de pessoas, posso escrever uma consulta como esta:


SELECT NOME, CARGO, SALARIO
FROM
PESSOAS P
INNER JOIN (TABLE(LIST_PKG.IDS('34,45,67,1,99')) T
ON T.COLUMN_VALUE=P.MATRICULA)


É claro que o melhor é colocar essas consultas numa procedure ou function:


PROCEDURE BUSCAR_PESSOAS_PROC (
P_MATRICULAS IN VARCHAR2,
P_CURSOR OUT SYS_REFCURSOR
) IS
BEGIN
OPEN P_CURSOR FOR
SELECT NOME, CARGO, SALARIO
FROM
PESSOAS P
INNER JOIN (TABLE(LIST_PKG.IDS(P_MATRICULAS)) T
ON T.COLUMN_VALUE=P.MATRICULA);
END;


E qual a mágica que precisa ocorrer dentro de LIST_PKG? Segue o código:


CREATE OR REPLACE PACKAGE BODY LIST_PKG AS

FUNCTION IDS(LIST IN VARCHAR2) RETURN ID_TABLE PIPELINED IS
LEN PLS_INTEGER;
TAB SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
IF LIST IS NULL THEN
RETURN;
END IF;

SYS.DBMS_UTILITY.COMMA_TO_TABLE(
'"' || REPLACE(LIST, ',', '","') || '"',
LEN, TAB);

FOR I IN 1 .. LEN LOOP
PIPE ROW(TRANSLATE(TAB(I), 'A"', 'A'));
END LOOP;
RETURN;
END IDS;

FUNCTION NAMES(LIST IN VARCHAR2) RETURN NAME_TABLE PIPELINED IS
LEN PLS_INTEGER;
TAB SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
IF LIST IS NULL THEN
RETURN;
END IF;

SYS.DBMS_UTILITY.COMMA_TO_TABLE(
'"' || REPLACE(LIST, ',', '","') || '"',
LEN, TAB);

FOR I IN 1 .. LEN LOOP
PIPE ROW(TRANSLATE(TAB(I), 'A"', 'A'));
END LOOP;
RETURN;

END NAMES;

END LIST_PKG;


Como se pode ver, as funções simplesmente dividem strings, separando os elementos entre vírgulas, e depois repassam os valores através do comando PIPE ROW. Pronto, nunca mais será preciso fazer ginástica com strings; essa package ocupa-se de todo o trabalho.

3 comentários:

Marcus Aurelius disse...

Bem interessante.

Mas não daria pra fazer TRANSLATE(TAB(I), '"', '') em vez de TRANSLATE(TAB(I), 'A"', 'A')?

Num teste rápido (sem reproduzir toda a função), funcionou.

Marcus Aurelius disse...

(continuando)
Ou usar REPLACE mesmo.

forinti disse...

Salve Marcus,

acho que trocar TRANSLATE por REPLACE é uma boa idéia mesmo, mas o TRANSLATE devolve NULL se o último parâmetro for NULL ou '' (que é igual a NULL para o Oracle). Então, aquele A só serve para evitar esse problema.