O bulk binding da PL/SQL é um recurso que surgiu na versão Oracle8i. O bulk binding permite codificar as declarações SQL que operam em todas as entradas de uma coleção, sem ter de fazer o LOOP em toda a coleção usando o código PL/SQL. Vários dos exemplos dados até aqui, usaram um LOOP FOR de cursor para carregar os dados de uma tabela de banco de dados para uma tabela ou array da PL/SQL. A mudança da SQL (FETCH) para a PL/SQL (para adicionar os dados ao array) é chamada de
mudança de contexto e consome muita overhead. Você pode usar o recurso de bulk binding para evitar
grande parte daquela overhead.
Duas novas palavras-chave suportam o binding: BULK COLLECT e FORALL.,
BULK COLLECT é usada com as declarações SELECT para colocar todos os dados em uma coleção. FORALL é usada com as declarações INSERT, UPDATE e DELETE para executar aquelas declarações uma vez para cada elemento de uma coleção.
Usando BULK COLLECT
Você pode usar as palavras-chave BULK COLLECT para ter os resultados de uma declaração SELECT colocados diretamente em uma coleção. Você pode usar BULK COLLECT com as declarações SELECT INTO e também com as declarações FETCH. Por exemplo, se RepCodigos e RepNomes fossem ambas tabelas aninhadas, você emitiria a seguinte declaração SELECT para gerar nelas uma entrada para cada representante existente na tabela representante:
SELECT rep_in_codigo, rep_st_nome BULK COLLECT INTO RepCodigos, RepNomes FROM representante;
Se você tivesse um cursor chamado cs_representante que retornasse os mesmos dados, você poderia escrever BULK COLLECT na declaração FETCH da seguinte maneira:
OPEN cs_representante;
FETCH cs_representante BULK COLLECT INTO RepCodigos, RepNomes; CLOSE cs_representante;
Até o Oracle 8i, por algum motivo, a Oracle não permitia que você usasse BULK COLLECT em uma coleção de registros. Assim sendo, se você selecionasse dez colunas, precisaria declarar dez coleções, uma para cada coluna.
A partir da versão 9r2 do Oracle Database, isso mudou, e passou a ser aceito o uso de coleções de registro. Dessa forma, se tivéssemos uma variável chamada cRep que fosse de um tipo registro que contivesse uma coluna para código e outra para nome, poderíamos reescrever nossos dois exemplos acima, como abaixo:
➢ Na declaração SELECT:
SELECT rep_in_codigo, rep_st_nome BULK COLLECT INTO cRep
FROM representante; ➢ No FETCH do cursor:
OPEN cs_representante;
FETCH cs_representante BULK COLLECT INTO cRep; CLOSE cs_representante;
Para exemplificar melhor, vamos reescrever o exemplo que classifica o cliente como novo ou antigo: DECLARE -- Declaração de cursores CURSOR cs_cliente IS SELECT ObjCliente( c.cli_in_codigo, c.cli_st_nome,
(CASE
WHEN (c.cli_dt_inclusao BETWEEN add_months(trunc(SYSDATE),-12) AND SYSDATE) THEN 'S'
ELSE 'N' END ) ) FROM cliente c; -- Declaração de variáveis cCli TCliente; vCount PLS_INTEGER := 0; BEGIN
-- Inicializa a coleção criando uma entrada vazia cCli := TCliente();
OPEN cs_cliente;
FETCH cs_cliente BULK COLLECT INTO cCli; CLOSE cs_cliente;
-- Imprimir código e nome de cada cliente informando se é novo ou antigo FOR i IN 1..cCli.COUNT 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(*)
INTO vCount
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;
/
Este bloco PL/SQL apresenta apenas duas alterações em relação ao bloco que usamos para exemplificar o uso de declaração SELECT com coleções:
1. A declaração SELECT do cursor cs_cliente, com o uso da instrução CASE, já retorna se o cliente é novo ou não e as colunas retornadas na declaração são moldadas para o objeto ObjCliente (uma espécie de CAST);
2. O código que executava um LOOP no cursor e atribuía os dados de cada cliente para a coleção, foi substituído por três linhas que:
a) Abre o cursor (OPEN cs_cliente);
b) Alimenta a coleção com todos os dados do cursor (FETCH cs_cliente BULK COLLECT INTO cCli);
c) Fecha o cursor (CLOSE cs_cliente).
Desta forma, ganhamos em performance e deixamos o código mais enxuto. Usando FORALL
A palavra-chave FORALL permite que você baseie uma declaração DML (Data Manipulation Language), ou seja, INSERT, UPDATE ou DELETE, no conteúdo de uma coleção. Quando FORALL é usada, a declaração é executada uma vez para cada entrada da coleção, mas apenas uma mudança de contexto é feita da PL/SQL para a SQL. O desempenho resultante é muito mais rápido do que aquilo que você teria se codificasse um LOOP na PL/SQL.
Para exemplificar o uso do FORALL, vamos alterar o exemplo do BULK COLLECT para passar o nome de todos os clientes para maiúsculas:
DECLARE -- Declaração de cursores CURSOR cs_cliente IS SELECT c.cli_in_codigo, c.cli_st_nome FROM cliente c; -- Definição de tipo
TYPE TClienteID IS TABLE OF cliente.cli_in_codigo%TYPE; TYPE TClienteNome IS TABLE OF cliente.cli_st_nome%TYPE; -- Declaração de variáveis
cCliID TClienteID; cCliNome TClienteNome; vCount PLS_INTEGER := 0; BEGIN
-- Carregar coleções com dados do cursor OPEN cs_cliente;
FETCH cs_cliente BULK COLLECT INTO cCliID, cCliNome; CLOSE cs_cliente;
-- Alterar todos dos nomes da tabela de cliente para maiúscula FORALL x IN cCliID.FIRST..cCliID.LAST
UPDATE cliente
SET cli_st_nome = upper(cCliNome(x)) WHERE cli_in_codigo = cCliID(x);
-- Capturar quantidade de linhas atualizadas vCount := SQL%ROWCOUNT;
dbms_output.put_line(vCount||' registro(s) atualizado(s).');
-- Efetivar alterações no banco COMMIT;
END; /
Observe que neste exemplo, definimos duas coleções cujos tipos estão definidos no próprio bloco, ao invés de estarem armazenados no banco de dados. Uma coleção é para conter o código do cliente e a outra para conter o nome. Isso é necessário porque na declaração UPDATE do recurso FORALL, não conseguimos referenciar o campo de um registro usado num bulk binding. Logo não conseguiríamos utilizar o campo codigo do objeto ObjCliente usado para definir os elementos do tipo TCliente (como vínhamos fazendo até aqui).
Se você tentar referenciar um campo de registro ao utilizar FORALL, receberá o seguinte erro de retorno:
PLS-00436: restrição de implementação: não é possível fazer referência a campos da tabela de registros BULK In-BIND