Transação autônoma em 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.

A transação será concluída nos seguintes 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

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 os detalhes salariais 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 os dados salariais 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.