GUILHERME DUARTE
Banco d e Dados 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) Expliqu e em linhas gerais o fu ncionamento da SQL Dinâmica consid erando 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 modif icaçõ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) Alter e a fu nç ão Atual izaSal ari o par a qu e est a tr ate de man eir a adeq uad a a saíd a do co man do de iteração (LOOP) assim com o eventuais erro s (exceções) de proc essamento na versão Postg reSQL . 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 cons ultas por ti po de conv ênio (tabela Tipo) persist indo essas informações na tabela consulta_total. O conceito de cursor deve ser utilizado. O procedimento deve ainda receber a informação de qual tip o 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 consul ta_total. Isso d eve ser realizado invoc ando o p rocedimento armazenado do 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;
