Embed Size (px)
Citation preview
George Hamilton
Slide Title
Asterio K. Tanaka
BANCO DE DADOS(Fundamentos e Projeto)
Asterio K. Tanakahttp://www.uniriotec.br/~tanaka
MODELO RELACIONAL E LINGUAGEM SQL
George Hamilton
Slide Title
Asterio K. Tanaka
Sistemas de arquivos
BD modelo em rede BD hierárquico
BD relacional
BD/OO
LinguagensOO
Modelossemânticos
Objetoscomplexos
Informationretrieval
InteligênciaartificialHipermídia
BD “inteligente”
Evolução dos Bancos de Dados(Khoshafian 1995)
George Hamilton
Slide Title
Asterio K. Tanaka
Categorias de Modelos de Dados• Modelos conceituais:
representam a realidade a um nível alto de abstração (ex: ER, modelos semânticos, OO)
• Modelos lógicos ou de implementação:
suportam descrições de dados que possam ser processadas por computador (ex: hierárquico, em rede, relacional, relacional-objeto, OO).
• Modelos físicos:
descrevem como os dados são armazenados no computador através de informações como tipos de arquivos, formatos e ordenação de registros, caminhos de acesso (ex: várias formas de estruturas de arquivos).
George Hamilton
Slide Title
Asterio K. Tanaka
Modelo Relacional
• Introduzido pelo pesquisador da IBM, Edward Codd, em 1970
• Características marcantes: modelo formal por natureza, estrutura de dados simples e uniforme.
• O modelo relacional representa os dados num banco de dados como uma coleção de relações ou tabelas.
• É o modelo mais utilizado comercialmente.
George Hamilton
Slide Title
Asterio K. Tanaka
ESTUDANTE
Nome CPF Tel_Res Cidade Tel_Trab Idade CR
Benjamim 305612435-20 (021)373-1616 Caxias 19 3,21
Catarina 381621245-10 (021)375-4409 Niteroi 18 2,89
Ricardo 422112320-00 Santos (021)749-1253 25 3,53
Carlos 489221110-02 (011)376-9821 São Paulo (021)749-6492 28 3,93
Barbara 533691238-11 (021)839-8461 Rio de Janeiro 19 3,25
atributos
tuplas
nome da relação
• Domínio, tipo de dado, formato• Esquema, grau, atributo• Instância, tupla• Intensão, extensão• Relação = subconjunto do produto cartesiano• Super-chave, chave candidata, chave primária,
chave alternativa
George Hamilton
Slide Title
Asterio K. Tanaka
Restrições de Integridade no Modelo Relacional
Restrições de domínio
• O valor de cada atributo A deve ser um valor atômico pertencente ao domínio dom(A), de acordo com seu tipo de dados
Restrições de chave
• Valores de chaves candidatas devem ser distintos (“unique”).
Restrição de integridade de entidade
• Nenhum valor de chave primária pode ser nulo (“not null”).
Restrição de integridade referencial
• Chave estrangeira ( “foreign key”).
George Hamilton
Slide Title
Asterio K. Tanaka
Restrições de integridade referencial
Nome CPF Data-Nasc Endereço Sexo Salário Supervisor Dept
Nome Número
Num-Dept Localidade
Nome Número Local Dept
CPF-Emp Num-Proj Horas
CPF-Emp Nome-Dep Sexo Data-Nasc Relacionamento
EMPREGADO
DEPARTAMENTO
LOCALIZACAO
PROJETO
TRABALHO
DEPENDENTE
Gerente Num Dept Data-Inicio
GERENCIA
George Hamilton
Slide Title
Asterio K. Tanaka
Uma instância de banco de dados relacional
Nome CPF Data-Nasc Endereço Sexo Salário Supervisor Dept
Nome
Num-Dept Localidade
Nome Núm Local Dept
CPF-Emp Num-Proj Horas
CPF-Emp Nome-Dep Sexo Data-Nasc Relacionamento
EMPREGADO
DEPARTAMENTO
LOCALIZACAO
PROJETO
TRABALHO
DEPENDENTE
JoaoFrankAliciaJaneRamonJoyceAhmadJames
123456789333445555999887777987654321666884444453453453987987987888665555
09Jan5508Dez4519Jul5820Jun3115Set5231Jul6229Mar5910Nov27
333445555888665555987654321888665555333445555333445555987654321 nulo
Santos,SPSantos,SPTatui,SPLins,SPMarilia,SPSantos,SPSantos,SPSantos,SP
MMFFMFMM
3000040000250004300038000250002500055000
55445541
PesquisaAdministracaoSede
541
333445555987654321888665555
22Mai8801Jan9219Jun91
14555
SantosCampinasLinsBauruSantos
123456789123456789666884444453453453453453453333445555333445555333445555333445555999887777999887777987987987987987987987654321987654321888665555
ProdutoXProdutoYProdutoZInformat.Reorgan.Beneficios
123102030
LinsBauruSantosCampinasSantosCampinas
555414
1231223102030101030302020
32,5 7,540,020,020,010,010,010,010,030,010,035,05,020,015,0nulo
333445555333445555333445555987654321123456789123456789123456789
AliceTeoJoyAbnerMiguelAliceBeth
FMFMMFF
05Abr7625Out7303Mai4829Fev3201Jan7831Dez7805Mai57
FilhaFilhoEsposaEsposoFilhoFilhaEsposa
Num
541
Num CPF Inicio
GERENCIA
George Hamilton
Slide Title
Asterio K. Tanaka
Operações de Atualização sobre Relações
• Três operações básicas:
INSERIR - DELETAR - MODIFICAR
(“Insert”, “Delete”, “Update”)
• Sempre que operações de atualização são aplicadas, deve-se verificar as restrições de integridade (chave, entidade e referencial).
George Hamilton
Slide Title
Asterio K. Tanaka
Álgebra Relacional
• Coleção de operações usadas para manipular relações inteiras. O resultado dessas operações é uma nova relação, que por sua vez pode ser manipulada pelas operações da álgebra relaciona (propriedade de fechamento da álgebra relacional)l.
• Dois grupos de operações:
– Operações da Teoria Matemática dos Conjuntos
UNIÃO - INTERSEÇÃO - DIFERENÇA - PRODUTO CARTESIANO
– Operações desenvolvidas especificamente para bancos de dados relacionais
SELEÇÃO - PROJEÇÃO - JUNÇÃO - funções agregadas
George Hamilton
Slide Title
Asterio K. Tanaka
Álgebra Relacional
a
PRODUTO
SELEÇÃO PROJEÇÃO UNIÃO INTERSEÇÃO DIFERENÇA
b
c
x
y
a
a
x
y
b
b
x
y
c
c
x
y
a1
JOIN
a2
a3
a
a
x
y
a
b
z
y
c x
b1
b1
b2
b1
b2
b3
c1
c2
c3
a1
a2
a3
b1
b1
b2
c1
c1
c2
x
y
a
DIVISÃO
George Hamilton
Slide Title
Asterio K. Tanaka
Operações da Teoria dos Conjuntos
UNIÃO R1 R2
INTERSEÇÃO R1 R2
DIFERENÇA R1 R2
PRODUTO CARTESIANO R1 X R2
George Hamilton
Slide Title
Asterio K. Tanaka
Operações de Bancos de Dados Relacionais
SELEÇÃO <condição de seleção> (R)
PROJEÇÃO <lista de atributos> (R)
JUNÇÃO R1 <condição de junção> R2
George Hamilton
Slide Title
Asterio K. Tanaka
Conjunto Completo de Operações da Álgebra Relacional
Pode ser demonstrado que o conjunto de operações da álgebra relacional { seleção, projeção, união, diferença, produto cartesiano }
{ X }
é um conjunto completo, isto é, qualquer uma das outras operações pode ser expressa como uma seqüência de operações deste conjunto.
Exemplos:
R S (R S) - ( (R - S) (S - R) )
R <condição > S <condiçao > (R X S)
George Hamilton
Slide Title
Asterio K. Tanaka
Funções Pré-definidas
Funções de agregações matemáticas sobre coleções de valores do banco de dados.
SOMA - SUM(atributo)
MÉDIA - AVG (atributo)
MÁXIMO - MAX(atributo)
MÍNIMO- MIN(atributo)
CONTAGEM - COUNT(atributo) ou COUNT(*)
George Hamilton
Slide Title
Asterio K. Tanaka
SQL (Structured Query Language)
• Linguagem de alto nível declarativa
• Padrão norte-americano (ANSI 1986) e internacional (ISO 1989) para os SGBDs relacionais.
• Originário do Sistema R da IBM, embrião dos SGBDs comerciais da IBM, SQL/DS e DB2.
• Atualmente, todos os SGBDs relacionais provêm uma variação da linguagem SQL.
• Padrões: SQL-86 (ANSI) e SQL-89 (ISO)
SQL-2 ou SQL-92 (ANSI/ISO em vigor)
SQL-3 (aprovada em 2000)
George Hamilton
Slide Title
Asterio K. Tanaka
Definição de Dados em SQL
CREATE DOMAIN <domínio> <tipo de dado primitivo>[ DEFAULT <valor default> ][ <lista de restrições de domínio> ]
DROP DOMAIN <domínio> ( CASCADE | RESTRICT )
Definição de domínios
CREATE DOMAIN CORES CHAR (8)DEFAULT ‘Branco’CONSTRAINT CORES_VALIDAS
CHECK ( VALUE IN (‘Branco’, ‘Azul’, ‘Vermelho’, ‘Verde’) )
George Hamilton
Slide Title
Asterio K. Tanaka
Criação, Alteração, Remoção de TabelasCREATE TABLE <tabela>
‘(‘ <coluna> <tipo da coluna> [ restrição do atributo ]{ , <coluna> <tipo da coluna> [ restrição do atributo ] }[ <restrição da tabela> { , <restrição da tabela> } ] ‘)’
ALTER TABLE <tabela> <lista de alterações na tabela>
DROP TABLE <tabela> ( CASCADE | RESTRICT )
CREATE TABLE DEPARTAMENTO (Nome VARCHAR(15) NOT NULL,Numero INTEGERPRIMARY KEY);
CREATE TABLE EMPREGADO (Nome VARCHAR(30) NOT NULL,CPF CHAR(9)NOT NULL,Data-Nasc DATE,Endereço VARCHAR(30),Sexo CHAR(1),Salário INTEGER,Supervisor CHAR(9)REFERENCES EMPREGADO,Dept INTEGERREFERENCES DEPARTAMENTO,PRIMARY KEY (CPF),UNIQUE (Nome));
George Hamilton
Slide Title
Asterio K. Tanaka
Criação, Alteração, Remoção de Tabelas
ALTER TABLE EMPREGADO
ADD COLUMN Cor_predileta CORES;
ALTER TABLE EMPREGADO
ADD FOREIGN KEY chave_dept
REFERENCES DEPARTAMENTO
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE EMPREGADO
ADD CONSTRAINT salario_minimo
CHECK (Salario >= 10000);
George Hamilton
Slide Title
Asterio K. Tanaka
Consultas em SQL
<comando-de-seleção> ::=
SELECT [DISTINCT] <lista de atributos>
FROM <lista de tabelas ou visões>
[WHERE <condição de seleção ou de junção>]
[GROUP BY <lista de atributos> [HAVING <condição de seleção>]]
[ORDER BY <coluna> [(ASC | DESC)] {,<coluna> [(ASC | DESC)]}
A condição de junção pode ser especificada em cláusula fora da cláusula WHERE como junções do tipo INNER JOIN, LEFT JOIN, RIGHT JOIN ou OUTER JOIN.
George Hamilton
Slide Title
Asterio K. Tanaka
Definição de visões
CREATE VIEW <nome-de-visão> [ (lista-de-colunas) ]
AS <comando-de-seleção>
CREATE VIEW empregados_sede
AS SELECT (Nome, Endereco, Sexo, Data-nasc)
FROM EMPREGADO, DEPARTAMENTO
WHERE EMPREGADO.Dept = DEPARTAMENTO.Numero
AND DEPARTAMENTO.Nome = “Sede”;
George Hamilton
Slide Title
Asterio K. Tanaka
Condições de junção em SQL
SELECT EMPREGADO.Nome, Sexo,Data_Nasc, Salário, DEPARTAMENTO.Nome
FROM DEPARTAMENTO INNER JOIN EMPREGADO ON DEPARTAMENTO.Número = EMPREGADO.Dept
WHERE Salário>=30000;
SELECT EMPREGADO.Nome, Sexo,Data_Nasc, Salário, DEPARTAMENTO.Nome
FROM DEPARTAMENTO, EMPREGADO WHERE DEPARTAMENTO.Número = EMPREGADO.Dept
AND Salário>=30000;
As consultas acima são equivalentes
George Hamilton
Slide Title
Asterio K. Tanaka
SQL como linguagem de consulta para usuários finais
A proposta inicial da SQL era prover uma interface de consulta “natural” para usuários finais através do comando SELECT. Logo se percebeu que isto só seria possível em consultas muito simples, devido à complexidade da linguagem.
Consulta: Para cada departamento com mais de 2 empregados, obtenha o nome do departamento e o número de empregados que ganham mais de 30.000.
SELECT D.Nome, COUNT(*)FROM DEPARTAMENTO AS D, EMPREGADO AS EWHERE D.Número=E.Dept AND E.Salário > 30000 AND E.Dept IN (SELECT Dept FROM EMPREGADO GROUP BY Dept HAVING COUNT(*) > 2)GROUP BY D.Nome;
George Hamilton
Slide Title
Asterio K. Tanaka
Comandos de Manipulação de Dados
INSERT INTO <tabela> [ ( <lista de colunas> ) ]( VALUES ( <lista de valores> ) | <comando SELECT> )
UPDATE <tabela>SET <coluna> = <valor> {, <coluna> = <valor>}[ WHERE <condição de seleção> ]
DELETE FROM <tabela>[ WHERE <condição de seleção> ]
George Hamilton
Slide Title
Asterio K. Tanaka
INSERT INTO EMPREGADOVALUES (‘Ricardo’, ‘653298653’, 30Dez52, ‘Tupã’, ‘M’, 37000, ‘987654321’, 4).
INSERT INTO EMPREGADO (CPF,Nome,Sexo) VALUES (‘Joana’,126126126-00,‘F’)
DELETE FROM EMPREGADOWHERE CPF = ‘123456789’.
DELETE FROM EMPREGADOWHERE Dept IN (SELECT Número
FROM DEPARTAMENTOWHERE Nome = ‘Pesquisa’).
UPDATE PROJETOSET Local = ‘Bauru’, Dept = 5WHERE Número = 10.
UPDATE EMPREGADOSET Salário = Salário * 1.1WHERE Dept IN (SELECT Número
FROM DEPARTAMENTOWHERE Nome = ‘Pesquisa’).
George Hamilton
Slide Title
Asterio K. Tanaka
Programação usando SQL
• SQL não é linguagem de programação!
• Duas abordagens para programação com SQL
• linguagem procedural hospedeira
C, C++, COBOL, FORTRAN, Java, Pascal, etc...
• extensões procedurais à SQL
Access Basic (VB) no MS Access
TRANSACT SQL no MS SQL Server e Sybase
PL/SQL no Oracle
etc ...
George Hamilton
Slide Title
Asterio K. Tanaka
Oracle PL/SQL
O PL/SQL é uma linguagem de 3a. geração que tempor objetivo processar informações do banco de dadosOracle.
Características:
• Permite recuperar, incluir, atualizar e excluir dados de tabelas
• Permite criar variáveis e constantes
• Permite utilizar comandos condicionais e de repetição
George Hamilton
Slide Title
Asterio K. Tanaka
PL/SQL
Estrutura de Um Bloco PL/SQL:
DECLARE /*Opcional*/Variáveis, cursores, exceções definidas pelo usuário
BEGIN /*Mandatório*/Comandos SQL e PL/SQL
EXCEPTION /* Opcional */ Ações a serem executadas quando ocorrem erros
END; /*Mandatório*/
George Hamilton
Slide Title
Asterio K. Tanaka
PL/SQL
Definição de Variáveis e Constantes:
VARIÁVEISSão áreas em memórias que servem para armazenar dados, podendo
conter diversos valores ao longo da execução do programa.
CONSTANTESTambém são áreas de memória que servem para armazenar dados, mas
que possui valor fixo e deve ser inicializado no ato da declaração e seguido da palavra "CONSTANT".
Na definição de variáveis ou constantes, deve-se especificar o tipo de dado e o tamanho. Pode-se especificar também que uma variável não pode conter valor nulo utilizando a restrição "NOT NULL". Neste caso ela deve ser iniciada com um valor no ato da definição.
George Hamilton
Slide Title
Asterio K. Tanaka
TIPOS DE DADOS
CHAR
Variáveis ou constantes que armazenam valores alfanuméricos com no máximo de 255 caracteres.
Exemplo:
Pagamento CHAR(40);
VARCHAR2
Variáveis que armazenam valores alfanuméricos de tamanho variável até o tamanho máximo de 2000.
Exemplo:
Nome VARCHAR2(45);
George Hamilton
Slide Title
Asterio K. Tanaka
TIPOS DE DADOS
NUMBER
Variáveis ou constantes que armazenam valores numéricos com no máximo de 38 caracteres. Caso não seja informado o tamanho, o default é 38. Também pode ser definido precisão escalar.
Exemplos:
Recibo NUMBER(2);
Codigo CONSTANT NUMBER(4) :=1;
Preco NUMBER(7,2)
George Hamilton
Slide Title
Asterio K. Tanaka
TIPOS DE DADOS
DATE
Variáveis ou constantes que armazenam data, hora (com minutos e segundo e o século).
Exemplo:
Data_receb DATE;
BOOLEAN
Variáveis ou constantes que armazenam TRUE, FALSE ou NULL.
Exemplo:
Flag BOOLEAN;
George Hamilton
Slide Title
Asterio K. Tanaka
DECLARAÇÃO DE VARIÁVEIS COM TIPOS DE ATRIBUTOS
Para declarar uma variável, constante ou coluna com a mesma definição da coluna de uma tabela, o atributo %TYPE pode ser utilizado.
Formato
nome_tabela.nome_coluna%TYPE;
Exemplo:
v_CPFemp empregado.CPF%TYPE;
George Hamilton
Slide Title
Asterio K. Tanaka
COMANDOS DE MANIPULAÇÃO DE DADOS EM PL/SQL
INSERT, DELETE, UPDATE e SELECT. O comando SELECT tem que ter a cláusula INTO para relacionar as variáveis onde serão armazenados os valores selecionados.
Para controlar as transações com o banco de dados (iniciadas implicitamente com qualquer comando de modificação de dados), utiliza-se os comandos COMMIT, SAVEPOINT e ROLLBACK.
Para garantir que os dados a serem manipulados não serão alterados ou excluídos por outros usuários, utiliza-se o comandos LOCK TABLE ou o comando SELECT com a cláusula FOR UPDATE.
George Hamilton
Slide Title
Asterio K. Tanaka
COMANDOS DE CONTROLE DE SEQÜÊNCIA
• Condicional
• IF ... THEN ... ELSE ... END IF
• IF ... ELSEIF ... END IF
• Repetição
• FOR ... LOOP ... END LOOP
• WHILE ... LOOP ... END LOOP
• LOOP ... END LOOP
• Exit
• EXIT
•EXIT[WHEN condição]
George Hamilton
Slide Title
Asterio K. Tanaka
Sintaxe de comando condicional:IF condição THENcomandos....[ELSEIF condição THEN comandos][ELSE comandos]END IF
Exemplo:DECLARE
estoque NUMBER(5);BEGIN
SELECT quantidade INTO estoqueFROM inventarioWHERE produto = ’Raquete de tenis'FOR UPDATE OF quantidade;
IF estoque > 0 THEN -- verifica quantidadeUPDATE inventario SET quantidade= quantidade- 1
WHERE produto = 'TENNIS RACKET';INSERT INTO historico_vendasVALUES (’Raquete de tenis vendida',SYSDATE);
ELSEINSERT INTO historico_vendas
VALUES (’Rquete de tenis fora sem estoque', SYSDATE);END IF;COMMITEND;/
George Hamilton
Slide Title
Asterio K. Tanaka
Sintaxe de comandos de repetição:
FOR contador IN [REVERSE] inicial...final LOOPrelação_de_comandos
END LOOP;
WHILE condição LOOPrelação_de_comandos
END LOOP;
LOOPrelação_de_comandos
END LOOP;
George Hamilton
Slide Title
Asterio K. Tanaka
Exemplo de comando de repetição:
-- Este bloco encontra o primeiro empregado que tenha salario acima de 40000
-- e esteja acima do empregado ‘333445555’ na cadeia de supervisão
DECLAREv_sal empregado.salario%TYPE;v_sup empregado.supervisor%TYPE;v_nome empregado.nome%TYPE;sup_inicial CONSTANT CHAR(9) := ‘333445555’;
BEGINSELECT salario, supervisor INTO v_sal, v_sup FROM empregadoWHERE CPF = sup_inicial;
WHILE v_sal < 40000 LOOPSELECT salario, supervisor, nome INTO v_sal, v_sup, v_nomeFROM empregadoWHERE CPF = v_sup;
END LOOP;INSERT INTO temp VALUES (NULL, v_sal, v_nome);COMMIT;END;/
George Hamilton
Slide Title
Asterio K. Tanaka
PL/SQL
Estrutura de Um Bloco PL/SQL:
DECLARE /*Opcional*/Variáveis, cursores, exceções definidas pelo usuário
BEGIN /*Mandatório*/Comandos SQL e PL/SQL
EXCEPTION /* Opcional */ Ações a serem executadas quando ocorrem erros
END; /*Mandatório*/
George Hamilton
Slide Title
Asterio K. Tanaka
TRATAMENTO DE ERROS (EXCEPTION HANDLERS)
Utilizando o EXCEPTION, quando ocorre um erro interno (EXCEPTION), a execução normal do bloco PL/SQL é parada e o controle é transferido para a parte do bloco PL/SQL que trata os erros, que é o EXCEPTION. Quando terminar o tratamento do erro, o controle da execução retorna o próximo comando executável do bloco PL/SQL.
George Hamilton
Slide Title
Asterio K. Tanaka
Tratadores de EXCEPTION PRÉ-DEFINIDOS
NO_DATA_FOUND
Quando não existirem dados para retornar no comando select.
OTHERS
Quando ocorrem outros erros.
Sintaxe:
WHEN tratador_exception THEN
comando1;
comando2;
comandon;
George Hamilton
Slide Title
Asterio K. Tanaka
Exemplo:
-- bloco PL/SQL que imprime dados sobre empregado que tem o maior salario
DECLARE v_nome empregado.nome%TYPE; v_endereco empregado.endereco%TYPE; v_salario empregado.salario%TYPE;
BEGIN SELECT nome, endereco, salario INTO v_nome, v_endereco, v_salario FROM empregado WHERE salario = (SELECT MAX (salario) FROM empregado); DBMS_OUTPUT.PUT_LINE (v_nome, v_endereco, v_salario );
EXCEPTION WHEN OTHERS DBMS_OUTPUT.PUT_LINE (‘Erro Detectado’);
END;
George Hamilton
Slide Title
Asterio K. Tanaka
Exemplo:-- bloco PL/SQL que aumenta em 10% os salarios dos empregados cujos salarios-- sejam menores do que a media salarial de todos os empregados-- o programa recalcula e imprime a media salarial se esta exceder 50000
DECLARE v_salmedio NUMBER;
BEGIN SELECT AVG(salario) INTO v_salmedio FROM empregado; UPDATE empregado SET salario = salario*1.1
WHERE salario < v_salmedio; SELECT AVG(salario) INTO v_salmedio FROM empregado; IF v_salmedio > 50000 THEN
DBMS_OUTPUT.PUT_LINE (‘Salario medio = ‘ | | v_salmedio ); END IF; COMMIT;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (‘Erro na atualizacao de salario‘) ROLLBACK;
END;
George Hamilton
Slide Title
Asterio K. Tanaka
CURSORES EM PL/SQL
O conjunto de linhas retornadas por uma consulta SELECT pode ter zero, uma ou múltiplas linhas, dependendo de quantas satisfazem a condição de seleção.
O PL/SQL necessita de uma área de trabalho para armazenar o conjunto de linhas retornadas por uma consulta SELECT . Esta área recebe um nome, e é conhecido como CURSOR.
George Hamilton
Slide Title
Asterio K. Tanaka
CURSORES
A) Declarando o Cursor:
CURSOR nome_cursor IS comando_select;
B) Abrindo o Cursor:
OPEN nome_cursor;
C) Buscando os dados armazenados no Cursor:
FETCH nome_cursor INTO variáveis_declaradas;
D) Fechando o Cursor:
CLOSE nome_cursor;
George Hamilton
Slide Title
Asterio K. Tanaka
-- exibe os CPFs de empregados cujos salarios são maiores do que os salarios de seus supervidores
DECLARE v_salario NUMBER; v_salsuper NUMBER; v_CPF CHAR (9); v_CPFsuper CHAR (9); CURSOR c_salario IS SELECT CPF, salario, supervisor FROM empregado;
BEGIN OPEN c_salario; LOOP
FETCH c_salario INTO v_CPF, v_salario, v_CPFsuper; EXIT WHEN c_salario%NOTFOUND; IF v_CPFsuper IS NOT NULL THEN
SELECT salario INTO v_salsuper FROM empregado WHERE CPF = v_CPFsuper; IF v_salario> v_salsuper THEN
DBMS_OUTPUT.PUT_LINE(v_CPF); END IF;
END IF; END LOOP; IF c_salario%ISOPEN THEN CLOSE c_salario;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘Erro no CPF ‘ | | v_CPF); IF c_salario%ISOPEN THEN CLOSE c_salario;
END;
George Hamilton
Slide Title
Asterio K. Tanaka
-- lista todos os emrpegados, aumenta os salarios em 10%, e mostra o antigo e o novo salario
DECLARE v_CPF empregado.CPF%TYPE; v_nome empregado.nome%TYPE; v_salario empregado.salario%TYPE; CURSOR emp IS SELECT CPF, nome, salario FROM empregado;
BEGIN OPEN emp; LOOP
FETCH emp INTO v_CPF, v_nome, v_salario; EXIT WHEN emp%NOTFOUND; DBMS_OUTPUT.PUTLINE(‘CPF:’ | | v_CPF | | ‘Antigo salario :’ | | v_salario); UPDATE empregado
SET salario = salario*1.1 WHERE CPF = v_CPF;
COMMIT; DBMS_OUTPUT.PUTLINE(‘CPF:’ | | v_CPF | | ‘Novo salario :’ | | v_salario*1.1);
END LOOP; CLOSE emp;
EXCEPTION WHEN OTHERS DBMS_OUTPUT.PUT_LINE (‘Erro Detectado’);
END:
George Hamilton
Slide Title
Asterio K. Tanaka
• Restrições de integridade embutidas nos sistemas de informação formam um componente importante de programas de aplicação, que podem ser distribuídas por todo o sistema. Isto pode gerar incompletude e redundância dessas restrições, que podem ser especificadas diferentemente. Desta forma, existem fortes argumentos para implementação centralizada das restrições de integridade relevantes do sistema de informação no banco de dados, como procedimentos armazenados ("stored procedures"), gatilhos("triggers") e funções definidas por usuários.
TRIGGER, STORED PROCEDURE e FUNÇÕES
George Hamilton
Slide Title
Asterio K. Tanaka
• Uma função definida pelo usuário computa e retorna um resultado baseado num conjunto de valores de entrada. Uma vez definida e criada dentro do SGBD, torna-se disponível para ser utilizada até mesmo em sentenças SQL
Funções
George Hamilton
Slide Title
Asterio K. Tanaka
CREATE [OR REPLACE]FUNCTION nomefunc [ (parâmetro,...) ]RETURN tiporetornoIS resultado tiporetorno
corpo da função: [declarações](bloco PL/SQL) BEGIN
............RETURN resultado[EXCEPTION]END;
onde o parâmetro é do tipo IN
Oracle PL/SQL
George Hamilton
Slide Title
Asterio K. Tanaka
CREATE FUNCTION procuraid(primeiro IN CHAR(50), ultimo IN CHAR(50))RETURN INTEGER ASidcli INTEGER;BEGIN
SELECT id INTO idcli FROM clienteWHERE sobrenome=ultimo AND renome=primeiro;RETURN idcli;
EXCEPTIONWHEN NO_DATA_FOUND THEN
raise_application_error(-20130,'ID de Cliente Inválida');END procuraid;
SELECT *FROM pedidosWHERE id_cliente=procuraid('Renata','Terra');
Funções
George Hamilton
Slide Title
Asterio K. Tanaka
• Procedimento armazenado é um módulo de lógica procedimental mantido, administrado e executado pelo SGBD. A razão primária para ser utilizado é mover as regras de negócio da aplicação para o banco de dados. Um procedimento armazenado não é fisicamente associado a outro objeto do banco de dados, como os gatilhos de banco de dados, podendo acessar e modificar uma ou mais tabelas.
STORED PROCEDURE
George Hamilton
Slide Title
Asterio K. Tanaka
CREATE [OR REPLACE] PROCEDURE nomeproc [ (parâmetro,...) ]IScorpo do procedimento: [declarações](bloco PL/SQL) BEGIN
[EXCEPTION]END;
onde parâmetro: nomepar [IN | OUT | IN OUT] tipo
[ { := | DEFAULT } expressão ]
Oracle PL/SQL
George Hamilton
Slide Title
Asterio K. Tanaka
CREATE PROCEDURE ELIMINA_CLIENTE
(IDCLIENTE IN INTEGER)
AS ULTIMO VARCHAR2(50);
PRIMEIRO VARCHAR2(50);
BEGIN
SELECT SOBRENOME, NOME INTO ULTIMO, PRIMEIRO
FROM CLIENTE WHERE ID = IDCLIENTE;
INSERT INTO HISTORICO_CLIENTE
VALUES (IDCLIENTE, ULTIMO, PRIMEIRO);
DELETE FROM CLIENTE WHERE ID = IDCLIENTE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIBE_MSG_ERRO (‘ID DE CLIENTE INVÁLIDA’);
END ELIMINA_CLIENTE;
STORED PROCEDURE
George Hamilton
Slide Title
Asterio K. Tanaka
• Gatilhos (“triggers”) são procedimentos especiais dirigidos por eventos, armazenados e executados em um SGBD, vinculados a uma tabela. Um gatilho não pode ser invocado diretamente pelo usuário, mas apenas disparado automaticamente pelo SGBD, devido à ocorrência de um evento resultante de uma ação, normalmente, de modificação da tabela associada ao gatilho.
TRIGGER (Gatilhos)
George Hamilton
Slide Title
Asterio K. Tanaka
Oracle PL/SQL
CREATE [OR REPLACE] TRIGGER nome_do_trigger
tempo do trigger: BEFORE ou AFTERevento disparador: DELETE ou INSERT ou UPDATE [OF coluna]nome da tabela: ON tabelatipode trigger: de comando ou de linha [FOR EACH ROW]condição: WHEN cláusulacorpo do trigger: [declarações](bloco PL/SQL) BEGIN
[EXCEPTION]END;
George Hamilton
Slide Title
Asterio K. Tanaka
CREATE TRIGGER DELETA_CLIENTE
BEFORE DELETE ON CLIENTE
FOR EACH ROW
BEGIN
INSERT INTO HISTORICO_CLIENTE
VALUES (:OLD.ID, :OLD.SOBRENOME, :OLD.NOME);
END DELETA_CLIENTE;
OBS: Referências a atributos em eventos
DELETE :OLD (valores deletados)
INSERT :NEW (valores inseridos
UPDATE :OLD :NEW
TRIGGER (Gatilhos)
George Hamilton
Slide Title
Asterio K. Tanaka
SQL Embutida em Linguagem Hospedeira(Ex: Linguagem C - EN2000, 10.5.3)
#include <stdio.h> #include <string.h> VARCHAR username[30];VARCHAR password[10];VARCHAR v_fname[15];VARCHAR v_minit[1];VARCHAR v_lname[15];VARCHAR v_address[30];char v_ssn[9];float f_salary;main () { strcpy (username.arr, "Scott"); username.len = strlen(username.arr); strcpy(password.arr,"TIGER"); password.len = strlen(password.arr); EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :username IDENTIFIED BY :password; EXEC SQL SELECT fname, minit, lname, address, salary
INTO :v_fname, :v_minit, :v_lname, :v_address, :f_salary FROM EMPLOYEE WHERE salary = (select max (salary) from employee);
printf (" Employee first name, Middle Initial, Last Name, Address, Salary \n"); printf ("%s %s %s %s %f \n ", v_fname.arr, v_minit.arr, v_lname.arr, v_address.arr, f_salary); } sql_error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf(" Error detected \n"); }
George Hamilton
Slide Title
Asterio K. Tanaka
SQL Embutida em Linguagem Hospedeira(Ex: Linguagem C - EN2000, 10.5.3)
. . . /* same include statements and variable declarations as E5 main () { strcpy (username.arr, "Scott"); username.len= strlen(username.arr); strcpy(password.arr,"TIGER"); password.len = strlen(password.arr); EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :username IDENTIFIED BY :password; EXEC SQL DECLARE EMP CURSOR FOR SELECT ssn, fname, minit, lname, salary FROM employee; EXEC SQL OPEN EMP; EXEC SQL WHENEVER NOTFOUND DO BREAK; for (;;) { EXEC SQL FETCH EMP INTO :v_ssn, :v_fname, :v_minit, :v_lname, :f_salary; printf ("Social Security Number : %d, Old Salary : %f ", v_ssn, f_salary); EXEC SQL UPDATE employee
SET salary = salary*1.1 WHERE ssn = :v_ssn;
EXEC SQL COMMIT; printf ("Social Security Number : %d New Salary : %f ", v_ssn, f_salary*1.1); } } sql_error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf(" Error detected \n"); }
George Hamilton
Slide Title
Asterio K. Tanaka
Índices em SQL
CREATE INDEX ind-nome ON EMPREGADO (Nome);
CREATE INDEX ind-dep-nomeON EMPREGADO (Dept ASC, Nome DESC);
CREATE UNIQUE INDEX ind-nome-unicoON EMPREGADO (Nome);
CREATE INDEX ind-depON EMPREGADO (Dept) CLUSTER;
DROP INDEX ind-nome;
George Hamilton
Slide Title
Asterio K. Tanaka
Controle de segurança em SQL
GRANT CREATETAB ON DATABASE TO usuario1;
GRANT INSERT, DELETE ON EMPREGADO, PROJETO TO usuario2;
GRANT SELECT ON empregados-sede TO usuario-3;
GRANT CONTROL ON INDEX ind-dep TO usuario1, usuario2;
GRANT ALL PRIVILEGES ON EMPREGADO TO usuario1;
REVOKE CONTROL ON INDEX ind-dep TO usuario2;
REVOKE DELETE ON EMPREGADO TO usuario2;
REVOKE ALL PRIVILEGES ON EMPREGADO TO usuario1;
George Hamilton
Slide Title
Asterio K. Tanaka
Controle de transações em SQL
• Uma transação é implicitamente iniciada quando ocorre uma operação que modifica o banco de dados (INSERT, UPDATE ou DELETE).
• Uma transação pode terminar normalmente (COMMIT) ou ser desfeita até o início da transação (ROLLBACK) ou até um ponto de controle (ROLLBACK TO SAVEPOINT).
INSERT ...; DELETE ...; UPDATE ...; COMMIT;
INSERT ...; DELETE ...; ROLLBACK; UPDATE ...; COMMIT;
INSERT ...; DELETE ...; SAVEPOINT sp1; UPDATE ...; ROLLBACK TO sp1; DELETE ...; SAVEPOINT sp2; UPDATE ...; ROLLBACK TO sp2; UPDATE ...; COMMIT;
