PROGRAMACION BASE DE DATOS
Enviado por clriosm • 8 de Mayo de 2015 • 1.619 Palabras (7 Páginas) • 152 Visitas
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)
1
2
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)
...