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

Creació de procediments

En aquesta pràctica, crearà, compilarà i farà crides a procediments que emeten ordres DML i de consulta. També aprendrà a manegar excepcions en els procediments.

Nota: si no ha realitzat un pas d'una pràctica, execute el script de solucions adequat del pas de la pràctica abans de continuar amb el següent pas o amb la següent pràctica.

En aquesta pràctica, crearà i cridarà al procediment ADD_JOB, a més a més de revisar el resultat. També crearà i cridarà a un procediment anomenat UPD_JOB per modificar un treball en la taula JOBS; tanmateix, crearà i cridarà a un procediment anomenat DEL_JOB per suprimir un treball de la taula JOBS. Per últim, crearà un procediment anomenat GET_EMPLOYEE per consultar la taula EMPLOYEES, que retorna el salari i l'identificador de treball d'un empleat quan es proporciona l'identificador de l'empleat.

1.- Crea, compila i crida al procediment ADD_JOB i revisa el resultat.

a) Crea un procediment anomenat ADD_JOB per afegir un nou treball en la taula JOBS. Proporciona l'identificador i el càrrec utilitzant dos paràmetres.

Nota: pots crear el procediment (així com altres objectes) mitjançant la introducció del codi en l'àre SQL Worksheet i, a continuació, fer clic en l¡icona Run Script (F5). De aquesta forma, es crea i compila el procediment. Per saber si el procediment conté o no errors, fes clic en el nom del mateix en el node de procediments i, a continuació, selecciona Compile en el menú emergent.

b) Crida al procediment amb IT_DBA com a identificadro del treball i Database Administrator com a càrrec. Consulta la taula JOBS i mira el resultat.

c) Crida al procediment de nou i passa-li com a identificador de treball ST_MAN i un càrrec Stock Manager. Què succeïx? Per què?

2.- Crea un procediment anomenat UPD_JOB per modificar un treball en la taula JOBS.

a) Crea un procediment anomenat UPD_JOB per actualitzar el càrrec. Proporciona el identificador de treball i un càrrec nou utilitzant dos paràmetres. Inclou el manegador d'excepcions necessari si no s'ha produït l'actualització.

b) Crida al procediment per canviar el càrrec de l'identificador de treball IT_DBA a Data Administrador. Consulta la taula JOBS i mira el resultat.

c) Prova la secció del manegador d'excepcions del procediment intentant actualitza un treball que no existeixi. Pots utilitzar l'identificador de treball IT_WEB i el càrrec Web Master.

3.- Crea un procediment anomenat DEL_JOB per suprimir un treball de la taula JOBS.

a) Crea un procediment anomenat DEL_JOB per suprimir un treball. Inclou el codi de manegador d'excepcions necessari si no s'ha suprimit cap treball.

b) Crida al procediment mitjançant l'identificador de treball IT_DBA. Consulta la taula JOBS i mira el resultat.

c) Prova la secció del manegador d'excepcions del procediment intentant suprimir un treball que no existeixi. Utilitza IT_WEB com a identificador de treball. Apareixerà el missatge que has inclòs en la secció del manegador d'excepcions del procediment com a sortida.

4.- Crea un procediment anomenat GET_EMPLOYEE per consultar la taula EMPLOYEES, que retorni el salari i l'identificador de treball d'un empleat quan es proporciona el identificador de l'empleat.

a) Crea un procediment que retorni un valor de les columnes SALARY i JOB_ID per l'identificador d'empleat especificat. Elimina els errors de sintaxi, si n'hi ha i, a continuació, recompila el codi.

b) Executa el procediment utilitzant les variables del host per als dos paràmetres OUT: una per al salari i l'altre per l'identificador de treball. Mostra el salari i l'identificador de treball per a l'identificador de empleat 120.

c) Crida al procediment de nou amb un EMPLOYEE_ID 300. Què succeïx? Per què?

Creació de funcions i depuració de subprogrames

1.- En aquesta pràctica, crearàs, compilaràs i utilitzaràs funcions emmagatzemades i un procediment:

1) Crea i crida a la funció GET_JOB per retornar un càrrec.

a) Crea i compila la funció anomenada GET_JOB per retornar un càrrec.

b) Crea una variable de host VARCHAR2 anomenada b_title, que permeti una longitud e 35 caràcters. Crida la funció amb l'identificador de treball SA_REP per que retorni el valor de la variable de host i, a continuació, imprimeix la variable de host per veure el resultat.

2) Crea una funció anomenada GET_ANNUAL_COMP per retornar el salari anual d'un empleat calculat a partir del salari mensual i la comissió transferits com a paràmetres.

a) Crea la funció GET_ANNUAL_COMP, que accepti valors de paràmetres del salari mensual i la comissió. Un o els dos valors transferits poden ser NULL, però la funció haurà de retornar un salari anual no NULL. Utilitza la següent fórmula bàsica per calcular el salari anual:

     (salary*12) + (commission_pct*salary*12)

b) Utilitza la funció en una sentència SELECT en la taula EMPLOYEES per als empleats del departament 30.

3) Crea un procediment, ADD_EMPLOYEE, per afegir un nou empleat en la taula EMPLOYEES. El procediment cridarà a una funció VALID_DEPTID per comprovar si l'identificador (ID) de departament especificat per al nou empleat existeix en la taula DEPARTMENTS.

a) Crea una funció anomenada VALID_DEPTID per validar l'identificador de departament especificat i retornar un valor BOOLEAN TRUE si existeix el departament.

b) Crea el procediment ADD_EMPLOYEE per afegir un empleat a la taula EMPLOYEES. La fila s'ha d'afegir a la taula EMPLOYEES si la funció VALID_DEPTID retorna TRUE; en cas contrari, alertarà a l'usuari amb un missatge adequat. Proporciona els següents paràmetres:

- first_name

- last_name

- email

- job: utilitza 'SA_REP' com a valor per defecte.

- mgr: utilitza 145 com a valor per defecte.

- sal: utilitza 1000 com a valor per defecte.

- comm: utilitza 0 com a valor per defecte.

- deptid: utilitza 30 com a valor per defecte.

- Utilitza la seqüència EMPLOYEES_SEQ per definir la columna employee_id.

- Defineix la columna hire_date com a TRUNC(SYSDATE).

c) Crida a ADD_EMPLOYEE per al nom 'Jane Harris' del departament 15, deixant els altres paràmetres amb els valors per defecte. Quin és el resultat?

d) Afegeix un altre empleat anomenat 'Joe Harris' en el departament 80, deixant els altres paràmetres amb els valors per defecte. Quin és el resultat?

2.- En aquesta pràctica, es presentarà la funcionalitat bàsica del depurador de SQL Developer:

• Crea un procediment i una funció.
• Afegeix punts de divisió en el procediment creat.
• Compila el procediment i la funció per al mode de depuració.
• Depura el procediment i desplaçat a la primera línia executable del codi.
• Mostra i modifica les variables dels subprogrames.

Nota: si no has realitzat un pas d'una pràctica, executa el sript de solucions adequat d'aquest pas del pràctica abans de continuar amb el següent pas o amb la següent pràctica.

1) Activa SERVEROUTPUT.

2) Executa el script sol_02_02_02.sql per crear el procediment emp_list.

Examina el codi del procediment i compila el procediment. Per què apareix l'error del compilador?

3) Executa el script sol_02_02_03.sql per la funció get_location.

Examina el codi de la funció, compila la funció i, a continuació, corregeix els possibles errors.

4) Recompila el procediment emp_list. El proceiment ha de compilarse correctament.

5) Edita el procediment emp_list i la funció get_location.

6) Agrega quatre punts de divisió al procediemtn emp_list en les següents línies de codi:

a) OPEN emp_cursor;

b) WHILE (emp_cursor%FOUND) AND (i <= pMaxRows) LOOP

c) v_city := get_location (emp_record.department_name);

d) CLOSE emp_cursor;

7) Compila el procediment emp_list per a la depuració.

8) Depura el procediment.

9) Introdueix 100 com a valor del paràmetre PMAXROWS.

10) Examina el valor de les variables en el separador Data. Quins són els valors assignats a REC_EMP y EMP_TAB? ¿Per què?

11) Utilitza l'opció de depuració Step Into per anar a cada línia de codi de emp_list i passar per el bucle while només una vegada.

12) Examina el valor de les variables en el separador Data. Quins són els valors assignats a REC_EMP?

13) Segueix premen F7 fins que s'executi la línia emp_tab(i) := rec_emp;.

Examina el valor de les variables en el separador Data. Quins són els valors assignats a EMP_TAB?

14) Utilitza el separador Data per a modificar el valor del comptador i a 98.

15) Segueix premen F7 fins que es vegi la llista de empleats en el separador Debugging – Log. Quants empleats apareixen?

16) Si utilitzes l'opció del depurador Step Over per a desplaçar-te per el codi, es desplaça per la funció get_location? ¿Per què?

Creació de paquets

En aquesta pràctica. crearàs cossos i especificacions de paquets. A continuació, cridaràs a les construccions dels paquets amb dades d'exemple.

1. Crea una especificació del paquet i un cos del paquet anomenat JOB_PKG, amb una còpia dels procediments ADD_JOB, UPD_JOB i DEL_JOB, així com la seva funció GET_JOB.

Nota: Utilitza el codi dels procediments i les funcions guardats prèviament al crear el paquet. Pots copiar el codi d'un procediment o una funció per a, a continuació, enganxar-lo en la secció adequada del paquet.

a) Crea l'especificació del paquet, inclosos els procediments i les capçaleres de funció com a construccions públiques.

b) Crea el cos del paquet amb les implantacions de cada un dels subprogrames.

c) Suprimeix els procediments i funció autònoms següents que acabes d'empaquetar amb els nodes Procedures i Functions del arbre Object Navigation:

i) Els procedimients ADD_JOB, UPD_JOB i DEL_JOB

ii) La funció GET_JOB

d) Crida al procediment empaquetat ADD_JOB transferint-li com a paràmetres els valors IT_SYSAN i SYSTEMS ANALYST.

e) Consulta la taula JOBS per a veure el resultat.

2. Crea i crida un paquet que contingui construccions públiques i privades.

a) Crea una especificació del paquet i un cos del paquet anomenats EMP_PKG, que contingui els següents procediments i funció creats anteriorment:

i) Procediment ADD_EMPLOYEE com a construcció pública

ii) Procedimient GET_EMPLOYEE com a construcció pública

iii) Funció VALID_DEPTID com a construcció privada

b) Crida al procediment EMP_PKG.ADD_EMPLOYEE, amb l'identificador de departament 15 per a l'empleada Jane Harris amb identificador de correu electrònic JAHARRIS. Com l'identificador de departament 15 no existeix, rebràs un missatge d'error com s'especifica en el manegador d'excepcions del procediment.

c) Crida al procediment empaquetat ADD_EMPLOYEE utilitzant l'identificador de departament 80 per al empleat David Smith amb l'identificador de correu electònic DASMITH.

d) Consulta la taula EMPLOYEES per verificar que s'ha afegit el nou empleat.

Ús de paquets proporcionats per Oracle

En aquesta pràctica, modificaràs el codi del paquet EMP_PKG que has creat anteriorment i, a continuació, sobrecarregaràs el procediment ADD_EMPLOYEE. A continuació crearàs dues funcions sobrecarregades anomenades GET_EMPLOYEE en el paquet EMP_PKG. També afegiràs un procediment públic a EMP_PKG per omplir una taula PL/SQL privada de identificadors de departament vàlids, a més a més de modificar la funció VALID_DEPTID per utilitzar el contingut de la taula PL/SQL privada amb el fi de validar els valors de identificador vàlids. També canviaràs la funció de procesament de validació VALID_DEPTID per utilitzar la taula PL/SQL privada de identificadors de departament. Per últim, reorganitzaràs els subprogrames en el cos i l'especificació del paquet per a que estiguin en seqüència alfabètica.

1. Modifica el codi del paquet EMP_PKG que has creat a la Pràctica 4, pas 2 i sobrecarrega el procediment ADD_EMPLOYEE.

a) En l'especificació del paquet, afegeix un nou procediment anomenat ADD_EMPLOYEE, que accepti els tres paràmetres següents:

i) First name

ii) Last name

iii) Department ID

b) Prem Run Script (F5) per crear i compilar el paquet.

c) Implanta el nou procediment ADD_EMPLOYEE en el cos del paquet, de la següent forma:

i) Formata la adreça de correu electrònic en caràcters en majúscula, utilitzant la primera lletra del nom concatenat amb les set primeres lletres del cognom.

ii) El procediment cridarà al procediment ADD_EMPLOYEE existent per realitzar l'operació INSERT real utilitzant els paràmetres i el correu electrònic formatat per proporcionar els valors.

iii) Prem Run Script per crear el paquet. Compila el paquet.

d) Crida al nou procediment ADD_EMPLOYEE utilitzant el nom Samuel Joplin per afegir-lo al departament 30.

e) Confirma que el nou empleat s'ha afegit a la taula EMPLOYEES.

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.

b) Haga clic en Run Script para volver a crear y compilar el paquete.

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.

d) Haga clic en Run Script para volver a crear y compilar el paquete.

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

f) Haga clic en Run Script (F5) para 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.

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;

d) Haga clic en Run Script (F5) para 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 el paquete. Compile el paquete.

b) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en

el departamento 15. ¿Qué sucede?

c) Inserte un nuevo departamento. Especifique 15 como identificador del

departamento y 'Security' como nombre del departamento. Confirme y verifique los cambios.

d) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en

el departamento 15. ¿Qué sucede?

e) Ejecute el procedimiento EMP_PKG.INIT_DEPARTMENTS para actualizar la

tabla interna PL/SQL con los últimos datos del departamento.

f) Pruebe el código llamando a ADD_EMPLOYEE con el nombre de empleado

James Bond, que trabaja en el departamento 15. ¿Qué sucede?

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. Asegúrese de introducir SET SERVEROUTPUT ON antes.

5. Reorganice los subprogramas en el cuerpo de 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?

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?

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?

Creació de disparadors

En esta práctica, creará disparadores de sentencia y de fila. También creará procedimientos que se llamarán desde los disparadores.

1. Las filas de la tabla JOBS almacenan los salarios mínimos y máximos permitidos para los distintos valores de JOB_ID. Le piden que escriba un código para garantizar que el salario de los empleados esté dentro del rango permitido por su tipo de trabajo, para operaciones de inserción y actualización.

a) Cree un procedimiento denominado CHECK_SALARY, de la siguiente forma:

i) El procedimiento acepta dos parámetros, uno para la cadena del identificador

de trabajo del empleado y el otro para el salario.

ii) El procedimiento utiliza el identificador de trabajo para determinar el salario

mínimo y máximo para el trabajo especificado.

iii) Si el parámetro del salario, mínimo y máximo incluidos, no está dentro del

rango de salarios, aparecerá una excepción de aplicación con el mensaje “Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>.” Sustituya los distintos elementos del mensaje por los valores que proporcionan los parámetros y las variables rellenados con consultas. Guarde el archivo.

b) Cree un disparador denominado CHECK_SALARY_TRG en la tabla EMPLOYEES

que arranque ante una operación INSERT o UPDATE en cada fila:

i) El disparador debe llamar al procedimiento CHECK_SALARY para ejecutar la

lógica de negocio.

ii) El disparador debe transferir el nuevo identificador de trabajo y salario a los

parámetros de procedimiento.

2. Pruebe el disparador CHECK_SAL_TRG utilizando los siguientes casos:

a) Utilice el procedimiento EMP_PKG.ADD_EMPLOYEE para agregar a la empleada

Eleanor Beh al departamento 30. ¿Qué sucede? ¿Por qué?

b) Actualice el salario del empleado 115 a 2.000 dólares. En otra operación de

actualización, cambie el identificador de trabajo del empleado a HR_REP. ¿Qué sucede en cada caso?

c) Actualice el salario del empleado 115 a 2.800 dólares. ¿Qué sucede?

3. Actualice el disparador CHECK_SALARY_TRG para que arranque sólo cuando los valores del identificador de trabajo o el salario hayan cambiado en realidad.

a) Implante la regla de negocio utilizando una cláusula WHEN para comprobar si los

valores JOB_ID o SALARY han cambiado. Nota: asegúrese de que la condición maneja NULL en los valores de OLD.column_name si se realiza una operación INSERT; si no es así, la operación de inserción fallará.

b) Compruebe el disparador ejecutando el procedimiento

EMP_PKG.ADD_EMPLOYEE con los siguientes valores de parámetros:

- p_first_name: 'Eleanor'

- p_last name: 'Beh'

- p_Email: 'EBEH'

- p_Job: 'IT_PROG'

- p_Sal: 5000

c) Actualice a los empleados con un trabajo IT_PROG incrementando su salario en

2.000 dólares. ¿Qué sucede?

d) Actualice a 9.000 dólares el salario de Eleanor Beh.

Indicación: utilice una sentencia UPDATE con una subconsulta en la cláusula WHERE. ¿Qué sucede?

e) Cambie el trabajo de Eleanor Beh a ST_MAN utilizando otra sentencia

UPDATE con una subconsulta. ¿Qué sucede?

4. Se le pide que evite que se suprima a los empleados durante las horas laborables.

a) Escriba un disparador de sentencia denominado DELETE_EMP_TRG en la tabla

EMPLOYEES para evitar que las filas se supriman durante horas laborables entre semana, es decir, de las 9:00 a.m. a las 6:00 p.m.

b) Intente suprimir los empleados con JOB_ID SA_REP que no estén asignados a

un departamento. Indicación: empleado Grant con identificador 178.

Creació de disparadors compostos, de DDL i d'events de base de dades

En esta práctica, implantará una regla de negocio sencilla para garantizar la integridad de los datos de los salarios de los empleados con respecto al rango de salarios válidos para sus trabajos. Cree un disparador para esta regla. Durante este proceso, los nuevos disparadores darán lugar a un efecto en cascada con disparadores creados en la sección práctica de la lección anterior. El efecto en cascada originará una excepción de tabla mutante en la tabla JOBS. A continuación, cree un paquete PL/SQL y disparadores adicionales para resolver el problema de la tabla mutante.

1. Los empleados reciben un aumento de sueldo automáticamente si el salario mínimo de un trabajo se aumenta a un valor superior que sus salarios actuales. Implante este requisito con un procedimiento empaquetado al que llame el disparador de la tabla JOBS. Cuando intenta actualizar el salario mínimo en la tabla JOBS e intenta actualizar los salarios de los empleados, el disparador CHECK_SALARY intenta leer la tabla JOBS, que está sujeta a cambios y obtendrá una excepción de tabla mutante que se resuelve creando un nuevo paquete y disparadores adicionales.

a) Actualice el paquete EMP_PKG (que actualizó por última vez en la práctica 8),

de la siguiente forma:

i) Agregue un procedimiento denominado SET_SALARY, que actualice

los salarios de los empleados.

ii) El procedimiento SET_SALARY acepta los dos parámetros siguientes:

el identificador de trabajo de aquellos salarios que puede que haya que actualizar y el nuevo salario mínimo para el identificador del trabajo

b) Cree un disparador de fila denominado UPD_MINSALARY_TRG en la tabla

JOBS, que llame al procedimiento EMP_PKG.SET_SALARY cuando el salario mínimo de la tabla JOBS se actualice para un identificador de trabajo especificado.

c) Escriba una consulta para mostrar el identificador de empleado, el apellido, el

identificador de trabajo, el salario actual y el salario mínimo para los empleados que sean programadores, es decir, su JOB_ID es 'IT_PROG'. A continuación, actualice el salario mínimo en la tabla JOBS para aumentarlo en 1.000 dólares. ¿Qué sucede?

2. Para resolver el problema de la tabla mutante, cree JOBS_PKG para mantener en memoria una copia de las filas de la tabla JOBS. A continuación, modifique el procedimiento CHECK_SALARY para utilizar los datos del paquete en vez de emitir una consulta en una tabla mutante para evitar la excepción. Sin embargo, debe crear un disparador de sentencia BEFORE INSERT OR UPDATE en la tabla EMPLOYEES para inicializar el estado del paquete JOBS_PKG antes de que arranque el disparador de fila CHECK_SALARY.

a) Cree un nuevo paquete denominado JOBS_PKG con la siguiente

especificación: PROCEDURE initialize; FUNCTION get_minsalary(jobid VARCHAR2) RETURN NUMBER; FUNCTION get_maxsalary(jobid VARCHAR2) RETURN NUMBER; PROCEDURE set_minsalary(jobid VARCHAR2,min_salary NUMBER); PROCEDURE set_maxsalary(jobid VARCHAR2,max_salary NUMBER);

b) Implante el cuerpo de JOBS_PKG, como se detalla a continuación:

i) Declare una tabla de índice PL/SQL privada denominada

jobs_tab_type indexada por un tipo de cadena basada en JOBS.JOB_ID%TYPE.

ii) Declare una variable privada denominada jobstab basada en

jobs_tab_type.

iii) El procedimiento INITIALIZE lee las filas en la tabla JOBS con un

bucle de cursor y utiliza el valor JOB_ID para el índice jobstab que se le asigne a la fila correspondiente.

iv) La función GET_MINSALARY utiliza un parámetro p_jobid como

índice para jobstab y devuelve min_salary para dicho elemento.

v) La función GET_MAXSALARY utiliza un parámetro p_jobid como

índice para jobstab y devuelve max_salary para dicho elemento.

vi) El procedimiento SET_MINSALARY utiliza su p_jobid como índice

para jobstab con el fin de definir el campo min_salary de su elemento en el valor del parámetro min_salary.

vii) El procedimiento SET_MAXSALARY utiliza su p_jobid como índice

para jobstab con el fin de definir el campo max_salary de su elemento en el valor del parámetro max_salary.

c) Copie el procedimiento CHECK_SALARY de la práctica 10, ejercicio 1a, y

modifique el código sustituyendo la consulta de la tabla JOBS con sentencias para definir las variables locales minsal y maxsal con valores de los datos JOBS_PKG llamando a las funciones GET_*SALARY adecuadas. Este paso debe eliminar la excepción de disparador mutante.

d) Implante un disparador de sentencia BEFORE INSERT OR UPDATE

denominado INIT_JOBPKG_TRG que utilice la sintaxis CALL para llamar al procedimiento JOBS_PKG.INITIALIZE, con el fin de garantizar que el estado del paquete sea actual antes de que se realicen las operaciones DML.

e) Pruebe los cambios de código ejecutando la consulta para mostrar los

empleados que son programadores y, a continuación, emita una sentencia de actualización para aumentar el salario mínimo del tipo de trabajo IT_PROG en 1.000 en la tabla JOBS. Después de esto, realice una consulta de los empleados con el tipo de trabajo IT_PROG para comprobar los cambios resultantes. ¿Los salarios de qué empleados se han definido en el mínimo para sus trabajos?

3. Debido a que CHECK_SALARY_TRG arranca el procedimiento CHECK_SALARY, antes de insertar o actualizar un empleado, debe comprobar si aún funciona como se esperaba.

a) Pruébelo agregando un nuevo empleado mediante

EMP_PKG.ADD_EMPLOYEE con los siguientes parámetros: (‘Steve’, ‘Morse’, ‘SMORSE’, and sal => 6500). ¿Qué sucede?

b) Para corregir el problema encontrado al agregar o actualizar un empleado:

i) Cree un disparador de sentencia BEFORE INSERT OR UPDATE

denominado EMPLOYEE_INITJOBS_TRG en la tabla EMPLOYEES que llame al procedimiento JOBS_PKG.INITIALIZE.

ii) Utilice la sintaxis CALL en el cuerpo del disparador.

c) Pruebe el disparador agregando el empleado Steve Morse de nuevo. Confirme

el registro insertado en la tabla EMPLOYEES mostrando el identificador de empleado, el nombre y el apellido, el salario, el identificador de trabajo y el identificador de departamento.