M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T3

Soluciones a la Práctica 3-1: Creación y Uso de Paquetes

En esta práctica, creará cuerpos y especificaciones de paquetes. A continuación, llamará a las construcciones de los paquetes con datos de ejemplo.

1) Cree una especificación del paquete y un cuerpo del paquete denominados JOB_PKG, con una copia de los procedimientos ADD_JOB, UPD_JOB y DEL_JOB, así como su función GET_JOB.

Nota: utilice el código de los procedimientos y las funciones guardados previamente al crear el paquete. Puede copiar el código de un procedimiento o una función para, a continuación, pegarlo en la sección adecuada del paquete.

a) Cree la especificación del paquete, incluidos los procedimientos y las cabeceras de función como construcciones públicas.

Abra el script /home/oracle/labs/plpu/solns/sol_03_01_a.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar la especificación del paquete. El código y el resultado se muestran de la siguiente forma:

    CREATE OR REPLACE PACKAGE job_pkg IS
      PROCEDURE add_job (p_jobid jobs.job_id%TYPE, p_jobtitle jobs.job_title%TYPE);
      PROCEDURE del_job (p_jobid jobs.job_id%TYPE);
      FUNCTION get_job (p_jobid IN jobs.job_id%type) RETURN jobs.job_title%type;
      PROCEDURE upd_job(p_jobid IN jobs.job_id%TYPE, p_jobtitle IN jobs.job_title%TYPE);
   END job_pkg;
   /
   SHOW ERRORS

b) Cree el cuerpo del paquete con las implantaciones de cada uno de los subprogramas.

Abra el script /home/oracle/labs/plpu/solns/sol_03_01_b.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar el cuerpo del paquete. El código y el resultado se muestran de la siguiente forma:

    CREATE OR REPLACE PACKAGE BODY job_pkg IS 
      PROCEDURE add_job (p_jobid jobs.job_id%TYPE, p_jobtitle jobs.job_title%TYPE) IS
        BEGIN
          INSERT INTO jobs (job_id, job_title)
          VALUES (p_jobid, p_jobtitle);
          COMMIT;
        END add_job;

      PROCEDURE del_job (p_jobid jobs.job_id%TYPE) IS
        BEGIN
          DELETE FROM jobs
          WHERE job_id = p_jobid;
          IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20203, 'No jobs deleted.');
          END IF;
        END DEL_JOB;

      FUNCTION get_job (p_jobid IN jobs.job_id%type) RETURN jobs.job_title%type IS
        v_title jobs.job_title%type;
        BEGIN
          SELECT job_title
          INTO v_title
          FROM jobs
          WHERE job_id = p_jobid;
          RETURN v_title;
       END get_job;

      PROCEDURE upd_job(p_jobid IN jobs.job_id%TYPE, p_jobtitle IN jobs.job_title%TYPE) IS
        BEGIN
          UPDATE jobs
          SET job_title = p_jobtitle
          WHERE job_id = p_jobid;
          IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20202, 'No job updated.');
          END IF;
        END upd_job;
    END job_pkg;
    /
    SHOW ERRORS

c) Suprima los procedimientos y función autónomos siguientes que acaba de empaquetar con los nodos Procedures y Functions del árbol Object Navigation:

i) Los procedimientos ADD_JOB, UPD_JOB y DEL_JOB

ii) La función GET_JOB

Para suprimir un procedimiento o una función, haga clic con el botón derecho en el nombre del procedimiento o de la función en el árbol Object Navigation y, a continuación, seleccione Drop en el menú emergente. Se mostrará la ventana Drop. Haga clic en Apply para borrar el procedimiento o la función. Aparece una ventana de confirmación; haga clic en OK.

d) Llame al procedimiento empaquetado ADD_JOB transfiriendo como parámetros los valores IT_SYSAN y SYSTEMS ANALYST.

Abra el script /home/oracle/labs/plpu/solns/sol_03_01_d.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento del paquete. El código y el resultado se muestran de la siguiente forma:

    EXECUTE job_pkg.add_job('IT_SYSAN', 'Systems Analyst')

e) Consulte la tabla JOBS para ver el resultado.

Abra el script /home/oracle/labs/plpu/solns/sol_03_01_e.sql.

Haga clic en el icono Run Script (F5) o en Execute Statement (F9) en la barra de herramientas de SQL Worksheet para consultar la tabla JOBS. El código y el resultado (utilizando el icono Run Script) se muestran de la siguiente forma:

    SELECT *
    FROM jobs
    WHERE job_id = 'IT_SYSAN';

2) Cree y llame a un paquete que contenga construcciones públicas y privadas.

a) Cree una especificación del paquete y un cuerpo del paquete denominados EMP_PKG, que contenga los siguientes procedimientos y función creados anteriormente:

i) Procedimiento ADD_EMPLOYEE como construcción pública

ii) Procedimiento GET_EMPLOYEE como construcción pública

iii) Función VALID_DEPTID como construcción privada

Abra el script /home/oracle/labs/plpu/solns/sol_03_02_a.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento del paquete. El código y el resultado se muestran de la siguiente forma:

    CREATE OR REPLACE PACKAGE emp_pkg IS
      PROCEDURE add_employee(
        p_first_name employees.first_name%TYPE,
        p_last_name employees.last_name%TYPE,
        p_email employees.email%TYPE,
        p_job employees.job_id%TYPE DEFAULT 'SA_REP',
        p_mgr employees.manager_id%TYPE DEFAULT 145,
        p_sal employees.salary%TYPE DEFAULT 1000,
        p_comm employees.commission_pct%TYPE DEFAULT 0,
        p_deptid employees.department_id%TYPE DEFAULT 30);

      PROCEDURE get_employee(
        p_empid IN employees.employee_id%TYPE,
        p_sal OUT employees.salary%TYPE,
        p_job OUT employees.job_id%TYPE);
    END emp_pkg;
    /
    SHOW ERRORS

    CREATE OR REPLACE PACKAGE BODY emp_pkg IS
      FUNCTION valid_deptid(p_deptid IN departments.department_id%TYPE)
        RETURN BOOLEAN IS
        v_dummy PLS_INTEGER;
      BEGIN
        SELECT 1
        INTO v_dummy
        FROM departments
        WHERE department_id = p_deptid;
        RETURN TRUE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
    END valid_deptid;

      PROCEDURE add_employee(
        p_first_name employees.first_name%TYPE,
        p_last_name employees.last_name%TYPE,
        p_email employees.email%TYPE,
        p_job employees.job_id%TYPE DEFAULT 'SA_REP',
        p_mgr employees.manager_id%TYPE DEFAULT 145,
        p_sal employees.salary%TYPE DEFAULT 1000,
        p_comm employees.commission_pct%TYPE DEFAULT 0,
        p_deptid employees.department_id%TYPE DEFAULT 30) IS
      BEGIN
        IF valid_deptid(p_deptid) THEN
          INSERT INTO employees(employee_id, first_name,
                                last_name, email,job_id, manager_id, 
                                hire_date, salary,commission_pct, 
                                department_id)
          VALUES (employees_seq.NEXTVAL, p_first_name,
                  p_last_name, p_email,
                  p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm,
                  p_deptid);
        ELSE
          RAISE_APPLICATION_ERROR (-20204, 'Invalid department ID. Try again.');
        END IF;
      END add_employee;

      PROCEDURE get_employee(p_empid IN employees.employee_id%TYPE,
                           p_sal OUT employees.salary%TYPE,
                           p_job OUT employees.job_id%TYPE) IS
      BEGIN
        SELECT salary, job_id
        INTO p_sal, p_job
        FROM employees
        WHERE employee_id = p_empid;
      END get_employee;
    END emp_pkg;
    /
    SHOW ERRORS

b) Llame al procedimiento EMP_PKG.ADD_EMPLOYEE, con el identificador de departamento 15 para la empleada Jane Harris con identificador de correo electrónico JAHARRIS. Como el identificador de departamento 15 no existe, recibirá un mensaje de error como se especifica en el manejador de excepciones del procedimiento.

Abra el script /home/oracle/labs/plpu/solns/sol_03_02_b.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento del paquete. El código y el resultado se muestran de la siguiente forma:

Nota: debe realizar el paso 3-2-a antes de realizar este paso. Si no ha realizado el paso 3-2-a, ejecute antes el script sol_03_02_a.sql.

    EXECUTE emp_pkg.add_employee('Jane', 'Harris','JAHARRIS',p_deptid => 15)

c) Llame al procedimiento empaquetado ADD_EMPLOYEE utilizando el identificador de departamento 80 para el empleado David Smith con el identificador de correo electrónico DASMITH.

Abra el script /home/oracle/labs/plpu/solns/sol_03_02_c.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento del paquete. El código y el resultado se muestran de la siguiente forma:

EXECUTE emp_pkg.add_employee('David', 'Smith','DASMITH', p_deptid => 80)

d) Consulte la tabla EMPLOYEES para verificar que se ha agregado el nuevo empleado.

Abra el script /home/oracle/labs/plpu/solns /sol_03_02_d.sql.

Haga clic en el icono Run Script (F5) o en el icono Execute Statement (F9) (asegurándose de que el cursor esté en cualquier parte del código de la sentencia SELECT) en la barra de herramientas de SQL Worksheet para consultar la tabla EMPLOYEES. El código y el resultado (icono Execute Statement) se muestran de la siguiente forma:

    SELECT *
    FROM employees
    WHERE last_name = 'Smith';

La siguiente salida se muestra en el separador Results, porque hemos ejecutado el código con el icono F9.