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

Soluciones a la Práctica 4-1: Trabajar con Paquetes

En esta práctica, modificará el código del paquete EMP_PKG que ha creado anteriormente y, a continuación, sobrecargará el procedimiento ADD_EMPLOYEE. A continuación, creará dos funciones sobrecargadas denominadas GET_EMPLOYEE en el paquete EMP_PKG. También agregará un procedimiento público a EMP_PKG para rellenar una tabla PL/SQL privada de identificadores de departamento válidos, además de modificar la función VALID_DEPTID para utilizar el contenido de la tabla PL/SQL privada con el fin de validar los valores de identificador válidos. También cambiará la función de procesamiento de validación VALID_DEPTID para utilizar la tabla PL/SQL privada de identificadores de departamento. Por último, reorganizará los subprogramas en el cuerpo y la especificación del paquete para que estén en secuencia alfabética.

1) Modifique el código del paquete EMP_PKG que ha creado en la Práctica 4, paso 2 y sobrecargue el procedimiento ADD_EMPLOYEE.

a) En la especificación del paquete, agregue un nuevo procedimiento denominado ADD_EMPLOYEE, que acepte los tres parámetros siguientes:

i) First name

ii) Last name

iii) Department ID

Abra el archivo /home/oracle/labs/plpu/solns/sol_04_01_a.sql.

El código se muestra 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);
 
       /* New overloaded add_employee */
 
       PROCEDURE add_employee(
         p_first_name employees.first_name%TYPE,
         p_last_name employees.last_name%TYPE,
         p_deptid employees.department_id%TYPE);
       
       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

b) Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL

Worksheet para crear y compilar el paquete.

c) Implante el nuevo procedimiento ADD_EMPLOYEE en el cuerpo del paquete, de la siguiente forma:

i) Formatee la dirección de correo electrónico en caracteres en mayúscula, utilizando la primera letra del nombre concatenado con las siete primeras letras del apellido.

ii) El procedimiento debe llamar al procedimiento ADD_EMPLOYEE existente para realizar la operación INSERT real utilizando los parámetros y el correo electrónico formateado para proporcionar los valores.

iii) Haga clic en Run Script para crear el paquete. Compile el paquete.

Abra el script /home/oracle/labs/plpu/solns/sol_04_01_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 (el código que se acaba de agregar se resalta en negrita en el cuadro de texto siguiente):

    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;

      /* New overloaded add_employee procedure */

      PROCEDURE add_employee(
        p_first_name employees.first_name%TYPE,
        p_last_name employees.last_name%TYPE,
        p_deptid employees.department_id%TYPE) IS
        p_email employees.email%type;
      BEGIN
        p_email := UPPER(SUBSTR(p_first_name, 1, 1)||SUBSTR(p_last_name, 1, 7));
        add_employee(p_first_name, p_last_name, p_email, p_deptid => p_deptid);
      END;

      /* End declaration of the overloaded add_employee procedure */

      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