Arquivos de Categoria: Grant

Banco de Dados II – Aula 09

Criando uma table space e usuário no Oracle 11 XE

Necessário entrar com a permissão de system, ou admin diretamente pelo SQL Plus.

Pode-se salvar o arquivo diretamente no diretório home do usuário oracle e executar o script através do SQL Plus*

create tablespace banco1 datafile '/u01/app/oracle/oradata/XE/banco1.dbf' size 10m autoextend on next 5m maxsize 50m online permanent extent management local autoallocate segment space management auto;

create user banco1 identified by banco1 default tablespace banco1 temporary tablespace temp;

grant create session, connect, resource to banco1;

alter user banco1 quota unlimited on banco1; 

connect banco1/banco1;

Até o momento, foi criado a table space chamada de banco1, um usuário banco1 com a senha banco1 e dados permissões de acesso ao usuário banco1

Após a conexão, podemos criar a as tabelas abaixo, fazendo as inserções de dados abaixo como exemplo:

CREATE TABLE DEPTO (
  CDDEPTO CHAR(02) PRIMARY KEY,
  NMDEPTO VARCHAR(30),
  RAMAL NUMBER(3)
);

CREATE TABLE CARGO (
  CDCARGO CHAR(02) PRIMARY KEY,
  NMCARGO VARCHAR(30),
  VRSALARIO NUMBER(8,2)
);

CREATE TABLE FUNCIONARIO (
  NRMATRIC NUMBER(04) PRIMARY KEY,
  NMFUNC   VARCHAR(30),
  DTADM    DATE,
  SEXO     CHAR(01),
  CDCARGO  CHAR(02),
  CDDEPTO  CHAR(02),
  CONSTRAINT FK_FUNC_CDCARGO FOREIGN KEY(CDCARGO) REFERENCES CARGO(CDCARGO),
  CONSTRAINT FK_FUNC_CDDEPTO FOREIGN KEY(CDDEPTO) REFERENCES DEPTO(CDDEPTO)
);


INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C1','COZINHEIRA',350);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C3','AUX ESCRITORIO',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C7','VIGIA',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C2','MECANICO',750);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C5','GERENTE',2300);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C4','ESCRITURARIO',600);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C8','PROGRAMADOR',NULL);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C9','ANALISTA_DE_SISTEMAS',2100);
COMMIT;


INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D1','ADMINISTRACAO',221);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D2','OFICINA',235);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D3','SERVICOS GERAIS',243);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D4','VENDAS',258);
COMMIT;


INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1001,'JOAO SAMPAIO','10-AUG-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1004,'LUCIO TORRES','02-MAR-94','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1034,'ROBERTO PEREIRA','23-MAY-92','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1021,'JOSE NOGUEIRA','10-NOV-94','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1029,'RUTH DE SOUZA','05-JAN-93','F','C1','D3');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1095,'MARIA DA SILVA','03-SEP-92','F','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1023,'LUIZ DE ALMEIDA','12-JAN-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1042,'PEDRO PINHEIRO','29-JUL-94','M','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1048,'ANA SILVEIRA','01-JUN-93','F','C5','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1015,'PAULO RODRIGUES','17-AUG-92','M','C2','D2');
COMMIT;

OBJETOS DO ORACLE

TABELA: tem a principal função armazenar dados;

VIEW: objeto que encapsula uma instrução select, podendo ter uma ou mais tabelas;

SEQUENCE: Gera uma sequencia numérica;

INDICE: Utilizado para melhorar a performance em consultas;

SINONIMO: apelido para objetos de dados;

TABELAS

É possível fazer a criação online;

Após ter sido criada, é possível modificar a sua estrutura;

Importante ter uma estimativa de tamanho para evitar que o banco de dados trave;

Nomes devem ter até 30 caracteres, começando com uma letra;

Nomes das tabelas podem ser: a-z, A-Z, 0-9, _ (underline);

Os caracteres # e $ podem também serem utilizados em nomes de tabelas, porém não recomendados pela Oracle;

É possível ter nomes iguais de objetos desde que sejam de usuários diferentes;

Não se deve utilizar palavras reservadas do Oracle (CREATE, TABLE, ALTER, USER, INSERT, etc);

Em tabelas, não há case-sensitive;

CREATE TABLE

Para se criar tabelas, é necessário que o usuário conectado tenha privilégios ;

CREATE TABLE CLIENTES (
  COD_CLI NUMBER(5),
  NOME_CLI VARCHAR2(30)
);

TIPOS DE DADOS

As colunas de uma tabela precisa ser definida com um tipo de dados, pois será tratado posteriormente pelo banco de dados;

VARCHAR2: utilizado para armazenar tipos de caracteres alfa-numéricos (aceitando letras e números), podendo ser definido de 1 a 4000 caracteres; A quantidade de bytes utilizada é da quantidade de caracteres + 1 (especifica o tamanho da string)

CHAR: permite armazenar caracteres alfa-numéricos, podendo ser definido de 1 a 2000 caracteres, com tamanho fixo (exemplos: CPF, CEP, CNPJ);

NUMBER: permite armazenar números inteiros ou decimais (precisão e escala m,n), que varia de 1 a 38. Exemplo NUMBER(10,2), pode armazenar 8 inteiros e 2 decimais;

DATE: permite armazenar data e hora que pode variar de 01/01/4712 AC até 31/12/9999 DC;

LONG: permite armazenar caracteres de até 2 GB (evite usar LONG – somente para compatibilidades passadas). Utilize LOB;

CLOB: permite armazenar caracteres de até 4 GB;

RAW(tamanho): permite armazenar dados binários brutos;

LONG RAW: armazena dados binários de tamanho variável de até 2 GB;

BLOB: Dados binários de até 4 GB;

BFILE: Armazena arquivos binários em arquivos externos ao banco de dados de até 4 GB;

ROWID: permite armazenar o endereço físico exclusivo de uma linha em uma tabela correspondente. Toda a tabela tem um ROWID, controlado pelo ORACLE. Pode-se acessar executando SELECT ROWID;

LONG: O tipo de dados LONG não pode ser copiado para outra tabela através de uma subquery; A coluna LONG não pode ser incluída no GROUP BY ou ORDER BY; É limitado a 1 coluna LONG por tabela; Não pode ser definida como CONSTRAINTS; Utilize CLOB em vez de LONG;

TIMESTAMP: É uma extensão do tipo DATE, armazenando até o segundo e fração de segundos;Pode especificar o fuso horário;
Exemplo:
TIMESTAMP WITH LOCAL TIME ZONE;
TIMESTAMP WITH TIME ZONE;

INTERVAL YEAR TO MONTH: É usado para armazenar diferença entre dois valores de datas/horários, levando-se em conta somente o mês e o ano;
Exemplo:
INTERVAL ’12-5’ YEAR(2) TO MONTH;
Grava um intervalo de doze anos e cinco meses;

INTERVAL YEAR TO MONTH
Exemplo:
INTERVAL ’231’ MONTH(2);
Grava um intervalo de 231;

INTERVAL ’543’ YEAR; Retorna erro, pois não foi definido o ano e o padrão para ano são de 2 dígitos;

INTERVAL DAY TO SECOND: armazena um período em dias, horas, minutos e segundos
Exemplo:
INTERVAL ’2 3:20:10.222’ DAY TO SECOND(3);
Grava 2 dias, 3 horas, 20 minutos, 10 segundos e 222 milhares de segundo;

INTERVAL ’7 5:12’ DAY TO MINUTE;
Grava 7 dias, 5 horas e 12 minutos;
Exemplo:
INTERVAL ’235 5 DAY(3) TO HOUR;
Grava 235 dias e 5 horas;

INTERVAL ’11:12:10.2222222’ HOUR TO SECOND(7);
Grava 11 horas, 12 minutos e 10,2222222 segundos;

EXEMPLOS DE CRIAÇÃOD DE TABELA USANDO OS TIPOS DE DADOS

CREATE TABLE TESTE_DADOS (
  CODIGO      NUMBER(10),
  NOME        VARCHAR(30),
  CEP         CHAR(8),
  NASCIMENTO DATE,
  SALARIO     NUMBER(10,2)
);

INSERT INTO TESTE_DADOS (CODIGO, NOME, CEP, NASCIMENTO, SALARIO)
VALUES
(1,'TESTE DE NOME','12548963',TO_DATE('07/15/2010','MM/DD/YYYY'),1501.45);

SELECT * FROM TESTE_DADOS;

CREATE TABLE TESTE_TABELA (
  HORA_ATUAL TIMESTAMP(7)
);

INSERT INTO TESTE_TABELA(HORA_ATUAL)
VALUES (SYSDATE);

SELECT * FROM TESTE_TABELA;

CREATE TABLE TABELA_INTERVALOS(
  INTERVALO_ANO INTERVAL YEAR(3) TO MONTH,
  INTERVALO_DIA INTERVAL DAY(3) TO SECOND
);

INSERT INTO TABELA_INTERVALOS(INTERVALO_ANO, INTERVALO_DIA)
VALUES (INTERVAL '120' MONTH(3),
        INTERVAL '180' DAY(3)
);

SELECT INTERVALO_ANO, INTERVALO_DIA
FROM TABELA_INTERVALOS;

SELECT TO_CHAR(SYSDATE+INTERVALO_ANO,'DD-MON-YYYY'),
               SYSDATE+INTERVALO_DIA AS intervalo_dia
FROM TABELA_INTERVALOS;

CONSTRAINTS

São regras que são impostas no nível de tabela (ou coluna), que no geral, impedem a inserção, atualização e deleção de dados indevidos;

As mais importantes são: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK;

Sempre que possível, nomeie as CONSTRAINS. Caso não, o ORACLE dá o nome à constraint de “SYS_…”;

Aconselhável criar as constraints logo na criação da tabela, porém podem ser criadas posteriormente;

Podem ser definidas em nível de tabela ou coluna;

CONSTRAINT NOT NULL

Serve para que uma determinada coluna não tenha valores nulos;
Por padrão, as colunas são NULL;
EXEMPLO:
CREATE TABLE NOME_TABELA(
NOME_COL TIPO_COL NOT NULL
);

CONSTRAINT UNIQUE

Serve para que uma determinada coluna (ou colunas) tenha valores únicos, podendo ter valores nulos;

As PRIMARY KEYS implicitamente são UNIQUE;

CONSTRAINT PRIMARY KEY

Serve para que uma coluna (ou colunas) tenham valores diferente entre tuplas, sendo valores únicos e não nulos;
É limitada a ser criado uma única PK por tabela, que pode ter várias colunas (todas as colunas precisam ser não nulas);
É automaticamente criado um índice, para melhor pesquisar os registros;

CONSTRAINT FOREIGN KEY

Serve para garantir a integridade dos relacionamentos entre as tabelas;
Para ser criada, é necessário ter uma PK ou UNIQUE de outra tabela;
Pode-se utilizar os comandos abaixo para se fazer exclusão de dados em cascata:
ON DELETE CASCADE;
ON DELETE SET NULL;

CONSTRAINT CHECK

Serve para fazer a checagem de um determinado dado antes da ação na tabela;
Algumas das expressões NÃO podem ser utilizadas com CHECK: SYSDATE, UID, USER e USERENV, ou a pseudocolunas como: CURRVAL, NEXTVAL, LEVEL e ROWNUM e consultas que fazem referencia a outros valores em outra linhas;

EXEMPLOS:

–nivel coluna

CREATE TABLE EMPREGADOS (
COD NUMBER(6) CONSTRAINT COD_EMP_PK PRIMARY KEY,
NOME VARCHAR2(30)
);

EXEMPLO:

–nivel tabela

CREATE TABLE EMPREGADOS (
COD NUMBER(6),
NOME VARCHAR2(30),
CONSTRAINT COD_EMP_PK PRIMARY KEY(COD)
);


CREATE TABLE TABELA_CONSTRAINTS (
ID NUMBER(10),
COD_EMP NUMBER(10),
NAO_NULO NUMBER(10) NOT NULL,
COLUNA_UNICA DATE,
COLUNA_CHECK NUMBER(10,2),
CONSTRAINT ID_PK PRIMARY KEY(ID),
CONSTRAINT UK_UNIQUE01 UNIQUE(COLUNA_UNICA),
CONSTRAINT FK_COD_EMP FOREIGN KEY(COD_EMP)
REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE,
CONSTRAINT CK_COL01 CHECK (COLUNA_CHECK > 10)
);

INSERT INTO TABELA_CONSTRAINTS VALUES (10,100, NULL, NULL,10);

INSERT INTO TABELA_CONSTRAINTS VALUES (10,100, 10, NULL,10);

INSERT INTO TABELA_CONSTRAINTS VALUES (10,100, 3,TO_DATE('15/10/2015','DD/MM/YYYY') ,11);

SELECT * FROM TABELA_CONSTRAINTS;

CONSTRAINTS DEFAULT

Define um valor padrão de uma coluna quando não for especificado nenhum valor (null implícito);
Pode ser usado funções de linhas como SYSDATE e o USER;
Não pode usar o nome de outra coluna como referencia;
O valor padrão precisa ter o mesmo tipo da coluna;

EXEMPLO DE DEFAULT

CREATE TABLE TESTE_DEFAULT (
ID NUMBER(10),
DATA DATE DEFAULT SYSDATE
);

INSERT INTO TESTE_DEFAULT (ID) VALUES (1);
INSERT INTO TESTE_DEFAULT (ID) VALUES (2);
INSERT INTO TESTE_DEFAULT (ID) VALUES (3);
INSERT INTO TESTE_DEFAULT (ID) VALUES (4);
INSERT INTO TESTE_DEFAULT (ID) VALUES (5);

SELECT * FROM TESTE_DEFAULT

CRIANDO UMA TABELA COM UMA SUBQUERY

Serve para criar rapidamente uma tabela com dados selecionado pela query;
Quando se tem expressões, é obrigatório o uso de alias;

EXEMPLO DE CREATE SELECT

CREATE TABLE TESTE_SUBSELECT AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY * 12 AS SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

DESC TESTE_SUBSELECT;

ALTER TABLE

Serve para fazer manutenção em tabelas já existentes, podendo modificar colunas, criar novas colunas, eliminar colunas, adicionar valor default às colunas;

EXEMPLO ALTER TABLE

ALTER TABLE TESTE_DEFAULT
ADD NOME_USER VARCHAR2(30);

UPDATE TESTE_DEFAULT SET NOME_USER = USER;

SELECT * FROM TESTE_DEFAULT;

ALTER TABLE TESTE_DEFAULT
MODIFY NOME_USER VARCHAR2(20) NOT NULL;

ALTER TABLE TESTE_DEFAULT
RENAME COLUMN NOME_USER TO NOME_USER2;

ALTER TABLE TESTE_DEFAULT
RENAME TO TESTE_DEFAULT2

SELECT * FROM TESTE_DEFAULT2

DROP TABLE

Serve para eliminar uma tabela com todos os seus dados e índices;
Após ser executado, transações pendentes são submetidos a um COMMIT, efetivando as transações;

EXEMPLO DE DROP TABLE

DROP TABLE TESTE_DEFAULT2

EXERCICIOS

Faça um script que contenha a criação de uma tablespace (schema), usuário para o schema criado, associação do usuário com o schema, chamando a tablespace de banco999 e o usuário de banco999, fazendo com que o usuário banco999 seja dono de outros objetos criados;

Crie os seguites objetos dentro desse schema:

ScreenHunter_519 Apr. 23 14.00

Posteriormente faça as seguintes inserções de dados:

ScreenHunter_520 Apr. 23 14.00

Video 1

 
Video 2

Anúncios
learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect