Banco de Dados - ORACLE AULA PRร TICA
Professora Ana Clรกudia
1
Comandos de Arquivo do SQL*Plus •
• • • • • •
•
SAV[E] nome do aqruivo [.ext] [REP[LACE] APP[END]– grava o conteúdo do buffer para um arquivo. Use APPEND para adicionar a um arquivo existente; use REPLACE para sobrescrever um arquivo existente. A extensão padrão é .sql. GET nome do arquivo [.ext] – escreve o conteúdo do arquivo para o buffer. A extensão padrão do arquivo é .sql. STA[RT] nome do aruivo [.ext] – executa um arquivo anteriormente gravado. @nome do aqruivo - executa um arquivo anteriormente gravado (o mesmo que o comando START). ED[IT] – carrega o editor e grava o conteúdo do buffer para um arquivo de nome afiedt.buf. ED[IT] nome do arquivo [.ext] – carrega o editor para editar o conteúdo de um arquivo já gravado. SPO[OL] nome do arquivo [.ext]|OFF|OUT – armazena o resultado da consulta em um arquivo. OFF fecha o arquivo de spool. OUT fecha o arquivo de spool e enviar o resultado do arquivo para impressora. 2 EXIT – sai do SQL*Plus.
DDL – Criação de Tabelas • Tipos de Dados: – VARCHAR(tam) – string de comprimento variável. – CHAR(tam) – string de comprimento fixo. – NUMBER(p,s) – dado do tipo numérico – DATE – dado do tipo data. – LONG – string de tamanho variável de até dois gigabytes – CLOB - string de tamanho variável de até quatro gigabytes. • Convenções de Nomes: – Devem começar com uma letra. – Pode ter um tamanho de até 30 caracteres. – Deve conter somente de A..Z, a..z, 0..9, _, $ e #. – Não deve duplicar o nome de objeto por outro usuário. – Não pode ser uma palavra reservado do Oracle. 3
DDL – Create Table • Criar a tabela dept. CREATTE TABLE dept (deptno NUMBER(2), Dname VARCHAR2(14), Loc VARCHAR(13));
• Confirmar a criação da tabela. DESCRIBE dept
• Criar uma tabela usando uma subquery. CREATE TABLE depto30 AS SELECT empno, ename, sal*12 “salario annual”, hiredate FROM emp WHERE deptno=30;
4
DDL – Alter Table •
•
•
Use o comando ALTER TABLE para: – Adicionar uma nova coluna – Modificar uma coluna existente • Alterar o tipo, o tamanho e o valor default. Adicionar uma Coluna. ALTER TABLE depto30 ADD (job VARCHAR2(9)); – Uma coluna adicionada a uma tabela que contém linhas será preenchida com valores nulos. Modificar uma Coluna. – Alterar o tamanho. ALTER TABLE depto30 MODIFY (ename VARCHAR2(15)); – Alterar o valor default. ALTER TABLE depto30 MODIFY ("salario anual" default 1000);
5
DDL – Truncate Table / Drop Table •
Comando Truncate table. – Libera o espaço de armazenamento usado pela tabela. – Remove todas as linhas da tabela. – Não pode desfazer a remoção através do comando de rollback – O Comando DELETE remove todas as linhas, no entanto, não libera espaço de armazenamento. TRUNCATE TABLE depto30;
•
Comando Drop Table. – Todos os dados e a estrutura da tabela são excluídos. – Todos os índices são eliminados. – Visões e sinônimos tornam-se inválidos. – Não é possivel fazer rollback nesse comando. DROP TABLE depto30;
6
EMPREGADO Nome
Matrícula
DataNasc
Salario
MatSupervisor
Depto
DEPARTAMENTO DeptoNome
MatGerente
PROJETO
DEPTO_LOCAL Depto
DeptoNum
PNome
PCódigo
PLocalização
PDepto
Localização
TRABALHA_EM Matrícula
PCódigo
Horas
DEPENDENTE Matrícula
DependenteNome
Sexo
DataNasc
Parentesco
7
Prática de Laboratório (Tabelas) 1. Usando o esquema do banco de dados de empregado apresentado, empregar os comandos CREATE TABLE para criação das tabelas de empregado e dependente. 2. Adicionar as colunas endereco e sexo à tabela empregado. 3. Alterar o tipo da coluna nome na tabela empregado para VARCHAR2(30); 4. Utilizar o comando TRUNCATE TABLE para eliminar a tabela dependente. Obter a estrutura da tabela dependente através do comnado DESCRIBE (ou abreviadamente DESC). 5. Utilizar o comando DROP TABLE para eliminar a tabela dependente. Tentar obter a estrutura da tabela dependente através do comando DESCRIBE. Verificar o ocorrido. 6. Recriar a tabela dependente. • Utilizar as seguintes definições de tipos para as variáveis: – sexo: char(1); matricula: Number(3); dataNasc: date; nome e dependenteNome: varchar(20), depto: char(3).
8
Definição de Restrições •
•
•
O que são restrições? – Reforçam regras a nível de tabela. – Evitam a exclusão de uma tabela se há dependentes. – Os seguintes tipos de restrição são válidos no Oracle: • NOT NULL – assegura que valores NULL não sejam permitidos para coluna. • UNIQUE – define a coluna ou colunas cujos valores devem ser únicos para todas as linhas em uma tabela. • PRIMARY KEY – identifica univocamente cada linha da tabela. • FOREIGN KEY – estabelece o relacionamento de chave estrangeira entre a coluna e uma coluna da tabela referenciada. • CHECK – especifica uma condição que deve ser verdadeira. Criar uma restrição. – No momento que a tabela é criada. – Após a criação da tabela. 9 Definir a restrição a nível de coluna ou tabela.
Restrição de Chave Primária •
A Restrição de Chave Primária: – Cria a chave primária para a tabela. – Somente uma chave primária pode ser criada para cada tabela. – A chave primária de uma tabela pode ser uma coluna ou uma combinação de colunas que identificam de forma únivoca as linhas da tabela. – Essa restrição assegura que colunas que são parte da chave primária não possam conter valores Nulls. – Chave primária composta é criada usando a definição a nível de tabela. – Chave primária com uma única coluna pode ser definida a nível de tabela ou coluna. – Um índice único é criado automaticamente para uma chave primária. Definição a nível de tabela CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10), … deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (empno));
10
Restrição de NOT NULL • A restrição NOT NULL só pode ser especificada a nível de coluna. • Colunas sem a restrição de NOT NULL pode conter valores Null por default. CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10) NOT NULL, … deptno NUMBER(7,2) NOT NULL); 11
Restrição de Chave Única • Requer que cada valor na coluna ou conjunto de colunas sejam únicos. • Permite a entrada de valores Nulls, a não ser que se tenha definido também a restrição de NOT NULL. • Definida a nível de tabela ou de linha. • Chave única composta é criada a nível de tabela. Definição a nível de • O Oracle Server cria implicitamente um índice único sobre a chave tabela única da tabela. CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT dept_dname_uk UNIQUE(dname));
12
Restrição de Chave Estrangeira •
• • •
A restrição de chave estrangeira ou de integridade referencial define uma coluna ou combinação de colunas como sendo a chave estrangeira e estabelece um relacionamento com uma chave primária na mesma tabela ou em uma tabela diferente. A chave estrangeira deve apresentar um valor existente na tabela pai ou ser Null. Pode ser definida a nível de tabela ou coluna. Chaves estrangeiras compostas são criadas usando-se a definição a nível de tabela. CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10) NOT NULL, job VARCHAR2(9); mgr NUMBER(4), Define a coluna na tabela hiredate DATE, filho sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE);
Define a coluna na tabela pai
Permite a exclusão na tabela pai e a exclusão das linhas dependentes na tabela filho
13
Restrição de Check • Define uma condição que cada linha deve satisfazer. • Não são permitidas expressões que se referem a outros valores em outras linhas. • Pode ser definida a nível de coluna ou tabela. Definição a nível de coluna
CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10) NOT NULL, job VARCHAR2(9) mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2) CHECK (sal BETWEEN 1000 AND 5000)); 14
Adição de Restrições • É possível adicionar uma restrição a uma tabela existente através do uso da declaração ALTER TABLE com a cláusula ADD. • Restrições de NOT NULL são adicionadas através do uso da cláusula MODIFY. ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp(empno); Adiciona uma chave estrangeira a tabela EMP indicando que o gerente representado pela coluna MGR deve existir como enpregado válido na tabela EMP.
15
Exclusão de Restrição •
Sintaxe: ALTER TABLE tabela DROP PRIMARY KEY | UNIQUE (coluna) | CONSTRAINT restrição [CASCADE];
•
Exemplo: – Remover a chave estrangeira definida sobre a coluna mgr (gerente) na tabela EMP. ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk; – Remover a chave primária da tabela DEPT e eliminar a chave estrangeira definida sobre a coluna deptno da tabela EMP. ALTER TABLE dept DROP PRIMARY KEY CASCADE;
•
É possível identificar o nome da restrição a partir da visão do dicionário USER_CONSTRAINTS e USER_CONS_COLUMNS. 16
Prática de Laboratório (Restrição) • 2. 3. 4. 5.
6.
Usar o esquema do banco de dados de empregado fornecido para resolver as seguintes questões: Adicionar as restrições de chave primária e integridade referencial as tabelas EMPREGADO e DEPENDENTE. Adicionar a restrição à tabela de EMPREGADO de que o salario de empregado deve ser maior do que 1000. Adicionar a restrição de que o departamento no qual o empregado trabalho não deve assumir valores Nulls. Usar a declaração de CREATE TABLE para definir a tabela Departamento, incluíndo as restrições de chave primária, restrição de integridade referencial e restrição de unicidade do campo DeptoNome. Adicionar a restrição de que o campo sexo das tabelas EMPREGADO e DEPENDENTE podem assumir os valores ‘F’ e ‘M’.
17
Criação de Visões • Por que criar visões? – Para restringir o acesso ao banco de dados. – Para tornar consultas complexas mais simples. – Para permitir independência dos dados. – Para apresentar diferentes visões do mesmo dado. • Criação de visões usando aliases para as colunas na subquery. CREATE VIEW SalDepto30 AS SELECT empno NUM_EMP, ename NOME, sal SALARIO FROM emp WHERE deptno=30; 18
Prática de Laboratório (Visão) • Utilizando o banco de dados do usuário Scott. 2. Crie a visão EMPDPTO10 que contenha detalhes dos empregados (empno, ename, job) que são do departamento 10. 3. Crie uma visão EMPGERENTES com detalhes do empregados que são gerentes de outros empregados. 4. Crie uma visão que retorne o empno (número do empregado), ename (nome), hiredate (data de admissão), e dname (nome do departamento no qual empregado trabalha. A visão deverá conter como nome para as sual colunas (NUM_EMP, NOME, DT_ADMISSAO, NOME_DEPTO). 5. Consulte a visão criada no exercício 3. 19
Índices • • •
Os índices são criados automaticamente quando se define uma restrição de chave primária ou de unicidade na definição das tabelas. Os índices podem ser criados manualmente sobre colunas para acelerar o tempo de acesso às linhas da tabela. Guia para criação de um índice. – Crie um índice se: • A coluna é usada freqüentemente na cláusula WHERE ou em uma condição de junção. • A coluna apresenta um amplo intervalo de valores. • A tabela é grande e a maioria das consultas esperam recuperar menos do que 4% das linhas. – Não crie um índice se: • A tabela é pequena. • As colunas não são freqüentemente usadas como uma condição em uma query • A tabela é freqüentemente atualizada. 20
Índices • Criação do índice. – Aumentar a velocidade de consultas de acesso a coluna ENAME da tabela EMP. CREATE INDEX emp_ename_idx ON emp(ename); • Verificação dos índices. – A visão USER_INDEXES contém o nome do índice e sua unicidade. – A visão USER_IND_COLUMNS contém o nome do índice, o nome da tabela, o nome da coluna e a posição que a coluna ocupa no índice. • Remoção do índice do dicionário de dados. – Remoção do índice EMP_ENAME_IDX. • DROP INDEX emp_ename_idx;
21
Prática de Laboratório (Índice) 1. Criar índices para chaves estrangeiras das tabelas EMPREGADO, DEPARTAMENTO e DEPENDENTE. 2. Criar índice para o campo DependenteNome da tabela DEPENDENTE e NOME da tabela EMPREGADO. 3. Confirmar a criação dos índices utilizando as visões do dicionário de dados. 22
Identificação • Troque sua password utilizando o passa a ter O usuário alunon1 a senha teste comando alter user. ALTER USER nome usuário IDENTIFIED BY nova senha;
– Exemplo: ALTER USER alunon1 IDENTIFIED BY teste; 23
Privilégios • Dois tipos de privilégios: – Systema: permite ao usuário a executar operaçõe particulares sobre os banco de dados. – Objeto: permite ao usuário acessar e manipular um objeto específico. • Concedendo privilégios de objetos: – GRANT UPDATE(ename, sal) ON scott.emp TO usuário1 WITH GRANT OPTION; • WITH GRANT OPTION: permite o usuário que recebeu o privilégio, repassá-lo a outros usuários. • Para conceder privilégios, o objeto deve estar em seu esquema ou o privilégio pode ter sido concedido com a cláusula WITH GRANT OPTION. • Revogando privilégios de objetos: – REVOKE SELECT ON scott.emp FROM usuario1; – REVOKE ALL ON scott.cliente FROM aluno1; 24
Prática de Laboratório (Privilégios) 1. 2. 3.
4.
Criar o diretório AULAORACLE no diretório raíz. Rodar o script DDL.sql, inicialmente no diretório da professora, copiado para o diretorio c:\AULAORACLE. Usando a sua conta, conceder privilégio de select, update(salario, depto), insert sobre a tabela EMPREGADO a um usuário (pode ser o cologa sentado à máquina ao lado) sem a opção WITH GRANT OPTION. Se o usuário que recebeu esse privilégio tentar concedê-lo a outro usuário, o que acontece? Usando a sua conta, conceder privilégio de select, update, insert sobre a tabela EMPREGADO a um usuário1 com a opção WITH GRANT OPTION. Após ter recebido o privilégio, o usuário1 deverá concedê-lo ao usuário2. Agora você deverá revogar o privilégio de select do usuário1. O usuário2 deverá tentar fazer uma operação de select na tabela EMPREGADO. O que ocorreu? 25
Comando Insert • Insere uma nova linha: – Contendo valores para cada coluna; • Se não forem passados valores para todas as colunas, é necessário que se discrimine quais colunas receberão esses valores. – Opcionalmente, listando as colunas na cláusula INSERT. • Nesse caso é necessário que os valores sejam listados de acordo com a ordem existente das colunas na tabela. • Exemplo INSERT INTO dept (deptno, dname, loc) VALUES (50, ‘MARKETING’,’RIO DE JANEIRO’); 26
Comando Insert • Inserindo linha com valores nulos – Omitindo a coluna da lista de colunas INSERT INTO dept (deptno, dname) VALUES (60, ‘FINANCEIRO’); – Especificando o valor NULL. INSERT INTO dept VALUES (70, ‘ATENDIMENTO’, NULL); • Copiando linhas de uma outra tabela. INSERT INTO gerente (id, nome, sal, dtAdmissao) SELECT empno, ename, sal, hiredate FROM emp WHERE job = ‘MANAGER’;
27
Comando Update • Modifica as linhas existentes em uma tabela. • Permite especificar quais linhas serão modificadas ao empregar a cláusula WHERE. UPDATE emp SET deptno = 20 WHERE empno = 7782 ; • Todas as linhas são modificadas se a cláusula WHERE for omitida. UPDATE emp SET deptno = 20 28
Comando Delete • Especifica linhas que são excluídas quando a cláusula WHERE é especificada. DELETE FROM department WHERE dname =‘DEVELOPMENT’; • Todas as linhas são excluídas se a cláusula WHERE for omitida. • DELETE FROM department;
29
Prática de Laboratório (DML) 1.
2.
3.
4. 5. 6.
Tentar fazer as seguintes inserções na tabela departamento, explicando o ocorrido em cada caso – deptoNome = ‘VENDAS’; deptoNum = ‘100’ – deptoNome = ‘VENDAS’; deptoNum = ‘200’ – deptoNome = ‘FINANCEIRO’; deptoNum = ‘200’ Tentar fazer as seguintes inserções na tabela empregado, explicando o ocorrido em cada caso: – Matricula = 1; sexo= ‘F’; Nome= ‘MARIA’; depto=’10’ – Matricula = 1; sexo= ‘F’; Nome= ‘MARIA’ – Matricula = 1; sexo= ‘F’; Nome= ‘JOANA’; depto = ‘100’ – Matricula = 2; sexo= ‘F’; Nome= ‘JOANA’; depto = ‘200’ – Matricula = 3; sexo= ‘M’; Nome= ‘MARCOS’; matSupervisor = 1; depto = ‘100’ – Matricula = 4; sexo= ‘0’; Nome= ‘CARLOS’; depto = ‘100’ Tentar fazer as seguintes inserções na tabela dependente, explicando o ocorrido. – dependenteNome = ‘RITA’; sexo = ‘F’ – Matricula = 1; dependenteNome = ‘MARIANA’; sexo = ‘F’; parentesco = ’FILHA’ – Matricula = 1; dependenteNome = ‘IGOR’; sexo = ‘M’; parentesco = ‘FILHO’ Recuperar os dependentes da empregada de nome JOANA. Atualizar o empregado de matrícula 3, atribuindo o valor 1 à matrícula de seu supervisor. Atualizar a tabela departamento, atribuindo a columa matGerente o valor 1 ao departamento cujo número é 100, e o valor 2 ao departamento cuja número é 200. 30
Transação no Banco de Dados • Consiste de uma das seguintes declarações: – Declarações de DML que levam o banco de um estado consistente a outro estado consistente. – Uma única declaração DDL. – Uma única declaração DCL (grant/revoke). • Quando uma transação começa e termina? • Começa quando a primeira declaração SQL é executada e termina quando um dos seguintes eventos ocorrem: • Um comando de COMMIT ou ROLLBACK é emitido. • Uma declaração de DDL ou DCL é executada (COMMIT automático). • O usuário sai do SQL*Plus. • A máquina falha ou devido a um crash no sistema.
31
Transação no Banco de Dados • Um COMMIT automático ocorre quando um dos seguintes eventos ocorrem: – Um comando DDL ou DCL é emitido. • Um ROLLBACK automático ocorre devido a um término anormal do SQL*Plus ou a uma falha no sistema. – Uma saída normal do SQL*Plus. • Estado dos dados antes do COMMIT ou ROLLBACK. – Operações de manipulação afetam uma porção da área de memória (Database Buffer Cache), portanto , o estado anterior do dado pode ser recuperado. – O usuário corrente pode consultar os resultados das operações de manipulação nos dados através de consultas às tabelas. – Outros usuários não podem ver os resultados que decorrem das operações de manipulação dos dados feitas pelo usuário corrente. – Outros usuários não podem alterar os dados que pertencem as linhas afetadas.
32
Transação no Banco de Dados • Estado dos Dados após o COMMIT: – Mudanças aos dados tornam-se permanentes no banco de dados. – O estado anterior é perdido. – Todos os usuário que têm o direito de leitura podem ver os resultados. – Os bloqueios são liberados.
• Estado dos Dados após o ROLLBACK: – Mudanças aos dados são desfeitas. – O estado anterior dos dados é restaurado. – Os bloqueios sobre as linhas afetadas são liberados. 33
Prática de Laboratório (Commit/Rollback) 1. 2.
3. 4. 5. 6.
7. 8.
Emitir um comando de COMMIT; Inserir uma linha na tabela DEPARTAMENTO através do seguinte comando: INSERT INTO departamento (deptoNome, deptoNum, matGerente) VALUES (‘MARKETING’, ‘300’, 1); Selecionar as linhas da tabela DEPARTAMENTO através do comando de SELECT. Emitir um comando de ROLLBACK. Novamente selecionar as linhas da tabela DEPARTAMENTO através do comando de SELECT. Usando a sua conta, conceder privilégio de SELECT e UPDATE sobre a tabela EMPREGADO ao usuário scott sem a opção WITH GRANT OPTION. • GRANT SELECT, UPDATE ON empregado TO scott; Inserir na tabela EMPREGADO a seguinte linha: – Matricula = 4; sexo= ‘M’; Nome= ‘CARLOS’; depto = ‘100’. Abrir outra janela do aplicativo SQL*Plus, conectando-se como usuário scott, senha tiger e serviço oranova. • Como scott, consultar (SELECT) a tabela EMPREGADO para verificar se essa nova linha foi inserida. Não esqueça de especificar o esquema do aluno que concedeu o privilégio. • Por exemplo, se a tabela pertence ao usuário alunon1, então é necessário na cláusula FROM referenciar a tabela EMPREGADO do usuário como ALUNON1.EMPREGADO, ao invés de simplesmente EMPREGADO. • O comando de seleção, SELECT user FROM dual, especifica o usuário de uma sessão de conexão ao servidor de banco de dados. 34
Prática de Laboratório (Commit/Rollback)
1.
2.
3.
Voltar a janela do aplicativo SQL*Plus, que mantêm a conexão com o banco de dados através da sua conta de aluno (alunon1, alunon2, …ou alunon24) e emitir o comando COMMIT. Voltar a janela do SQL*Plus que mantêm a conexão do usuário scott e consultar a tabela EMPREGADO através do comando SELECT e verificar se essa nova linha foi inserida. Na janela do aplicativo SQL SQL*Plus, que mantêm a conexão com o banco de dados através da sua conta de aluno, dar o comando: • UPDATE empregado SET matSupervisor = 1 WHERE matricula = 4; • Não esqueça de referenciar o esquema a que pertence a tabela EMPREGADO. 35
Prática de Laboratório (Commit/Rollback) 12 . Voltar a janela do SQL*Plus que mantêm a conexão com o banco através do usuário scott . Dar o seguinte comando: UPDATE esquema.empregado SET salario = 1000 WHERE matricula = 4; Observe que a janela parece congelada, pois o servidor ORACLE bloqueia as linhas sobre as quais estão sendo executadas operações de atualização. 13. Voltar a janela do aplicativo SQL SQL*Plus, que mantêm a conexão com o banco de dados através da sua conta de aluno, e dar o comando: COMMIT; para confirmar no banco de dados as atualizações feitas aos dados. Listar as linhas da tabela EMPREGADO através do comando SELECT. 14. Voltar a janela do SQL*Plus que mantêm a conexão com o banco através do usuário scott e verificar o ocorrido. Selecionar as linhas da tabela EMPREGADO e emitir um comando COMMIT para confirmar as atualizações feitas pelo usuário scott. 15. Voltar a janela do aplicativo SQL SQL*Plus, que mantêm a conexão com o banco de dados através da sua conta de aluno, e revogar os privilégios de acesso concedidos ao scott. REVOKE ALL ON EMPREGADO FROM SCOTT; 36
Referência Bibliográfica
• Manual - Introduction to Oracle: SQL an PL/SQL – Vol 1 e 2.
37