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.