Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T3»
De wikiserver
Línia 104: | Línia 104: | ||
::ii) Procedimiento GET_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.''' | :'''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:''' | :'''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:''' | ||
+ | <pre> | ||
+ | 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) | |
− | PROCEDURE get_employee( | + | RETURN BOOLEAN IS |
− | p_empid IN employees.employee_id%TYPE, | + | v_dummy PLS_INTEGER; |
− | p_sal OUT employees.salary%TYPE, | + | BEGIN |
− | p_job OUT employees.job_id%TYPE); | + | SELECT 1 |
− | END emp_pkg; | + | INTO v_dummy |
− | / | + | FROM departments |
− | SHOW ERRORS | + | WHERE department_id = p_deptid; |
− | CREATE OR REPLACE PACKAGE BODY emp_pkg IS | + | RETURN TRUE; |
− | FUNCTION valid_deptid(p_deptid IN | + | EXCEPTION |
− | departments.department_id%TYPE) RETURN BOOLEAN IS | + | WHEN NO_DATA_FOUND THEN |
− | v_dummy PLS_INTEGER; | + | RETURN FALSE; |
− | BEGIN | + | END valid_deptid; |
− | SELECT 1 | + | |
− | INTO v_dummy | + | PROCEDURE add_employee( |
− | FROM departments | + | p_first_name employees.first_name%TYPE, |
− | WHERE department_id = p_deptid; | + | p_last_name employees.last_name%TYPE, |
− | RETURN TRUE; | + | p_email employees.email%TYPE, |
− | EXCEPTION | + | p_job employees.job_id%TYPE DEFAULT 'SA_REP', |
− | WHEN NO_DATA_FOUND THEN | + | p_mgr employees.manager_id%TYPE DEFAULT 145, |
− | RETURN FALSE; | + | p_sal employees.salary%TYPE DEFAULT 1000, |
− | END valid_deptid; | + | p_comm employees.commission_pct%TYPE DEFAULT 0, |
− | PROCEDURE add_employee( | + | p_deptid employees.department_id%TYPE DEFAULT 30) IS |
− | p_first_name employees.first_name%TYPE, | + | BEGIN |
− | p_last_name employees.last_name%TYPE, | + | IF valid_deptid(p_deptid) THEN |
− | p_email employees.email%TYPE, | + | INSERT INTO employees(employee_id, first_name, |
− | p_job employees.job_id%TYPE DEFAULT 'SA_REP', | + | last_name, email,job_id, manager_id, |
− | p_mgr employees.manager_id%TYPE DEFAULT 145, | + | hire_date, salary,commission_pct, |
− | p_sal employees.salary%TYPE DEFAULT 1000, | + | department_id) |
− | p_comm employees.commission_pct%TYPE DEFAULT 0, | + | VALUES (employees_seq.NEXTVAL, p_first_name, |
− | p_deptid employees.department_id%TYPE DEFAULT 30) IS | + | p_last_name, p_email, |
− | BEGIN | + | p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm, |
− | IF valid_deptid(p_deptid) THEN | + | p_deptid); |
− | INSERT INTO employees(employee_id, first_name, | + | ELSE |
− | last_name, email, | + | RAISE_APPLICATION_ERROR (-20204, 'Invalid department ID. Try again.'); |
− | job_id, manager_id, hire_date, salary, | + | END IF; |
− | commission_pct, department_id) | + | END add_employee; |
− | VALUES (employees_seq.NEXTVAL, p_first_name, | + | |
− | p_last_name, p_email, | + | PROCEDURE get_employee(p_empid IN employees.employee_id%TYPE, |
− | p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm, | + | p_sal OUT employees.salary%TYPE, |
− | p_deptid); | + | p_job OUT employees.job_id%TYPE) IS |
− | ELSE | + | BEGIN |
− | RAISE_APPLICATION_ERROR (-20204, 'Invalid | + | SELECT salary, job_id |
− | department ID. Try again.'); | + | INTO p_sal, p_job |
− | END IF; | + | FROM employees |
− | END add_employee; | + | WHERE employee_id = p_empid; |
− | PROCEDURE get_employee( | + | END get_employee; |
− | p_empid IN employees.employee_id%TYPE, | + | END emp_pkg; |
− | p_sal OUT employees.salary%TYPE, | + | / |
− | p_job OUT employees.job_id%TYPE) IS | + | SHOW ERRORS |
− | 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 | ||
[[Imatge:M2UF3_SOL_CUP_5.png |400px|center| Solucions Creació i ús de paquets]] | [[Imatge:M2UF3_SOL_CUP_5.png |400px|center| Solucions Creació i ús de paquets]] | ||
− | b) Llame al procedimiento EMP_PKG.ADD_EMPLOYEE, con el identificador de | + | :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. |
− | departamento 15 para la empleada Jane Harris con identificador de correo | + | |
− | electrónico JAHARRIS. Como el identificador de departamento 15 no existe, | + | :'''Abra el script /home/oracle/labs/plpu/solns/sol_03_02_b.sql.''' |
− | recibirá un mensaje de error como se especifica en el manejador de excepciones | + | :'''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:''' |
− | del procedimiento. | + | |
− | Abra el script /home/oracle/labs/plpu/solns/sol_03_02_b.sql. | + | :'''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.''' |
− | Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL | + | <pre> |
− | Worksheet para llamar al procedimiento del paquete. El código y el | + | EXECUTE emp_pkg.add_employee('Jane', 'Harris','JAHARRIS',p_deptid => 15) |
− | resultado se muestran de la siguiente forma: | + | </pre> |
− | 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) | ||
[[Imatge:M2UF3_SOL_CUP_6.png |400px|center| Solucions Creació i ús de paquets]] | [[Imatge:M2UF3_SOL_CUP_6.png |400px|center| Solucions Creació i ús de paquets]] | ||
− | c) Llame al procedimiento empaquetado ADD_EMPLOYEE utilizando el | + | :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. |
− | 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:''' | ||
+ | <pre> | ||
+ | EXECUTE emp_pkg.add_employee('David', 'Smith','DASMITH', p_deptid => 80) | ||
+ | </pre> | ||
[[Imatge:M2UF3_SOL_CUP_7.png |400px|center| Solucions Creació i ús de paquets]] | [[Imatge:M2UF3_SOL_CUP_7.png |400px|center| Solucions Creació i ús de paquets]] | ||
− | d) Consulte la tabla EMPLOYEES para verificar que se ha agregado el nuevo empleado. | + | :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. | + | :'''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) | + | :'''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:''' |
− | (asegurándose de que el cursor esté en cualquier parte del código de la | + | <pre> |
− | sentencia SELECT) en la barra de herramientas de SQL Worksheet para | + | SELECT * |
− | consultar la tabla EMPLOYEES. El código y el resultado (icono Execute | + | FROM employees |
− | Statement) se muestran de la siguiente forma: | + | WHERE last_name = 'Smith'; |
− | SELECT * | + | </pre> |
− | FROM employees | + | :'''La siguiente salida se muestra en el separador Results, porque hemos ejecutado el código con el icono F9.''' |
− | WHERE last_name = 'Smith'; | ||
− | La siguiente salida se muestra en el separador Results, porque hemos | ||
− | ejecutado el código con el icono F9. | ||
[[Imatge:M2UF3_SOL_CUP_8.png |400px|center| Solucions Creació i ús de paquets]] | [[Imatge:M2UF3_SOL_CUP_8.png |400px|center| Solucions Creació i ús de paquets]] |
Revisió del 19:25, 23 abr 2016
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 [[Imatge:M2UF3_SOL_CUP_5.png |400px|center| Solucions Creació i ús de paquets]] :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.''' <pre> 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.