Criar, alterar e eliminar uma tabela |
Neste módulo vamos ver as seguintes operações:
Criar uma tabela
Para criar uma tabela usamos o comando CREATE TABLE, indicando o nome da tabela, os nomes das colunas que a constituem e quais as restrições a aplicar. A sintaxe deste comando pode ser vista no manual "SQL Reference".
Como exemplo aqui fica um diagrama entidade relacionamento do qual vamos criar todas as tabelas e relações:
create table EQUIPA ( ID_EQUIPA NUMBER(10,0) not null, NOME VARCHAR2(100) constraint nn_equipa_nome not null, constraint PK_EQUIPA primary key (ID_EQUIPA) ); create table JOGADOR ( ID_JOGADOR NUMBER(10,0) not null, NOME varchar2(100) not null, POSICAO varchar2(100) not null, BI VARCHAR(100), constraint PK_JOGADOR primary key (ID_JOGADOR), constraint UK_JOGADOR unique (BI) ); create table JOGO ( EQUIPA_CASA NUMBER(10,0) not null, EQUIPA_VISITANTE NUMBER(10,0) not null, DATA DATE not null, LOCAL VARCHAR2(100) default 'Estádio Alvalade XXI' not null, RISCO_SEGURANCA VARCHAR(100) not null, constraint PK_JOGO primary key (EQUIPA_CASA, EQUIPA_VISITANTE, DATA), constraint FK_JOGO_REF_EQUIPA_CASA foreign key (EQUIPA_CASA) references EQUIPA (ID_EQUIPA), constraint FK_JOGO_REF_EQUIPA_VISITANTE foreign key (EQUIPA_VISITANTE) references EQUIPA (ID_EQUIPA), constraint CK_JOGO_01 check (risco_seguranca in ('Alto','Medio','Baixo') and equipa_casa != equipa_visitante) ); create table CONTRATADO ( ID_JOGADOR NUMBER(10,0) not null, ID_EQUIPA NUMBER(10,0) not null, DATA_INICIO DATE not null, DATA_FIM DATE not null, constraint PK_CONTRATADO primary key (ID_JOGADOR, ID_EQUIPA, DATA_INICIO), constraint FK_CONTRATA_REFERENCE_JOGADOR foreign key (ID_JOGADOR) references JOGADOR (ID_JOGADOR), constraint FK_CONTRATA_REFERENCE_EQUIPA foreign key (ID_EQUIPA) references EQUIPA (ID_EQUIPA), constraint CKT_CONTRATADO check (data_inicio < data_fim) ); |
create table succeeded. create table succeeded. create table succeeded. create table succeeded. |
Criar uma tabela com comando SELECT
É possível criar uma tabela aproveitando linhas de uma outra tabela já existente. Para isso utiliza-se uma variante do comando CREATE TABLE que recorre a uma instrução SELECT para escolher as linhas e as colunas que pretendemos na nova tabela. Ter em conta que:
No exemplo abaixo vamos criar a tabela DEPT30 com o número, nome, função e salário dos empregados do departamento 30:
create table DEPT30 as ( select empno, ename, job, sal from emp where deptno=30 ); |
create table succeeded. |
No exemplo abaixo vamos criar uma tabela com o nome, salário e nível (SALGRADE) dos empregados:
create table EMP_SALS (emp_name, salary, grade) as ( select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal ); |
create table succeeded. |
Este comando pode ser usado para criar uma tabela sem linhas, desde que a clausula WHERE do select não devolva resultados.
Alterar o nome da tabela
O comando ALTER TABLE pode ser usado para alterar o nome de uma tabela, como mostra o exemplo abaixo. A sintaxe completa deste comando pode ser consultada no manual "SQL Reference".
alter table CONTRATADO rename to HIRED; alter table HIRED rename to CONTRATADO; |
alter table CONTRATADO succeeded. alter table HIRED succeeded. |
Adicionar uma coluna a uma tabela existente
O comando ALTER TABLE pode ser usado para adicionar uma coluna a uma tabela. No exemplo abaixo adicionamos a coluna custo de transferência:
alter table CONTRATADO add custo_transferencia NUMBER(15, 2); |
alter table CONTRATADO succeeded. |
Este comando permite adicionar várias colunas numa única operação:
alter table NOME_TABELA add (COLUNA1 datatype, COLUNA2 datatype); |
Na sintaxe anterior repare no uso de parentesis depois de ADD e na virgula a separar as diferentes colunas.
Alterar o nome de uma coluna existente na tabela
No comando ALTER TABLE pode ser usado para renomear uma coluna da tabela. No exemplo abaixo alteramos o nome da coluna CUSTO_TRANSFERENCIA que foi adicionada no exemplo anterior:
alter table CONTRATADO rename column CUSTO_TRANSFERENCIA to CUSTO_TRANSFERENCIA_EURO; |
alter table CONTRATADO succeeded. |
Alterar o tipo de uma coluna existente na tabela
O comando ALTER TABLE pode ser usado para modificar o tipo de dados suportado por uma coluna. No exemplo abaixo alteramos o tipo de dados da coluna CUSTO_TRANSFERENCIA_EURO, que foi criada no exemplo anterior:
alter table CONTRATADO modify CUSTO_TRANSFERENCIA_EURO number(20, 2); |
alter table CONTRATADO succeeded. |
Esta alteração requer cuidado quando a tabela já tem dados. Se aumentarmos o domínio os dados existentes são mantidos. Se mudarmos o tipo os dados existentes são removidos. Para estas situações propomos os seguintes passos:
Este comando permite alterar várias colunas numa única operação:
alter table NOME_TABELA modify (COLUNA1 datatype, COLUNA2 datatype); |
Na sintaxe anterior repare no uso de parentesis depois de MODIFY e na virgula a separar as diferentes colunas.
Remover uma coluna existente na tabela
O comando ALTER TABLE pode ser usado para remover uma coluna da tabela:
alter table CONTRATADO drop column CUSTO_TRANSFERENCIA_EURO cascade constraints; |
alter table CONTRATADO succeeded. |
Remover uma tabela
O comando DROP TABLE permite remover uma tabela. A sintaxe completa deste comando pode ser consultada no manual "SQL Reference".
A sua sintaxe mais simples é:
drop table EQUIPA; |
No nosso exemplo este comando não vai funcionar porque uma das colunas da tabela é usada como FOREIGN KEY noutra. Se o SGBD permitisse a remoção, as linhas de CONTRATADO ficariam sem a respectiva referência em equipa, o que provoca inconsistência nos dados. Será possível remover as tabelas se seguirmos uma sequência bem determinada: primeiro as tabelas com FOREIGN KEY.
drop table CONTRATADO; drop table JOGO; drop table EQUIPA; drop table JOGADOR; |
drop table CONTRATADO succeeded. drop table JOGO succeeded. drop table EQUIPA succeeded. drop table JOGADOR succeeded. |
Se as restrições FOREIGN KEY forem previamente eliminadas já é possível fazer o DROP TABLE numa sequência qualquer. A remoção da FOREIGN KEY pode ser activada recorrendo à opção CASCADE CONSTRAINTS pertencente ao comando DROP TABLE
drop table CONTRATADO cascade constraints; drop table EQUIPA cascade constraints; drop table JOGADOR cascade constraints; drop table JOGO cascade constraints; |
drop table CONTRATADO succeeded. drop table EQUIPA succeeded. drop table JOGADOR succeeded. drop table JOGO succeeded. |
Recuperar uma tabela removida
No módulo sobre transacções vimos que na base de dados Oracle os comandos DML (Data Manipulation Language) podem ser desfeitos. Isto significa que podemos fazer ROLLBACK (UNDO) de instruções INSERT, UPDATE e DELETE. No entanto, os comandos DDL (Data Definition Language) não podem ser desfeitos, o que foi referido como sendo operações que terminam uma transacção por terem um COMMIT implícito.
Para a maior parte dos comandos DDL não é grave a impossibilidade de fazer UNDO. Por exemplo não é grave para uma operação CREATE, pois o objecto criado pode facilmente ser removido com um DROP. Também não é demasiado grave para um comando DROP INDEX, pois pode ser recuperado pela reconstrução do respectivo índice, reexecutando um CREATE INDEX que produza um índice equivalente ao inicial. No entanto é muito grave para um DROP TABLE, pois os dados da tabela perdem-se.
A partir da versão 10g a base de dados Oracle possui um caixote de lixo ("recycle bin"), identificado por RECYCLEBIN, onde são colocadas as tabelas removidas, que podem depois ser recuperadas. O próximo exemplo demonstra as funcionalidades associadas ao RECYCLEBIN.
Vamos começar por criar a tabela TESTE e inserir uma linha:
/*para o caso da tabela já existir*/ drop table teste; create table teste (c number); insert into teste values (1); commit; select * from teste; |
Em seguida vamos confirmar que TESTE existe na lista de tabelas do utilizador, depois vamos removê-la e verificar que já não consta nessa lista:
select * from user_tables where table_name = 'TESTE'; drop table teste; select * from user_tables where table_name = 'TESTE'; select * from user_tables; |
Depois do comando DROP verificamos que a tabela "TESTE" já não está na lista de tabelas do utilizador, pelo que vamos procurá-la no RECYCLEBIN:
select * from recyclebin where original_name='TESTE' order by droptime desc; |
Este query consulta a vista RECYCLEBIN e devolve pelo menos uma linha onde a coluna ORIGINAL_NAME tem o valor "TESTE". Se devolver mais que uma linha significa que no passado foram criadas várias tabelas TESTE e eliminadas a seguir (por exemplo durante o módulo anterior). Vamos preocupar-nos com a primeira linha, pois é a que tem a data da coluna DROPTIME mais recente. Nessa linha a coluna OBJECT_NAME tem um valor parecido com este: "BIN$auHnbZuRRrWtoYKaEDe9Ww==$0". Não será exactamente igual pois é gerado pela base de dados de forma automática.
Uma consulta aos objectos do utilizador mostra que os objectos contidos no RECYCLEBIN aparecem na USER_OBJECTS:
Select * from user_objects; select * from user_objects where object_name in (select object_name from recyclebin where original_name='TESTE'); |
Este(s) objecto(s) é(são) do tipo tabela (TABLE) e no entanto não aparece(m) na lista das tabelas do utilizador. Se fizermos uma consulta ao objecto cujo nome foi identificado no passo anterior, verificamos que tem os conteúdos que antes pertenciam à tabela TESTE:
select * from "BIN$auHnbZuRRrWtoYKaEDe9Ww==$0"; |
Se quisermos recuperar a tabela TESTE podemos usar o comando FLASHBACK, como mostra o próximo exemplo:
flashback table teste to before drop; select * from teste; |
O comando FLASHBACK permitiu recuperar a tabela e o seu conteúdo. A opção TO BEFORE DROP é requerida na configuração usada nesta base de dados. Outras configurações da base de dados permitem opções de FLASHBACK mais sofisticadas, mas estão fora do âmbito deste curso.
Se existirem várias tabelas TESTE no RECYCLEBIN será recuperada a que foi removida em último lugar, ou seja, a que tem data DROPTIME mais recente e portanto a última a ter entrado no RECYCLEBIN.
Para remover definitivamente a tabela TESTE (sem permitir a sua recuperação) usamos o comando DROP TABLE com a opção PURGE. Neste caso a tabela não é renomeada pois é efectivamente removida:
drop table teste purge; |
Para eliminar definitivamente uma tabela que está referenciada no RECYCLEBIN usamos o comando PURGE TABLE TESTE. Se existirem várias tabelas com o nome TESTE será eliminada a que tem a data DROPTIME mais antiga, ou seja, a que tinha entrado no RECYCLEBIN há mais tempo.
Para eliminar definitivamente todos os objectos referenciados no RECYCLEBIN usamos o comando PURGE RECYCLEBIN:
purge recyclebin; |
Ver colunas de uma tabela
O comando DESCRIBE (abreviado para DESC) permite ver as colunas que constituem uma tabela, como mostra o exemplo abaixo para a tabela EQUIPA:
desc EQUIPA |
Name Null Type ------------------------------ -------- --------------------- ID_EQUIPA NOT NULL NUMBER(10) NOME NOT NULL VARCHAR2(100) 2 rows selected |
Alguns clientes Oracle não suportam o comando DESC pelo que, em alternativa, podemos consultar as vistas do dicionário de dados USER_TAB_COLUMNS ou ALL_TAB_COLUMNS. Estas dão mais inforamção que DESC, como se pode comprovar pelo exemplo abaixo:
select * from user_tab_columns where table_name = 'EQUIPA' |
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------- ------------- ------------------------------ ---------------------- ---------------------- ---------------------- -------- ---------------------- ---------------------- ------------ ---------------------- -------------------------------- -------------------------------- ---------------------- ---------------------- ---------------------- ------------------------- ---------------------- -------------------------------------------- ---------------------- ------------ ---------- ---------------------- ---------------------- --------- ------------- ------------- --------------- EQUIPA ID_EQUIPA NUMBER 22 10 0 N 1 NO NO 0 NO YES NONE EQUIPA NOME VARCHAR2 100 N 2 CHAR_CS 100 NO NO 100 B NO YES NONE 2 rows selected |
Adicionar um comentário
O comando COMMENT permite adicionar um comentário a uma tabela ou a uma coluna de uma tabela. Os comentários são úteis porque podem dar informação adicional sobre as tabelas ou colunas.
No exemplo abaixo adicionamos um comentário à tabela EMP:
comment on table EMP is 'Informacao sobre os empregados da empresa'; |
comment on succeeded. |
No exemplo abaixo adicionamos um comentário à coluna EMPNO da tabela EMP:
comment on column emp.empno is 'Guarda o numero de empregado. E'' unico para cada empregado'; |
comment on succeeded. |
Os comentários podem ser vistos consultando as seguintes vistas do dicionário de dados: USER_TAB_COMMENTS, USER_COL_COMMENTS, ALL_TAB_COMMENTS, ALL_COL_COMMENTS.
select * from USER_TAB_COMMENTS where table_name = 'EMP'; select * from USER_COL_COMMENTS where table_name = 'EMP'; |
TABLE_NAME TABLE_TYPE COMMENTS ------------------------------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMP TABLE Informacao sobre os empregados da empresa 1 rows selected TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMP EMPNO Guarda o numero de empregado. E' unico para cada empregado EMP ENAME EMP JOB EMP MGR EMP HIREDATE EMP SAL EMP COMM EMP DEPTNO 8 rows selected |
A introdução de um comentário vazio remove o comentário, como no exemplo abaixo:
comment on column emp.empno is ''; |
comment on succeeded. |
Realizado por Turtle Learning ®. Última alteração em 2012-05-01