Cursores FOR UPDATE no Oracle PL/SQL

Você já se deparou com a necessidade de bloquear linhas específicas em seu banco de dados Oracle para garantir atualizações seguras? Os Cursores FOR UPDATE no Oracle PL/SQL são a solução que você precisa! Neste guia abrangente, vamos explorar como utilizar essa poderosa ferramenta para otimizar suas operações de banco de dados e evitar conflitos de concorrência.

O que são Cursores FOR UPDATE?

Imagine que você está em uma biblioteca e quer reservar alguns livros para ler mais tarde. Ao fazer isso, você impede que outras pessoas peguem esses livros até que você termine. Os Cursores FOR UPDATE funcionam de maneira similar no banco de dados.

Normalmente, quando executamos um SELECT, o Oracle não bloqueia nenhuma linha nas tabelas do banco de dados. No entanto, às vezes, precisamos bloquear as linhas para atualizá-las posteriormente. É aí que entra o SELECT FOR UPDATE.

A sintaxe básica é:

SELECT ...
FOR UPDATE [OF lista_de_colunas]

1 2	SELECT ... FOR UPDATE [OF lista_de_colunas]

Exemplo Prático com o Schema HR

Vamos ver um exemplo usando a tabela EMPLOYEES do schema HR:

DECLARE
   CURSOR c_emp IS
      SELECT employee_id, salary
      FROM employees
      WHERE department_id = 30
      FOR UPDATE;
   
   r_emp c_emp%ROWTYPE;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp INTO r_emp;
      EXIT WHEN c_emp%NOTFOUND;
      
      IF r_emp.salary < 5000 THEN
         UPDATE employees
         SET salary = r_emp.salary * 1.05
         WHERE employee_id = r_emp.employee_id;
         
         DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);
      END IF;
   END LOOP;
   
   ROLLBACK; -- ou COMMIT se quiser salvar as alterações
   CLOSE c_emp;
END;
/

DECLARE

CURSOR c_emp IS

SELECT employee_id, salary

FROM employees

WHERE department_id = 30

FOR UPDATE;

r_emp c_emp%ROWTYPE;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp INTO r_emp;

EXIT WHEN c_emp%NOTFOUND;

IF r_emp.salary < 5000 THEN

UPDATE employees

SET salary = r_emp.salary * 1.05

WHERE employee_id = r_emp.employee_id;

DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);

END IF;

END LOOP;

ROLLBACK; -- ou COMMIT se quiser salvar as alterações

CLOSE c_emp;

END;

Neste exemplo, estamos aumentando em 5% o salário de todos os funcionários do departamento 30 que ganham menos de 5000.

Este exemplo demonstra um cenário comum de uso de cursores FOR UPDATE para atualização de dados.

Declaração do Cursor:
- Um cursor chamado c_emp é declarado.
- Ele seleciona employee_id e salary da tabela employees.
- A cláusula WHERE department_id = 30 filtra apenas os funcionários do departamento 30.
- A cláusula FOR UPDATE é crucial aqui. Ela indica que as linhas retornadas por este cursor serão bloqueadas para atualização.
Variável de Registro:
- r_emp c_emp%ROWTYPE; declara uma variável de registro que corresponde à estrutura do cursor. Isso permite armazenar uma linha completa do resultado do cursor.

BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp INTO r_emp;
      EXIT WHEN c_emp%NOTFOUND;
      
      -- Lógica de processamento aqui
   END LOOP;
   
   ROLLBACK; -- ou COMMIT se quiser salvar as alterações
   CLOSE c_emp;
END;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp INTO r_emp;

EXIT WHEN c_emp%NOTFOUND;

-- Lógica de processamento aqui

END LOOP;

ROLLBACK; -- ou COMMIT se quiser salvar as alterações

CLOSE c_emp;

END;

Abertura do Cursor:
- OPEN c_emp; abre o cursor. Neste momento, o Oracle bloqueia todas as linhas que correspondem à consulta do cursor.
Loop de Processamento:
- O loop FETCH recupera cada linha do cursor e a armazena em r_emp.
- EXIT WHEN c_emp%NOTFOUND; sai do loop quando não há mais linhas para processar.
Lógica de Atualização:

IF r_emp.salary < 5000 THEN
   UPDATE employees
   SET salary = r_emp.salary * 1.05
   WHERE employee_id = r_emp.employee_id;
   
   DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);
END IF;

IF r_emp.salary < 5000 THEN

UPDATE employees

SET salary = r_emp.salary * 1.05

WHERE employee_id = r_emp.employee_id;

DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);

END IF;

Para cada funcionário, verifica se o salário é menor que 5000.
Se for, aumenta o salário em 5% (multiplicando por 1.05).
A cláusula WHERE na instrução UPDATE garante que apenas o funcionário atual é atualizado.
Uma mensagem é exibida para cada atualização.

Controle de Transação:
- ROLLBACK; no final reverte todas as alterações. Poderia ser substituído por COMMIT; para salvar as alterações.
Fechamento do Cursor:
- CLOSE c_emp; fecha o cursor, liberando os recursos associados.

Pontos Importantes sobre o Cursor FOR UPDATE:

Bloqueio de Linhas: Quando o cursor é aberto, todas as linhas que correspondem à consulta são bloqueadas. Isso impede que outros processos modifiquem essas linhas até que a transação seja concluída (com COMMIT ou ROLLBACK).
Consistência: Garante que os dados não sejam alterados por outros processos enquanto estão sendo processados por este bloco PL/SQL.
Atualização Seletiva: Permite examinar os dados antes de decidir se uma atualização é necessária (neste caso, apenas para salários menores que 5000).
Eficiência: É mais eficiente do que bloquear toda a tabela, pois bloqueia apenas as linhas relevantes.
Controle de Concorrência: Ajuda a evitar problemas de concorrência em ambientes multi-usuário.

Este código demonstra um uso típico de cursores FOR UPDATE, onde você precisa examinar e potencialmente atualizar um conjunto específico de linhas, garantindo que elas não sejam modificadas por outros processos durante a operação.

Pontos Importantes

As linhas são bloqueadas quando o cursor é aberto, não quando são buscadas do conjunto de resultados.
Se você fizer COMMIT ou ROLLBACK enquanto processa o cursor, as operações de FETCH subsequentes falharão, pois o bloqueio nas linhas do cursor será liberado.

Veja um exemplo que demonstra esse comportamento:

DECLARE
   CURSOR c_emp IS
      SELECT employee_id, salary
      FROM employees
      WHERE department_id = 30
      FOR UPDATE;
   
   r_emp c_emp%ROWTYPE;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp INTO r_emp;
      EXIT WHEN c_emp%NOTFOUND;
      
      IF r_emp.salary < 5000 THEN
         UPDATE employees
         SET salary = r_emp.salary * 1.05
         WHERE employee_id = r_emp.employee_id;
         
         DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);
         
         -- Este ROLLBACK causará falha no próximo FETCH
         ROLLBACK;
      END IF;
   END LOOP;
   
   CLOSE c_emp;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Erro: ' || SQLERRM);
END;
/

DECLARE

CURSOR c_emp IS

SELECT employee_id, salary

FROM employees

WHERE department_id = 30

FOR UPDATE;

r_emp c_emp%ROWTYPE;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp INTO r_emp;

EXIT WHEN c_emp%NOTFOUND;

IF r_emp.salary < 5000 THEN

UPDATE employees

SET salary = r_emp.salary * 1.05

WHERE employee_id = r_emp.employee_id;

DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);

-- Este ROLLBACK causará falha no próximo FETCH

ROLLBACK;

END IF;

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Erro: ' || SQLERRM);

END;

Este script provavelmente resultará em um erro “ORA-01002: fetch out of sequence” após o primeiro ROLLBACK.

Cláusula WHERE CURRENT OF

Os cursores FOR UPDATE podem ser usados em conjunto com a cláusula WHERE CURRENT OF. Esta cláusula é usada com as instruções UPDATE ou DELETE para atualizar ou excluir linhas retornadas pelo cursor FOR UPDATE.

Exemplo:

DECLARE
   CURSOR c_emp IS
      SELECT employee_id, salary
      FROM employees
      WHERE department_id = 30
      FOR UPDATE;
BEGIN
   FOR r_emp IN c_emp LOOP
      IF r_emp.salary < 5000 THEN
         UPDATE employees
         SET salary = r_emp.salary * 1.05
         WHERE CURRENT OF c_emp;
         
         DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);
      END IF;
   END LOOP;
   
   ROLLBACK; -- ou COMMIT se quiser salvar as alterações
END;
/

DECLARE

CURSOR c_emp IS

SELECT employee_id, salary

FROM employees

WHERE department_id = 30

FOR UPDATE;

BEGIN

FOR r_emp IN c_emp LOOP

IF r_emp.salary < 5000 THEN

UPDATE employees

SET salary = r_emp.salary * 1.05

WHERE CURRENT OF c_emp;

DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);

END IF;

END LOOP;

ROLLBACK; -- ou COMMIT se quiser salvar as alterações

END;

Neste exemplo, a cláusula WHERE CURRENT OF c_emp garante que estamos atualizando apenas a linha atual retornada pelo cursor.

Este código é uma variação do exemplo anterior, utilizando uma abordagem ligeiramente diferente e introduzindo o uso da cláusula WHERE CURRENT OF. Vamos analisar detalhadamente:

DECLARE
   CURSOR c_emp IS
      SELECT employee_id, salary
      FROM employees
      WHERE department_id = 30
      FOR UPDATE;
BEGIN
   -- Código principal aqui
END;
/

DECLARE

CURSOR c_emp IS

SELECT employee_id, salary

FROM employees

WHERE department_id = 30

FOR UPDATE;

BEGIN

-- Código principal aqui

END;

Declaração do Cursor:
- O cursor c_emp é declarado da mesma forma que no exemplo anterior.
- Seleciona employee_id e salary dos funcionários do departamento 30.
- A cláusula FOR UPDATE indica que as linhas retornadas serão bloqueadas para atualização.

BEGIN
   FOR r_emp IN c_emp LOOP
      -- Lógica de processamento aqui
   END LOOP;
   
   ROLLBACK; -- ou COMMIT se quiser salvar as alterações
END;

BEGIN

FOR r_emp IN c_emp LOOP

-- Lógica de processamento aqui

END LOOP;

ROLLBACK; -- ou COMMIT se quiser salvar as alterações

END;

Loop FOR…IN:
- Diferentemente do exemplo anterior, este código usa um loop FOR...IN.
- Esta construção combina a abertura do cursor, o fetch das linhas e o fechamento do cursor em uma única estrutura.
- r_emp é implicitamente declarado e preenchido com cada linha do cursor a cada iteração.
Lógica de Atualização:

IF r_emp.salary < 5000 THEN
   UPDATE employees
   SET salary = r_emp.salary * 1.05
   WHERE CURRENT OF c_emp;
   
   DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);
END IF;

IF r_emp.salary < 5000 THEN

UPDATE employees

SET salary = r_emp.salary * 1.05

WHERE CURRENT OF c_emp;

DBMS_OUTPUT.PUT_LINE('Salário atualizado para o funcionário: ' || r_emp.employee_id);

END IF;

A lógica de atualização é similar ao exemplo anterior.
Verifica se o salário é menor que 5000 e, se for, aumenta em 5%.

Cláusula WHERE CURRENT OF:
- A parte mais importante deste exemplo é o uso de WHERE CURRENT OF c_emp.
- Esta cláusula é específica para cursores FOR UPDATE.
- Ela atualiza apenas a linha atual que está sendo processada pelo cursor.
- Isso elimina a necessidade de especificar WHERE employee_id = r_emp.employee_id.
Controle de Transação:
- ROLLBACK; no final reverte todas as alterações. Pode ser substituído por COMMIT; para salvar as mudanças.

Pontos Importantes:

Simplicidade: O loop FOR...IN simplifica o código, eliminando a necessidade de abrir, fechar e fazer fetch explicitamente do cursor.
WHERE CURRENT OF: Esta cláusula é mais eficiente e menos propensa a erros do que especificar manualmente a condição WHERE para atualização.
Bloqueio Implícito: Mesmo usando o loop FOR...IN, o cursor ainda bloqueia as linhas devido à cláusula FOR UPDATE.
Consistência: Garante que cada linha seja atualizada no contexto correto, evitando problemas de concorrência.
Eficiência: Este método é geralmente mais eficiente em termos de código e desempenho, especialmente para conjuntos de dados maiores.
Segurança: Reduz o risco de atualizar acidentalmente a linha errada, pois a atualização está diretamente vinculada à linha atual do cursor.

Este código demonstra uma abordagem mais concisa e segura para trabalhar com cursores FOR UPDATE, especialmente útil quando você precisa atualizar linhas específicas com base em condições, garantindo ao mesmo tempo a integridade e consistência dos dados em um ambiente de banco de dados concorrente.

Conclusão

Os Cursores FOR UPDATE são ferramentas poderosas quando precisamos garantir a integridade dos dados durante atualizações em massa. Eles nos permitem bloquear linhas específicas para prevenir conflitos de concorrência.

Pontos-chave para lembrar:

Use FOR UPDATE quando precisar bloquear linhas para atualizações futuras.
As linhas são bloqueadas quando o cursor é aberto, não quando são buscadas.
Cuidado com COMMIT ou ROLLBACK durante o processamento do cursor.
A cláusula WHERE CURRENT OF é útil para atualizar ou deletar a linha atual do cursor.

Pratique bastante! Tente criar diferentes cenários usando as tabelas do schema HR onde o FOR UPDATE seria útil. Por exemplo, você poderia criar um script para dar aumentos salariais baseados em diferentes critérios, usando FOR UPDATE para garantir que ninguém mais modifique esses salários durante o processo.

O que são Cursores FOR UPDATE?

Exemplo Prático com o Schema HR

Pontos Importantes

Cláusula WHERE CURRENT OF

Conclusão

Posts relacionados