O que devemos conhecer sobre SQL/Oracle Parte 11: Gerenciando objetos de schema

Adicionando e modificando colunas

É possível alterar colunas existentes em uma tabela, seja adicionando uma nova ou alterando uma já existente. Durante a adição de uma coluna é necessário especificar seu nome e seu tipo de dados, também é possível incluir um valor default e uma restrição. Toda coluna adicionada é incluída no final da lista de colunas de uma tabela, vejamos um exemplo:

ALTER TABLE FUNCIONARIOS ADD (DATA_DEMISSAO VARCHAR2(20), ATIVO CHAR(1) DEFAULT ‘S’ NOT NULL);

No exemplo acima incluímos duas novas colunas, sendo que em uma delas especificamos um valor default e uma restrição. É importante ressaltar que para o caso de restrições NOT NULL devemos especificar um valor default se essa tabela já possuir registros.

Para o caso de modificações em colunas existentes é possível alterar o tipo de dados, o valor default e as restrições, quando o comando for construído e mais de uma modificação for necessária ela deve obedecer a essa ordem, porém o comando não exige todas as alterações, apenas uma é o suficiente, exemplo:

ALTER TABLE FUNCIONARIOS MODIFY (DATA_DEMISSAO DATE);

Quando falamos de restrições NOT NULL é com a cláusula MODIFY que devemos alterá-lo, seja para incluir ou remover:

ALTER TABLE FUNCIONARIOS MODIFY(ATIVO NULL);

Ou para incluir:

ALTER TABLE FUNCIONARIOS MODIFY(ATIVO NOT NULL);

Quando é necessário alterar colunas temos que levar em consideração se existem registros, se existirem pode ser que esses valores não permitam a alteração da coluna:

• Não é possível alterar uma coluna para numérico se algum registro contiver algum caracter que não seja um numero;
• Não é possível modificar uma coluna para torna-la chave primaria se já existirem valores duplicados;

Para um melhor entendimento do que pode ou não ser feito analise o quadro abaixo:

Tipo da mudança	Quando a tabela não tiver registros	Quando uma tabela tiver registros
		Quando a coluna é nula	Quando a coluna contém alguns valores nulos	Quando a coluna contém dados em todos os registros
Tipos de dados	SIM		NÃO. Conversão automática de tipos não é permitida aqui.
Precisão e escala	SIM		SIM, se os valores existentes não perderem dígitos significantes, caso contrário não.
NOT NULL	SIM	NÃO, a menos que um valor DEFULT seja definido.		SIM
PRIMARY KEY	SIM	NÃO		SIM, se os valores existentes forem únicos.
UNIQUE	SIM		SIM, apenas se os valores forem únicos.
FOREIGN KEY	SIM		SIM, desde que possuam valores que façam referencia a tabela referenciada no comando.
CHECK	SIM		SIM, se os valores existentes não violarem a restrição CHECK.
DEFAULT	SIM

Renomear uma coluna é um caso a parte, não é possível faze-lo com nenhum dos comandos indicados acima, para tal utilizamos a sintaxe:

ALTER TABLE FUNCIONARIOS RENAME COLUMN COD TO CODIGO;

Resumindo:

• O comando ALTER TABLE pode ser usado para adicionar ou modificar colunas;
• A cláusula ADD do comando ALTER TABLE é usado para adicionar colunas a tabelas;
• Uma coluna é adicionada ao se especificar seu nome e tipo, também e possível definir uma restrição;
• A cláusula MODIFY pode ser usada para modificar colunas existentes;
• É possível modificar o tipo de dados de uma coluna, desde que não existam dados nessa coluna;

Removendo colunas e setando colunas para UNUSED

É perfeitamente possível remover colunas de uma tabela, muitas vezes essa ação concede não só ganho no espaço de armazenamento como também pode aumentar a velocidade de consultas. Para remover uma coluna:

ALTER TABLE FUNCIONARIOS DROP COLUMN ATIVO;

Ou

ALTER TABLE FUNCIONARIOS DROP (ATIVO);

Também é possível remover mais de uma coluna

ALTER TABLE FUNCIONARIOS DROP (ATIVO, DATA_DEMISSAO);

Uma coluna que é referenciada por outra tabela não pode ser removida, a menos que essa referencia seja eliminada. Suponhamos que exista uma tabela chamada DEPENDENTES, e que na tabela de funcionários exista uma coluna chamada CODDEPENDENTE que faz referencia a coluna CODIGO da tabela de dependentes. Para remover a coluna CODIGO da tabela de dependentes poderíamos fazer o seguinte:

ALTER TABLE DEPENDENTES DROP COLUMN CODIGO CASCADE CONTRAINTS;

Desse modo a coluna da tabela de dependentes e a restrição da tabela de funcionários serão eliminadas.

O processo de remover uma coluna, dependendo do ambiente, pode ser muito demorado por isso em alguns casos é preferível utilizar o UNUSED, basicamente ele tem os mesmos efeitos da remoção de uma coluna, porém funciona de forma mais rápida. Devemos deixar claro que uma coluna setada como UNUSED não poderá ser recuperada, nem com um ROLLBACK, apesar de não ter sido removida ela jamais poderá ser utilizada novamente. Outro ponto importante é que ela ainda estará associada à tabela, cedo ou tarde o comando de remoção dessa coluna deve ser executado. Agora vejamos como fazer:

ALTER TABLE FUNCIONARIOS SET UNUSED COLUMN ATIVO;

Ou

ALTER TABLE FUNCIONARIOS SET UNUSED (ATIVO, DATA_DEMISSAO);

Para remover essas colunas devemos:

ALTER TABLE FUNCIONARIOS DROP UNUSED COLUMNS;

Resumindo:

• A cláusula DROP do comando ALTER TABLE é usado para remover colunas;
• Quando uma coluna é removida seus dados são perdidos;
• Remover uma coluna pode ser um processo que exija grande processamento se a tabela possuir muitos registros;
• Uma coluna que possui restrição quando removido também tem sua restrição removida;
• SET UNUSED torna uma coluna indisponível permanentemente, e não pode ser recuperada;
• SET UNUSED pode ser usada para evitar o problema com processamento em tabelas com muitos registros;
• Para remover as colunas indisponíveis geradas pelo SET UNUSED basta utilizar o comando ALTER TABLE DROP UNUSED COLUMNS;

Adicionando restrições

Para adicionar restrições utilizamos o comando ALTER TABLE, dessa forma podemos tanto criar restrições IN-LINE, modificando colunas ao especificar a restrição como parte da mesma, ou podemos especificar restrições OUT-OF-LINE, onde o foco não é a coluna e sim a própria restrição, veja os exemplos:

Adicionando uma restrição IN-LINE

ALTER TABLE FUNCIONARIOS MODIFY CODIGO CONSTRAINT PK_FUNC PRIMARY KEY;

Adicionando uma restrição OUT-OF-LINE

ALTER TABLE FUNCIONARIOS ADD CONSTRAINT PK_FUNC PRIMARY KEY (CODIGO);

A grande diferença aqui, da mesma maneira que as restrições geradas em tempo de criação da tabela é a possibilidade de criar chaves compostas com o uso da restrição OUT-OF-LINE.

Como visto anteriormente restrições NOT NULL são um caso aparte, não é possível utilizar o ADD CONSTRAINT, é necessário alterar a coluna:

ALTER TABLE FUNCIONARIOS MODIFY ATIVO CONSTRAINT NN_FUNC NOT NULL;

Em alguns casos o comando ALTER TABLE não poderá realizar uma alteração na restrição, e para isso será necessário criá-la novamente. Esses casos são:

• Adicionar ou remover colunas da lista de colunas das chaves primária;
• Alterar a logica da restrição CHECK;
• Alterar a tabela que a chave estrangeira faz referencia;

Para remover restrições utilizamos o comando ALTER TABLE com a cláusula DROP. Considerando que uma tabela pode ter apenas uma chave primaria, para removermos essa restrição não precisamos o nome da restrição ou mesmo as colunas relacionadas, nesse caso também é possível indicar o CASCADE, removendo as restrições referenciais, e o KEEP INDEX ou DROP INDEX que indica se desejamos manter ou remover o índice relacionado a essa chave:

ALTER TABLE FUNCIONARIOS DROP PRIMARY KEY CASCADE;

Agora se a intenção for remover uma restrição de chave única devemos especificar as colunas envolvidas (com as mesmas opções vistas acima):

ALTER TABLE FUNCIONARIOS DROP UNIQUE (CODIGO, DEPARTAMENTO);

Outra forma de remover uma restrição é pelo seu nome (aqui a única opção é o CASCADE):

ALTER TABLE FUNCIONARIOS DROP CONSTRAINT PK_FUNC;

A única exceção é o NOT NULL, ele não pode ser removido, para tal devemos usar o MODIFY.

Em certos casos a remoção da restrição é necessária apenas por algum tempo, com a finalidade de algum teste, por exemplo, então nesse caso podemos apenas desativa-la. Uma restrição desativada continua existindo, com sua definição salva na base, porem ela para de ser verificada. A ativação/desativação pode ser feita de três formas:

• Se for uma PRIMARY KEY basta identificar a tabela e indicar seu tipo;
• Se for UNIQUE basta identificar as colunas;

Para as outras restrições devemos indica-las pelo nome:

ALTER TABLE FUNCIONARIOS DISABLE FK_DEPARTAMENTO;

Vale lembrar que para o caso do DISABLE podemos utilizar o CASCADE, forçando o desligamento de restrições relacionadas a ela, o mesmo não é valido para o ENABLE, cada restrição deve ser habilitada manualmente.

• O comando ALTER TABLE pode ser usado para adicionar restrições a uma tabela com a cláusula ADD;
• A cláusula DROP pode ser usada para remover restrições;
• Uma restrição desabilitada pode ser habilitada novamente desde que os dados existentes satisfaçam a restrição;

Ainda sobre restrições é importante destacar o fato de que, caso uma tabela que seja referenciada por outra seja incluída em um comando DROP TABLE ocorrera erro após a execução, isso devido a integridade referencial. Para realizar a exclusão da tabela será necessário incluir CASCADE CONSTRAINTS:

DROP TABLE FUNCIONARIOS CASCADE CONSTRAINTS;

Como já vimos, registros de uma tabela que são referenciados por outra não podem ser apagados, devido a restrição referencial, porem existe uma maneira de realizar isso, existe uma cláusula na criação da restrição que pode “contornar” esse fato:

ALTER TABLE FUNCIONARIOS ADD CONSTRAINT FK_DEPARTAMENTO FOREIGN KEY (DEPARTAMENTO) DEFERENCES DEPARTAMENTOS (CODIGO) ON DELETE SET NULL;

Com o exemplo acima os registros excluídos na tabela de departamentos serão bem sucedidos, sendo que a tabela de funcionários receberá o valor nulo para a coluna referencial.

Outra opção quanto a restrições é a possibilidade de torna-las “adiáveis”, ou seja, suas validações ocorrem apenas após o COMMIT. Para conseguir tal funcionalidade a restrição tem que ser definida como DEFERRABLE, e durante a sessão da transação a restrição em questão deve ser apontado como adiável, lembrando que isso se da apenas para a sessão atual, veja o exemplo:

Indica que a restrição pode ter sua validação adiada

ALTER TABLE FUNCIONARIOS ADD CONSTRAINT FK_DEPARTAMENTO FOREIGN KEY (DEPARTAMENTO) DEFERENCES DEPARTAMENTOS (CODIGO) DEFERRABLE;

Indica na sessão atual que essa funcionalidade foi ativada, pode ser para uma restrição em questão ou para todas desse tipo

SET CONSTRAINT FK_DEPARTAMENTO DEFERRED;

Ou 

SET CONSTRAINT ALL DEFERRED;

Para alterar basta

SET CONSTRAINT FK_DEPARTAMENTO IMMEDIATE;

Ou 

SET CONSTRAINT ALL IMMEDIATE;

Pode ser desejável alterar o nome de uma restrição, para isso utilizamos o RENAME CONSTRAINT:

ALTER TABLE FUNCIONARIOS RENAME CONSTRAINT FK_DEPARTAMENTO TO FK_DEP_FUNC;

Criando índices com o comando CREATE TABLE

Quando uma tabela é criada, um índice é gerado de forma automática para as restrições de chave primaria e única. Isso ocorre de forma implícita, e o próprio banco indica um nome para esse novo objeto, algo como SYS_C0011223.

Mas também é possível realizar essa criação no próprio comando de criação da tabela, com a cláusula USING INDEX:

De forma IN-LINE

CREATE TABLE FUNCIONARIOS(CODIGO NUMBER(10) PRIMARY KEY PK_FUNC USING INDEX (CREATE INDEX IX_FUNC ON FUNCIONARIOS(CODIGO)), NOME VARCHAR2(30) );

Ou de forma OUT-OF-LINE

CREATE TABLE FUNCIONARIOS(CODIGO NUMBER(10), NOME VARCHAR2(30), CONSTRAINT PK_FUNC PRIMARY KEY(CODIGO) USING INDEX (CREATE INDEX IX_FUNC ON FUNCIONARIOS(CODIGO)) );

Resumindo:

• Quando uma restrição PRIMARY KEY ou UNIQUE é criada como parte de um comando CREATE TABLE um índice é automaticamente criado;
• A cláusula USING INDEX pode ser chamada para definir de forma explicita um índice;

Criando índices baseados em funções

Quando temos consultas em uma mesma tabela fazendo uso de uma determinada condição podemos acelerar o resultado providenciando um índice para essa condição. É possível determinar uma função do próprio banco, ou mesmo uma expressão de calculo.

Vamos supor que nós temos em nosso ambiente consultas que buscam na tabela de funcionários o nome sempre em minúsculo, para agilizar esse processo poderíamos  criar o seguinte índice:

Usando uma função do banco

CREATE INDEX IX_NOM_FUNC_LOWER ON FUNCIONARIOS (LOWER(NOME));

Teríamos um ganho de performance na seguinte consulta

SELECT * FROM FUNCIONARIOS WHERE LOWER(NOME) = ‘silva’;

Agora suponhamos que existam consultas que utilizem como condição o salario do funcionário multiplicado por um índice qualquer:

CREATE INDEX IX_CALC_SAL_FUNC ON FUNCIONARIOS (SALARIO * 1.15);

Nesse caso teríamos um ganho de performance na seguinte consulta

SELECT * FROM FUNCIONARIOS WHERE (SALARIO * 1.15) > 2000;

Resumindo:

• Índices podem ser baseados em uma expressão, não precisa necessariamente incluir uma função SQL, pode ser baseado em uma expressão de qualquer tipo;
• Consultas que utilizam a função podem se beneficiar do índice;

Criando e utilizando tabelas externas

Tabelas externas são utilizadas apenas para leitura, são definidas na base de dados mas existem fora dele, ou seja, sua definição ocorre dentro da base e seus dados estão fora. Não é possível aplicar comandos DML em tabelas externas, não é possível incluir restrições ou índices, seu uso é restrito ao comando SELECT. Seu beneficio é utilizar informações de fontes de dados que não fazem parte da base de dados, como arquivos por exemplo.

Para criar uma tabela externa precisamos fazer com que o banco reconheça um diretório dentro do sistema operacional, para isso devemos criar um objeto do tipo DIRECTORY:

CREATE OR REPLACE DIRECTORY ARQUIVOS_EXT AS ‘C:\ARQUIVOS’;

O comando acima permite a manipulação de arquivos dentro do diretório ‘C:\ARQUIVOS’ do sistema operacional, fazendo menção sempre ao nosso objeto DIRECTORY chamado ARQUIVOS_EXT.

Agora que possuímos um DIRECTORY, basta colocarmos nossa fonte de dados externa dentro dele e criar a estrutura de nossa tabela externa:

Nosso arquivo deve possuir o seguinte conteúdo, e se chamar dados.txt

COD        NOME

---------- ------------------------------

1          MARIA

2          FLAVIA

3          ANA

E para criar nossa tabela externa

CREATE TABLE FUNCIONARIOS_EXT

( COD  CHAR(10)

, NOME CHAR(30) 

)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

  DEFAULT DIRECTORY ARQUIVOS_EXT

  ACCESS PARAMETERS

  ( RECORDS DELIMITED BY NEWLINE

    SKIP 2

    FIELDS

    ( COD  CHAR(10)

     ,NOME CHAR(10)

     )

  )

  LOCATION ('DADOS.TXT')

)

Em nossa estrutura definimos inicialmente quais serão nossas colunas, depois incluímos a cláusula ORGANIZATION EXTERNAL, que é obrigatória, em seguida indicamos o uso do ORACLE_LOADER para carregar a estrutura, em seguida indicamos nosso objeto DIRECTORY e logo a seguir indicamos os parâmetros de acesso, onde definimos que a cada nova linha teremos um novo registro, definimos também que as duas primeiras linhas do arquivo devem ser ignoradas e quais colunas do arquivo serão usadas (é muito importante que o tamanho seja respeitado) e por fim indicamos o nome de nosso arquivo.

Depois de criado utilizamos esse objeto dentro de um comando SELECT normal:

SELECT * FROM FUNCIONARIOS_EXT;

Resumindo:

• Tabelas externas são tabelas read-only que se associam a objetos fora do banco de dados;
• A comunicação entre os arquivos externos que contem dados e o objeto Oracle é baseado na logica do SQL Loader ou no Oracle Data Pump;
• É necessário utilizar o objeto DIRECTORY como parte da definição de uma tabela externa;