Transação autônoma no Oracle PL/SQL: confirmação, reversão

O que são instruções TCL em PL/SQL?

TCL significa Declarações de Controle de Transação. Ele salvará as transações pendentes ou reverterá a transação pendente. Essas instruções desempenham um papel vital porque, a menos que a transação seja salva, as alterações por meio das instruções DML não serão salvas no banco de dados. Abaixo estão as diferentes declarações TCL.

COMPRAR Salva todas as transações pendentes
RECUPERAR Descarte todas as transações pendentes
SALVAR PONTO Creates a point in the transaction till which rollback can be done later
REVERTER PARA Descarte todas as transações pendentes até o especificado

A transação será concluída sob o seguintewing cenários.

  • Quando qualquer uma das declarações acima for emitida (exceto SAVEPOINT)
  • Quando as instruções DDL são emitidas. (DDL são instruções de confirmação automática)
  • QUANDO as declarações DCL são emitidas. (DCL são instruções de confirmação automática)

O que é transação autônoma

Em PL/SQL, todas as modificações feitas nos dados serão denominadas como uma transação. Uma transação é considerada concluída quando o salvamento/descarte é aplicado a ela. Se nenhum salvamento/descarte for dado, a transação não será considerada concluída e as modificações feitas nos dados não se tornarão permanentes no servidor.

Independentemente de algumas modificações feitas durante uma sessão, PL/SQL tratará toda a modificação como uma única transação e salvar/descartar esta transação afetará todas as alterações pendentes naquela sessão. A Transação Autônoma fornece uma funcionalidade ao desenvolvedor na qual permite fazer alterações em uma transação separada e salvar/descartar essa transação específica sem afetar a transação da sessão principal.

  • Esta transação autônoma pode ser especificada no nível do subprograma.
  • Para fazer com que qualquer subprograma funcione em uma transação diferente, a palavra-chave 'PRAGMA AUTONOMOUS_TRANSATION' deve ser informada na seção declarativa desse bloco.
  • Ele instruirá o compilador a tratar isso como uma transação separada e salvar/descartar dentro deste bloco não refletirá na transação principal.
  • A emissão de COMMIT ou ROLLBACK é obrigatória antes de sair desta transação autônoma para a transação principal porque a qualquer momento apenas uma transação pode estar ativa.
  • Assim, uma vez que fizemos uma transação autônoma, precisamos salvá-la e concluir a transação, só então poderemos voltar para a transação principal.

Sintaxe:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; 
.
BEGIN
<executin_part>
[COMMIT|ROLLBACK]
END;
/
  • Na sintaxe acima, o bloco foi feito como uma transação autônoma.

Exemplo 1: Neste exemplo vamos entender como funciona a transação autônoma.

DECLARE
   l_salary   NUMBER;
   PROCEDURE nested_block IS
   PRAGMA autonomous_transaction;
    BEGIN
     UPDATE emp
       SET salary = salary + 15000
       WHERE emp_no = 1002;
   COMMIT;
   END;
BEGIN
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1001;
   dbms_output.put_line('Before Salary of 1001 is'|| l_salary);
   SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
   dbms_output.put_line('Before Salary of 1002 is'|| l_salary);    
   UPDATE emp 
   SET salary = salary + 5000 
   WHERE emp_no = 1001;

nested_block;
ROLLBACK;

 SELECT salary INTO  l_salary FROM emp WHERE emp_no = 1001;
 dbms_output.put_line('After Salary of 1001 is'|| l_salary);
 SELECT salary INTO l_salary FROM emp WHERE emp_no = 1002;
 dbms_output.put_line('After Salary of 1002 is '|| l_salary);
end;

saída

Before:Salary of 1001 is 15000 
Before:Salary of 1002 is 10000 
After:Salary of 1001 is 15000 
After:Salary of 1002 is 25000

Transação Autônoma

Explicação do código:

  • Linha de código 2: Declarando l_salary como NUMBER.
  • Linha de código 3: Declarando o procedimento nested_block
  • Linha de código 4: Tornando o procedimento nested_block como 'AUTONOMOUS_TRANSACTION'.
  • Linha de código 7-9: Aumentar o salário do funcionário número 1002 em 15000.
  • Linha de código 10: Confirmando a transação.
  • Linha de código 13-16: Imprimindo o salário details dos funcionários 1001 e 1002 antes das alterações.
  • Linha de código 17-19: Aumentar o salário do funcionário número 1001 em 5000.
  • Linha de código 20: Chamando o procedimento nested_block;
  • Linha de código 21: Descartando a transação principal.
  • Linha de código 22-25: Imprimindo o salário details dos funcionários 1001 e 1002 após alterações.
  • O aumento salarial do funcionário número 1001 não é refletido porque a transação principal foi descartada. O aumento salarial do funcionário número 1002 é refletido porque esse bloqueio foi feito como uma transação separada e salvo no final.
  • Portanto, independentemente de salvar/descartar na transação principal, as alterações na transação autônoma foram salvas sem afetar as alterações da transação principal.