ustify>
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(
evento varchar2(100),
username varchar2(100),
objeto varchar2(100),
objtype varchar2(100),
objowner varchar2(100),
sql varchar2(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
Quando falo em Sequence, fico dispensado de dar maiores explicações, basta dizer que ao criarmos uma sequence em nossa base, estaremos criando um gerador de valores que são incrementados em um valor pré-definido.
Para o nosso exemplo, deixarei os valores da sequence nos valores “default” do banco, ou seja, o valor inicial é zero e o valor atual é sempre incrementado em 1.
Inicialmente vamos criar uma tabela Cliente, com os atributos Código, Nome, Telefone e Tipo para nosso teste, conforme Listagem 2.
CREATE TABLE CLIENTE(
CODIGO INTEGER NOT NULL PRIMARY KEY,
NOME VARCHAR(20),
TELEFONE VARCHAR2(10),
TIPO VARCHAR2(15));
Listagem 2. Criação da tabela do nosso exemplo.
Criada a tabela, vamos agora criar uma sequence para incrementar o campo CLIENTE.CODIGO, que será nossa chave primária nesta tabela.
Na Listagem 3, podemos ver o quão simples é criar uma Sequence, muito mais simples do que fazer este controle no próprio sistema.
CREATE SEQUENCE INCREMENTACODIGO;
Listagem 3. Criação da sequence.
Antes de partir para a criação da trigger, que fará com que o campo CODIGO receba o valor incrementado, vamos ver duas formas de obter o valor da sequence que acabamos de criar.
A primeira forma nos retorna o valor atual da sequence (Figura 1), e a segunda nos retorna o valor já incrementado (Figura 2).
