Oracle PL/SQL BULK COLLECT: Exemplo FORALL

O que é COLETA EM GRANEL?

A COLETA EM MASSA reduz as trocas de contexto entre os SQL e o mecanismo PL/SQL, permitindo que o mecanismo SQL busque os registros de uma só vez.

Oracle PL/SQL Oferece a funcionalidade de buscar registros em lote, em vez de buscá-los um por um. Este comando BULK COLLECT pode ser usado em uma instrução SELECT para preencher os registros em lote ou para buscar um conjunto de registros. cursor Em lote. Como o BULK COLLECT busca os registros em lote, a cláusula INTO deve sempre conter uma variável de tipo de coleção. A principal vantagem de usar o BULK COLLECT é o aumento de desempenho, que reduz a interação entre o banco de dados e o mecanismo PL/SQL.

Sintaxe:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

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

Cláusula FORALL

A instrução FORALL executa Operações DML em dados em massa. Assemelha-se a uma instrução de laço FOR, exceto que em um laço FOR as ações ocorrem no nível do registro, enquanto em FORALL não existe o conceito de laço. Em vez disso, todos os dados presentes no intervalo especificado são processados ​​simultaneamente.

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 presentes entre os limites inferior e superior.

Cláusula LIMIT

O conceito de coleta em massa carrega todos os dados na variável de coleção de destino de uma só vez, ou seja, todos os dados serão inseridos na variável de coleção de uma só vez. No entanto, isso não é recomendável quando o número total de registros a serem carregados é muito grande, pois, ao tentar carregar todos os dados, o PL/SQL consome mais memória da sessão. Portanto, é sempre bom limitar o tamanho dessa operação de coleta em massa.

Esse limite de tamanho pode ser facilmente alcançado introduzindo a condição ROWNUM na instrução SELECT, enquanto que no caso de um 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 lote.

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 juntamente com a cláusula LIMIT.

BULK COLLECT Atributos

Semelhante aos atributos de cursor, o comando BULK COLLECT possui a função %BULK_ROWCOUNT(n), que retorna o número de linhas afetadas na n-ésima instrução DML do comando FORALL, ou seja, fornece 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 do valor na coleção para a qual a contagem de linhas é necessária.

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

A captura de tela abaixo mostra este exemplo de BULK COLLECT e FORALL juntamente com sua saída em Oracle.

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 salary=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

Code Explicação:

• Code linha 2: Declarando o cursor guru99_det para a instrução 'SELECT emp_name FROM emp'.
• Code linha 3: Declarando lv_emp_name_tbl como um tipo de tabela VARCHAR2(50).
• Code linha 4: Declarando lv_emp_name como o tipo lv_emp_name_tbl.
• Code linha 6: Abrindo o cursor.
• Code linha 7: Obtendo o cursor usando BULK COLLECT com o tamanho LIMIT definido como 5000 e armazenando-o na variável lv_emp_name.
• Code linhas 8-11: Configure um loop FOR para imprimir todos os registros da coleção lv_emp_name.
• Code linha 12: Utilizando a função FORALL para atualizar o salário de todos os funcionários em 5000.
• Code linha 14: Comprometer-se com o transação.

Perguntas Frequentes