M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T4
De wikiserver
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
- 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)
- 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:
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:
- 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.
- 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:
- 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.
- 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;
/
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.
4) Cambie la función de procesamiento de validación VALID_DEPTID para utilizar la tabla PL/SQL privada de identificadores de departamento.
- a) Modifique la función VALID_DEPTID para realizar la validación utilizando la tabla PL/SQL de valores de identificadores de departamento. Haga clic en Run Script (F5) para crear y compilar el paquete.
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_a.sql.
- Haga clic en Run Script (F5) para crear y compilar el paquete. 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
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
RETURN valid_departments.exists(p_deptid);
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
- b) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en el departamento 15. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_b.sql.
EXECUTE emp_pkg.add_employee('James', 'Bond', 15)
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para probar la inserción de un nuevo empleado. La operación de inserción para agregar un empleado ha fallado con una excepción, porque el departamento 15 no existe.
- c) Inserte un nuevo departamento. Especifique 15 como identificador del departamento y 'Security' como nombre del departamento. Confirme y verifique los cambios.
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_c.sql.
- El código y el resultado se muestran de la siguiente forma:
INSERT INTO departments (department_id, department_name)
VALUES (15, 'Security');
COMMIT;
- d) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en el departamento 15. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_d.sql.
- El código y el resultado se muestran de la siguiente forma:
EXECUTE emp_pkg.add_employee('James', 'Bond', 15)
- La operación de inserción para agregar un empleado falla con una excepción.
- El departamento 15 no existe como entrada en la variable de estado de paquete de (la tabla de índice) de la matriz asociativa PL/SQL.
e) Ejecute el procedimiento EMP_PKG.INIT_DEPARTMENTS para actualizar la tabla interna PL/SQL con los últimos datos del departamento.
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_e.sql.
- El código y el resultado se muestran de la siguiente forma:
EXECUTE EMP_PKG.INIT_DEPARTMENTS
- f) Pruebe el código llamando a ADD_EMPLOYEE con el nombre de empleado James Bond, que trabaja en el departamento 15. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_f.sql.
- El código y el resultado se muestran de la siguiente forma.
EXECUTE emp_pkg.add_employee('James', 'Bond', 15)
- La fila se inserta finalmente porque el registro del departamento 15 existe en la base de datos y en la tabla de índice PL/SQL del paquete, debido a la llamada de EMP_PKG.INIT_DEPARTMENTS, que refresca los datos de estado del paquete.
- g) Suprima al empleado James Bond y el departamento 15 de sus respectivas tablas, confirme los cambios y refresque los datos del departamento llamando al procedimiento EMP_PKG.INIT_DEPARTMENTS.
- Abra el script /home/oracle/labs/plpu/solns/sol_04_04_g.sql.
- El código y el resultado se muestran de la siguiente forma.
DELETE FROM employees
WHERE first_name = 'James' AND last_name = 'Bond';
DELETE FROM departments WHERE department_id = 15;
COMMIT;
EXECUTE EMP_PKG.INIT_DEPARTMENTS
5) Reorganice los subprogramas en el cuerpo y la especificación del paquete para que estén en secuencia alfabética.
- a) Edite la especificación del paquete y reorganice los subprogramas de forma alfabética. Haga clic en Run Script para volver a crear la especificación del paquete. Compile la especificación del paquete. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_04_05_a.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar el paquete. El código y el resultado se muestran de la siguiente forma. Los subprogramas de la especificación del paquete ya están en orden alfabético.
CREATE OR REPLACE PACKAGE emp_pkg IS
/* the package spec is already in an alphabetical order. */
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;
PROCEDURE init_departments;
PROCEDURE print_employee(p_rec_emp employees%rowtype);
END emp_pkg;
/
SHOW ERRORS
- b) Edite el cuerpo del paquete y reorganice todos los subprogramas de forma alfabética. Haga clic en Run Script para volver a crear la especificación del paquete. Recompile la especificación del paquete. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_04_05_b.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear el paquete. El código y el resultado se muestran de la siguiente forma.
-- Package BODY
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
TYPE boolean_tab_type IS TABLE OF BOOLEAN
INDEX BY BINARY_INTEGER;
valid_departments boolean_tab_type;
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 init_departments IS
BEGIN
FOR rec IN (SELECT department_id FROM departments)
LOOP
valid_departments(rec.department_id) := TRUE;
END LOOP;
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;
FUNCTION valid_deptid(p_deptid IN
departments.department_id%TYPE) RETURN BOOLEAN IS
v_dummy PLS_INTEGER;
BEGIN
RETURN valid_departments.exists(p_deptid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END valid_deptid;
BEGIN
init_departments;
END emp_pkg;
/
SHOW ERRORS
- El paquete no se compila correctamente porque se hace referencia a la función VALID_DEPTID antes de que se declare.
- c) Corrija el error de compilación utilizando una declaración anticipada en el cuerpo para la referencia de subprograma adecuada. Haga clic en Run Script para volver a crear el paquete y, a continuación, recompile el paquete. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_04_05_c.sql.
- La declaración anticipada de la función aparece resaltada en el cuadro de código siguiente. Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para volver a crear y compilar el paquete.
- El código y el resultado se muestran de la siguiente forma.
-- Package BODY
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
TYPE boolean_tab_type IS TABLE OF BOOLEAN
INDEX BY BINARY_INTEGER;
valid_departments boolean_tab_type;
/* forward declaration of valid_deptid */
FUNCTION valid_deptid(p_deptid IN
departments.department_id%TYPE)
RETURN BOOLEAN;
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 alphabetical location of function init_departments. */
PROCEDURE init_departments IS
BEGIN
FOR rec IN (SELECT department_id FROM departments)
LOOP
valid_departments(rec.department_id) := TRUE;
END LOOP;
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 alphabetical location of function valid_deptid. */
FUNCTION valid_deptid(p_deptid IN
departments.department_id%TYPE) RETURN BOOLEAN IS
v_dummy PLS_INTEGER;
BEGIN
RETURN valid_departments.exists(p_deptid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END valid_deptid;
BEGIN
init_departments;
END emp_pkg;
/
SHOW ERRORS
- Una declaración anticipada de la función VALID_DEPTID permite al cuerpo del paquete compilarse correctamente, como se muestra a continuación:
