Conceito de transação em blocos PL/SQL no Oracle

Olá,

Não é raro vermos iniciantes em Oracle, principalmente desenvolvedores, tendo algumas dúvidas em relação ao conceito de transações no banco de dados. Sabemos que o Oracle abre uma transação implicitamente quando executamos a primeira instrução SQL (DML) e que, para finalizar esta transação, deveremos emitir o comando COMMIT ou ROLLBACK. Vale a pena salientar que qualquer comando DDL (CREATE, ALTER, DROP, etc.) irá finalizar a transação com COMMIT mesmo que a instrução SQL execute com falha. Mas como fica a transação quando as instruções SQL estão dentro de um bloco PL/SQL ou de uma stored procedure? Neste artigo irei demonstrar exemplos da execução de blocos PL/SQL para demonstrar como as instruções SQL são afetadas dentro do mesmo.

Conceito de transação em blocos PL/SQL

Veremos abaixo como as instruções SQL irão se comportar dentro de um bloco PL/SQL padrão.

 

SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11  END;
 12  /
*
ERRO na linha 1:
ORA-01722: número inválido
ORA-06512: em line 8

O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 

SQL> select * from T1;

não há linhas selecionadas

SQL> select * from T2;

não há linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas

Pelo resultado demonstrado acima, podemos perceber que por padrão, um procedimento PL/SQL fará rollback de toda a transação caso aconteça algum erro, independente se alguma instrução SQL foi executada com sucesso.

Conceito de transação em blocos PL/SQL (EXCEPTION) 

Veremos abaixo como as instruções SQL irão se comportar caso usemos a cláusula EXCEPTION dentro do bloco PL/SQL.
 

SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11   exception
 12    when others then
 13     dbms_output.put_line('Error msg: '||sqlerrm);
 15  END;
 16  /
Error msg: ORA-01722: número inválido

Procedimento PL/SQL concluído com sucesso.

O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 

SQL> select * from T1;

3 linhas selecionadas

SQL> select * from T2;

3 linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas

Pelo resultado demonstrado acima, podemos perceber que criando uma EXCEPTION, as instruções SQL serão executadas até dar algum erro. Após o erro, a execução irá parar e a transação ficará aberta até a execução da instrução COMMIT ou ROLLBACK.

Conceito de transação em blocos PL/SQL (RAISE)

Veremos abaixo como as instruções SQL irão se comportar caso usemos a cláusula RAISE_APPLICATION_ERROR dentro do bloco PL/SQL.

SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11   exception
 12    when others then
 13     raise_application_error(-20000, 'Ocorreu um erro');
 14  END;
 15  /
*
ERRO na linha 1:
ORA-20000: Ocorreu um erro
ORA-06512: em line 13

O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 

SQL> select * from T1;

não há linhas selecionadas

SQL> select * from T2;

não há linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas

Pelo resultado demonstrado acima, podemos perceber que criando uma EXCEPTION e usando o comando RAISE_APPLICATION_ERROR, será feito rollback de toda transação da mesma forma que se não tivesse sendo utilizado a cláusula EXCEPTION.