Administrando Objetos no Oracle 10g Express Edition (Parte 2)

Criando uma nova TRIGGER e uma FUNCTION
Dando continuidade ao estudo anterior, criaremos agora uma TRIGGER com o seguite objetivo:
Antes da inserção (ou atualização) de um elemento na tabela "item", caso o campo "informacoes" esteja vazio, a TRIGGER deverá preenchê-lo com o valor padrão "Não informado". Para isso (levando em consideração a parte didática) vamos criar uma FUNCTION para retornar o valor padrão de mensagem caso o campo esteja vazio. Após isso, na TRIGGER, será feita a verificação e, quando necessário, define-se o valor do campo com o retorno da FUNCTION.
Observação: lembre-se que todos os comandos devem ser realizados pelo usuário "admin" criado no estudo anterior.

A FUNCTION é similar a uma PROCEDURE, porém é necessário definir um valor de retorno.

create or replace function get_item_informacoes
return CLOB is
begin
  return 'Não informado';
end get_item_informacoes;
/

Vamos agora a criação da TRIGGER. A sintaxe da TRIGGER já foi estudada anteriormente. Nossa única atenção reserva-se na chamada da FUNCTION, que aliás, é muito simples.

create or replace TRIGGER trigger_item_informacoes
before insert or update on item
for each row
declare
  valor_campo CLOB;
  valor_default CLOB;
begin
  select :new.informacoes into valor_campo from dual;
  if valor_campo IS NULL then
    select get_item_informacoes into valor_default from dual;
    select valor_default into :new.informacoes from dual;
  end if;
end trigger_item_informacoes;
/

Observe que nesta TRIGGER, a chamada ocorre antes (BEFORE) dos eventos DML insert e update para a tabela "item". Apenas como entendimento, eventos DML disparam quando uma instrução INSERT, UPDATE ou DELETE executa. Eventos DDL disparam quando uma instrução CREATE, ALTER ou UPDATE executa. No primeiro momento (dentro do escopo), definimos o valor da variável valor_campo (declarada como CLOB) com o valor contido no campo "informacoes" da linha que será inserida (:new). Se o valor da variável for null (if valor_campo IS NULL then) o TRIGGER vai definir or valor da variável "valor_default" com o retorno da FUNCTION "get_item_informacoes". Em seguida, o valor do campo "informacoes" da nova linha (:new) será definido com o valor armazenado na variável "valor_default" que, em nosso caso, representa a string "Não informado".

Vamos inserir valores na tabela "item" como exemplo:

insert into item (descricao,quantidade,tipo_item) values ( 'Revista Z', 3 , 1 );
insert into item (descricao,quantidade,tipo_item) values ( 'Revista W', 1 , 1 );

Ao realizarmos uma consulta teremos o resultado abaixo (levando em consideração as inserções efetuadas no estudo anterior).

    ID    DESCRICAO      INFORMACOES   QUANTIDADE  TIPO_ITEM  DATA_INSERCAO
----------  -------------------------  ----------------------         ------------------------  -------------------- ----------------------------------------------------
20082  Revista Y            Editora YY             5                        1                   21-DEC-08 02.47.23.000000 PM
20085  Revista W           Não informado      1                        1                   21-DEC-08 02.47.58.000000 PM
20081  Revista X            Editora XX             10                      1                   21-DEC-08 02.47.20.000000 PM
20084  Revista Z            Não informado      3                        1                   21-DEC-08 02.47.55.000000 PM

Observe que nenhuma ordenação foi imposta na consulta e que existe um salto de índice entre a "Revista Y" e a "Revista Z". Isso ocorreu pelo fato da última inserção do artigo anterior. Foi realizada uma tentativa de inserção da "Revista X" novamente, porém, a cláusula UNIQUE do campo "descricao" não permitiu o término do procedimento. Contudo, a SEQUENCE "seq_item_id" foi requisitada, utilizando o elemento "3". Por esse motivo a "Revista Z" passou a ter o ID 20084 (que é o ano corrente + o próximo índice retornado da SEQUENCE).

Criando uma VIEW
Para que utilizamos uma VIEW? Uma VIEW é um objeto similar a uma tabela. Elas aceitam comandos de INSERT, DELETE, UPDATE e SELECT. Na verdade uma VIEW é uma tabela montada a partir de uma SELECT definida. Por exemplo, poderíamos criar uma SELECT mais apropriada para a obtenção dos dados da tabela "item" para facilitar sua visualização. Isso pode ser feito através de uma VIEW. Ela é construída em tempo de execução quando é chamada. Deve-se ter cuidado ao utilizar comandos DML (INSERT, UPDATE ou DELETE) para VIEWs. Em VIEWs simples, os comandos poderão ser realizados, porém, em VIEWs complexas (com JOINs) ocorrerão falha.
Para nosso estudo, vamos construir uma VIEW para a tabela "item" que deverá retornar o nome das colunas em um formato mais adequado, ordenar as linhas pelo campo "descricao" e acrescentar a string " und." no valor retornado pelo campo "quantidade". Não queremos exibir o campo "informacoes".
Atenção: caso o Oracle retorne uma restrição de permissão para criar VIEW através do usuário "admin", entre com o usuário "sys", altere o usuário "admin" marcando a opção "CREATE VIEW". Efetue o logon com "admin" e crie suas VIEWs.

Primeiramente, vamos construir uma VIEW simples, apenas para ordenar os registros:

create or replace view view_item as
select id, descricao, quantidade, tipo_item from item order by descricao;

Observe que a sintaxe é simples: apenas informamos o nome da VIEW seguido (as) da SQL necessária. Veja a consulta a partir da VIEW:

select * from view_item;
    ID    DESCRICAO       QUANTIDADE     TIPO_ITEM
----------  ------------------------   --------------------------     -------------------
20085  Revista W           1                             1
20081  Revista X            10                           1
20082  Revista Y            5                             1
20084  Revista Z            3                             1

Para completar nosso meta, vamos recriar a VIEW. Observe que a única coisa que devemos aprimorar é o SQL:

create or replace view view_item as
select id, descricao as "Descrição", ( to_char( quantidade ) || ' und.')
as "Qtde", tipo_item as "Tipo" from item order by descricao;

Ao realizarmos uma consulta, teremos:

select * from view_item;


    ID    Descrição           Qtde                       Tipo
----------  -------------------------  ----------------------         ----------------
20085  Revista W           1 und.                     1
20081  Revista X            10 und.                   1
20082  Revista Y            5 und.                     1
20084  Revista Z            3 und.                     1

Criando uma VIEW melhorada a partir de um JOIN
Conforme vimos anteriormente, as VIEWs podem facilitar a interação com os dados. Vamos agora tratar os dados da última VIEW criada de forma que ele traga o nome do "tipo_item" ao invés do "id" referenciando a tabela "tipo_item". Para isso, será necessário utilizar o recurso JOIN. Caso você queira saber mais sobre a utilização do JOIN, clique aqui. Em nosso estudo, a SQL necessária é apresentada abaixo:

select
  item.id as "Id",
  item.descricao as "Descrição",
  ( to_char( item.quantidade ) || ' und.') as "Qtde",
  tipo_item.descricao as "Tipo"
from
  item INNER JOIN tipo_item on
  item.tipo_item = tipo_item.id
order by item.descricao;

Entendendo o comando: após a sintaxe SELECT temos os campos da consulta, onde, como você pode observar, é necessário especificar a tabela do campo para os casos em que o nome do campo exista nas duas tabelas referenciadas na consulta. Ou seja, ambas as tabelas "item" e "tipo_item" contém o campo "id". Neste caso, desejamos exibir o "id" do "item", portanto, chamamos a primeira coluna de "item.id". No caso do campo "quantidade" isso não é necessário, porém, apenas para manter a padronização, prefiro explicitar a chamada da tabela "item". Após o "from" indicamos que queremos a tabela "item" junto com "tipo_item" sendo que "item.tipo_item" deve ser igual a "tipo_item.id". Vamos agora recriar a VIEW:

create or replace view view_item as
select
  item.id as "Id", item.descricao as "Descrição",
  ( to_char( item.quantidade ) || ' und.') as "Qtde", tipo_item.descricao as "Tipo"
from item INNER JOIN tipo_item on item.tipo_item = tipo_item.id
order by item.descricao;

Com nossa consulta, teremos:

select * from view_item;

    Id    Descrição           Qtde                       Tipo
----------  -------------------------  ----------------------         ----------------
20085  Revista W           1 und.                     Revista
20081  Revista X            10 und.                   Revista
20082  Revista Y            5 und.                     Revista
20084  Revista Z            3 und.                     Revista 

Espero ter ajudado!

"May be force be with you", Master Yoda
Abs, Guilherme Pontes.

Referências:
http://www.linhadecodigo.com.br/Artigo.aspx?id=335&pag=3
http://www.dba-oracle.com/t_ddl_triggers.htm
Steven Feuerstein, Bill Pribyl e Ghip Dawes (2006). Linguagem PL/SQL Oracle. Alta Books.
http://www.devmedia.com.br/articles/viewcomp.asp?comp=1945
http://www.rstoever.com/2006/07/18/oracle-force-view-insert-into-view/
http://www.criarweb.com/artigos/consultas-sql-de-uniao-internas.html