Respostas BD2

Prévia do material em texto

--1a--
CREATE OR REPLACE PROCEDURE pAumentaSalario(pValor NUMBER) IS
vAumento Number :=1+(pValor/100);
CURSOR curEmp is
	SELECT first_name||* *|| lastname as nome_completo, job_id, salary
	FROM employees
		ORDER BY first_name;
recEMp curEmp%ROWTYPE;
BEGIN
	UPDATE employees
	SET SALARY = SALARY*vAumento;
	
	FOR recEMp in curEmp LOOP
		DBMS_OUTPUT.PUTLINE('nome_completo:' || recEMp.nome_completo ||
		'Função: ' || recEMp.job_id ||
		'Salario Antigo: ' ||recEMp.salary ||
		'Salario Novo: ' || recEMp.salary*vAumento);
	END LOOP
	
	EXCEPTION
		WHEN OTHERS THEN
			RAISE_APPLICATION_ERROR('-200001','Erro Inesperado');
	END;
	
--1b--
CREATE OR REPLACE PROCEDURE pAniversariantes(mes NUMBER) IS
CURSOR curFuncionarios is
	SELECT first_name||* *|| lastname as nome_completo, phone_number, hire_date
	FROM employees
	WHERE EXTRACT (MONTH FROM hire_date) = pMes;
		ORDER BY hire_date;
recFuncionarios curFuncionarios%ROWTYPE;
BEGIN
	FOR recFuncionarios IN curFuncionarios LOOP
		DBMS_OUTPUT.PUTLINE('Nome: ' || recFuncionarios.nome_completo || ' ' || recFuncionarios.phone_number || ' ' || recFuncionarios.email || 
		recFuncionarios.hire_date);
	END LOOP;
		
END;
	
--1c--
CREATE OR REPLACE PROCEDURE pFolhaPagamento IS
CURSOR curFolha IS
	SELECT D.departament_name, SUM(salary)
	FROM departaments D, employees E 
	WHERE D.departament_id = E.departament_id
	GROUP BY departament_name;
	
recFolha curFolha%ROWTYPE;
	
BEGIN
	
	FOR recFolha IN curFolha LOOP
		DBMS_OUTPUT.PUTLINE(
			'Departamento' || recFolha.departament_name ||
			'Soma Salario' || recFolha.total);
	END LOOP;
	
END;
--1d--
CREATE OR REPLACE PACKAGE pckEmployee as
	procedure pFolhaPagamento;
	procedure pAniversariantes(pMes NUMBER);
	procedure pAumentaSalario(pValor NUMBER);
	
END;