Em PL/SQL você consegue executar uma declaração SELECT em uma coleção, como se ela fosse uma tabela. Isso pode ser muito útil se você precisar filtrar registros de uma coleção ou executar loops constantes no mesmo cursor, por exemplo. Mas para realizar uma operação como essa, você precisa:
1. Criar um objeto armazenado de banco de dados que contenha as colunas da coleção;
2. Criar um uma tipo (type) armazenado no banco de dados que seja uma tabela do objeto criado;
A criação de um objeto como esse pode se parecer muito com a definição de tipo registro ( redord), como no exemplo abaixo:
CREATE OR REPLACE TYPE ObjCliente AS OBJECT (cli_in_codigo INTEGER,
cli_st_nome VARCHAR2(20), cli_ch_novo CHAR(1)
) /
Estando o objeto criado, você deve criar um tipo tabela desse objeto. O comando para isso é quase igual a definição de um tipo tabela dentro de um bloco. A única diferença é o uso da palavra CREATE (ou CREATE OR REPLACE). Veja:
CREATE OR REPLACE TYPE TCliente IS TABLE OF ObjCliente; /
Por fim, dentro do seu bloco, você só precisa declarar uma variável do tipo criado e usá-la como as coleções que já vimos até aqui:
DECLARE
cCli TCliente; BEGIN
NULL; END; /
Depois que você carregar os dados de sua coleção, você pode executar uma declaração SELECT nela. Para que PL/SQL visualizar a coleção como uma tabela, é necessário moldar a coleção como sendo do seu tipo, através da função CAST e depois, moldar resultado desse CAST como uma tabela. Veja o exemplo abaixo:
-- Imprimir total de clientes novos SELECT COUNT(*)
INTO vCount
FROM TABLE(CAST(cCli AS TCliente)) Onde:
➢ vCount é uma variável do tipo inteira previamente declarada;
➢ CAST é a função que informa ao PL/SQL que cCli deve ser visto como um tipo Tcliente; ➢ e TABLE faz com que a PL/SQL veja a variável do tipo TCliente, como uma tabela.
CUIDADO: Quando usamos um tipo tabela aninhada armazenado para manipular coleções, nos deparamos com um problema de inicialização da coleção. Não basta inicializar a coleção usando o construtor do seu tipo, como vimos na seção de tabelas aninhadas. Além disso, a cada entrada estendida, é necessário inicializar a entrada com valores nulos antes de manipulá-la, usando uma variável do mesmo tipo do elemento da coleção ou usando o próprio tipo para passar os valores nulos, como no exemplo abaixo:
cCli(1):= ObjCliente(NULL,NULL,NULL);
Nesse exemplo, a entrada 1 da coleção cliente é inicializada com nulo para cada um dos seus três campos, usando para isso o objeto ObjCliente, usado para definir o tipo de cada elemento do tipo TCliente, que por sua vez foi usado para declarar a variável cCli.
Para entendermos melhor, vejamos o seguinte exemplo: 1. Criação do objeto armazenado:
CREATE OR REPLACE TYPE ObjCliente AS OBJECT (Codigo INTEGER,
Nome VARCHAR2(20), Novo CHAR(1)
) /
2. Criação do tipo armazenado, sendo cada elemento do tipo do objeto criado no passoa 1: CREATE OR REPLACE TYPE TCliente IS TABLE OF ObjCliente;
/
3. Criação do bloco que recupera todos os clientes, através de um cursor, e inclui cada cliente retornado na coleção, alimentando o código do cliente, nome do cliente e, com base na data de
inclusão, se o cliente é novo ou não. Após alimentar a coleção, imprimimos cada um dos clientes informando se é um cliente novo ou antigo. Por fim, executamos uma declaração SELECT para imprimir a quantidade de clientes novos e outra para imprimir a quantidade de clientes antigos:
DECLARE -- Declaração de cursores CURSOR cs_cliente IS SELECT c.* FROM cliente c; -- Declaração de variáveis cCli TCliente; vIndice PLS_INTEGER := 0; vCount PLS_INTEGER := 0; BEGIN
-- Inicializa a coleção criando uma entrada vazia cCli := TCliente();
-- Adiciona uma entrada na coleção para cada representante do cadastro FOR csc IN cs_cliente LOOP
-- Estender a coleção em 1 entrada cCli.EXTEND;
-- Definir valor do índice vIndice := cCli.LAST;
-- Inicializar a entrada estendida
cCli(vIndice):= ObjCliente(NULL,NULL,NULL);
-- Atualizar os dados da entrada
cCli(vIndice).Codigo := csc.cli_in_codigo; cCli(vIndice).Nome := csc.cli_st_nome;
IF csc.cli_dt_inclusao BETWEEN add_months(trunc(SYSDATE),-12) AND SYSDATE THEN
cCli(vIndice).Novo := 'S'; ELSE cCli(vIndice).Novo := 'N'; END IF; END LOOP;
-- Imprimir código e nome de cada cliente informando se é novo ou antigo FOR i IN 1..vIndice LOOP
CASE cCli(i).Novo
WHEN 'S' THEN
dbms_output.put_line('Cliente '||cCli(i).Nome||' ('||cCli(i).Codigo||') é Novo!');
WHEN 'N' THEN
dbms_output.put_line('Cliente '||cCli(i).Nome||' ('||cCli(i).Codigo||') é Antigo!');
END CASE; END LOOP;
-- Quebrar linha
dbms_output.put_line('');
-- Imprimir total de clientes novos SELECT COUNT(*)
FROM TABLE(CAST(cCli AS TCliente)) cn WHERE cn.novo = 'S';
dbms_output.put_line('Total de Clientes Novos: '||vCount);
-- Imprimir total de clientes antigos SELECT COUNT(*)
INTO vCount
FROM TABLE(CAST(cCli AS TCliente)) cn WHERE cn.novo = 'N';
dbms_output.put_line('Total de Clientes Antigos: '||vCount); END;
/
O uso desse recurso pode representar ganhos consideráveis em aplicações críticas, pois os dados são recuperados uma única vez e pode ser utilizado em memória quantas vezes for necessário, além de possibilitar uma manipulação pontual de cada registro através dos métodos de tabela, já estudados.
Criando um cursor explícito a partir de uma coleção
Outra maneira de utilizar uma coleção em uma instrução SQL, é atribuindo seu retorno para uma variável do tipo REF CURSOR e manipulá-la como um curso explícito.
Para isso basta definirmos um tipo REF CURSOR e declararmos uma variável desse tipo. Depois é só abrir o cursor referenciando a variável definida, usando para isso a declaração SELECT feita na coleção.
O exemplo abaixo é semelhante ao anterior, porém, após alimentarmos a coleção, abrimos um cursor baseado nessa coleção restringindo seu retorno aos clientes novos. Em seguido iniciamos um LOOP no cursor e imprimimos cada um dos clientes na tela.
DECLARE /******** Declaração de cursores ********/ CURSOR cs_cliente IS SELECT c.* FROM cliente c; /******** Definição de tipos ********/ TYPE TCursor IS REF CURSOR;
TYPE TRegistroCliente IS RECORD( Codigo INTEGER, Nome VARCHAR2(20), Novo CHAR(1) ); /******** Declaração de variáveis ********/ -- variáveis de cursores cs_ClienteNovo TCursor; -- coleções cCli TCliente; -- registros rCliente TRegistroCliente; -- esclares vIndice PLS_INTEGER := 0; BEGIN
-- Inicializa a coleção criando uma entrada vazia cCli := TCliente();
-- Adiciona uma entrada na coleção para cada representante do cadastro FOR csc IN cs_cliente LOOP
-- Estender a coleção em 1 entrada cCli.EXTEND;
-- Definir valor do índice vIndice := cCli.LAST;
-- Inicializar a entrada estendida
cCli(vIndice):= ObjCliente(NULL,NULL,NULL);
-- Atualizar os dados da entrada
cCli(vIndice).Codigo := csc.cli_in_codigo; cCli(vIndice).Nome := csc.cli_st_nome;
IF csc.cli_dt_inclusao BETWEEN add_months(trunc(SYSDATE),-12) AND SYSDATE THEN
cCli(vIndice).Novo := 'S'; ELSE
cCli(vIndice).Novo := 'N'; END IF;
END LOOP;
-- Abri o cursor com uma declaração SELECT na coleção cCli OPEN cs_ClienteNovo FOR SELECT Codigo,Nome,Novo
FROM TABLE(CAST(cCli AS TCliente)) cn WHERE cn.novo = 'S';
-- Imprimir código e nome de cada cliente novo LOOP
-- Recupera registro do cursor aberto a partir da coleção FETCH cs_ClienteNovo INTO rCliente;
-- Sai do loop quando não encontrar registro no cursor EXIT WHEN cs_ClienteNovo%NOTFOUND;
dbms_output.put_line('Cliente '||rCliente.Nome||
' ('||rCliente.Codigo||') é Novo!' ); END LOOP; -- Fecha cursor CLOSE cs_ClienteNovo; END; /
IMPORTANTE: Tipos de coleções locais não são permitidos em instruções SQL, apenas tipos armazenados.