O que devemos conhecer sobre SQL/Oracle Parte 2: Usando comandos DDL para criar e manipular tabelas

Categorizando os principais objetos de uma base de dados

Os objetos de banco de dados disponibilizam as estruturas necessárias para que uma aplicação seja formada, ou seja, é tudo aquilo que dá sentido na concepção de uma base de dados. Esses objetos vão desde tabelas, que é a estrutura base, até objetos como visões.

Todos os objetos do banco possuem uma categoria, ou pertencem a um “schema” ou são do tipo “non-schema”. Objetos “schema” pertencem a um usuário e existem enquanto essa conta de usuário existir. Já os objetos “non-schema” existem para dar suporte ao uso do banco, como por exemplo, usuários, regras e sinônimos públicos. Um pequeno resumo sobre objetos:

• Tabelas armazenam dados;
• Constraints são regras em tabelas;
• Views funcionam como “janelas/visões” para tabelas;
• Indexes proporcionam ganho de velocidade durante consultas em tabelas, funciona como um índice de um livro;
• Sequences são contadores de objetos;
• Synonyms são uma forma alternativa de se nomear objetos;
• Users são objetos que possuem outros objetos;
• Roles são conjuntos de direitos, ou privilégios, que podem ser concedidos a usuários de modo que esses usuários possam acessar esses objetos;
• Objetos podem ser “schema” ou “nom-schema”;
• Tables, views, indexes, sequences, e private synonyms são objetos “schema”;
• User, roles e public synonyms são objetos “non-schema”;

Criando uma tabela simples

As tabelas são os objetos mais utilizados e conhecidos de um banco, sua sintaxe padrão é a seguinte:

CREATE TABLE nome_da_tabela (nome_da_coluna tipo_de_dados);

É importante ter em mente na criação de uma tabela (ou outro objeto qualquer) que o nome não pode ser o mesmo de outro objeto que esteja no mesmo “namespace”, ou seja, no caso de uma tabela o nome escolhido não pode ser o mesmo de outra tabela, visão, sequencia ou sinônimo privado do “schema” atual. 

Basicamente existe um “namespace” geral do banco onde ficam os usuários, regras e sinônimos públicos, e a cada “schema” temos um “namespace” para tabelas, visões, sequencias, sinônimos privados e tipos definidos pelo usuário, um “namespace” para índices e outro “namespace” para restrições.

Ainda quanto a nomeação devemos ter em mente que os nomes devem ter no máximo 30 caracteres, o caractere inicial deve ser uma letra, e o nome não pode ser uma palavra reservada.

Resumindo:

• O comando CREATE TABLE é usado para criar uma tabela;
• Você insere um nome seguindo as regras de nomeação de objetos;
• Você insere nome para as colunas da tabela, seguindo as mesmas regras;
• Todas as tabelas devem possuir ao menos uma coluna;

Revisando a estrutura de uma tabela

A revisão da estrutura da tabela é feita por meio de comandos implementados por ferramentas que acessam a base, como por exemplo, o comando DESC do SQL Plus:

• O comando DESC pode ser usado para mostrar a estrutura de uma tabela;
• A estrutura inclui o nome da tabela, o nome das colunas, os tipos de dados, e opcionalmente as constraints;
• Toda coluna deve possuir um tipo de dado;
• Tipos de dados incluem numéricos, caracteres e tipos data, como por exemplo, VARCHAR2, CHAR (pode ser criado sem uma precisão), NUMER e DATE;
• Tipos de dados também incluem tipos como BLOB;

Listando os tipos de dados disponíveis para as colunas

A seguir mostraremos os principais tipos de dados do Oracle:

• Caracteres
• CHAR(n): caracteres de tipo fixo, onde “n” diz respeito ao tamanho, se for especificado tamanho três e for armazenado um texto de tamanho um, a base será ocupada por três posições (sendo as restantes completadas com espaços a direita). O tamanho é opcional e caso omitido na declaração assume um. O valor máximo para ele é de 2000;
• VARCHAR2(n): caracteres de tamanho variável, diferente de CHAR, se uma variável com tamanho três for definida e um texto com tamanho um for usado apenas um é salvo na base. A especificação do tamanho é obrigatória, e seu tamanho máximo de 4000 bytes (o que não são necessariamente 4000 caracteres);
• Numérico
• NUMBER(n, m): recebe números positivos e negativos, sendo “n” sua precisão (numero máximo de dígitos significantes, total) e “m” a escala, correspondente ao total de dígitos do lado direito do separador decimal. Ambos, “n” e “m”, são opcionais, onde “n” assume o valor máximo e “m” se torna zero. O valor de “n” pode variar de 1 a 38, e “m” de -84 a 127. Se um valor atribuído tiver precisão maior que o especificado ocorre erro e o valor é rejeitado, o mesmo não ocorre com a escala, onde sempre ocorre um arredondamento (.5 arredonda acima), por exemplo, uma declaração de NUMBER recebendo 4.56 armazena 4.56, uma declaração NUMBER(2) recebendo 4.56 armazena 5, uma declaração NUMBER(5,2) recebendo 4.56 armazena 4.56, uma declaração NUMBER(5,2) recebendo 4.5678 armazena 4.57;
• Datas
• DATE: recebe data e hora, incluindo ano, mês, dia, hora, minuto e segundo. Podem ser armazenados utilizando valores literais ou com funções de conversão. Literais são utilizadas dentro de aspas simples, e podem ser especificadas de varias maneiras. O padrão do formato de data pode ser identificado pelo parâmetro NLS_DATE_FORMAT, por padrão o corrente é o americano com ‘DD-MON-RR’, onde ‘DD’ é o dia em dois dígitos, ‘MON’ e a abreviação do mês em três letras e ‘RR’ e o ano em dois dígitos;
• TIMESTAMP(n): é uma extensão de DATE, adicionando a porção de fração dos segundos. Armazena ano, mês, dia, hora, minuto, segundo e as frações de segundo. O valor de “n” define o tamanho da fração dos segundos, indo de 1 a 9, se omitido assume o valor 6 por default;
• TIMESTAMP(n) WITH TIME ZONE: variação de TIMESTAMP adicionando o nome da zona de tempo ou sua compensação. É utilizado para rastrear informação de diferentes zonas de tempo de áreas geográficas, como por exemplo, uma base de dados que armazena informações de países diferentes;
• TIMESTAMP(n) WITH LOCAL TIME ZONE: variação de TIMESTAMP, diferenciando de TIMESTAMP WITH TIME ZONE pelo fato de não armazenar junto a coluna o nome da zona de tempo, este ultimo é recuperado e enviado da sessão atual;
• INTERVAL YEAR(n) TO MONTH: armazena a diferença entre anos e meses, sendo “n” com variação entre 0-9, quando omitido assume o valor 2. Útil para armazenar diferenças entre datas;
• INTERVAL DAY(n1) TO SECOND(n2): armazena a diferença entre dias, horas, minutos e segundos, sendo “n1” a precisão para dias (de 0-9, default 2) e “n2” a precisão para segundos (0-9, default 6). Útil para armazenar diferenças entre datas;
• Grandes objetos (large objects (LOBs))
• BLOB: abreviação de “Binary large object“, aceita dados binários como imagens ou vídeos. Sua declaração ocorre sem precisão ou escala. Seu tamanho máximo e de 4 gb. Não podem ser chaves primarias, ou serem utilizados com DISTINCT, GROUP BY, ORDER BY ou junções;
• CLOB: abreviação para “Character large object”, aceita grandes textos. Sua declaração ocorre sem precisão ou escala;
• NCLOB: aceita o CLOB em Unicode. Serve como alternativa a representação ASCII;

Resumindo:

• Cada coluna deve possuir um tipo de dados;
• Tipos de dados incluem números, caracteres, datas e texto;
• Tipos de dados também podem ser binários como, por exemplo, BLOB;

Explicando como restrições são criadas em tempo de criação de tabela

Restrições são objetos que oferecem suporte a tabelas, inserindo regras de modo a organizar os dados recebidos. Essas restrições podem ser criadas no mesmo comando de criação da tabela das seguintes formas:

• In line

A restrição segue a definição da coluna, apos o tipo, desse modo não é possível especificar, por exemplo, uma chave primaria composta:

CREATE TABLE FUNCIONARIOS(ID_FUN NUMBER CONSTRAINT PK_FUN PRIMARY KEY, NM_FUM VARCHAR2(50));

• Out of line

A especificação das restrições ocorre apos todas as declarações das colunas, nesse caso é possível indicar restrições compostas, sempre separadas por virgula:

CREATE TABLE FUNCIONARIOS(ID_FUN NUMBER, NM_FUN VARCHAR2(50), CONSTRAINT PK_FUN PRIMARY KEY (ID_FUN));

Também é possível criar restrições fora do comando de criação de tabelas, com ALTER TABLE, desse modo primeiro criamos a tabela e em um segundo comando indica-se a alteração da mesma para adicionar a restrição:

CREATE TABLE FUNCIONARIOS(ID_FUN NUMBER, NM_FUN VARCHAR2(50));

ALTER TABLE FUNCIONARIOS ADD CONSTRAINT PK_FUN PRIMARY KEY (ID_FUN);

Sobre os tipos de restrições:

• NOT NULL
• O conceito de NULL indica que ele é a falta de valor, ou seu desconhecimento, logo ele não é zero nem espaço. Por isso todo calculo com colunas nulas sempre resultam em valor nulo. A restrição NOT NULL garante que para as colunas definidas com essa restrição sempre haverá valor. O NOT NULL não pode ser declarado nos moldes “Out of line”, caso seja adicionado com o ALTER TABLE é necessário utilizar o comando equivalente ao “In line”:

ALTER TABLE FUNCIONARIOS MODIFY NM_FUN NOT NULL;

• UNIQUE
• Essa restrição indica que para a(s) coluna(s) marcadas como UNIQUE não poderão existir valores repetidos na mesma tabela, ou seja, se uma coluna com essa restrição recebeu a valor 1, os próximos registros inseridos não podem possuir o valor 1. No caso de valores NULL isso não se aplica, já que NULL, como visto anteriormente, é a ausência de valor;

• PRIMARY KEY
• Uma PRIMARY KEY é o identificador único para cada registro de uma tabela, só pode haver um, pode-se dizer que ele é a combinação das restrições NOT NULL e UNIQUE;

• FOREIGN KEY
• Relaciona uma ou mais colunas de uma tabela a uma chave primária, ou única, de outra. Desse modo fica garantido que as colunas com essa restrição irão possuir apenas valores previamente existentes na tabela referencial. As colunas envolvidas devem possuir o mesmo tipo de dados, o nome não importa. A FOREIGN KEY em conjunto com a PRIMARY KEY constitui a integridade referencial entre duas tabelas;

• CHECK
• Aplica certas regras no momento da recepção dos dados, como por exemplo, restringir valores que estejam dentro de um limite. Considerando uma coluna com o tipo NUMBER(1), queremos restringir para ela apenas os valores 0 ou 1, para tal:

CREATE TABLE FUNCIONARIOS(ID_FUN NUMBER, NM_FUN VARCHAR2(50), TP_FUN NUMBER(1) CHECK (TP_FUN IN(0, 1) ) );

Resumindo:

• Os tipos de restrições são NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, e CHECK;
• Uma coluna com a restrição NOT NULL deve possuir um valor para cada registro inserido na tabela;
• A restrição UNIQUE indica que para a coluna em especifico sempre serão necessários valores distintos para cada registro inserido;
• A restrição PRIMARY KEY é a combinação das restrições NOT NULL e UNIQUE;
• Uma PRIMARY KEY pode ser incluída para uma ou mais colunas, quando em muitas colunas é considerada uma chave composta;
• Uma tabela pode possuir apenas uma PRIMARY KEY;
• Uma FOREIGN KEY relaciona uma ou mais colunas de uma tabela com colunas similares de outra;
• Uma FOREIGN KEY requer que a segunda tabela já possua uma PRIMARY KEY associada à coluna relacionada antes que essa FOREIGN KEY seja criada;
• Uma vez criada, a FOREIGN KEY assegura que os valores inseridos na tabela correspondem a valores existentes na tabela referenciada;
• Restrições podem ser criadas com CREATE TABLE ou com ALTER TABLE;
• Restrições podem ser definidas como parte de uma coluna “in line” ou depois “out of line”;