Artigo da SQL Magazine 40 - Introdução a PL/SQL

Clique aqui para ler esse artigo em PDF.

Clique aqui para ler todos os artigos desta edição

Introdução a PL/SQL

Integre comandos PL/SQL usando Oracle 10g

PL/SQL - (Procedural Language/Structured Query Language) ou, simplesmente, a linguagem baseada em programação estruturada para consultas do Oracle, é (digamos) uma mistura de Pascal + SQL. Uma forma muito interessante e útil de programarmos em linguagem SQL é utilizar os comandos permitidos através do PL/SQL.

Neste artigo será mostrado como isto é possível. Serão criados campos seqüenciais, cursores, functions, triggers e stored procedures utilizando comandos PL/SQL no Oracle 10g em sua versão 10.2.0.2.0.

Quem trabalha com Oracle sabe da importância, ou ao menos da necessidade, de ter em sua base várias triggers, stored procedures, functions e cursores, mas muitas vezes encontra dificuldades em criar, utilizar, ou até mesmo integrar estas funções, assim como eu encontrei no momento em que precisei, e isto foi o que me motivou a escrever e publicar este artigo.

Trigger

Uma trigger nada mais é que um ‘gatilho’ que é disparado por um dos eventos do banco de dados, dependendo de como foi definido. Uma trigger pode ser disparada antes ou depois de um INSERT, UPDATE ou até mesmo de um DELETE em algum registro da tabela, por exemplo.

São inúmeras as vantagens de utilizar triggers em um sistema. Um cenário típico ocorre quando existe a necessidade de se armazenar os dados de um registro em uma tabela temporal. Neste caso, poderá ser criada uma trigger que será disparada antes ou depois de um UPDATE ou DELETE do registro, e ela simplesmente replicará o registro na tabela temporal.

Um problema que todo desenvolvedor Oracle encontra é a falta de campos auto-incrementáveis. Problema? Só se for para quem ainda não utiliza trigger e SEQUENCE, e isto é o que será mostrado no próximo tópico.

Podemos considerar o uso de triggers como sendo uma fase adicional no processo de ‘acordo’ para a finalização de transação. Por exemplo, digamos que uma trigger altere um campo de uma tabela logo após uma inserção em outra tabela, a tal inserção só será validada caso a alteração também seja viável, ou seja, além de toda a ‘válida burocracia’ do Oracle para uma inserção normal de dados ele ainda tem de checar se a alteração, pós-inserção causada pela trigger é possível, caso contrário, o ‘acordo’ transacional não é realizado. Ainda utilizando o exemplo da atualização após inserção, podemos ir mais além. Para isso, basta consideramos a possibilidade de esperas adicionais na transação de atualização devido LOCKs ou um índice mal elaborado, por exemplo. Ou seja, devemos estar atentos à necessidade real de uso de uma trigger.

O que definir se eu devo ou não usar triggers? Os testes! Eu, particularmente, sou a favor que toda a inteligência do sistema, possível de residir na camada de dados, deva permanecer na mesma, pois ela é segura, possui fácil acesso para processos de documentação, correção, auditoria, etc. além de ser perfeitamente restaurável. Veja um exemplo na Listagem 1.

create table audit_DDL(

  eventovarchar2(100),
  username  varchar2(100),
  objetovarchar2(100),
  objtype   varchar2(100),
  objowner  varchar2(100),
  sqlvarchar2(4000));

create or replace trigger trg_audit_DDL
  after ddl on DDL.schema
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000);
  n number;
begin

  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
  END LOOP;

  insert into audit_DDL(evento,username,objeto,objtype,objowner,sql)
values(ora_sysevent,ora_login_user,ora_dict_obj_name,ora_dict_obj_type,ora_dict_obj_owner,stmt);

end;
/

Listagem 1. Trigger que alimenta uma tabela (previamente criada) com informações sobre DDLs executadas no schema.

O exemplo da Listagem 1 cria uma trigger de auditoria de comandos DDL em um schema (after ddl on DDL.schema).

Sempre que uma instrução DDL for executada no schema, será inserido na tabela AUDIT_DDL as informações do evento DDL executado, qual o usuário que efetuou o comando, o objeto afetado e seu tipo, o dono do objeto e o comando executado.

A trigger deve ser criada no schema que irá executar os DDLs e não necessariamente no schema que contém os objetos.

Sequences