Módulo 13

/*ALTER TABLE nome_tablea "COMANDO"

ADD - ADICIONAR COLUNA
RESTRIÇÃO: NÃO PODE SER ADICIONADO COLUNA NOT NULL ONDE A TABELA JÁ POSSUI DADOS, NESSES CASOS É NECESSÁRIO CRIAR UMA COLUNA COMO NULL, CRIAR UM
SCRIPT PARA PREENCHER TODOS OS REGISTROS DA TABELA E APÓS ISSO ALTERAR AQUELA COLUNA PARA NOT NULL

MODIFY - MODIFICAR O TIPO DE DADOS DA COLUNA (NÃO CONSEGUE DEFINIR UM NOME PARA CONSTRAINT)
RESTRIÇÕES: PARA DIMINUIR A LARGURA DA COLUNA NOS SEGUINTES CASOS: SE A COLUNA TIVER APENAS VALORES NULOS,
SE A TABELA NÃO TIVER NENHUMA LINHA, OU SE TODAS AS LINHAS POSSUEM UMA LARGURA INFERIOR OU IGUAL AO NUYMERO
SENDO ALTERADO; ALTERAR O TIPO DA COLUNA SE TODOS OS VALORES FOREM NULOS OU SE FOR DE UM CHAR para VARCHAR2;
PARA OS VALORESD DEFAULT ELES SÓ SERÃO VÁLIDOS PARA OS VALORES QUE FOREM INSERIDOS POSTERIORMENTE.

DROP - EXCLUIR COLUNA
RESTRIÇÕES: A COLUNA PODE OU NÃO PODE CONTER DADOS; SÓ É POSSIVEL ELIMINAR UMA COLUNA POR VEZ; A TABELA DEVE CONTINUAR COM NO MINIMO UMA COLUNA
APÓS EXCLUIRMOS A COLUNA;

ON DELETE CASCADE: CONSEGUIMOS DELETAR O REGISTRO TANTO DA CHAVE PRIMEIRA


ELIMINANDO UMA COLUNA EM UM AMBIENTE CRÍTICO (AMBIENTE MUITO UTILIZADO)

*/

CREATE TABLE teste(
    NOME VARCHAR(20)
);

CREATE TABLE teste_numero(
    NUMERO NUMBER(10)
);

ALTER TABLE teste ADD Lucas VARCHAR(100) NOT NULL;

ALTER TABLE teste MODIFY Lucas NUMBER(10);

ALTER TABLE teste DROP COLUMN Lucas;

ALTER TABLE teste SET UNUSED COLUMN Lucas; -- DEIXAR COLUNA INATIVA

ALTER TABLE teste DROP UNUSED COLUMNS; -- COLUNAS INATIVAS


--ALTER TABLE PARA CONSTRAINTS

ALTER TABLE nome_tabela DROP CONSTRAINT nome_constraint;

SET CONSTRAINTS nome_constraint IMMEDIATE;

ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

CASCADE CONSTRAINTS; -- EXCLUIR TODAS AS CONSTRAINTS QUE FAZEM REFERENCIA A COLUNA

ALTER TABLE teste
 ADD CONSTRAINT pk_tb_teste
PRIMARY KEY (teste_numero);

ALTER TABLE teste DROP CONSTRAINT pk_tb_teste;

--SYS_C0017514442
ALTER TABLE teste MODIFY teste_numero PRIMARY KEY;

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'teste';

ALTER TABLE teste
 ADD CONSTRAINT fk01_teste
 FOREIGN KEY (teste_numero)
 REFERENCES hr.employees(employee_id)
 ON DELETE CASCADE -- CASO A EMPLOYEE_ID SEJA DELETADO DA TABELA EMPLOYEES ELE SERÁ DELETADO TAMBÉM NA TABELA TESTE
 DEFERRABLE INITIALLY DEFERRED; -- PARA VALIDAR AS CONSTRAINTS DEPOIS DE EXECUTAR OS INSERT

ALTER TABLE teste DISABLE CONSTRAINT fk01_teste;

ALTER TABLE teste ENABLE CONSTRAINT fk01_teste;

SET CONSTRAINTS ALL IMMEDIATE; --NA SEÇÃO ATUAL TODOS OS COMANDOS SERÃO VALIDADOS APÓS O COMMIT

ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

ALTER TABLE teste
 DROP COLUMN teste_numero CASCADE CONSTRAINTS; -- EXCLUIR TODAS AS CONSTRAINTS

-- FALANDO SOBRE INDICES

CREATE TABLE teste_indice(
    teste_id NUMBER(10) PRIMARY KEY
    USING INDEX
        (CREATE INDEX teste_id_idx
        ON teste_indice (teste_id)),
    nome_teste VARCHAR2(100),
    sobrenome_teste VARCHAR2(100)
);

SELECT INDEX_NAME, TABLE_NAME
   FROM USER_INDEXES
 WHERE TABLE_NAME = 'TESTE_INDICE';

CREATE TABLE teste_indice_2(
    teste_id NUMBER(10),
    nome_teste VARCHAR2(100),
    sobrenome_teste VARCHAR2(100)
);

CREATE INDEX teste_id_idx2
ON teste_indice_2 (teste_id);

ALTER TABLE teste_indice_2
ADD PRIMARY KEY (teste_id)
USING INDEX teste_id_idx2;

CREATE INDEX upper_dept_namne_idx
ON teste_indice_2(UPPER(nome_teste));

DROP INDEX upper_dept_namne_idx;



/* PURGE, FLASHBACK e TABLEAS EXTERNAS

RECYCLEBIN: LIXEIRA DO ORACLE
FLASHBACK: COMANDO PARA RECUPERAR ARQUIVOS USANDO O COMANDO DROP TABLE (A PARTIR DO ORACLE 10g)
PURGE: COMANDO PARA EXCLUIR DE VEZ OS ARQUIVOS SEM IR PARA LIXEIRA

TABELAS EXTERNAS: SÃO LIDAS PELO ORACLE, PORÉM ARMAZENADAS FORA DO BANCO DE DADOS, NESSAS TABELAS NÃO SE PODE EXECUTAR COMANDOS DML.

*/

SELECT * FROM RECYCLEBIN; -- SELECIONAR LIXEIRA

FLASHBACK TABLE teste_indice_2  TO BEFORE DROP; -- RECUPERAR TABELA

DROP TABLE teste_indice_2 PURGE; -- DELETAR A TABELA PERMANENTE

PURGE RECYCLEBIN; -- ESVAZIAR LIXEIRA

GRANT ANY DIRECTORY TO hr; -- PERMISSÃO PARA CRIAR DIRETÓRIO

CREATE OR REPLACE DIRECTORY ext_dir AS 'C:\queries'; -- CRIANDO DIRETÓRIO

SELECT * FROM ALL_DIRECTORIES; -- LISTAR DIRETÓRIOS

-- CRIANDO TABELA EXTERNA

CREATE TABLE ext_table2 (
    fname CHAR(25),
    lname CHAR(25))
ORGANIZATION EXTERNAL
        (TYPE ORACLE_LOADER
        DEFAULT DIRECTORY ext_dir
        ACESS PARAMETERS
          (RECORDS DELIMITED BY NEWLINE
           NOBADFILE
           NOLOGFILE
           FIELDS TERMINATED BY ',')
LOCATION ('emp.csv'))
PARALLEL 5
REJECT LIMIT 200;



/* Crie uma tabela chamada employees_2 que ter os dados da tabela employees, com a mesma estrutura,
porém só terá as colunas first_name, last_name, department_id e hire_date, para essa nova tabela as suas colunas
devem ter os seguintes nomes: nome, sobrenome, departamento e admissao.*/

CREATE TABLE employees_2
AS
    (SELECT first_name Nome,
            last_name Sobrenome,
            department_id Departamento,
            hire_date Admissão FROM hr.employees);

/* Altere a tabela employees_2 adicione a coluna comissao, que deve ser um número de 10,
esta coluna não pode receber valores nulos e deve ter uma constraint que garanta que ela não vai receber valores menores que 0.
Essa constraint deve ter o nome ck_comissao*/

ALTER TABLE employees_2
 ADD Comissao NUMBER(8,2) DEFAULT 10 NOT NULL;

 ALTER TABLE employees_2
 ADD CONSTRAINT ck_comissao CHECK (Comissao >= 0);

/* Elimine a tabela employees_2, depois restaure a tabela. */

DROP TABLE employees_2;

FLASHBACK TABLE employees_2 TO BEFORE DROP;

/* Adicione uma referencia de chave estrangeira, tabela employees_2 no campo departamento com a tabela departments. */

ALTER TABLE employees_2
 ADD CONSTRAINT fk_depto
 FOREIGN KEY (Departamento)
 REFERENCES hr.departments(department_id);

/* Crie um índice no campo admissao tabela employees_2 */

CREATE INDEX idx_admissao
ON employees_2 (Admissão);

/* Elimine a tabela employees_2 de uma forma que ela não poderá ser restaurada. */

DROP TABLE employees_2 PURGE;