• Author / Uploaded
• Ricardo Herrera

Citation preview

TALLER STORED FUNCTIONS

1. Crear una función llamada ANNUAL_COMP, que dado como parámetro el código de un empleado me devuelva el total de salario y comisión anual que debería recibir al año el empleado. CREATE OR REPLACE FUNCTION ANNUAL_COMP (p_id IN EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN NUMBER IS v_salary EMPLOYEES.SALARY%TYPE; v_commision EMPLOYEES.COMMISSION_PCT%TYPE; BEGIN SELECT salary+salary*NVL(COMMISSION_PCT,0) INTO employees WHERE employee_id=p_id; RETURN v_salary; END ANNUAL_COMP; /

v_salary

FROM

2. Crear un procedimiento llamado NEW_EMP, para ingresar un nuevo empleado en la tabla EMPLOYEES el procedimiento llamara a una función VALID_DEPTID que validará que el código del departamento exista en la tabla DEPARTMENTS. CREATE OR REPLACE FUNCTION VALID_DEPTID (p_id IN DEPARTMENTS.DEPARTMENT_ID%TYPE) RETURN BOOLEAN IS valido BOOLEAN := true; dep_name DEPARTMENTS.DEPARTMENT_NAME%TYPE; BEGIN SELECT department_name INTO dep_name FROM DEPARTMENTS WHERE department_id=p_id; IF SQL%NOTFOUND THEN valido:=false; END IF; RETURN valido; END VALID_DEPTID; /

SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE NEW_EMP (emp_id IN employees.employee_id%TYPE, first_n IN EMPLOYEES.FIRST_NAME%TYPE, last_n IN EMPLOYEES.LAST_NAME%TYPE, mail IN EMPLOYEES.EMAIL%TYPE, phone IN EMPLOYEES.PHONE_NUMBER%TYPE, hire_d IN EMPLOYEES.HIRE_DATE%TYPE, j_id IN EMPLOYEES.JOB_ID%TYPE, sal IN EMPLOYEES.SALARY%TYPE, com_pct IN EMPLOYEES.COMMISSION_PCT%TYPE, man_id IN EMPLOYEES.MANAGER_ID%TYPE, dep_id IN EMPLOYEES.DEPARTMENT_ID%TYPE, bonus IN EMPLOYEES.EMPLOYEE_BONUS%TYPE) IS err_no_valid_dept EXCEPTION; BEGIN IF VALID_DEPTID(dep_id) THEN INSERT INTO EMPLOYEES VALUES(emp_id, first_n, last_n, mail, phone, hire_d, j_id, sal, com_pct, man_id, dep_id, bonus); ELSE RAISE err_no_valid_dept; END IF; COMMIT;

EXCEPTION WHEN err_no_valid_dept THEN DBMS_OUTPUT.PUT_LINE('El departamento al que se quiere ingresar no existe'); END NEW_EMP; / EXECUTE NEW_EMP(207,'ADRIAN','MENA', 'ALMENA4', '515.123.4567', '17/06/87', 'AD_PRES', 24000, 0, 101, 90, 0);

3. Crear una función llamada QUERY_JEFE que dado como parámetro el código de un empleado me devuelva el nombre de su jefe inmediato, probar su funcionamiento mediante un select a todos los empleados de la tabla employees.

CREATE OR REPLACE FUNCTION query_jefe (e_id IN employees.employee_id%TYPE) RETURN VARCHAR2 IS v_nomjefe employees.last_name%TYPE; BEGIN SELECT f.last_name INTO v_nomjefe FROM employees e INNER JOIN employees f ON e.manager_id=f.employee_id AND e.employee_id = e_id; RETURN v_nomjefe; END query_jefe; / --Al empleado con id 130 SELECT query_jefe(employee_id) as "Jefe", last_name as "Empleado" from employees where employee_id = 130; --A todos lo empleados

SELECT query_jefe(employee_id) as "Jefe", last_name as "Empleado" from employees;

4. Crear una función llamada QUERY_DEPT que dado como parámetro el código de un departamento me devuelva el número de puestos de trabajo diferentes que existen en ese departamento. CREATE OR REPLACE FUNCTION QUERY_DEPT (d_id IN departments.department_id%TYPE) RETURN NUMBER IS v_jobtypes number := 0;

BEGIN SELECT count(count(JOB_ID)) INTO v_jobtypes FROM employees where department_id=d_id group by job_id; RETURN v_jobtypes; END QUERY_DEPT; / SELECT query_dept(department_id), department_name from departments where DEPARTMENT_ID = 80;