BEGIN OPEN c FOR SELECT FROM empregado; SELECT COUNT() INTO tot FROM empregado;

(1)

Centro de Ciências – CC Departamento de Computação - DC Fundamentos de Bancos de Dados

Exercício: Triggers e Procedures

Objetivos: Utilização de triggers e procedures.

Lista de Exercícios

1. ESQUEMA DE TRABALHO

EMPREGADO EMP_MATRICULA EMP_NOME EMP_SEXO

BANCO_HORAS BAN_ANO BAN_MES

EMP_MATRICULA (FK) BAN_TOTAL_HORAS FREQUENCIA

FREQ_DATA

EMP_MATRICULA (FK) FREQ_HORA_ENTRADA FREQ_HORA_SAIDA

FREQ_HORAS_EXCEDENTES FREQ_HORAS_NOTURNAS FREQ_OBS

PERIODO PER_ANO PER_MES

(2)

2. ATIVIDADES

2.1. Crie uma stored procedure que receba como parâmetro de entrada três parâmetros a matrícula do

empregado(int), o ano(int) e o mês(int). Este procedimento deve inserir uma tupla no banco de horas referente ao empregado e período em questão.

2.2. Crie uma stored procedure que receba como parâmetro de entrada dois parâmetros o ano(int) e o

mês (int). Este procedimento deve inserir uma tupla no banco de horas para todos os empregados referente período em questão.

mês(int). Este procedimento deve retornar a matrícula do empregado com maior número de horas no banco de horas.

2.4. Crie uma stored procedure que receba como parâmetro de entrada quatro parâmetros a matrícula

do empregado(int), o ano(int), o mês(int) e o último dia do mês(int). Este procedimento deve inserir um conjunto de tuplas na relação freqüência referentes ao empregado e período em questão.

2.5. Crie uma stored procedure que receba como parâmetro de entrada três parâmetros a matrícula do

empregado(int), o ano(int) e o mês(int). Este procedimento deve atualizar o valor do banco de horas neste mês para o empregado em questão.

mês (int). Este procedimento deve atualizar o valor do banco de horas neste mês para todos os funcionários.

2.7. Crie um trigger sobre a relação período com a seguinte finalidade: Quando um período for

inserido deve-se inserir também, para todos os empregados, na relação freqüência, uma tupla para cada dia no período em questão. Além disso, deve-se inserir também uma tupla para cada empregado no banco de horas.

2.8. Crie um trigger sobre a tabela freqüência com o objetivo de atualizar o banco de horas sempre a

freqüência for atualizada.

2.9. Crie um trigger para que ao se excluir um período sejam excluídas também todas as freqüências

referentes a este período. Deve-se excluir também todas as tuplas no banco de horas referentes a este período.

2.10. Crie um trigger para manter a seguinte regra de negócio. Cada empregado que trabalha em um dia feriado deve receber seis horas no banco de horas. Além disso, cada hora extra em um feriado vale o dobro no banco de horas. Considere a inserção e a remoção de um feriado.

(3)

CREATE OR REPLACE FUNCTION questao1(mat INTEGER, ano INTEGER, mes INTEGER) RETURNS VOID AS

$body$ BEGIN

INSERT INTO banco_horas(ban_ano, ban_mes, emp_matricula, ban_total_horas)

VALUES (ano, mes, mat,0); RETURN;

END; $body$

LANGUAGE plpgsql;

---TESTE---

INSERT INTO empregado(emp_matricula, emp_nome, emp_sexo) VALUES (1, 'Joao Paulo', 'M'); INSERT INTO empregado(emp_matricula, emp_nome, emp_sexo) VALUES (2, 'Maria Alice', 'F'); INSERT INTO empregado(emp_matricula, emp_nome, emp_sexo) VALUES (3, 'Carlos Eduardo', 'M'); INSERT INTO empregado(emp_matricula, emp_nome, emp_sexo) VALUES (4, 'Fernando', 'M');

INSERT INTO empregado(emp_matricula, emp_nome, emp_sexo) VALUES (5, 'Claudia', 'F'); SELECT questao1(1,2015,1);

SELECT questao1(2,2015,1); SELECT questao1(3,2015,1); SELECT questao1(4,2015,1); SELECT questao1(5,2015,1);

--- QUESTÃO 2 ---

CREATE OR REPLACE FUNCTION questao2(ano INTEGER, mes INTEGER) RETURNS VOID AS

$body$ DECLARE

c REFCURSOR;

reg empregado%ROWTYPE; i INTEGER;

tot INTEGER; BEGIN

OPEN c FOR SELECT * FROM empregado;

SELECT COUNT(*) INTO tot FROM empregado; i:=1;

FOR i IN 1..tot LOOP FETCH c INTO reg;

PERFORM questao1(reg.emp_matricula,ano,mes); END LOOP;

CLOSE c; RETURN; END;

$body$

(4)

--- QUESTÃO 3 – S1 ---

CREATE OR REPLACE FUNCTION questao3(ano INTEGER, mes INTEGER) RETURNS INTEGER AS

$body$ DECLARE

matricula INTEGER; BEGIN

SELECT emp_matricula INTO matricula FROM banco_horas

WHERE ban_ano=ano AND ban_mes=mes AND ban_total_horas=all (SELECT MAX(ban_total_horas)

FROM banco_horas

WHERE ban_ano=ano AND ban_mes=mes); RETURN matricula;

END; $body$

--- QUESTÃO 3 – S2 ---

CREATE OR REPLACE FUNCTION questao3(ano INTEGER, mes INTEGER) RETURNS INTEGER AS

$body$ DECLARE

matricula INTEGER; maxhoras INTEGER; BEGIN

SELECT MAX(ban_total_horas) INTO maxhoras FROM banco_horas

WHERE ban_ano=ano AND ban_mes=mes;

SELECT emp_matricula INTO matricula FROM banco_horas

WHERE ban_ano=ano AND ban_mes=mes AND ban_total_horas=maxhoras; RETURN matricula;

END; $body$

---TESTE---

UPDATE banco_horas SET ban_total_horas = 10 WHERE ban_ano=2015 AND ban_mes=1 AND emp_matricula=1;

(5)

dia INTEGER) RETURNS VOID AS $body$

DECLARE

i INTEGER; BEGIN

i:=1;

FOR i IN 1..dia LOOP

INSERT INTO frequencia

VALUES (mat, CAST(ano || '-' || mes || '-' || i AS DATE), '08:00', '18:00', 0, 0, 'AUDITORIA: Inserido Atraves de SP Exercicio 04');

END LOOP; RETURN; END;

$body$

---TESTE---

SELECT questao4(1,2015,1,31);SELECT questao4(2,2015,1,31);SELECT

questao4(3,2015,1,31);SELECT questao4(4,2015,1,31);SELECT questao4(5,2015,1,31); SELECT questao4(1,2015,2,28);SELECT questao4(2,2015,2,28);SELECT

questao4(3,2015,2,28);SELECT questao4(4,2015,2,28);SELECT questao4(5,2015,2,28);

--- QUESTÃO 5 ---

CREATE OR REPLACE FUNCTION questao5(mat INTEGER, ano INTEGER, mes INTEGER) RETURNS VOID AS

$body$ DECLARE

total_horas REAL; BEGIN

SELECT SUM(freq_horas_excedentes) INTO total_horas FROM frequencia WHERE emp_matricula = mat AND

DATE_PART('year',CAST(freq_data AS TIMESTAMP)) = ano AND DATE_PART('month', CAST(freq_data AS TIMESTAMP))= mes ; UPDATE banco_horas SET ban_total_horas = total_horas

WHERE emp_matricula = mat AND ban_ano = ano AND ban_mes = mes; RETURN;

END; $body$

---TESTE---

UPDATE frequencia SET freq_horas_excedentes = 1 WHERE freq_data=CAST('2015-01-01' AS DATE) AND emp_matricula=1;

(6)

CREATE OR REPLACE FUNCTION questao6(ano INTEGER, mes INTEGER) RETURNS VOID AS

$body$ DECLARE

c REFCURSOR;

reg empregado%ROWTYPE; i INTEGER;

tot INTEGER; BEGIN

OPEN c FOR SELECT * FROM empregado;

SELECT COUNT(*) INTO tot FROM empregado; i:=1;

FOR i IN 1..tot LOOP FETCH c INTO reg;

PERFORM questao5(reg.emp_matricula,ano,mes); END LOOP;

CLOSE c; RETURN; END;

$body$

---TESTE--- SELECT questao6(2015,1);

(7)

--- QUESTÃO 7 ---

CREATE OR REPLACE FUNCTION diasmes(ano INTEGER, mes INTEGER) RETURNS INTEGER AS

$body$ DECLARE

datastr TEXT; datadate DATE; diasmes INTEGER; BEGIN

IF (mes=12) THEN diasmes :=31; ELSE

datastr := ano || '-' || (mes+1) || '-' || '01'; datadate := CAST(datastr AS DATE);

datadate :=datadate - 1;

diasmes :=CAST(DATA_PART('day' , datadate) AS INTEGER); END IF;

RETURN udia; END;

$body$

--- CREATE OR REPLACE FUNCTION questao7() RETURNS TRIGGER AS $body$

DECLARE

cursorEmpregado CURSOR FOR SELECT * FROM empregado; empregadoLinha empregado%ROWTYPE;

diaFinalMes INTEGER; BEGIN

OPEN cursorEmpregado;

FETCH cursorEmpregado INTO empregadoLinha;

diaFinalMes := PERFORM diasmes(new.per_ano,new.per_mes);

--PREECHE O BANCO DE HORAS DE TODOS OS FUNCIONÁRIOS NO PERÍODO INFORMADO PERFORM questao2(new.per_ano,new.per_mes,diaFinalMes);

LOOP

EXIT WHEN NOT FOUND;

-- PREENCHE A FREQUÊNCIA DE UM FUNCIONÁRIO DO MÊS INFORMADO PERFORM questao4(empregadoLinha.emp_matricula, new.per_ano, new.per_mes, diaFinalMes);

FETCH cursorEmpregado INTO empregadoLinha; END LOOP;

CLOSE cursorEmpregado; RETURN new;

END; $body$

(8)

CREATE OR REPLACE FUNCTION questao8() RETURNS TRIGGER AS $body$

DECLARE

ano INTEGER; mes INTEGER; BEGIN

ano:=DATA_PART('year',new.freq_data); mes:=DATA_PART('month',new.freq_data);

--ATUALIZA BANCO DE HORAS

PERFORM questao5(new.emp_matricula,ano,mes );

RETURN new; END;

$body$

---

CREATE TRIGGER tr_atualiza_banco_hora AFTER UPDATE ON frequencia FOR EACH ROW EXECUTE PROCEDURE questao8();

---TESTE---

--- QUESTÃO 9 ---

CREATE OR REPLACE FUNCTION questao9() RETURNS TRIGGER AS $body$

BEGIN

DELETE FROM frequencia

WHERE DATE_PART('month', CAST(freq_data AS TIMESTAMP))= OLD.per_mes AND DATE_PART('year', CAST(freq_data AS TIMESTAMP))= OLD.per_ano;

DELETE FROM banco_horas

WHERE ban_ano = old.per_ano AND ban_mes = old.per_mes; RETURN OLD;

END; $body$

---

CREATE TRIGGER tr_deleta_freq_banco_hora AFTER DELETE ON periodo FOR EACH ROW EXECUTE PROCEDURE questao9();

(9)

CREATE OR REPLACE FUNCTION questao10Insercao() RETURNS TRIGGER AS $body$

DECLARE

cursorEmpregadoFeriado CURSOR IS

SELECT emp_matricula, freq_horas_excedentes FROM frequencia

WHERE freq_data = new.fer_data; empregadoMat INTEGER;

dataFeriado DATE;

horasExtrasExcedentes REAL; BEGIN

OPEN cursorEmpregadoFeriado;

FETCH cursorEmpregadoFeriado INTO empregadoMat, horasExtrasExcedentes; LOOP

UPDATE banco_horas SET ban_total_horas = ban_total_horas + 6 WHERE ban_ano = DATE_PART('year', NEW.fer_data) AND

ban_mes = DATE_PART('month', NEW.fer_data) AND emp_matricula = empregadoMat;

IF horasExtrasExcedentes > 0 THEN

UPDATE banco_horas SET ban_total_horas=ban_total_horas+ horasExtrasExcedentes

WHERE date_part('year' , NEW.fer_data)=ban_ano AND date_part('month', NEW.fer_data)=ban_mes AND emp_matricula = empregadoMat;

END IF;

FETCH cursorEmpregadoFeriado INTO empregadoMat, horasExtrasExcedentes;

END LOOP;

CLOSE cursorEmpregadoFeriado; RETURN NEW;

END; $body$

--- CREATE TRIGGER upd_banco_horas AFTER INSERT ON feriado FOR EACH ROW EXECUTE PROCEDURE questao10Insercao();

---TESTE---

(10)

--- QUESTÃO 10 ---

CREATE OR REPLACE FUNCTION questao10Remocao() RETURNS TRIGGER AS $body$

DECLARE

cursorEmpregadoFeriado CURSOR IS

SELECT emp_matricula, freq_horas_excedentes FROM frequencia

WHERE freq_data = OLD.fer_data; empregadoMat INTEGER;

dataFeriado DATE;

horasExtrasExcedentes REAL; BEGIN

OPEN cursorEmpregadoFeriado;

FETCH cursorEmpregadoFeriado INTO empregadoMat, horasExtrasExcedentes; LOOP

UPDATE banco_horas SET ban_total_horas = ban_total_horas - 6 WHERE ban_ano = DATE_PART('year', OLD.fer_data) AND

ban_mes = DATE_PART('month', OLD.fer_data) AND emp_matricula = empregadoMat;

IF horasExtrasExcedentes > 0 THEN

UPDATE banco_horas SET ban_total_horas=ban_total_horas- horasExtrasExcedentes

WHERE DATE_PART('year', OLD.fer_data)=ban_ano AND DATE_PART('month', OLD.fer_data)=ban_mes AND emp_matricula = empregadoMat;

END IF;

FETCH cursorEmpregadoFeriado INTO empregadoMat, horasExtrasExcedentes;

END LOOP;

CLOSE cursorEmpregadoFeriado; RETURN OLD;

END; $body$

--- CREATE TRIGGER upd2_banco_horas AFTER DELETE ON feriado FOR EACH ROW EXECUTE PROCEDURE questao10Remocao();

---TESTE---