Usando Declarações DDL

Usando Declarações DDL para Criar e Gerenciar Tabelas

Um esquema é a coleção de múltiplos objetos de banco de dados, que são conhecidos como objetos de esquema.

• Tabela – para armazenar dados

• Ver – para projetar dados em um formato desejado a partir de uma ou mais tabelas

• Sequência – para gerar valores numéricos

• Index – para melhorar o desempenho das consultas nas tabelas

• Synonym – nome alternativo de um objeto

Um dos primeiros passos na criação de uma base de dados é criar as tabelas que irão armazenar os dados de uma organização.O projeto da base de dados envolve a identificação dos requisitos do usuário do sistema para vários sistemas organizacionais, como entrada de pedidos, gerenciamento de estoque e contas a receber. Independentemente do tamanho e complexidade do banco de dados, cada banco de dados é composto de tabelas.

Criar a tabela

Para criar uma tabela no banco de dados, um DBA deve ter certas informações em mãos – o nome da tabela, nome da coluna, tipos de dados da coluna e tamanhos das colunas. Todas essas informações podem ser modificadas posteriormente usando os comandos DDL.

Convenções de nomenclatura de tabelas –

• O nome que escolher para uma tabela deve seguir estas regras padrão:

  >

• O nome deve começar com uma letra A-Z ou a-z

• Pode conter números e sublinhados

• Pode estar em UPPER de minúsculas

• Pode ter até 30 caracteres de comprimento

• Não pode usar o mesmo nome de outro objecto existente no seu esquema

• Não pode ser uma palavra reservada SQL

Seguindo as directrizes acima, EMP85′ pode ser um nome de tabela válido.Mas 85EMP não é. Da mesma forma, UPDATE não pode ser um nome de tabela escolhido, pois é uma palavra-chave SQL reservada.

O comando CREATE TABLE

A CREATE TABLE é uma instrução DDL que é usada para criar tabelas no banco de dados. A tabela é criada assim que o script CREATE TABLE é executado e está pronta para manter os dados em diante.Mas para criar uma tabela em qualquer esquema do usuário, o usuário deve ter CREATE ANY TABLE schema.

Aqui está a sintaxe de uma instrução básica CREATE TABLE. Pode haver muitas cláusulas adicionais para fornecer explicitamente as especificações de armazenamento ou valores de segmento.

CREATE TABLE table ( { column datatype ... | table_constraint} ... | table_constraint} ]...) 

Na sintaxe acima, DEFAULT especifica o valor padrão que pode ser usado durante a instrução INSERT se a coluna for ignorada. Ele não pode conter referências a outras colunas da tabela ou pseudo-colunas (CURRVAL, NEXTVAL, LEVEL e ROWNUM) exceto SYSDATE e USER, ou constantes de data que não estejam totalmente especificadas.

Constraints são as regras definidas opcionalmente no nível da coluna ou da tabela (abordadas mais adiante neste capítulo). Essas regras são verificadas durante qualquer ação de dados (Insert, update) na tabela e levantam o erro para abortar a ação após sua violação.

Por exemplo, a instrução CREATE TABLE abaixo cria uma tabela EMP_TEST. Observe as especificações da coluna, o tipo de dados e a precisão.

CREATE TABLE SCOTT.EMP_TEST(EMPID NUMBER,ENAME VARCHAR2(100),DEPARTMENT_ID NUMBER,SALARY NUMBER,JOB_ID VARCHAR2(3),HIREDATE DATE,COMM NUMBER);

Um usuário pode consultar as tabelas do esquema de outro usuário prefixando o nome do usuário ou esquema com o nome da tabela. Por exemplo, um GUEST do usuário deseja consultar o nome do funcionário e o salário da tabela EMP_TEST que é propriedade do SCOTT. Ele pode emitir a consulta abaixo –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Uma coluna pode conter um valor padrão durante o tempo de criação da tabela, o que ajuda a restringir a entrada dos valores NULL na coluna. O valor padrão pode ser deduzido de uma função literal, expressão ou SQL que deve retornar um tipo de dado compatível para a coluna. Na instrução CREATE TABLE abaixo, note que a coluna LOCATION_ID tem o valor padrão 100.

CREATE TABLE SCOTT.DEPARTMENT(DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – Create table using subquery

A table can be created from an existing table in the database using a subquery option.it copia a estrutura da tabela, bem como os dados da tabela. Os dados também podem ser copiados com base nas condições. As definições do tipo de dados da coluna incluindo as restrições explicitamente impostas NOT NULL são copiadas para a nova tabela.

O script CTAS abaixo cria uma nova tabela EMP_BACKUP. Os dados dos funcionários do departamento 20 são copiados na nova tabela.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Tipos de dados

Tipos de dados são usados para especificar o comportamento básico de uma coluna na tabela.

Número tipo de dados

O tipo de dados NÚMERO abrange tanto o número inteiro,ponto fixo,como valores numéricos de ponto flutuante. As primeiras versões do Oracle definiram diferentes tipos de dados para cada um destes diferentes tipos de números,mas agora o tipo de dados NÚMERO serve a todos estes propósitos.Ocasionalmente, o tipo de dados NUMERO é usado para armazenar números de identificação onde esses números são gerados pelo SGBD como números seqüenciais.

NUMBER (p, s), onde p é a precisão até 38 dígitos e s é a escala (número de dígitos à direita do ponto decimal). A escala pode variar entre -84 a 127,

NUMBER (p), é um número de ponto fixo com uma escala de zero e uma precisão de p.

FLOAT, onde p é a precisão binária que pode variar de 1 a 126. Se p não for especificado o valor padrão é binário 126,

Data tipo de dados

Para cada tipo de dados DATE, Century, Year, Month, Day, Hour, Minute, Second são armazenados na base de dados. Cada sistema de banco de dados tem um formato de data padrão definido pelo parâmetro de inicialização NLS_DATE_FORMAT. Este parâmetro é normalmente definido como DD-MON-YYY.Se você não especificar uma hora, a hora padrão é 12:00:00 a.m.

Dados de caracteres

Oracle suporta três tipos de dados de caracteres predefinidos, incluindo CHAR, VARCHAR, VARCHAR2, e LONG.VARCHAR e VARCHAR2 são realmente sinônimos, e a Oracle recomenda o uso de VARCHAR2 em vez de VARCHAR.Por exemplo, um número de Segurança Social (SSN) nos Estados Unidos é atribuído a cada cidadão e tem sempre 9 caracteres de tamanho (mesmo que um SSN seja composto estritamente de dígitos, os dígitos são tratados como caracteres), e seria especificado como CHAR(9). Use o datatype VARCHAR2 para armazenar dados alfanuméricos de comprimento variável. Por exemplo, o nome ou endereço de um cliente varia consideravelmente em termos do número de caracteres a serem armazenados. O tamanho máximo de uma coluna VARCHAR2 é de 4.000 caracteres.

Constraints

Constraints são o conjunto de regras definidas nas tabelas Oracle para assegurar a integridade dos dados. Estas regras são aplicadas para cada coluna ou conjunto de colunas. Sempre que a tabela participa numa acção de dados, estas regras são validadas e levantam excepções em caso de violação. Os tipos de restrição disponíveis NÃO são NULL, Primary Key, Unique, Check, e Foreign Key.

A sintaxe abaixo pode ser usada para impor restrição no nível da coluna.

Sintaxe:

column constraint_type

Todas as restrições exceto NOT NULL, também podem ser definidas no nível da tabela. Restrições compostas só podem ser especificadas no nível da tabela.

NOT NULL Constraint

A restrição NOT NULL significa que uma linha de dados deve ter um valor para a coluna especificada como NOT NULL.Se uma coluna for especificada como NOT NULL,o Oracle RDBMS não permitirá que linhas sejam gravadas na tabela de empregados que violam essa restrição.

Syntax:

COLUMN 

Criação UNIQUE

Por vezes é necessário impor a unicidade para um valor de coluna que não seja uma coluna chave primária. A restrição UNIQUE pode ser usada para impor essa regra e o Oracle rejeitará qualquer linha que viole a restrição única.

Sintax:

Nível da coluna:

COLUMN 

Nível da tabela: CONSTRAINT UNIQUE (nome da coluna)

Nota: Oracle cria internamente um índice único para evitar duplicação nos valores da coluna. Os índices seriam discutidos mais tarde em PL/SQL.

CREATE TABLE TEST( ... , NAME VARCHAR2(20) CONSTRAINT TEST_NAME_UK UNIQUE, ... );

No caso de chave única composta, ela deve ser definida no nível da tabela como abaixo.

CREATE TABLE TEST( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

Chave primária

Cada tabela deve normalmente conter uma coluna ou conjunto de colunas que identifique exclusivamente linhas de dados que estão armazenados na tabela. Esta coluna ou conjunto de colunas é referida como a chave primária.

Pontos a serem observados –

• Uma tabela pode ter apenas uma chave primária.

• Polunas múltiplas podem ser agrupadas sob uma chave primária composta.

• Oracle cria internamente um índice único para evitar duplicação nos valores das colunas.Os índices seriam discutidos mais tarde em PL/SQL.

Sintax:

Nível de coluna:

COLUMN 

Nível de tabela:

CONSTRAINT PRIMARY KEY 

O exemplo seguinte mostra como usar a restrição PRIMARY KEY a nível de coluna.

CREATE TABLE TEST( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... ); 

O exemplo seguinte mostra como definir chave primária composta usando a restrição PRIMARY KEY no nível da tabela.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) ); 

Foreign Key

Quando duas tabelas compartilham a relação pai-filho com base em uma coluna específica, a coluna de junção na tabela filha é conhecida como Foreign Key.Esta propriedade da coluna correspondente na tabela pai é conhecida como integridade referencial. Os valores da coluna Foreign Key na tabela filha podem ser nulos ou devem ser os valores existentes da tabela pai.

Syntax:

Column Level:

COLUMN 

Table level:

CONSTRAINT 

O exemplo seguinte mostra como usar a restrição FOREIGN KEY no nível da coluna.

Oracle permite que uma única coluna tenha mais de uma restrição CHECK. Na verdade, não há limite prático para o número de restrições CHECK que podem ser definidas para uma coluna.

Sintaxe:

Nível de coluna:

COLUMN CONSTRAINT 

Nível de tabela:

CONSTRAINT CHECK (condition)

O exemplo a seguir mostra como usar a restrição CHECK no nível da coluna.

CREATE TABLE TEST( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in ('A','B','C')), ...);

O exemplo a seguir mostra como usar a restrição CHECK no nível da tabela.

>

CREATE TABLE TEST( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate),);

A expressão TABELA ALTERA

A DBA pode fazer modificações na estrutura da tabela ou nas definições de coluna após a tabela ter sido criada no banco de dados.O comando DDL ALTER TABLE é usado para executar tais ações. O comando Alter fornece múltiplos utilitários exclusivos para objetos de esquema. A instrução ALTER TABLE é usada para adicionar, soltar, renomear e modificar uma coluna em uma tabela.

A instrução ALTER TABLE abaixo renomeia a tabela EMP para EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

O comando abaixo de ALTER TABLE adiciona uma nova coluna TESTCOL à tabela EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

O comando abaixo de ALTER TABLE renomeia a coluna TESTCOL para TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

O comando abaixo de ALTER TABLE renomeia a coluna TESTNEW da tabela EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

O comando abaixo de ALTER TABLE adiciona a chave primária na coluna EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

O comando abaixo de ALTER TABLE TABLE adiciona a chave primária.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

O comando abaixo de ALTER TABLE TABLE muda o modo de tabela para somente leitura.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Read only tables came as an enhancement in Oracle 11g.It allows the tables to be used for read only purpose. Em versões anteriores do oráculo, as tabelas eram lidas apenas concedendo privilégio de leitura SELECT aos outros usuários, mas o dono ainda tinha o privilégio de escrita de leitura. Mas agora, se uma tabela é definida como Read only, mesmo o dono não tem acesso sobre manipulação de dados.

Sintax:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Illustração

SQL>CREATE TABLE ORATEST (id NUMBER)SQL>INSERT INTO ORATEST VALUES (1);SQL>ALTER TABLE ORATEST READ ONLY;SQL> INSERT INTO ORATEST VALUES (2);INSERT INTO ORATEST VALUES (2) *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> UPDATE ORATEST SET id = 2;UPDATE ORATEST SET id = 2 *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> DELETE FROM ORATEST;DELETE FROM ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> TRUNCATE TABLE ORATEST;TRUNCATE TABLE ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));ALTER TABLE ORATEST ADD (description VARCHAR2 (50))*ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST READ WRITE;Table altered.SQL> DELETE FROM ORATEST;1 row deleted.

Declaração da TABELA DROP

A declaração da TABELA DROP é usada para remover uma tabela do banco de dados. A tabela que caiu e seus dados não estão mais disponíveis para seleção. A tabela que caiu pode ser recuperada usando o utilitário FLASHBACK, se disponível em recyclebin.Dropping a table deixa cair o índice e aciona associados a ela.

Sintax:

DROP TABLE 

A declaração abaixo irá soltar a tabela e colocá-la na lixeira.

DROP TABLE emp_new;

A declaração abaixo irá soltar a tabela e também retirá-la da lixeira.

DROP TABLE emp_new PURGE;