Taller Stored Procedures Oracle 11g.docx

TALLER STORED PROCEDURES

1. Crear un procedimiento llamado ADD_JOB para insertar un nuevo puesto de trabajo en la tabla JOBS, ingrese el id y el puesto de trabajo mediante parámetros. a. Ejecute el procedimiento ADD_JOB con el valor para jod_id igual a “IT_DBA” y el job_title igual a “Database Administrator”. Administrator”.

CREATE OR REPLACE PROCEDURE ADD_JOB (j_id IN JOBS.JOB_ID%TYPE, JOBS.JOB_ID%TYPE, j_title IN JOBS.JOB_TITLE%TYPE) IS BEGIN INSERT INTO JOBS VALUES (j_id, j_title, null, null); COMMIT; END ADD_JOB; /

EXECUTE ADD_JOB('IT_DBA', 'Database Administrator'); Administrator');

2. Crear un procedimiento llamado DEL_JOB para eliminar un puesto de trabajo de la tabla JOBS, crear la excepción necesaria cuando un puesto de trabajo no pueda ser borrado o no exista en la base a. Llame al procedimiento anterior para eliminar el puesto de trabajo con el codigo IT_DBA

SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE DEL_JOB (j_id IN JOBS.JOB_ID%TYPE) IS e_invalid_job EXCEPTION; BEGIN DELETE FROM JOBS WHERE JOB_ID=j_id; IF SQL%ROWCOUNT=0 THEN RAISE e_invalid_job; END IF; COMMIT; EXCEPTION WHEN e_invalid_job THEN SYS.DBMS_OUTPUT.PUT_LINE('Este trabajo no existe'); END DEL_JOB; / EXECUTE DEL_JOB('IT_DBA');

3. Crear un procedimiento llamado QUERY_EMP que recupere el salario y job_id de la tabla employees cuando envié como parámetro el código del empleado

CREATE OR REPLACE PROCEDURE QUERY_EMP (e_id IN EMPLOYEES.EMPLOYEE_ID%TYPE, salario OUT EMPLOYEES.SALARY%TYPE, j_id OUT JOBS.JOB_ID%TYPE) IS e_invalid_job EXCEPTION; BEGIN SELECT salary, job_id INTO salario, j_id FROM EMPLOYEES WHERE EMPLOYEE_ID=e_id; COMMIT; END QUERY_EMP; /

4. Crear un procedimiento llamado QUERY_DEP que envíe como parámetro el código de un departamento y me devuelva información del número de empleados que existen y cual es el valor total de salarios pagados

CREATE OR REPLACE PROCEDURE QUERY_DEP (dep_id IN DEPARTMENTS.DEPARTMENT_ID%TYPE, n_empleados OUT NUMBER, total_salarios_dep OUT employees.salary%TYPE) IS BEGIN SELECT COUNT(department_id), SUM(salary) INTO n_empleados, total_salarios_dep FROM employees WHERE department_id=dep_id; COMMIT; END QUERY_DEP; / VARIABLE n_empleados NUMBER; VARIABLE total_salarios_dep NUMBER; EXECUTE QUERY_DEP(80, :n_empleados, :total_salarios_dep); PRINT n_empleados; PRINT total_salarios_dep;

5. Crear un procedimiento llamado QUERY_CIUDAD que envíe como parámetro el código de un departamento y me devuelva información del nombre de la ciudad y los empleados que trabajan en ese departamento

CREATE OR REPLACE PROCEDURE QUERY_CIUDAD (dep_id IN DEPARTMENTS.DEPARTMENT_ID%TYPE, n_empleados_dep OUT NUMBER, ciudad_dep OUT LOCATIONS.CITY%TYPE) IS BEGIN SELECT l.city INTO ciudad_dep FROM departments d, locations l WHERE d.location_id=l.location_id AND d.department_id=dep_id; SELECT COUNT(department_id) INTO n_empleados_dep FROM employees WHERE department_id=dep_id;

COMMIT; END QUERY_CIUDAD; /

VARIABLE n_empleados NUMBER; VARIABLE ciudad_dep VARCHAR2(30);

EXECUTE QUERY_CIUDAD(80, :n_empleados, :ciudad_dep); PRINT n_empleados; PRINT ciudad_dep;