Este es el Certamen 2 de PROGRAMA DE BASES DE DATOS (OCA DEVELOP)
[ 1 ] Crear una tabla EMP , una función CAL_NEWSAL, los los procedimientos UPD_NEWSAL , NEW_EMP y el trigger VAL_SALARY
para insertar y actualizar datos de un empleado (55 pts)
1A Crear una tabla EMP con información a partir de una consulta que seleccione el
employee_id,first_name,last_name y salary de la tabla EMPLOYEES (4 pts)
CREATE TABLE emp AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY
FROM employees;
1B Agrega a la tabla EMP dos nuevas columnas PERCENT de tipo NUMBER(8,2) y NEW_SALARY de tipo NUMBER de (8,2) (5 pts)
ALTER TABLE emp ADD percent NUMBER(8,2);
ALTER TABLE emp ADD new_salary NUMBER(8,2);
1C Crear una Funcion llamada CALL_NEWSAL que calcule el nuevo salario del empleado a partir del salariode éste
y un factor que representa el porcentaje en términos decimales. La formula para calcular el nuevo slario es: (6 pts)
(v_salary + ((v_salary – (v_salary * 12 * v_factor)) * v_factor))
CREATE OR REPLACE FUNCTION cal_newsal (v_salary IN employees.SALARY%TYPE,v_factor IN NUMBER) RETURN NUMBER
IS
BEGIN
RETURN (v_salary + ((v_salary - (v_salary * 12 * v_factor)) * v_factor));
END cal_newsal;
/
1D CreValid Employee ID
CREATE OR REPLACE FUNCTION valid_employee(v_employee_id IN employees.EMPLOYEE_ID%TYPE) RETURN BOOLEAN
IS
v_temporal VARCHAR(1);
BEGIN
SELECT 'x'
INTO v_temporal
FROM emp
WHERE EMPLOYEE_ID = v_employee_id;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
/
1D Crear un Procedimiento UPD_NEWSAL que permita actualizar, en la columna PERCENT el porcentaje de
incremento y en la columna NEW_SAL el nuevo incremento y en la columna NEW_SAL el nuevo salario. Para ello deberá pasar
el identificador del empleado y la función que calcula el nuevo salario creada en el punto anterior. En caso de actualizar
un empleado que no existe dse deberá informar lo ocurrido a través de un mensaje apropiado. Finalmente hacer los cambios permanentes.
CREATE OR REPLACE PROCEDURE upd_newsal(v_employee_id IN employees.EMPLOYEE_ID%TYPE,v_factor IN NUMBER)
IS
empleado_salary employees.SALARY%TYPE;
BEGIN
IF valid_employee(v_employee_id) THEN
SELECT SALARY
INTO empleado_salary
FROM emp
WHERE EMPLOYEE_ID = v_employee_id;
UPDATE emp
SET NEW_SALARY = cal_newsal (empleado_salary,v_factor)
WHERE EMPLOYEE_ID = v_employee_id;
ELSE
RAISE_APPLICATION_ERROR(-20001,'ERROR: El id de Usuario No Existe');
END IF;
END upd_newsal;
/
1E Testear el procedimiento UPD_NEWSAL para los empleados 100,105,110, con un factor de 0.01,0.02 y 0.03 correspondiente (3 pts)
SET SERVEROUTPUT ON
BEGIN
upd_newsal(100,0.01);
END;
/
SET SERVEROUTPUT ON
BEGIN
upd_newsal(105,0.05);
END;
/
SET SERVEROUTPUT ON
BEGIN
upd_newsal(110,0.03);
END;
/
1F Crear un PROCEDIMIENTO NEW_EMP que permita para un nuevo empleado ingresar employee_id,first_name,last_name,salary,percent,new_salary,
para el new_salary utilizar la funcion cal_newsal. Conjuntamente deberá validar el salario, a través de un trigger de DML por fila llamado
VAL_SALARY que controle que el salario no debe exceder de 25.000 (18 pts)
CREATE OR REPLACE TRIGGER val_salary
BEFORE INSERT OR UPDATE OF SALARY
ON emp
FOR EACH ROW
BEGIN
IF :new.SALARY > 25000 THEN
RAISE_APPLICATION_ERROR(-20002,'ERROR: El salario sobrepasa los 25.000');
END IF;
END val_salary;
/
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE new_emp(v_employee_id IN employees.EMPLOYEE_ID%TYPE,
v_first_name IN employees.FIRST_NAME%TYPE,
v_last_name IN employees.LAST_NAME%TYPE,
v_salary IN employees.SALARY%TYPE,
v_percent IN NUMBER
)
IS
nuevoSalario NUMBER;
BEGIN
IF valid_employee(v_employee_id) THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Ya Hay un usuario con esa ID');
ELSE
nuevoSalario := cal_newsal(v_salary,v_percent);
INSERT INTO emp (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,PERCENT,NEW_SALARY)
VALUES( v_employee_id,
v_first_name,
v_last_name,
v_salary,
v_percent,
nuevoSalario
);
END IF;
END new_emp;
/
1G TESTEAR EL PROCEDIMIENTO NEW_EMP Para los siguientes datos (2 pts)
Fila1:
employee_id:200, first_name: Albert,last_name: Einstein, salary: 20000, percent: 0.01, new_salary: cal_newsal
Fila2:
employee_id:300, first_name: Hilary,last_name: Clinton, salary: 26000, percent: 0.02, new_salary: cal_newsal
SET SERVEROUTPUT ON
BEGIN
new_emp(200,'Albert','Einstein',20000,0.01);
END;
/
SET SERVEROUTPUT ON
BEGIN
new_emp(300,'Hilary','Clinton',20000,0.02);
END;
/
1H Elimina tabla EMP (1 pts)
DROP TABLE emp;
1I Elimina Funcion cal_newSal (1 pts)
DROP FUNCTION cal_newsal;
1J Elimina Procedure upd_newsal (1 pts)
DROP PROCEDURE upd_newsal;
1K Elimina Procedure new_emp (1 pts)
DROP PROCEDURE new_emp;
1L Elimina Trigger val_salary (1 pts)
DROP TRIGGER val_salary;
[ 2 ] CREAR Y LLAMAR UN PACKAGE QUE CONTIENE CONSTRUCTORES PÚBLICOS Y PRIVADOS (45 pts)
2A Crear una tabla EMP con información, a partir de una consulta que seleccione el employee_id,first_name,
last_name y department_id de la tabla EMPLOYEES (4 pts)
CREATE TABLE emp AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID
FROM employees;
2B Crear una especificacion y un cuerpo de package llamado EMP_PACK que contiene un procedimiento
NEW_EMP como constructor público, y una función VALID_DEPT_ID como un constructor privado (29 pts)
CREATE OR REPLACE PACKAGE emp_pack IS
PROCEDURE NEW_EMP( v_employee_id IN employees.EMPLOYEE_ID%TYPE,
v_first_name IN employees.FIRST_NAME%TYPE,
v_last_name IN employees.LAST_NAME%TYPE,
v_department_id IN employees.DEPARTMENT_ID%TYPE
);
END emp_pack;
/
SET SERVEROUTPUT ON
CREATE OR REPLACE PACKAGE BODY emp_pack IS
---------------------------------------------------------------------------------
-- FUNCION PARA VALIDAR EL DEPARTAMENTO RETURN [BOOLEAN]
---------------------------------------------------------------------------------
FUNCTION VALID_DEPT_ID(v_id_depto IN employees.DEPARTMENT_ID%TYPE) RETURN BOOLEAN
IS
v_temporal VARCHAR(1);
BEGIN
SELECT 'x'
INTO v_temporal
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = v_id_depto;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
---------------------------------------------------------------------------------
-- PROCEDIMIENTO NEW_EMP
---------------------------------------------------------------------------------
PROCEDURE NEW_EMP( v_employee_id IN employees.EMPLOYEE_ID%TYPE,
v_first_name IN employees.FIRST_NAME%TYPE,
v_last_name IN employees.LAST_NAME%TYPE,
v_department_id IN employees.DEPARTMENT_ID%TYPE
)
IS
BEGIN
IF VALID_DEPT_ID(v_department_id) THEN
INSERT INTO emp(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID)
VALUES(v_employee_id,v_first_name,v_last_name,v_department_id);
ELSE
DBMS_OUTPUT.PUT_LINE('ERROR: No existe Departamento');
END IF;
END;
END emp_pack;
/
2C Llamar al procedimiento NEW_EMP, utilizando como identificador de departamento el número 15. Como
el departamento 15 no existe en la tabla DEPARTMENT, se deberá desplegar un mensaje informando lo ocurrido (5 pts)
SET SERVEROUTPUT ON
BEGIN
emp_pack.NEW_EMP(305,'Juan','Perez',15);
END;
/
2D Eliminar Tabla emp (1 pts)
DROP TABLE emp;
2E Eliminar funcion VALID_DEPT_ID (1 pts)
DROP FUNCTION emp_pack.VALID_DEPT_ID;
2F Eliminar procedimiento NEW_EMP (1 pts)
DROP PROCEDURE emp_pack.NEW_EMP;
2G Eliminar especificacion y cuerpo del package llamado EMP_PACK (1 pts)
DROP PACKAGE emp_pack;
