Controle de Transações em PL/SQL

Olá pessoal, hoje vamos falar sobre um assunto muito importante em bancos de dados: o controle de transações em PL/SQL. Depois desta aula, você será capaz de:

Utilizar os comandos COMMIT, ROLLBACK e SAVEPOINT
Usar o comando SET TRANSACTION

Então vamos lá, mãos à obra!

O que é uma Transação?

No Oracle, uma transação é uma única instrução SQL ou uma série delas que o banco de dados trata como uma unidade única. Isso significa que todas as instruções na unidade ou têm sucesso ou falham juntas.

Cada aplicação (SQL*Plus, SQL Developer, e várias ferramentas PL/SQL de terceiros) mantém uma única sessão de banco de dados para cada instância de login de usuário. As alterações nos dados feitas por uma única sessão são visíveis apenas dentro dessa sessão e não são visíveis para nenhuma outra sessão até que sejam confirmadas (committed) no banco de dados, ou em outras palavras, “salvas”. Se as mudanças nos dados não foram confirmadas, elas podem ser descartadas ou revertidas (rolled back).

O PL/SQL usa comandos da Linguagem de Controle de Transações (TCL) para gerenciar o processamento de transações. Nesta aula, você aprenderá como usar os comandos COMMIT, ROLLBACK, SAVEPOINT e SET TRANSACTION.

Os Comandos COMMIT, ROLLBACK e SAVEPOINT

Uma transação de banco de dados começa quando a primeira instrução DML (Data Manipulation Language) ou DDL (Data Definition Language) é executada e termina de várias maneiras:

Um comando COMMIT ou ROLLBACK (sem SAVEPOINT) é executado.
Uma instrução DDL como CREATE ou ALTER é encontrada.
Um usuário encerra a sessão do banco de dados normalmente; ou seja, a sessão não é encerrada de forma anormal.

Quando um comando COMMIT é emitido, todas as alterações de dados na transação se tornam permanentes e visíveis para outros usuários que possam estar usando as mesmas estruturas de dados. Por outro lado, quando um comando ROLLBACK é emitido, todas as alterações de dados na transação são desfeitas e os dados retornam ao seu estado anterior.

Um comando SAVEPOINT permite que você divida uma transação em unidades menores para que possa reverter uma parte da transação para o savepoint. Isso significa que todas as instruções emitidas após o savepoint são revertidas, e todas as instruções antes do savepoint permanecem.

Agora, vamos criar a tabela TEST_TAB e inserir alguns registros para viabilizar os exemplos que vimos. Aqui estão os comandos SQL:

-- Cria a tabela TEST_TAB
CREATE TABLE TEST_TAB (
  col1 NUMBER,
  col2 VARCHAR2(50)
);

-- Cria a sequência TEST_SEQ
CREATE SEQUENCE test_seq START WITH 1;

-- Insere alguns registros na tabela TEST_TAB
INSERT INTO TEST_TAB (col1, col2) VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);
INSERT INTO TEST_TAB (col1, col2) VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);
INSERT INTO TEST_TAB (col1, col2) VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

-- Confirma a transação
COMMIT;

-- Cria a tabela TEST_TAB

CREATE TABLE TEST_TAB (

col1 NUMBER,

col2 VARCHAR2(50)

);

-- Cria a sequência TEST_SEQ

CREATE SEQUENCE test_seq START WITH 1;

-- Insere alguns registros na tabela TEST_TAB

INSERT INTO TEST_TAB (col1, col2) VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

-- Confirma a transação

COMMIT;

Agora, vamos ver um exemplo prático. Considere o código:

SELECT * 
  FROM TEST_TAB;

DECLARE
   v_col1 test_tab.col1%TYPE;  
   v_col2 test_tab.col2%TYPE;
BEGIN
   INSERT INTO test_tab (col1, col2)
   VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);
   
   SAVEPOINT A;
   
   UPDATE test_tab
      SET col2 = 'Update1 – '||col2  
    WHERE col1 = 11
   RETURNING col1, col2
     INTO v_col1, v_col2;

   DBMS_OUTPUT.PUT_LINE ('After update: '||v_col1||', '||v_col2); 
   
   ROLLBACK TO A;
END;
/

SELECT *
  FROM TEST_TAB;

SELECT *

FROM TEST_TAB;

DECLARE

v_col1 test_tab.col1%TYPE;

v_col2 test_tab.col2%TYPE;

BEGIN

INSERT INTO test_tab (col1, col2)

VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

SAVEPOINT A;

UPDATE test_tab

SET col2 = 'Update1 – '||col2

WHERE col1 = 11

RETURNING col1, col2

INTO v_col1, v_col2;

DBMS_OUTPUT.PUT_LINE ('After update: '||v_col1||', '||v_col2);

ROLLBACK TO A;

END;

SELECT *

FROM TEST_TAB;

Neste exemplo, você insere um novo registro na tabela TEST_TAB. Em seguida, você define o savepoint A, atualiza uma única linha na tabela TEST_TAB e exibe os valores recém-atualizados na tela. Após a instrução UPDATE, você reverte suas alterações para o savepoint A e confirma o restante da transação.

Os comandos SELECT antes e depois do exemplo ilustram como as alterações de dados são refletidas na TEST_TAB antes e depois do script.

      COL1 COL2      
---------- ----------
        11 Row 1

After update: 11, Update1 – Row 1

      COL1 COL2      
---------- ----------
        11 Row 1   
        31 Row 31

COL1 COL2

---------- ----------

11 Row 1

After update: 11, Update1 – Row 1

COL1 COL2

---------- ----------

11 Row 1

31 Row 31

Observe como as alterações emitidas pela instrução UPDATE não são refletidas na tabela TEST_TAB.

Devido à natureza de uma transação de banco de dados, um bloco PL/SQL pode conter várias transações, e uma única transação pode abranger vários blocos PL/SQL. Considere o seguinte bloco PL/SQL que contém três transações, onde as duas primeiras são confirmadas e a terceira é revertida.

DECLARE
   v_col1 test_tab.col1%TYPE;
   v_col2 test_tab.col2%TYPE;
BEGIN
   -- Inicia uma nova transação  
   INSERT INTO test_tab (col1, col2) 
   VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);
   
   -- Termina a transação
   COMMIT;
   
   -- Inicia uma segunda transação
   UPDATE test_tab
      SET col2 = 'Updated – '||col2
    WHERE col1 = 11  
   RETURNING col1, col2
     INTO v_col1, v_col2;
     
   DBMS_OUTPUT.PUT_LINE ('After update: '||v_col1||', '||v_col2);
   
   -- Termina a segunda transação 
   COMMIT;
   
   -- Inicia uma terceira transação
   INSERT INTO test_tab (col1, col2)
   VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);
   
   -- Termina a terceira transação
   ROLLBACK;  
END;

DECLARE

v_col1 test_tab.col1%TYPE;

v_col2 test_tab.col2%TYPE;

BEGIN

-- Inicia uma nova transação

INSERT INTO test_tab (col1, col2)

VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

-- Termina a transação

COMMIT;

-- Inicia uma segunda transação

UPDATE test_tab

SET col2 = 'Updated – '||col2

WHERE col1 = 11

RETURNING col1, col2

INTO v_col1, v_col2;

DBMS_OUTPUT.PUT_LINE ('After update: '||v_col1||', '||v_col2);

-- Termina a segunda transação

COMMIT;

-- Inicia uma terceira transação

INSERT INTO test_tab (col1, col2)

VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

-- Termina a terceira transação

ROLLBACK;

END;

Agora, considere uma versão modificada do mesmo exemplo em que uma única transação abrange dois blocos PL/SQL.

-- Inicia uma nova transação
INSERT INTO test_tab (col1, col2)  
VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

-- Bloco PL/SQL 1
DECLARE 
   v_col1 test_tab.col1%TYPE;
   v_col2 test_tab.col2%TYPE;
BEGIN
   UPDATE test_tab 
      SET col2 = 'Updated – '||col2
    WHERE col1 = 11
   RETURNING col1, col2  
     INTO v_col1, v_col2;
     
   DBMS_OUTPUT.PUT_LINE ('After update: '||v_col1||', '||v_col2);  
END;
/

-- Bloco PL/SQL 2  
BEGIN
   INSERT INTO test_tab (col1, col2)
   VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL); 
END;
/

-- Termina a transação
COMMIT;

-- Inicia uma nova transação

INSERT INTO test_tab (col1, col2)

VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

-- Bloco PL/SQL 1

DECLARE

v_col1 test_tab.col1%TYPE;

v_col2 test_tab.col2%TYPE;

BEGIN

UPDATE test_tab

SET col2 = 'Updated – '||col2

WHERE col1 = 11

RETURNING col1, col2

INTO v_col1, v_col2;

DBMS_OUTPUT.PUT_LINE ('After update: '||v_col1||', '||v_col2);

END;

-- Bloco PL/SQL 2

BEGIN

INSERT INTO test_tab (col1, col2)

VALUES (test_seq.NEXTVAL, 'Row '||test_seq.CURRVAL);

END;

-- Termina a transação

COMMIT;

Após esses dois exemplos, a TEST_TAB contém registros como os seguintes:

      COL1 COL2                    
---------- -------------------------
        11 Updated - Updated - Row 1    
        31 Row 31                 
        41 Row 41
        61 Row 61
        71 Row 71

COL1 COL2

---------- -------------------------

11 Updated - Updated - Row 1

31 Row 31

41 Row 41

61 Row 61

71 Row 71

O Comando SET TRANSACTION

O comando SET TRANSACTION permite especificar várias características de uma transação:

Uma cláusula READ ONLY ou READ WRITE define uma transação como somente leitura, o que significa que nenhuma alteração nos dados é permitida, ou como leitura/gravação, o que significa que alterações nos dados são permitidas.
Uma cláusula ISOLATION LEVEL define o modo de isolamento da transação como SERIALIZABLE ou READ COMMITTED, determinando assim o comportamento da transação.
Uma cláusula USE ROLLBACK SEGMENT define a transação como leitura/gravação implicitamente e atribui a transação a um segmento de rollback específico.
Uma cláusula NAME permite que você nomeie a transação.

A documentação do Oracle contém informações detalhadas sobre o comando SET TRANSACTION e suas opções.

Vale ressaltar que o comando SET TRANSACTION deve ser o primeiro comando na transação e pode aparecer apenas uma vez. No entanto, não é necessário para iniciar uma transação, como ilustrado na seção anterior.

Vejamos um exemplo simples que ilustra como o comando SET TRANSACTION pode ser usado.

DECLARE
   v_date DATE;
BEGIN
   -- Termina uma transação anterior, se houver
   COMMIT;
   
   -- Inicia uma nova transação READ ONLY  
   SET TRANSACTION READ ONLY NAME 'Get date';
   
   SELECT sysdate
     INTO v_date  
     FROM DUAL;
     
   DBMS_OUTPUT.PUT_LINE ('Today is '||
      TO_CHAR(v_date, 'MM/DD/YYYY'));
      
   -- Termina a transação READ ONLY    
   COMMIT;  
END;

DECLARE

v_date DATE;

BEGIN

-- Termina uma transação anterior, se houver

COMMIT;

-- Inicia uma nova transação READ ONLY

SET TRANSACTION READ ONLY NAME 'Get date';

SELECT sysdate

INTO v_date

FROM DUAL;

DBMS_OUTPUT.PUT_LINE ('Today is '||

TO_CHAR(v_date, 'MM/DD/YYYY'));

-- Termina a transação READ ONLY

COMMIT;

END;

Neste exemplo, você define uma transação somente leitura que determina a data de hoje. Observe os dois comandos COMMIT usados neste script. O primeiro comando COMMIT encerra qualquer transação anterior que você possa ter iniciado nesta sessão. O segundo comando COMMIT encerra a transação ‘Get date' somente leitura.

Nesta aula, aprendemos sobre:

Transações no Oracle e como elas funcionam
Os comandos COMMIT, ROLLBACK e SAVEPOINT para controlar transações
O comando SET TRANSACTION e suas opções

Agora você já sabe como controlar transações em PL/SQL! Pratique bastante esses conceitos para fixá-los.

O que é uma Transação?

Os Comandos COMMIT, ROLLBACK e SAVEPOINT

O Comando SET TRANSACTION

Related Posts