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

De wikiserver
Dreceres ràpides: navegació, cerca

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.

Solucions Treballar amb paquets
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
Solucions Treballar amb paquets
d) Llame al nuevo procedimiento ADD_EMPLOYEE utilizando el nombre Samuel Joplin para agregarlo al departamento 30.
Abra el script /home/oracle/labs/plpu/solns/sol_04_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 emp_pkg.add_employee('Samuel', 'Joplin', 30)
Solucions Treballar amb paquets
e) Confirme que el nuevo empleado se ha agregado a la tabla EMPLOYEES.
Abra el script /home/oracle/labs/plpu/solns/sol_04_01_e.sql.
Haga clic en cualquier parte de la sentencia SELECT y, a continuación, haga clic en el icono Execute Statement (F9) de la barra de herramientas de SQL Worksheet para ejecutar la consulta. El código y el resultado se muestran de la siguiente forma:
    SELECT *
    FROM employees
    WHERE last_name = 'Joplin';

b) Haga clic en Run Script para volver a crear y compilar la especificación del paquete. Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar la especificación del paquete. A continuación se muestra el resultado:

Solucions Treballar amb paquets

2) En el paquete EMP_PKG, cree dos funciones sobrecargadas denominadas GET_EMPLOYEE:

a) En la especificación del paquete, agregue las siguientes funciones:
i) La función GET_EMPLOYEE que acepta el parámetro denominado p_emp_id basado en el tipo employees.employee_id%TYPE. Esta función debe devolver EMPLOYEES%ROWTYPE.
ii) La función GET_EMPLOYEE que acepta el parámetro denominado p_family_name de tipo employees.last_name%TYPE. Esta función debe devolver EMPLOYEES%ROWTYPE.
Abra el script /home/oracle/labs/plpu/solns/sol_04_02_a.sql.
    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 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);

      /* New overloaded get_employees functions specs starts here: */

      FUNCTION get_employee(p_emp_id employees.employee_id%type)
        return employees%rowtype;

      FUNCTION get_employee(p_family_name employees.last_name%type)
        return employees%rowtype;

      /* New overloaded get_employees functions specs ends here. */
   
    END emp_pkg;
    /
    SHOW ERRORS
b) Haga clic en Run Script para volver a crear y compilar la especificación del

paquete.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar la especificación del paquete. A continuación se muestra el resultado:
Solucions Treballar amb paquets
Nota: como se menciona anteriormente, si el código contiene un mensaje de error, puede recompilarlo mediante el siguiente procedimiento para ver los detalles del error o la advertencia en el separador Compiler – Log. Para compilar la especificación del paquete, haga clic con el botón derecho en el nombre de la especificación del paquete (o en todo el paquete) en el árbol Object Navigator y, a continuación, seleccione Compile en el menú de acceso directo. La advertencia se esperaba y es sólo para su información.
Solucions Treballar amb paquets
c) En el cuerpo del paquete:
i) Implante la primera función GET_EMPLOYEE para consultar un empleado mediante el identificador del mismo.
.ii) Implante la segunda función GET_EMPLOYEE para utilizar el operador de igualdad en el valor suministrado en el parámetro p_family_name.
Abra el script /home/oracle/labs/plpu/solns/sol_04_02_c.sql.
Las funciones que se acaban de agregar se resaltan en el siguiente cuadro de código.
    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 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);

      /* New overloaded get_employees functions specs starts here: */

      FUNCTION get_employee(p_emp_id employees.employee_id%type)
        return employees%rowtype;

      FUNCTION get_employee(p_family_name employees.last_name%type)
        return employees%rowtype;

      /* New overloaded get_employees functions specs ends here. */
    END emp_pkg;
    /
    SHOW ERRORS
    -- package body

    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 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;

      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;

     /* New get_employee function declaration starts here */

     FUNCTION get_employee(p_emp_id employees.employee_id%type)
       return employees%rowtype IS
       rec_emp employees%rowtype;
     BEGIN
       SELECT * INTO rec_emp
       FROM employees
       WHERE employee_id = p_emp_id;
       RETURN rec_emp;
     END;

     FUNCTION get_employee(p_family_name employees.last_name%type)
       return employees%rowtype IS
       rec_emp employees%rowtype;
     BEGIN
       SELECT * INTO rec_emp
       FROM employees
       WHERE last_name = p_family_name;
       RETURN rec_emp;
     END;

     /* New overloaded get_employee function declaration ends here */

    END emp_pkg;
    /
    SHOW ERRORS
d) Haga clic en Run Script para volver a crear el paquete. Compile el paquete.
Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar el paquete. A continuación se muestra el resultado:
Solucions Treballar amb paquets
e) Agregue un procedimiento de utilidad PRINT_EMPLOYEE al paquete EMP_PKG, de la siguiente forma:
i) El procedimiento acepta EMPLOYEES%ROWTYPE como parámetro.
ii) El procedimiento muestra lo siguiente para un empleado en una línea, mediante el paquete DBMS_OUTPUT:
- department_id
- employee_id
- first_name
- last_name
- job_id
- salary
Abra el script /home/oracle/labs/plpu/solns /sol_04_02_e.sql.
El código que se acaba de agregar se resalta en el siguiente cuadro de código.
    -- Package SPECIFICATION

    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 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);
 
      FUNCTION get_employee(p_emp_id employees.employee_id%type)
        return employees%rowtype;

      FUNCTION get_employee(p_family_name
        employees.last_name%type)
        return employees%rowtype;

      /* New print_employee print_employee procedure spec */

      PROCEDURE print_employee(p_rec_emp employees%rowtype);

    END emp_pkg;
    /
    SHOW ERRORS
    -- Package BODY

    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 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;

      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;

      FUNCTION get_employee(p_emp_id employees.employee_id%type)
        return employees%rowtype IS
        rec_emp employees%rowtype;
      BEGIN
        SELECT * INTO rec_emp
        FROM employees
        WHERE employee_id = p_emp_id;
        RETURN rec_emp;
      END;

      FUNCTION get_employee(p_family_name
        employees.last_name%type)
        return employees%rowtype IS
        rec_emp employees%rowtype;
      BEGIN
        SELECT * INTO rec_emp
        FROM employees
        WHERE last_name = p_family_name;
        RETURN rec_emp;
      END;

      /* New print_employees procedure declaration. */

      PROCEDURE print_employee(p_rec_emp employees%rowtype) IS
        BEGIN
          DBMS_OUTPUT.PUT_LINE(p_rec_emp.department_id ||' '||
                               p_rec_emp.employee_id||' '||
                               p_rec_emp.first_name||' '||
                               p_rec_emp.last_name||' '||
                               p_rec_emp.job_id||' '||
                               p_rec_emp.salary);
      END;

    END emp_pkg;
    /
    SHOW ERRORS
f) Haga clic en Run Script (F5) para crear y compilar el paquete.
Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar el paquete.
Solucions Treballar amb paquets
g) Utilice un bloque anónimo para llamar a la función EMP_PKG.GET_EMPLOYEE con un identificador de empleado 100 y con apellido 'Joplin'. Utilice el procedimiento PRINT_EMPLOYEE para mostrar los resultados para cada fila devuelta. Asegúrese de introducir SET SERVEROUTPUT ON antes.
Abra el script /home/oracle/labs/plpu/solns /sol_04_02_g.sql.
    SET SERVEROUTPUT ON
    
    BEGIN
      emp_pkg.print_employee(emp_pkg.get_employee(100));
      emp_pkg.print_employee(emp_pkg.get_employee('Joplin'));
    END;
    /
Solucions Treballar amb paquets

3) Como la compañía no cambia con frecuencia sus datos de departamento, puede mejorar el rendimiento de EMP_PKG agregando un procedimiento público, INIT_DEPARTMENTS, para rellenar una tabla PL/SQL privada de identificadores de departamento válidos. Modifique la función VALID_DEPTID para utilizar el contenido de la tabla PL/SQL privada con el fin de validar los valores de los identificadores de departamento.

Nota: el script del archivo de soluciones sol_04_03.sql contiene el código de los pasos a, b y c.
a) En la especificación del paquete, cree un procedimiento denominado INIT_DEPARTMENTS sin parámetros. Para ello, agregue lo siguiente a la sección de especificación del paquete antes de la especificación PRINT_EMPLOYEES:
    PROCEDURE init_departments;
b) En el cuerpo del paquete, implante el procedimiento INIT_DEPARTMENTS para almacenar todos los identificadores de departamento en una tabla de índice PL/SQL privada denominada valid_departments que contiene valores BOOLEAN.
i) Declare la variable valid_departments y su definición de tipo boolean_tab_type antes que todos los procedimientos del cuerpo.
Introduzca lo siguiente al comienzo del cuerpo del paquete:
    TYPE boolean_tab_type IS TABLE OF BOOLEAN
      INDEX BY BINARY_INTEGER;
    valid_departments boolean_tab_type;
ii) Utilice el valor de la columna department_id como índice para crear la entrada en la tabla de índice para indicar su presencia y asignar a la entrada un valor de TRUE. Introduzca la declaración del procedimiento INIT_DEPARTMENTS al final del cuerpo del paquete (justo después del procedimiento print_employees), de la siguiente forma:
    PROCEDURE init_departments IS
    BEGIN
      FOR rec IN (SELECT department_id FROM departments)
      LOOP
        valid_departments(rec.department_id) := TRUE;
      END LOOP;
    END;
c) En el cuerpo, cree un bloque de inicialización que llame al procedimiento INIT_DEPARTMENTS para inicializar la tabla, de la siguiente forma:
    BEGIN
      init_departments;
    END;
Abra el script /home/oracle/labs/plpu/solns/sol_04_03.sql.
El código que se acaba de agregar se resalta en el siguiente cuadro de código.
-- Package SPECIFICATION
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 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);
FUNCTION get_employee(p_emp_id employees.employee_id%type)
return employees%rowtype;
FUNCTION get_employee(p_family_name
employees.last_name%type)
return employees%rowtype;
/* New procedure init_departments spec */
PROCEDURE init_departments;
PROCEDURE print_employee(p_rec_emp employees%rowtype);
END emp_pkg;
/
SHOW ERRORS
-- Package BODY
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
/* New type */
TYPE boolean_tab_type IS TABLE OF BOOLEAN
INDEX BY BINARY_INTEGER;
valid_departments boolean_tab_type;
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 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;
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;
FUNCTION get_employee(p_emp_id employees.employee_id%type)
return employees%rowtype IS
rec_emp employees%rowtype;
BEGIN
SELECT * INTO rec_emp
FROM employees
WHERE employee_id = p_emp_id;
RETURN rec_emp;
END;
FUNCTION get_employee(p_family_name
employees.last_name%type)
return employees%rowtype IS
rec_emp employees%rowtype;
BEGIN
SELECT * INTO rec_emp
FROM employees
WHERE last_name = p_family_name;
RETURN rec_emp;
END;
PROCEDURE print_employee(p_rec_emp employees%rowtype) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_rec_emp.department_id ||' '||
P_rec_emp.employee_id||' '||
P_rec_emp.first_name||' '||
P_rec_emp.last_name||' '||
P_rec_emp.job_id||' '||
P_rec_emp.salary);
END;
/* New init_departments procedure declaration. */
PROCEDURE init_departments IS
BEGIN
FOR rec IN (SELECT department_id FROM departments)
LOOP
valid_departments(rec.department_id) := TRUE;
END LOOP;
END;
/* call the new init_departments procedure. */
BEGIN
init_departments;
END emp_pkg;
/
SHOW ERRORS
d) Haga clic en Run Script (F5) para volver a crear y compilar el paquete.
Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar el paquete.
Solucions Treballar amb paquets