333107183-Guilherme-Unidade1-pdf

Prévia do material em texto

GUILHERME DUARTE 
 
 
 
 
 
 
 
 
 
 
 
 
 
Banco de Dados 2 
Exercícios Unidade 1 
 
Documento apresentado como composição de nota de 
prevista no plano da ensino da disciplina de Interface 
Banco de Dados 2, no Curso de Tecnologias da 
Informação e Comunicação, na Universidade Federal de 
Santa Catarina. 
 
Prof. Dr. Alexandre Leopoldo Gonsalves. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Araranguá, 2016 
 
1) Explique em linhas gerais o funcionamento da SQL Dinâmica considerando a 
especificação/implementação JDBC e JPA. 
 
Com o SQL dinâmico é possível, por exemplo, realizar um procedimento que opere sobre os dados de 
uma tabela. É possível também utilizar a linguagem DDL, onde é possível criar, alterar, remover objetos dentro 
de um procedimento ou bloco PL/SQL. 
 
2) Considerando a função de atualização de Salário (AtualizaSalario) apresentada em Oracle e 
PostgreSQL faça seguintes modificações: 
 
A) Crie uma tabela de categoria com código e percentual; 
B) Altere a tabela empregado para que esta possua uma referência para uma tabela de categoria que possui o 
percentual de aumento; 
 
C) Durante o loop de atualização deve ser chamada uma função que, recebendo o código da categoria, recupere 
a tupla correspondente e retorne o percentual; 
D) Somente depois disso a tupla da tabela empregado deve ser atualizada com o novo salário utilizando o 
percentual obtido na tabela de categoria. 
 
CREATE TABLE EMPREGADO 
(CODIGO NUMBER, 
NOME VARCHAR2(50), 
SALARIO NUMBER(10,2)); 
BEGIN 
DELETE FROM EMPREGADO; 
INSERT INTO EMPREGADO (CODIGO, NOME, SALARIO) VALUES (1, 'Empregado 1', 1500.00); 
INSERT INTO EMPREGADO (CODIGO, NOME, SALARIO) VALUES (2, 'Empregado 2', 2500.00); 
INSERT INTO EMPREGADO (CODIGO, NOME, SALARIO) VALUES (3, 'Empregado 3', 3200.00); 
INSERT INTO EMPREGADO (CODIGO, NOME, SALARIO) VALUES (4, 'Empregado 4', 900.00); 
INSERT INTO EMPREGADO (CODIGO, NOME, SALARIO) VALUES (5, 'Empregado 5', 1130.00); 
END; 
SELECT * FROM EMPREGADO; 
CREATE OR REPLACE PROCEDURE AtualizaSalario ( percentual NUMBER ) IS 
codigoe number; 
salarioe number(10,2); 
cursor c1 is 
 
select codigo, salario from empregado; 
BEGIN 
open c1; 
loop 
fetch c1 into codigoe, salarioe; 
exit when c1%notfound; 
salarioe := salarioe * (1 + (percentual / 100)); 
update empregado 
set salario = salarioe 
where codigo = codigoe; 
end loop; 
commit; 
close c1; 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(-20001,'Ocorreu um erro durante execução do procedimento AtualizaSalario - 
'||SQLCODE||' -ERRO- '||SQLERRM); 
rollback; 
END; 
/ 
begin 
AtualizaSalario(12.5); 
end; 
/ 
SELECT * FROM EMPREGADO; 
 
CREATE OR REPLACE FUNCTION CalculaAumento(salario IN NUMBER, percentual IN NUMBER) 
RETURN NUMBER IS 
BEGIN 
RETURN salario * (1 + (percentual / 100)); 
END CalculaAumento; 
/ 
select salario, CalculaAumento(salario, 11.2) from empregado; 
update empregado 
set salario = CalculaAumento(salario, 11.2); 
CREATE OR REPLACE PACKAGE PSalario AS 
PROCEDURE AtualizaSalario ( percentual NUMBER ); 
FUNCTION CalculaAumento(salario IN NUMBER, percentual IN NUMBER) RETURN NUMBER; 
END PSalario; 
/ 
CREATE OR REPLACE PACKAGE BODY PSalario AS 
PROCEDURE AtualizaSalario ( percentual NUMBER ) IS 
codigoe number; 
salarioe number(10,2); 
cursor c1 is 
select codigo, salario from empregado; 
BEGIN 
open c1; 
loop 
 
fetch c1 into codigoe, salarioe; 
exit when c1%notfound; 
salarioe := salarioe * (1 + (percentual / 100)); 
update empregado 
set salario = salarioe 
where codigo = codigoe; 
end loop; 
commit; 
close c1; 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(-20001,'Ocorreu um erro durante execução do procedimento AtualizaSalario - 
'||SQLCODE||' -ERRO- '||SQLERRM); 
 
END AtualizaSalario; 
FUNCTION CalculaAumento(salario IN NUMBER, percentual IN NUMBER) 
RETURN NUMBER IS 
BEGIN 
RETURN salario * (1 + (percentual / 100)); 
END CalculaAumento; 
END PSalario; 
/ 
begin 
PSalario.AtualizaSalario(12.5); 
end; / 
 
select salario, PSalario.CalculaAumento(salario, 11.2) from empregado; 
update empregado 
set salario = PSalario.CalculaAumento(salario, 11.2); 
CREATE OR REPLACE TRIGGER ListaMudancaEmpregado 
BEFORE DELETE OR INSERT OR UPDATE ON Empregado 
FOR EACH ROW 
WHEN (new.codigo > 0) 
DECLARE 
diferenca number; 
BEGIN 
diferenca := :new.salario - :old.salario; 
dbms_output.put('Salário antigo: ' || :old.salario); 
dbms_output.put(' Novo salário: ' || :new.salario); 
dbms_output.put_line(' Diferença: ' || diferenca); 
END; 
/ 
CREATE TABLE DESPESA 
(CODIGO NUMBER, -- 1 Refere-se a salários 
TOTAL NUMBER); 
/ 
insert into despesa (codigo, total) values (1, 0.0); 
DROP TRIGGER SumarizaSalario; 
CREATE OR REPLACE TRIGGER SumarizaSalario 
AFTER DELETE OR INSERT OR UPDATE ON Empregado 
DECLARE 
vtotal number; 
 
cursor c1 is 
select sum(salario) from empregado; 
BEGIN 
open c1; 
fetch c1 into vtotal; 
update despesa 
set total = vtotal 
where codigo = 1; 
close c1; 
END; 
 
3) Altere a função AtualizaSalario para que esta trate de maneira adequada a saída do comando de 
iteração (LOOP) assim como eventuais erros (exceções) de processamento na versão PostgreSQL. 
 
CREATE OR REPLACE FUNCTION AtualizaSalario ( percentual NUMERIC ) RETURNS integer AS $$ 
DECLARE 
codigoe INTEGER; 
salarioe NUMERIC; 
ctr INTEGER=0; 
c1 CURSOR FOR SELECT codigo, salario FROM empregado; 
 
BEGIN 
OPEN c1; 
LOOP 
FETCH c1 INTO codigoe, salarioe; 
ctr := ctr + 1; 
EXIT WHEN ctr > 5; 
salarioe := salarioe * (1 + (percentual / 100)); 
UPDATE empregado 
SET salario = salarioe 
WHERE codigo = codigoe; 
END LOOP; 
CLOSE c1; 
RETURN 1; 
END; 
$$ LANGUAGE plpgsql; 
 
4) Considerando o esquema a seguir elabore um PSM (procedimento armazenado) em PL/SQL (Oracle) 
ou PL/pgSQL (Postgre) que sumarize o total das consultas por tipo de convênio (tabela Tipo) persistindo 
essas informações na tabela consulta_total. O conceito de cursor deve ser utilizado. O procedimento 
deve ainda receber a informação de qual tipo de convênio deve ser processado. 
 
CREATE OR REPLACE PROCEDURE prAtualizaTotalConsultas(tipop integer) IS 
soma numeric; 
cursor c1 is 
select sum(valor) from consulta where cod_tipo = tipop; 
BEGIN 
 open c1; 
 fetch c1 into soma; 
 close c1; 
 insert into consulta_total (cod_tipo, total) values (tipop, soma); 
 commit; 
EXCEPTION 
 WHEN DUP_VAL_ON_INDEX THEN 
update consulta_total 
 
set total = soma 
where cod_tipo = tipop; 
commit; 
WHEN OTHERS THEN 
raise_application_error(-20001,'Ocorreu um erro durante execução do procedimento prAtualizaTotalConsultas - 
'||SQLCODE||' -ERRO- '||SQLERRM); 
End; 
5) Discuta as vantagens e desvantagens da utilização de PSMs. 
 
Vantagens 
- Desempenho - se criarmos uma stored procedure para executar uma consulta, o usuário precisará de apenas 
um comando para executar a consulta: EXEC nomeProcedimento. Uma busca simples como “select * from 
tabela” seria executado a cada chamada, enquanto o procedimento contendo a consulta seria compilado uma 
única vez. 
- Pode ser armazenado no servidor e invocado por qualquer um dos programas de aplicação; 
- Redução de transferência de dados entre cliente e servidor; 
 
Desvantagens 
- Impactam no desenvolvimento de aplicações independentes de banco de dados uma vez que esse tipo de 
abordagem utiliza linguagens proprietárias; 
- Muitos SGDBs possibilitam que procedimentos armazenados sejam escritos em linguagem de programação de 
uso geral; 
- Dependência da base de dados: se por acaso haver a necessidade de mudarmos de base por algum motivo 
qualquer seriamos obrigado a reescrever todas as storeds procedures o que seria muito trabalhoso se existirem 
muitas na base 
 
6) Implemente um trigger que após cada inserção/alteração/exclusão na tabela de consulta faça a 
sumarização na tabela consulta_total. Isso deve ser realizado invocando o procedimento armazenadodo 
exercício 4. 
 
CREATE OR REPLACE TRIGGER SUMARIZACAO 
AFTER INSERT OR UPDATE OR DELETE ON CONSULTA_TOTAL 
vtotal number; 
cursor c1 is 
select sum(salario) from empregado; 
BEGIN 
open c1; 
fetch c1 into vtotal; 
update despesa 
set total = vtotal 
where codigo = 1; 
close c1; 
. 
. 
. 
END;