Administrando Objetos no Oracle 10g Express Edition

A instalação e configuração do Oracle 10g Express Edition deve estar finalizada para proceder com este estudo. Você poderá realizar o procedimento através do tutorial InstalandoOracle 10g no Ubuntu.
Após realizada a instalação, e logar no aplicativo de administração, localizado em "http://127.0.0.1:9090/apex/", você terá acesso ao prompt para edição de comandos SQL pelo menu:

1. SQL
2. SQL Commands

Após a criação de um usuário (SCHEMA) para trabalho, você precisará acessar o "SQL Commands" para executar as ações mostradas.

Tipo de dados do Oracle Express Edition
O Oracle apresenta muitos tipos (domínios) de dados. Vamos nos atentar às possibilidades oferecidas nesta versão:

• NUMBER: Valor numérico que pode ser declarado com precisão e escala, onde, repectivamente, definimos a quantidade de dígito do número e a quantidade de elementos à direita (com um valor positivo para a escala) ou esquerda (valor da escala negativo) do ponto onde ocorre o arredondamento. Ou seja, em NUMBER(2), podemos definir números com 2 elementos. Em NUMBER(4,2), serão armazenados dois elementos à direita do ponto (Ex. 2.05). Em NUMBER(4,-2), valores como 4578 serão armazenados como 4600 (os dois elementos à esquerda do ponto serão arredondados).
• VARCHAR2: String alfanumérico de tamanho variável. Ou seja, ao definirmos VARCHAR2(10) e armazenarmos o valor "TESTE", serão ocupados no banco apenas 5 caracteres, ao invés de 10.
• DATE: Campo fixo capaz de armazenar Data e Hora. Incluir dados do Século, Ano, Mês, Dia, Hora e Segunda. Milissegundos não podem ser armazenados no DATE. Varia entre 01/01/4712 a.C. a 31/12/9999 d.C.
• TIMESTAMP: Valor capaz de armazenar a Data e Hora, incluindo valores dos Milissegundos.
• CHAR: String alfanumérico de comprimento fixo. Ou seja, a declararmos um CHAR(10), independentemente do valor armazenado, o campo ocupará no banco 10 caracteres.
• CLOB: Tipo específico do domínio LOB (apontadores para objetos grandes, com até 4 GB). O CLOB são localizadores LOB para objetos grandes de caracteres (alfanuméricos) dentro do banco de dados (textos grandes).
• BLOB: O BLOB (também um tipo específico do LOB) são localizadores LOB para objetos grandes binários dentro do banco de dados (como por exemplo, uma imagem).
• NVARCHAR2: Tipo de armazenamento para caracteres alfanuméricos nacionais (conjunto de caracteres NLS), ou seja, tipos de caracteres secundários, com características próprias de determinada nacionalidade (como o japonês, por exemplo).
• BINARY_FLOAT: Número de ponto flutuante, com cumprimento ao IEEE-754, de precisão simples. Necessita de menos memória e utiliza aritmética de máquina nativa - o que proporciona alto desempenho para aplicações com computação intensiva. Pode ser comparado ao infinitivo ou ao NaN (Not a Number). Apresenta o armazenamento da precisão binária, o que pode provocar erros no arredondamento - em relação aos números de precisão decimal como o NUMBER.
  
• BINARY_DOUBLE: Essencialmente semelhante ao BINARY_FLOAT, porém com precisão dupla. Ainda sim é armazenado com precisão binária - fato que deve ser evitado em aplicações com necessidades financeiras de arredondamento. Alto desempenho devido a utilização da aritmética de máquina nativa.
  

Criando Usuários (através da Interface Gráfica)
Para manipular os dados e tabelas do banco, vamos criar um usuário. Seguindo as "boas práticas" de implementação de banco de dados, é necessário realizar esse procedimento. O aplicativo do Oracle (http://127.0.0.1:9090/apex) oferece facilidades em prol desse procedimento. Para criarmos o usuário, acesse o menu Administration, Database Users e Create User, conforme mostrado abaixo:

No campo "Username", entre com o login do novo usuário. Caso queira seguir este estudo, crie um usuário "admin", com senha "admin". Lembre-se que a senha digitada em "Password" precisa ser confirmada em "Confirm Password". Caso marque a opção "Epire Password", no próximo login, será necessário definir uma nova senha. Em "Account Status" mantenha a opção "Unlocked" para que o usuário fique ativo.
No formulário "User Privileges", existem as "Roles" padrão e os "Direct Grant System Privileges" que, respectivamente, transmitem ao usuário regras de acesso padrão ou específicas (a cada possibilidade). As "Roles" indicam:

• CONNECT: permite apenas que o usuário se conecte no sistema (ou seja, a permissão de CREATE SESSION).
• RESOURCE: permite que o usuário crie objetos do banco (como tabelas, sequences, e outros itens geralmeente utilizados para a criação e manutenção do banco de dados).
• DBA: administrador do banco, com todos os privilégios.

Marque para o usuário "admin" as opções "CONNECT" e "RESOURCE" e clique no botão "Create".
Pronto! Nosso usuário de administração do banco está pronto. Realize um logout e entre com este usuário.
Devemos lembrar o seguinte: ao criar um usuário (em nosso caso, "admin") estamos criando um SCHEMA, local onde serão alocados os objetos contidos no banco de dados. Isso significa que temos um local distinto do usuário "sys" para criar nossos objetos (ou seja, pode-se criar uma tabela "teste" no SCHEMA associado ao usuário "sys" e uma tabela "teste" no SCHEMA associado ao usuário "admin").

Criando TABLE, SEQUENCE e TRIGGER
Vamos agora criar uma tabela "TIPO_ITEM", responsável por armazenar os possíveis tipos dos itens de uma biblioteca de locação. Os requisitos desta tabela são (principalmente):

1. A tabela deverá conter dois campos
1. id: chave primária numérica de identificação da tupla (linha)
2. descricao: nome do tipo de item, com no máximo 40 caracteres
2. O campo "id" deverá ser incrementado automaticamente
3. O campo "descrição não poderá ser nulo e deve ser único

Para resolver algumas restrições impostas nos requisitos, precisamos trabalhar com "constraints" (pode-se entender como regras associadas aos objetos e campos do banco de dados). A priori, precisaremos das seguintes "constraints":

• NOT NULL: faz com que o campo no qual está associada seja de preenchimento obrigatório.
• UNIQUE: não permite duplicidade do registro do campo na qual a regra foi definida.
• PRIMARY KEY: define determinado campo como chave primária.

Deve-se ressaltar que as "constraints" podem ser definidas junto com o campo, ou através do uso da diretiva constraint - geralmente acrescentada no final dos comandos de criação da tabela. A regras NOT NULL e UNIQUE, por serem mais simples, foram definidas no próprio campo. Já a regra PRIMARY KEY foi definida separadamente.
Outro detalhe importante é que, para definirmos um AUTO_INCREMENTE no Oracle, serão necessários dois procedimentos: (a) a criação de uma SEQUENCE e (b) a criação de uma TRIGGER. Porque? A SEQUENCE estabelecerá um incremento de acordo com certas regras, ou seja, uma variável do próprio banco que será utilizada para definir o valor do campo "id" sempre que necessário (na inserção, por exemplo). A TRIGGER irá indicar que, sempre que um registro for inserido na tabela TIPO_ITEM, o valor do "id" corresponderá ao próximo elemento retornado pela SEQUENCE. Por ordem lógica, primeiro criaremos a SEQUENCE, em segundo lugar a tabela, e por último a TRIGGER.

create sequence seq_tipo_item_id
  start with 1
  maxvalue 99999999
  increment by 1
  nocycle;

Essa SEQUENCE de nome "seq_tipo_item_id" iniciará sua contagem em 1, incrementando 1 a cada NEXTVAL (próximo valor requerido), com tamanho máximo de 99999999, não cíclica (quando alcançado o final, ela não retorna o índice para o número 1). Vamos criar a tabela:

create table tipo_item (
  id NUMBER(8) not null,
  descricao VARCHAR2(40) not null unique,
  constraint pk_tipo_item_id primary key (id)
);

Observe que "id" é um NUMBER com 8 elementos, sendo que o mesmo não poderá ser nulo e é chave primária (pela constraint "pk_tipo_item_id"). O campo "descricao" é único, não nulo e possui no máximo 40 caracteres. Criando a TRIGGER:

create or replace trigger trigger_tipo_item_id
before insert on tipo_item
for each row
begin
  select seq_tipo_item_id.nextval into :new.id from dual;
end trigger_tipo_item_id;

Vamos a explicação. A primeira linha cria (ou sobrescreve) uma TRIGGER com o nome de "trigger_tipo_item_id". Em seguida, indicamos que BEFORE (antes) de INSERT (inserir) um registro na tabela "tipo_item", para cada linha (for each row) executamos o que está no escopo da TRIGGER (entre o BEGIN e END - que também indica o nome da TRIGGER). O comando definido realiza um SELECT na buscando o NEXTVAL da SEQUENCE seq_tipo_item_id e inserindo-o no campo "id" da :NEW (nova linha que vai ser inserida). A sintaxe "from dual" indica que a consulta foi realizada na tabela DUAL. Segundo o texto obtido em http://www.devmedia.com.br/articles/viewcomp.asp?comp=7849, a tabela DUAL é uma pequena tabela no dicionário de dados que o Oracle ou qualquer usuário pode referenciar para garantir um resultado conhecido. Esta tabela possui apenas uma coluna, chamada DUMMY com apenas uma linha, contendo o valor X. A DUAL é criada automaticamente pelo Oracle, sob o esquema SYS, mas pode ser acessada por outros usuários. Sempre que precisamos verificar um resultado conhecido, como a data e hora do servidor ou o valor atual de uma sequence, simplesmente fazemos a consulta referenciando a tabela DUAL. Isto por que toda consulta SQL deve envolver uma tabela, porém, se utilizarmos qualquer tabela “povoada” nesta consulta, teremos uma série de inconvenientes, como estratégia de acesso ou eventual utilização de índices, etc.
Desta forma, ao inserirmos um registro na tabela "tipo_item", automaticamente estaremos buscando o próximo valor da seqüência criada antes da linha ser inserida, e colocando-o no campo "id".

insert into tipo_item (descricao) values ('Revista');

Após essa inserção, uma consulta na tabela retornaria:

select * from tipo_item;     ID        DESCRICAO
----------      --------------------------------------------------------------------------------
     1         Revista

Perfeito. Vamos implementar a segunda tabela. Como premissas para a tabela "ITEM" temos:

1. A tabela deverá conter os campos:
1. id: chave primária da tabela. Este campo será composto por 12 números. O ano atual em 4 digitos (Ex. 2008) seguido de um sequencial de 8 digitos (Ex. 23). Como resultado, teríamos (Ex.) 200823
   
2. descricao: nome do item, com no máximo 100 caracteres.
   
3. informacoes: campo que conterá informações sobre o item, tais como Autor (no caso de livros), Edição (revistas), Diretor (DVDs), e quaisquer outros dados necessários para a classificação do registro.
4. quantidade: número de itens.
5. tipo_item: identificação (id) do tipo de item cadastrado.
6. data_insercao: data na qual o registro foi inserido.
   
2. O campo "id" será a PRIMARY KEY e o campo "tipo_item" será a FOREIGN KEY (chave estrangeira). Ambos são NOT NULL.
   
3. O campo "descricao" não poderá ser nulo e é unico.
4. O campo "informacoes" poderá armazenar textos (tipo CLOB) e pode ser nulo.
5. O campo "quantidade" possui como valor DEFAULT o número 1.
6. O campo "data_insercao" deve ser definido automaticamente pelo sistema (pela TRIGGER).
   

Observe as novas regras:

• DEFAULT: define um valor padrão para o campo.
• FOREIGN KEY: chave estrangeira, que relacionará o campo "tipo_item" da tabela "item" ao campo "id" da tabela "tipo_item".

De forma similar ao "TIPO_ITEM", precisaremos de uma SEQUENCE, porém, a TRIGGER será mais detalhada.
Vamos para a criação da SEQUENCE:

create sequence seq_item_id
  start with 1
  maxvalue 99999999
  increment by 1
  nocycle;

Criando a tabela:

create table item (
  id NUMBER(12) not null,
  descricao VARCHAR2(100) not null unique,
  informacoes CLOB,
  quantidade NUMBER(6) default 1,
  tipo_item NUMBER(8) not null,
  data_insercao TIMESTAMP not null,
  constraint pk_item_id primary key (id),
  constraint fk_item_tipo_item foreign key (tipo_item) references tipo_item(id)
);

A ideia da TRIGGER é similar (funcionará antes da linha ser inserida, porém, sua programação é um pouco mais complexa). Pegaremos a data atual através do SYSDATE. Esse valor será necessario para preenchimento do campo "data_insercao" e para compor o ID (com o ano atual).
Vamos agora para a criação da TRIGGER:

create or replace trigger trigger_item_id
before insert on item
for each row
declare
  data_atual TIMESTAMP;
  now_id NUMBER(4);
  now_data CHAR(4);
  full_id CHAR(12);
begin
  select SYSDATE into data_atual from dual;
  select seq_item_id.nextval into now_id from dual;
  select TO_CHAR(data_atual, 'YYYY') into now_data from dual;
  select ( now_data || TO_CHAR(now_id) ) into full_id from dual;
  select TO_NUMBER(full_id) into :new.id from dual;
  select data_atual into :new.data_insercao from dual;
end trigger_item_id;
/

Entendeu? Well, em "declare" declaramos as variáveis necessárias: "data_atual" para obter o valor de SYSDATE, "now_id" para guardar o seq_item_id.nextval, "now_data" para guardar o ano atual e "full_id" para armazenar a composição de now_data + now_id. Aliás, os nomes das variáveis não foram muito bem definidos (opsss!). A primeira linha dentro do escopo (após o begin) busca o valor de SYSDATE (e guarda-o em "data_atual"). Em seguida, busca-se o NEXTVAL da sequence "set_item_id" e armazena-o em "now_id". A linha três busca o ano de "data_atual" e guarda-o em "now_data". Em seguida criamos "full_id" com os valores de "now_data" e "now_id". Por fim, é definido o id com "full_id" e o campo "data_insercao" com "data_atual" da linha que está sendo inserida (no caso, ":new"). Certamente nós poderíamos reescrever esse código com uma quantidade menor de linhas! Aqui optei em separar as etapas para facilitar a explicação.

Vamos realizar algumas inserções:

insert into item (descricao,informacoes,quantidade,tipo_item) values ( 'Revista X', 'Editora XX' , 10 , 1 );
insert into item (descricao,informacoes,quantidade,tipo_item) values ( 'Revista Y', 'Editora YY' , 5 , 1 );

Caso tentássemos a seguinte inserção:

insert into item (descricao,informacoes,quantidade,tipo_item) values ( 'Revista X', 'Editora AA' , 1 , 1 );

O Oracle retornaria a mensagem "ORA-00001: restrição exclusiva (ADMIN.SYS_C004059) violad" que indica que a inserção falhou pela cláusula UNIQUE associada ao campo "descricao".

Referências:
http://www.techonthenet.com/oracle/sequences.php
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_2011.htm
http://www.orafaq.com/wiki/PL/SQL_FAQ
http://eduardolegatti.blogspot.com/2008/11/coluna-do-tipo-auto-incremento-no.html
http://www.psoug.org/reference/convert_func.html
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/operators003.htm
http://www.mydigitallife.info/2006/01/14/insert-text-literal-and-concatenation-in-oracle-sql/
http://www.javafree.org/javabb/topic-856362-Diferenca+de+dias+no+Oracle
http://forum.imasters.uol.com.br/lofiversion/index.php/t190902.html
http://www.orafaq.com/scripts/sql/set_sequence.txt

Obrigado pela companhia.