TBD5 Packages e Cursores

Prévia do material em texto

Tecnologias De Base De Dados 
Packages e Triggers
Docente:
Dr. Vicente
CC-FCUAN-2018
3º Ano
Domingos Kiassungua
Edson Cabina
Bernardo Issenguel
Monitores:
01/11/2018 7:23
Sumário
Package
Estrutura de Packages
Triggers
Exercícios
01/11/2018 10:33CC-FCUAN- 2018
Exercícios
Tipos de Triggers
TABELAS
1. Categoria(codigo, designacao, salario) 
2. Funcionario(codigo, nome, genero, data_nasc, 
premio, _ _ _ _ _ _ _ _) 
3. Cliente(codigo, nome, idade, genero) 
4. Produto(codigo, nome, preco, qtd) 
5. Venda(codigo, qtd, data, _ _ _ _ _ _ _ , _ _ _ _ _ _ _ _, 
_ _ _ _ _ _ _ _ _).
01/11/2018 7:23CC-FCUAN- 2018
cod_funcionario
cod_produto
cod_categoria
cod_cliente
01/11/2018 7:23CC-FCUAN- 2018
INTRODUÇÃO
❑Packages: são blocos PL/SQL que têm a caracteristica de 
funcionar como repositório que pode agrupar objectos.
❑ Dentro de um package pode conter: 
➢ Stored Procedure e Functions 
➢ Declaração de váriaveis.
➢ Cursores
➢ Excepções
01/11/2018 7:23CC-FCUAN- 2018
STRUTURA DE PACKAGE 
Package é formado por no maximo duas secções.
❑ especification (especificação).
❑ body (corpo).
Não podemos ter um package body sem o package 
especification.
Não se pode aceder a um programa em um package body 
sem estar declarado em um package especification 
PACKAGE ESPECIFICATION
No package especification pode conter:
❑ Declaração de variáveis e constantes.
❑ Especificação de procedures e functions.
❑ Declaração de cursores.
❑ Declaração de exceptions.
❑ Declaração de types.
CC-FCUAN- 2018 01/11/2018 7:23
PACKAGE BODY
No package body pode conter:
❑ Códigos PL/SQL.
❑ Códigos de procedures e functions.
❑ Declaração de variáveis e constantes.
❑ Declaração de cursores.
❑ Declaração de exceptions.
❑ Declaração de types.
CC-FCUAN- 2018 01/11/2018 7:23
PACKAGES - SINTAXE DE DECLARAÇÃO
❑ Package especification
❑ Package body
CC-FCUAN- 2018 01/11/2018 7:23
create package nome_package is
procedure nome_procedure;
email varchar2(255);
end nome_package;
Create package body 
nome_package is
procedure nome_procedure is
begin
end;
end nome_package;
Criar um package para 
área de vendas que contém 
uma procedure que lista 
todos produtos vendidos 
em uma determinada data.
CREATE PACKAGE vendas IS
PROCEDURE apresentar_venda_por_data(data date);
END vendas;
CC-FCUAN- 2018 01/11/2018 7:23
Exemplo – Packages (especification)
Criar um package para 
área de vendas que 
contém uma procedure 
que lista todos produtos 
vendidos em uma 
determinada data.
CREATE PACKAGE BODY vendas IS
PROCEDURE apresentar_venda_por_data(data date) IS
CURSOR c_vendas IS
SELECT p.nome as nome FROM venda v, produto p
WHERE p.codigo = v.cod_produto AND v.data = data;
v_venda c_vendas%ROWTYPE;
BEGIN
FOR v_venda in c_vendas LOOP
DBMS_OUTPUT.PUT_LINE(‘Nome:’||v_venda.nome);
END LOOP;
END;
END vendas;
CC-FCUAN- 2018 01/11/2018 7:23
Exemplo – Packages (body)
Package – Chamando membros
DECLARE
v_data date;
BEGIN
v_data :=:INSIRA_DATA;
vendas.apresentar_venda_por_data(v_data);
END
CC-FCUAN- 2018 01/11/2018 7:23
EXERCÍCIOS
1. Crie um packege (especification e body) para área
administrativa (funcionários) que contenha o seu
respectivo CRUD.
2. Crie um package (especification e body) contendo todas
a function’s resolvidas no laboratório 4 (aula prática 4).
3. Crie um packge (especification e body) que contém
stored procedures que tratam da estátistica das vendas, ou
seja, o package deve conter stored procedures que
respodem as seguintes questões: qual é o produto mais
vendido?, qual é a média das vendas no primeiro
bimestre,trimestre e semestre?, qual é o produto menos
requisitado?, etc.
01/11/2018 7:23CC-FCUAN- 2018
TRIGGERS
É um bloco PL/SQL que dispara (executa) automaticamente 
quando uma determinada acção ocorre em um SGBD.
Este disparo pode acontecer mediante a dois eventos:
❑ Por alterações feitas em registos de uma determinada tabela.
❑ Por acções feitas a nível de sistema.
CC-FCUAN- 2018 01/11/2018 7:23
TRIGGERES – TIPOS DE TRIGGERS
Existem dois tipos de triggers:
❑ Triggers de base de dados.
❑ Triggers de sistema.
CC-FCUAN- 2018 01/11/2018 7:23
TRIGGERS – TRIGGER DE BASE DE DADOS
Triggers de base de dados são triggers associadas 
obrigatoriamente a uma tabela. E elas podem ser de dois 
tipos:
❑ Triggers de tabela
❑ Triggers de linha.
CC-FCUAN- 2018 01/11/2018 7:23
TRIGGER DE TABELA
Triggers que executam uma única vez independente do 
número de linhas afectadas pelo comando dml.
❑DECLARAÇÃO
CC-FCUAN- 2018 01/11/2018 7:23
CREATE OR REPLACE TRIGGER nome_trigger
BEFORE DELETE OR INSERT OR UPDATE OF coluna ON nome_tabela
BEGIN
<instruções>
END;
TRIGGER DE TABELA
ONDE:
❑ create or replace trigger: cria ou substitui uma trigger.
❑ nome_trigger: É o nome dado a trigger no acto da sua criação ou 
actualização.
❑ before: Define que o disparo da trigger será feito antes da inserção 
(insert), remoção (delete) ou actualização (update) na tabela referida. 
Outra alternativa seria definirmos como after e neste caso o disparo 
acontece depois de um comando dml.
❑ begin: Inicia o bloco de instruções das triggers.
❑ end: Encerra o bloco de instruções das triggers.
CC-FCUAN- 2018 01/11/2018 7:23
Crie uma trigger que 
diminui a quantidade de 
um produto assim que o 
mesmo for vendido.
CREATE OR REPLACE TRIGGER diminui_qtd_produto
AFTER INSERT ON venda
DECLARE
v_codigo venda.codigo%TYPE;
v_qtd venda.qtd%TYPE;
v_cod_produto venda.cod_produto%TYPE;
BEGIN
SELECT MAX(v.codigo) INTO v_codigo FROM venda v;
SELECT v.qtd,v.cod_produto INTO v_qtd, v_cod_produto
FROM venda v WHERE v.codigo = v_codigo;
UPDATE produto p set p.qtd = (qtd-v_qtd) 
WHERE p.codigo = v_cod_produto; 
END;CC-FCUAN- 2018
Exemplo 1:
TRIGGER DE LINHA
Triggers que executam para cada linha afectada por um comando dml.
❑ DECLARAÇÃO.
CC-FCUAN- 2018 01/11/2018 7:23
CREATE OR REPLACE TRIGGER nome_trigger
BEFORE INSERT OR DELECT OR UPDATE ON nome_tabela
REFERENCING OLD AS VELHO
NEW AS NOVO
FOR EACH ROW
BEGIN
<instruções>
END;
TRIGGER DE LINHA
ONDE:
❑nome_trigger: É o nome dado a trigger no acto da sua criação ou 
actualização.
❑ before: Define que o disparo da trigger será feito antes da inserção 
(insert), remoção (delete) ou actualização (update) na tabela referida. 
Outra alternativa seria definirmos como after e neste caso o disparo 
acontece depois de um comando dml.
❑ for each row: implica dizer que a trigger vai ser executada para cada 
linha afectada pelo comando dml.
❑ referencing old e new: referências que apontam para os dados novos e 
anteriores a respectivamente.
CC-FCUAN- 2018 01/11/2018 7:23
Crie uma nova tabela 
(Audi_vendas), que contém 
o código do produto, 
funcionário e a data da 
venda. E em seguida crie 
uma trigger que preencha 
esta tabela assim que uma 
ou mais vendas forem 
removidas.
CREATE OR REPLACE TRIGGER add_venda_estornada
AFTER DELETE ON venda 
REFERENCING OLD AS v 
FOR EACH ROW 
BEGIN
INSERT INTO audi_venda
VALUES(:v.cod_produto,:v.cod_funcionario,:v.data);
END;
CC-FCUAN- 2018 01/11/2018 7:23
Exemplo 2:
EXERCÍCIOS
4. Crie uma trigger que regista o histórico de alterações de
salários da tabela categoria.
5. Crie uma trigger que faça a atribuição à um novo
funcionário um prémio de 5% do salário de sua categoria.
6. Crie uma trigger que penalise um funcionário com a
categória diferente de balconista, reduzindo 25% em seu
salário.
01/11/2018 7:23CC-FCUAN- 2018
TRIGGERS DE SISTEMA
❑ São objetos criados a nível de sistema e não de tabelas. 
Astriggers de sistemas são executadas sempre que uma 
operação acontece em um SGBD.
❑ Também temos a disposição os atributos de evento que
nos dão informações sobre o banco de dados, transações e 
sobre as operações que disparam o trigger
❑ Tanto alista de eventos que ocorrem no sistema, quanto 
seus atributos são listados em duas tabelas a baixo.
CC-FCUAN- 2018 01/11/2018 11:38
LISTA DE EVENTOS DE SISTEMA
NOME EVENTO DESCRIÇÃO DO EVENTO
after logon Depois de uma conexão ser completada no banco de dados.
before create / after create Quando um objeto é criado no banco de dados
before alter / after alter Quando um objeto é alterado no banco de dados
before drop / after drop Quando um objeto é eliminado do banco de dados
before logoff Quando o usuário desconecta do banco de dados
before commit / after commiT Quando um commit é executado
before ddl / after ddl Quando um comando ddl é executado
startup Quando o banco de dados é aberto
CC-FCUAN- 2018 01/11/2018 11:35
LISTA DE ATRIBUTOS DE EVENTOS
ATRIBUTO DESCRIÇÃO
ora_client_ip_address Retorna o IP da máquina cliente no evento de 
LOGON, se o protocolo for TCP/IP
ora_database_name nome do banco de dados;
ora_dict_obj_name nome do objeto que sofreu o evento ddl
ora_dict_obj_ownerR proprietário (utulizador/schema) do objeto que sofreu 
o evento ddl
ora_dict_obj_owner_list retorna lista de proprietários (usuário/schemas) dos 
objetos afetados no evento;
CC-FCUAN- 2018 01/11/2018 11:49
Cria uma trigger que 
regista o histórico do 
utilizador. Para tal 
crie também a tabela 
utilizador 
CREATE OR REPLACE TRIGGER 
regista_hist_utilizador
AFTER LOGON ON database
BEGIN
INSERT INTO hist_utilizador VALUES
(ORA_LOGIN_USER,sysdate,’Conexão'||ORA
_DATABASE_NAME);
END;
CC-FCUAN- 2018 01/11/2018 11:55
Exemplo 3