Oracle PL/SQL BULK COLLECT: Exemplo FORALL

O que é COLETA EM GRANEL?

BULK COLLECT reduz as alternâncias de contexto entre o mecanismo SQL e PL/SQL e permite que o mecanismo SQL busque os registros de uma só vez.

Oracle PL/SQL fornece a funcionalidade de buscar os registros em massa, em vez de buscá-los um por um. Este BULK COLLECT pode ser usado na instrução 'SELECT' para preencher os registros em massa ou buscar o cursor em massa. Como BULK COLLECT busca o registro em BULK, a cláusula INTO deve sempre conter uma variável do tipo coleção. A principal vantagem de usar BULK COLLECT é aumentar o desempenho reduzindo a interação entre o banco de dados e o mecanismo PL/SQL.

Sintaxe:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

Na sintaxe acima, BULK COLLECT é usado para coletar os dados das instruções 'SELECT' e 'FETCH'.

Cláusula FORALL

O FORALL permite realizar operações DML em dados em massa. É semelhante ao da instrução do loop FOR, exceto em laço FOR as coisas acontecem em nível de registro, enquanto no FORALL não existe o conceito de LOOP. Em vez disso, todos os dados presentes no intervalo determinado são processados ​​ao mesmo tempo.

Sintaxe:

FORALL <loop_variable>in<lower range> .. <higher range> 

<DML operations>;

Na sintaxe acima, a operação DML fornecida será executada para todos os dados que estão presentes entre o intervalo inferior e superior.

Cláusula LIMIT

O conceito de coleta em massa carrega todos os dados na variável de coleta de destino em massa, ou seja, todos os dados serão preenchidos na variável de coleta de uma só vez. Mas isto não é aconselhável quando o total de registros que precisam ser carregados é muito grande, pois quando PL/SQL tenta carregar todos os dados, consome mais memória da sessão. Portanto, é sempre bom limitar o tamanho desta operação de coleta em massa.

No entanto, este limite de tamanho pode ser facilmente alcançado introduzindo a condição ROWNUM na instrução 'SELECT', enquanto no caso do cursor isso não é possível.

Para superar isso Oracle forneceu a cláusula 'LIMIT' que define o número de registros que precisam ser incluídos no volume.

Sintaxe:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

Na sintaxe acima, a instrução de busca do cursor usa a instrução BULK COLLECT junto com a cláusula LIMIT.

BULK COLLECT Atributos

Semelhante a cursor atributos BULK COLLECT possui %BULK_ROWCOUNT(n) que retorna o número de linhas afetadas no n^th Instrução DML da instrução FORALL, ou seja, fornecerá a contagem de registros afetados na instrução FORALL para cada valor da variável de coleção. O termo 'n' indica a sequência de valores na coleção, para a qual a contagem de linhas é necessária.

Exemplo 1: Neste exemplo, projetaremos todos os nomes dos funcionários da tabela emp usando BULK COLLECT e também aumentaremos o salário de todos os funcionários em 5000 usando FORALL.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||c_emp_name);
END LOOP:
FORALL i IN lv_emp_name.FIRST .. lv emp_name.LAST
UPDATE emp SET salaiy=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;	
Dbms_output.put_line(‘Salary Updated‘);
CLOSE guru99_det;
END;
/

saída

Employee Fetched:BBB
Employee Fetched:XXX 
Employee Fetched:YYY
Salary Updated

Explicação do código:

• Linha de código 2: Declarando o cursor guru99_det para a instrução 'SELECT emp_name FROM emp'.
• Linha de código 3: Declarando lv_emp_name_tbl como tipo de tabela de VARCHAR2(50)
• Linha de código 4: Declarando lv_emp_name como tipo lv_emp_name_tbl.
• Linha de código 6: Abrindo o cursor.
• Linha de código 7: Buscando o cursor usando BULK COLLECT com o tamanho LIMIT como variável intl lv_emp_name 5000.
• Linha de código 8-11: Configurando o loop FOR para imprimir todos os registros da coleção lv_emp_name.
• Linha de código 12: Usando FORALL atualizando o salário de todos os funcionários em 5000.
• Linha de código 14: Confirmando a transação.