Usando declarações DDL

Usando instruções DDL para criar e gerenciar tabelas

Um esquema é a coleção de vários objetos de banco de dados, que são conhecidos como objetos de esquema. Esses objetos têm acesso direto por seu esquema proprietário. A tabela abaixo lista os objetos de esquema.

Tabela - para armazenar dados
Visualização - para projetar dados em um formato desejado de uma ou mais tabelas
Sequência - para gerar valores numéricos
Índice - para melhorar o desempenho das consultas nas tabelas
Sinônimo - nome alternativo de um objeto

Uma das primeiras etapas na criação de um banco de dados é criar as tabelas que armazenarão os dados de uma organização. O design do banco 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 da complexidade do banco de dados, cada banco de dados é composto de tabelas.

Criando a mesa

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

Convenções de nomenclatura de tabela -

O nome que você escolher para uma mesa deve seguir estas regras padrão:
O nome deve começar com uma letra AZ ou az
Pode conter números e sublinhados
Pode ser em MAIÚSCULAS ou minúsculas
Pode ter até 30 caracteres de comprimento
Não é possível usar o mesmo nome de outro objeto existente em seu esquema
Não deve ser uma palavra reservada SQL

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

Instrução CREATE TABLE

O 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 armazenar os dados adiante. O usuário deve ter o privilégio de sistema CREATE TABLE para criar a tabela em seu próprio esquema. Mas para criar uma tabela no esquema de qualquer usuário, o usuário deve ter o esquema CREATE ANY TABLE.

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

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

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 pseudocolunas (CURRVAL, NEXTVAL, LEVEL e ROWNUM), exceto SYSDATE e USER, ou constantes de data que não são totalmente especificadas.

As restrições são as regras definidas opcionalmente no nível da coluna ou no nível da tabela (abordadas posteriormente neste capítulo). Essas regras são verificadas durante qualquer ação de dados (inserir, atualizar) na tabela e geram 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, tipo de dados e 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 de usuário ou esquema com o nome da tabela. Por exemplo, um usuário GUEST deseja consultar o nome do funcionário e o salário da tabela EMP_TEST que pertence a 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. Isso ajuda a restringir os valores NULL que entram na coluna. O valor padrão pode ser deduzido de um literal, expressão ou função SQL que deve retornar um tipo de dados compatível para a coluna. Na instrução CREATE TABLE abaixo, observe que a coluna LOCATION_ID possui o valor padrão 100.

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

CTAS - Criar tabela usando subconsulta

Uma tabela pode ser criada a partir de uma tabela existente no banco de dados usando uma opção de subconsulta. Ela 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 de tipo de dados da coluna, incluindo as restrições NOT NULL explicitamente impostas, são copiadas para a nova tabela.

O script CTAS abaixo cria uma nova tabela EMP_BACKUP. Os dados do funcionário do departamento 20 são copiados para a nova tabela.

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

Tipos de dados

Os tipos de dados são usados para especificar o comportamento básico de uma coluna na tabela. Em uma base mais ampla, o comportamento da coluna pode pertencer a um número, caractere ou uma família de datas. Existem vários outros subtipos que pertencem a essas famílias.

Tipo de dados numéricos

O tipo de dados NUMBER abrange valores numéricos inteiros, de ponto fixo e de ponto flutuante. As primeiras versões do Oracle definiam diferentes tipos de dados para cada um desses diferentes tipos de números, mas agora o tipo de dados NUMBER atende a todos esses propósitos. Escolha o tipo de dados NUMBER quando uma coluna deve armazenar dados numéricos que podem ser usados em cálculos matemáticos. Ocasionalmente, o tipo de dados NUMBER é usado para armazenar números de identificação onde esses números são gerados pelo DBMS como números sequenciais.

NÚMERO (p, s), onde p é a precisão de até 38 dígitos es é a escala (número de dígitos à direita da vírgula decimal). A escala pode variar entre -84 a 127.

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

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

Tipo de dados de data

Para cada tipo de dados DATE, Século, Ano, Mês, Dia, Hora, Minuto, Segundo são armazenados no banco de dados. Cada sistema de banco de dados possui um formato de data padrão que é definido pelo parâmetro de inicialização NLS_DATE_FORMAT. Este parâmetro geralmente é definido como DD-MON-YY. Se você não especificar um horário, o horário padrão é 12h00

Tipo de dados de personagem

A Oracle oferece suporte a três tipos de dados de caracteres predefinidos, incluindo CHAR, VARCHAR, VARCHAR2 e LONG.VARCHAR e VARCHAR2, na verdade, são sinônimos e a Oracle recomenda o uso de VARCHAR2 em vez de VARCHAR. Use o tipo de dados CHAR quando a coluna armazenará valores de caracteres de comprimento fixo. Por exemplo, um número de previdência social (SSN) nos Estados Unidos é atribuído a cada cidadão e sempre tem 9 caracteres (embora um SSN seja estritamente composto de dígitos, os dígitos são tratados como caracteres) e seriam especificados como CHAR (9). Use o tipo de dados VARCHAR2 para armazenar dados alfanuméricos de comprimento variável. Por exemplo, o nome ou endereço de um cliente variará consideravelmente em termos do número de caracteres a serem armazenados. O tamanho máximo de uma coluna VARCHAR2 é de 4.000 caracteres.

Tipo de dados LOB

A Oracle oferece vários tipos de dados LOB diferentes, incluindo CLOB (objeto grande de caracteres) e BLOB (objeto grande binário). As colunas desses tipos de dados podem armazenar dados não estruturados, incluindo texto, imagem, vídeo e dados espaciais. O tipo de dados CLOB pode armazenar até oito terabytes de dados de caracteres usando o conjunto de caracteres do banco de dados CHAR. O tipo de dados BLOB é usado para armazenar grandes objetos binários não estruturados, como aqueles associados a dados de imagem e vídeo, onde os dados são simplesmente um fluxo de valores de "bits". Um tipo de dados BLOB pode armazenar até Oito terabytes de dados binários. O tipo de dados NCLOB pode armazenar objetos grandes de caracteres em caracteres nacionais multibyte configurados de 8 TB a 128 TB. O valor do tipo de dados BFILE funciona como um localizador de arquivo ou ponteiro para arquivo no sistema de arquivos do servidor. O tamanho máximo de arquivo suportado é de 8 TB a 128 TB.

Restrições

Restrições são o conjunto de regras definidas nas tabelas Oracle para garantir a integridade dos dados. Essas regras são aplicadas para cada coluna ou conjunto de colunas. Sempre que a tabela participa da ação de dados, essas regras são validadas e geram exceção em caso de violação. Os tipos de restrição disponíveis são NOT NULL, Chave primária, Única, Cheque e Chave estrangeira.

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

Sintaxe:

column [data type] [CONSTRAINT constraint_name] constraint_type

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

Restrição NOT NULL

Uma 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 armazenadas na tabela de funcionários que violem essa restrição. só pode ser definido no nível da coluna, e não no nível da tabela.

Sintaxe:

COLUMN [data type] [NOT NULL]

Restrição única

Às vezes, é necessário impor exclusividade para um valor de coluna que não seja uma coluna de chave primária. A restrição UNIQUE pode ser usada para impor essa regra e o Oracle rejeitará todas as linhas que violem a restrição exclusiva. A restrição exclusiva garante que os valores da coluna sejam distintos , sem duplicatas.

Sintaxe:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [nome da restrição] UNIQUE (nome da coluna)

Observação: o Oracle cria internamente um índice exclusivo para evitar a duplicação nos valores da coluna. Os índices seriam discutidos posteriormente 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 conforme abaixo.

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

Chave primária

Cada tabela deve conter normalmente uma coluna ou conjunto de colunas que identificam exclusivamente as linhas de dados armazenados na tabela. Esta coluna ou conjunto de colunas é referido como a chave primária. A maioria das tabelas tem uma única coluna como a chave primária. colunas-chave são restritas a NULLs e valores duplicados.

Pontos a serem observados -

Uma tabela pode ter apenas uma chave primária.
Várias colunas podem ser batidas em uma chave primária composta.
O Oracle cria internamente um índice exclusivo para evitar a duplicação nos valores da coluna. Os índices seriam discutidos posteriormente em PL / SQL.

Sintaxe:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

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

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

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

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

Chave Estrangeira

Quando duas tabelas compartilham o relacionamento pai-filho com base em uma coluna específica, a coluna de junção na tabela-filho é conhecida como Chave Estrangeira. Esta propriedade da coluna correspondente na tabela-pai é conhecida como Integridade referencial. podem ser nulos ou devem ser os valores existentes da tabela pai. Observe que apenas as colunas de chave primária da tabela referenciada são elegíveis para impor integridade referencial.

Se uma chave estrangeira é definida na coluna da tabela filho, o Oracle não permite que a linha pai seja excluída, se ela contiver qualquer linha filho. No entanto, se a opção ON DELETE CASCADE for fornecida no momento da definição da chave estrangeira, o Oracle exclui todas as linhas filho enquanto a linha pai está sendo excluída. Similarmente, ON DELETE SET NULL indica que quando uma linha na tabela pai é excluída, os valores da chave estrangeira são definidos como nulos.

Sintaxe:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

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

CREATE TABLE TEST
(ccode varchar2(5) 
     CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode),
   ...
);

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST
(ccode varchar2(5) 
   CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
   ON DELETE CASCADE,
   ...
);

Verificar restrição

Às vezes, os valores de dados armazenados em uma coluna específica devem estar dentro de algum intervalo de valores aceitável. Uma restrição CHECK requer que a condição de verificação especificada seja verdadeira ou desconhecida para cada linha armazenada na tabela. A restrição de verificação permite impor uma regra condicional em um coluna, que deve ser validada antes que os dados sejam inseridos na coluna. A condição não deve conter uma subconsulta ou pseudocoluna CURRVAL NEXTVAL, LEVEL, ROWNUM ou SYSDATE.

O 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:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] 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),
);

Instrução ALTER TABLE

Um DBA pode fazer alterações na estrutura da tabela ou nas definições da 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 vários utilitários exclusivos para objetos de esquema. A instrução ALTER TABLE é usada para adicionar, eliminar, renomear e modificar uma coluna em uma tabela.

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

ALTER TABLE EMP RENAME TO EMP_NEW;

A instrução ALTER TABLE abaixo adiciona uma nova coluna TESTCOL à tabela EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

A instrução ALTER TABLE abaixo renomeia a coluna TESTCOL para TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

A instrução ALTER TABLE abaixo remove a coluna TESTNEW da tabela EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

A instrução ALTER TABLE abaixo adiciona a chave primária à coluna EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

A instrução ALTER TABLE abaixo elimina a chave primária.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

A instrução ALTER TABLE a seguir muda o modo da tabela para somente leitura.

ALTER TABLE EMP_NEW READ ONLY;

Tabelas só de leitura

As tabelas somente leitura são um aprimoramento do Oracle 11g. Permite que as tabelas sejam usadas somente para leitura. Em versões anteriores do oracle, as tabelas eram feitas somente para leitura concedendo o privilégio SELECT aos outros usuários, mas o proprietário ainda tinha o privilégio de leitura e gravação. Mas agora, se uma tabela for definida como Somente leitura, mesmo o proprietário não tem acesso à manipulação de dados .

Sintaxe:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

Ilustraçã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.

Instrução DROP TABLE

A instrução DROP TABLE é usada para remover uma tabela do banco de dados. A tabela eliminada e seus dados não permanecem mais disponíveis para seleção. A tabela eliminada pode ser recuperada usando o utilitário FLASHBACK, se disponível em recyclebin. Eliminar uma tabela elimina o índice e os gatilhos associados a ela.

Sintaxe:

DROP TABLE [TABLE NAME] [PURGE]

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

DROP TABLE emp_new;

A instrução abaixo irá remover a mesa e também jogá-la na lixeira.

DROP TABLE emp_new PURGE;

↰ Previous page Next page ↱