Programação avançada com FORALL: Como usar o comando que permite executar operações DML em lote, de forma rápida e eficiente

Programação avançada com FORALL: Como usar o comando que permite executar operações DML em lote, de forma rápida e eficiente

Olá, caro leitor !

Se você está interessado em aprender como usar o comando FORALL no PL/SQL, o subconjunto procedural do SQL usado no Oracle Database, você veio ao lugar certo. Neste artigo, vamos mostrar como você pode usar esse comando que permite executar operações DML (Data Manipulation Language) em lote, de forma rápida e eficiente, usando o Oracle 19c como base.

Mas afinal, o que é o comando FORALL? O comando FORALL é um comando que permite executar operações DML, como INSERT, UPDATE, DELETE ou MERGE, em um conjunto de dados armazenados em uma coleção PL/SQL, que é uma estrutura de dados que pode armazenar vários valores do mesmo tipo. O comando FORALL é usado para melhorar o desempenho das operações DML, pois ele reduz o número de chamadas à camada SQL, enviando os dados da coleção em um único bloco, em vez de enviar um dado por vez. O comando FORALL também simplifica o código das operações DML, pois ele usa uma sintaxe compacta e intuitiva, que evita o uso de laços explícitos.

O comando FORALL tem a seguinte sintaxe:

FORALL index IN lower_bound .. upper_bound [SAVE EXCEPTIONS]
  DML_statement;

Onde:

• index é o nome de uma variável que representa o índice da coleção, que é usado para referenciar os dados da coleção na operação DML.
• lower_bound e upper_bound são os limites inferior e superior do intervalo de índices da coleção, que definem o conjunto de dados que serão processados pela operação DML. Esses limites podem ser expressões numéricas ou constantes, mas devem ser do mesmo tipo de dado que o índice da coleção.
• SAVE EXCEPTIONS é uma cláusula opcional que indica que o comando FORALL deve continuar a executar mesmo que ocorram erros, e que os erros devem ser armazenados em uma coleção de exceções, que pode ser consultada posteriormente. Se essa cláusula não for usada, o comando FORALL será interrompido na primeira ocorrência de um erro, e o erro será propagado para o bloco PL/SQL que o chamou.
• DML_statement é a operação DML que será executada em lote, usando os dados da coleção. A operação DML pode ser um INSERT, um UPDATE, um DELETE ou um MERGE, e deve usar a variável de índice para referenciar os dados da coleção. A operação DML também pode usar outras variáveis, constantes ou expressões, desde que sejam compatíveis com a operação.

Para ilustrar o uso do comando FORALL, vamos ver alguns exemplos práticos.

Exemplo 1: Usando o comando FORALL para inserir dados em uma tabela

Suponha que temos uma tabela chamada produtos, com as seguintes colunas: id, nome, preco e estoque. Suponha também que temos uma coleção PL/SQL do tipo TABLE, chamada novos_produtos, que armazena registros com os mesmos campos da tabela produtos. A coleção novos_produtos contém os dados de alguns produtos que queremos inserir na tabela produtos.

-- Cria a tabela produtos

CREATE TABLE produtos (
  id NUMBER PRIMARY KEY,
  nome VARCHAR2(50) NOT NULL,
  preco NUMBER(10,2) NOT NULL,
  estoque NUMBER NOT NULL
);

-- Declara o tipo de registro produto_t, que contém os mesmos campos da tabela produtos

DECLARE
  TYPE produto_t IS RECORD (
    id NUMBER,
    nome VARCHAR2(50),
    preco NUMBER(10,2),
    estoque NUMBER
  );
  
-- Declara o tipo de coleção produto_tab, que é uma tabela de registros do tipo produto_t

  TYPE produto_tab IS TABLE OF produto_t INDEX BY PLS_INTEGER;
  
-- Declara a coleção novos_produtos, que é uma instância do tipo produto_tab
  novos_produtos produto_tab;

BEGIN
  -- Preenche a coleção novos_produtos com alguns dados
  novos_produtos(1).id := 1;
  novos_produtos(1).nome := 'Caneta';
  novos_produtos(1).preco := 1.50;
  novos_produtos(1).estoque := 100;
  
  novos_produtos(2).id := 2;
  novos_produtos(2).nome := 'Caderno';
  novos_produtos(2).preco := 10.00;
  novos_produtos(2).estoque := 50;
  
  novos_produtos(3).id := 3;
  novos_produtos(3).nome := 'Lápis';
  novos_produtos(3).preco := 0.50;
  novos_produtos(3).estoque := 200;
  
  -- Usa o comando FORALL para inserir os dados da coleção na tabela produtos

  FORALL i IN 1 .. novos_produtos.COUNT
    INSERT INTO produtos VALUES (novos_produtos(i).id, novos_produtos(i).nome, novos_produtos(i).preco, novos_produtos(i).estoque);
    
  -- Exibe uma mensagem confirmando a inserção

  DBMS_OUTPUT.PUT_LINE('Foram inseridos ' || SQL%ROWCOUNT || ' produtos na tabela.');
END;
/

O comando FORALL percorre o intervalo de índices da coleção novos_produtos, que vai de 1 até o número de elementos da coleção, e executa um INSERT na tabela produtos, usando os dados da coleção. O comando FORALL envia os dados da coleção em um único bloco, em vez de enviar um dado por vez, o que melhora o desempenho da operação. O comando FORALL também simplifica o código da operação, pois evita o uso de um laço explícito, como um FOR ou um WHILE, para inserir os dados.

O resultado do comando FORALL é exibido na tela, confirmando que foram inseridos três produtos na tabela.

Foram inseridos 3 produtos na tabela.

Exemplo 2: Usando o comando FORALL para atualizar dados em uma tabela

Suponha que temos uma tabela chamada clientes, com as seguintes colunas: id, nome, email e telefone. Suponha também que temos uma coleção PL/SQL do tipo TABLE, chamada novos_emails, que armazena registros com os campos id e email. A coleção novos_emails contém os dados de alguns clientes que queremos atualizar na tabela clientes.

-- Cria a tabela clientes

CREATE TABLE clientes (
  id NUMBER PRIMARY KEY,
  nome VARCHAR2(50) NOT NULL,
  email VARCHAR2(50) NOT NULL,
  telefone VARCHAR2(20) NOT NULL
);

-- Insere alguns dados na tabela clientes

INSERT INTO clientes VALUES (1, 'Ana', 'ana@gmail.com', '11-1111-1111');
INSERT INTO clientes VALUES (2, 'Bruno', 'bruno@gmail.com', '22-2222-2222');
INSERT INTO clientes VALUES (3, 'Carla', 'carla@gmail.com', '33-3333-3333');

-- Declara o tipo de registro email_t, que contém os campos id e email

DECLARE
  TYPE email_t IS RECORD (
    id NUMBER,
    email VARCHAR2(50)
  );
  
-- Declara o tipo de coleção email_tab, que é uma tabela de registros do tipo email_t

  TYPE email_tab IS TABLE OF email_t INDEX BY PLS_INTEGER;
  
-- Declara a coleção novos_emails, que é uma instância do tipo email_tab
  novos_emails email_tab;

BEGIN
  -- Preenche a coleção novos_emails com alguns dados

  novos_emails(1).id := 1;
  novos_emails(1).email := 'ana@hotmail.com';
  
  novos_emails(2).id := 2;
  novos_emails(2).email := 'bruno@yahoo.com';
  
  novos_emails(3).id := 3;
  novos_emails(3).email := 'carla@outlook.com';
  
  -- Usa o comando FORALL para atualizar os dados da coleção na tabela clientes

  FORALL i IN 1 .. novos_emails.COUNT
    UPDATE clientes SET email = novos_emails(i).email WHERE id = novos_emails(i).id;
    
  -- Exibe uma mensagem confirmando a atualização

  DBMS_OUTPUT.PUT_LINE('Foram atualizados ' || SQL%ROWCOUNT || ' clientes na tabela.');
END;
/

O comando FORALL percorre o intervalo de índices da coleção novos_emails, que vai de 1 até o número de elementos da coleção, e executa um UPDATE na tabela clientes, usando os dados da coleção. O comando FORALL envia os dados da coleção em um único bloco, em vez de enviar um dado por vez, o que melhora o desempenho da operação. O comando FORALL também simplifica o código da operação, pois evita o uso de um laço explícito, como um FOR ou um WHILE, para atualizar os dados.

O resultado do comando FORALL é exibido na tela, confirmando que foram atualizados três clientes na tabela.

Foram atualizados 3 clientes na tabela.

Exemplo 3: Usando o comando FORALL para deletar dados em uma tabela

Suponha que temos uma tabela chamada pedidos, com as seguintes colunas: id, data, valor e cliente_id. Suponha também que temos uma coleção PL/SQL do tipo TABLE, chamada pedidos_cancelados, que armazena números inteiros que representam os ids dos pedidos que queremos deletar na tabela pedidos.

-- Cria a tabela pedidos

CREATE TABLE pedidos (
  id NUMBER PRIMARY KEY,
  data DATE NOT NULL,
  valor NUMBER(10,2) NOT NULL,
  cliente_id NUMBER NOT NULL,
  CONSTRAINT fk_cliente_id FOREIGN KEY (cliente_id) REFERENCES clientes (id)
);

-- Insere alguns dados na tabela pedidos

INSERT INTO pedidos VALUES (1, '2021-10-01', 100.00, 1);
INSERT INTO pedidos VALUES (2, '2021-10-02', 200.00, 2);
INSERT INTO pedidos VALUES (3, '2021-10-03', 300.00, 3);
INSERT INTO pedidos VALUES (4, '2021-10-04', 400.00, 1);
INSERT INTO pedidos VALUES (5, '2021-10-05', 500.00, 2);

-- Declara o tipo de coleção pedido_tab, que é uma tabela de números inteiros

DECLARE
  TYPE pedido_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  
-- Declara a coleção pedidos_cancelados, que é uma instância do tipo pedido_tab
  pedidos_cancelados pedido_tab;

BEGIN
  -- Preenche a coleção pedidos_cancelados com alguns dados

  pedidos_cancelados(1) := 2;
  pedidos_cancelados(2) := 4;
  pedidos_cancelados(3) := 5;
  
  -- Usa o comando FORALL para deletar os dados da coleção na tabela pedidos

  FORALL i IN 1 .. pedidos_cancelados.COUNT
    DELETE FROM pedidos WHERE id = pedidos_cancelados(i);
    
  -- Exibe uma mensagem confirmando a deleção

  DBMS_OUTPUT.PUT_LINE('Foram deletados ' || SQL%ROWCOUNT || ' pedidos na tabela.');
END;
/

O comando FORALL percorre o intervalo de índices da coleção pedidos_cancelados, que vai de 1 até o número de elementos da coleção, e executa um DELETE na tabela pedidos, usando os dados da coleção. O comando FORALL envia os dados da coleção em um único bloco, em vez de enviar um dado por vez, o que melhora o desempenho da operação. O comando FORALL também simplifica o código da operação, pois evita o uso de um laço explícito, como um FOR ou um WHILE, para deletar os dados.

O resultado do comando FORALL é exibido na tela, confirmando que foram deletados três pedidos na tabela.

Foram deletados 3 pedidos na tabela.

Exemplo 4: Usando o comando FORALL com a cláusula SAVE EXCEPTIONS para tratar erros em lote

Em alguns casos, pode ser útil usar o comando FORALL com a cláusula SAVE EXCEPTIONS, que permite que o comando FORALL continue a executar mesmo que ocorram erros, e que os erros sejam armazenados em uma coleção de exceções, que pode ser consultada posteriormente. Essa cláusula é útil para tratar erros em lote, sem interromper a execução do comando FORALL, e sem perder as informações sobre os erros.

Para usar o comando FORALL com a cláusula SAVE EXCEPTIONS, é necessário declarar uma variável do tipo SQL%BULK_EXCEPTIONS, que é um tipo de registro predefinido que contém os campos ERROR_INDEX e ERROR_CODE, que armazenam o índice e o código do erro, respectivamente. Essa variável é usada para armazenar os erros que ocorrem durante a execução do comando FORALL, e pode ser usada para exibir, corrigir ou reprocessar os erros.

Vamos ver um exemplo de como usar o comando FORALL com a cláusula SAVE EXCEPTIONS para tratar erros em lote.

Suponha que temos uma tabela chamada funcionarios, com as seguintes colunas: id, nome, salario e departamento_id. Suponha também que temos uma coleção PL/SQL do tipo TABLE, chamada aumentos, que armazena registros com os campos id e salario. A coleção aumentos contém os dados de alguns funcionários que queremos atualizar na tabela funcionarios, aumentando o seu salário em 10%.

-- Cria a tabela funcionarios

CREATE TABLE funcionarios (
  id NUMBER PRIMARY KEY,
  nome VARCHAR2(50) NOT NULL,
  salario NUMBER(10,2) NOT NULL,
  departamento_id NUMBER NOT NULL,
  CONSTRAINT fk_departamento_id FOREIGN KEY (departamento_id) REFERENCES departamentos (id)
);

-- Insere alguns dados na tabela funcionarios

INSERT INTO funcionarios VALUES (1, 'Alice', 1000.00, 1);
INSERT INTO funcionarios VALUES (2, 'Bob', 2000.00, 2);
INSERT INTO funcionarios VALUES (3, 'Charlie', 3000.00, 3);
INSERT INTO funcionarios VALUES (4, 'David', 4000.00, 4);
INSERT INTO funcionarios VALUES (5, 'Eve', 5000.00, 5);

-- Declara o tipo de registro aumento_t, que contém os campos id e salario

DECLARE
  TYPE aumento_t IS RECORD (
    id NUMBER,
    salario NUMBER(10,2)
  );
  
-- Declara o tipo de coleção aumento_tab, que é uma tabela de registros do tipo aumento_t

  TYPE aumento_tab IS TABLE OF aumento_t INDEX BY PLS_INTEGER;
  
-- Declara a coleção aumentos, que é uma instância do tipo aumento_tab
  aumentos aumento_tab;
  
-- Declara a variável erros, que é do tipo SQL%BULK_EXCEPTIONS

  erros SQL%BULK_EXCEPTIONS;
BEGIN
  -- Preenche a coleção aumentos com alguns dados
  aumentos(1).id := 1;
  aumentos(1).salario := 1100.00;
  
  aumentos(2).id := 2;
  aumentos(2).salario := 2200.00;
  
  aumentos(3).id := 3;
  aumentos(3).salario := 3300.00;
  
  -- Dado inválido: viola a restrição de chave estrangeira

  aumentos(4).id := 6;
  aumentos(4).salario := 6600.00;
  
  -- Dado inválido: viola a restrição de not null

  aumentos(5).id := 5;
  aumentos(5).salario := NULL;
  
  -- Usa o comando FORALL com a cláusula SAVE EXCEPTIONS para atualizar os dados da coleção na tabela funcionarios

  FORALL i IN 1 .. aumentos.COUNT SAVE EXCEPTIONS
    UPDATE funcionarios SET salario = aumentos(i).salario WHERE id = aumentos(i).id;
    
  -- Exibe uma mensagem confirmando a atualização

  DBMS_OUTPUT.PUT_LINE('Foram atualizados ' || SQL%ROWCOUNT || ' funcionarios na tabela.');
  
  -- Trata os erros que ocorreram durante a execução do comando FORALL
  -- Verifica se a variável erros contém algum erro

  IF erros.COUNT > 0 THEN
    -- Exibe uma mensagem informando o número de erros

    DBMS_OUTPUT.PUT_LINE('Foram encontrados ' || erros.COUNT || ' erros durante a atualização.');
    -- Percorre a variável erros, exibindo o índice e o código do erro

    FOR i IN 1 .. erros.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Erro ' || i || ': Índice ' || erros(i).ERROR_INDEX || ', Código ' || erros(i).ERROR_CODE);
    END LOOP;
  END IF;
END;
/

O comando FORALL percorre o intervalo de índices da coleção aumentos, que vai de 1 até o número de elementos da coleção, e executa um UPDATE na tabela funcionarios, usando os dados da coleção. O comando FORALL envia os dados da coleção em um único bloco, em vez de enviar um dado por vez, o que melhora o desempenho da operação. O comando FORALL também simplifica o código da operação, pois evita o uso de um laço explícito, como um FOR ou um WHILE, para atualizar os dados.

O comando FORALL usa a cláusula SAVE EXCEPTIONS, que indica que o comando deve continuar a executar mesmo que ocorram erros, e que os erros devem ser armazenados na variável erros, que é do tipo SQL%BULK_EXCEPTIONS. Essa variável é usada para armazenar os erros que ocorrem durante a execução do comando FORALL, e pode ser usada para exibir, corrigir ou reprocessar os erros.

O resultado do comando FORALL é exibido na tela, confirmando que foram atualizados três funcionários na tabela.

Foram atualizados 3 funcionarios na tabela.

O bloco PL/SQL também trata os erros que ocorreram durante a execução do comando FORALL, verificando se a variável erros contém algum erro. Se sim, o bloco PL/SQL exibe uma mensagem informando o número de erros, e percorre a variável erros, exibindo o índice e o código do erro.

Foram encontrados 2 erros durante a atualização.
Erro 1: Índice 4, Código -2291
Erro 2: Índice 5, Código -1400

Como podemos ver, o comando FORALL com a cláusula SAVE EXCEPTIONS permite que o comando continue a executar mesmo que ocorram erros, e que os erros sejam armazenados em uma coleção de exceções, que pode ser consultada posteriormente. Essa cláusula é útil para tratar erros em lote, sem interromper a execução do comando FORALL, e sem perder as informações sobre os erros.

Neste artigo, vimos como usar o comando FORALL no PL/SQL, que permite executar operações DML em lote, de forma rápida e eficiente. O comando FORALL permite que você execute operações DML, como INSERT, UPDATE, DELETE ou MERGE, em um conjunto de dados armazenados em uma coleção PL/SQL, de forma simples e modular. O comando FORALL também melhora o desempenho das operações DML, pois ele reduz o número de chamadas à camada SQL, enviando os dados da coleção em um único bloco, em vez de enviar um dado por vez. O comando FORALL também simplifica o código das operações DML, pois ele usa uma sintaxe compacta e intuitiva, que evita o uso de laços explícitos. Além disso, vimos como usar o comando FORALL com a cláusula SAVE EXCEPTIONS, que permite que o comando continue a executar mesmo que ocorram erros, e que os erros sejam armazenados em uma coleção de exceções, que pode ser consultada posteriormente.

Espero que este artigo tenha sido útil e divertido para você. Se você gostou, compartilhe com seus amigos e colegas que também querem aprender mais sobre PL/SQL.

Valeuuuuu

Referências

• Oracle Database PL/SQL Language Reference 19c