Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: PL-SQL-2»

Revisió del 18:15, 20 abr 2016

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 paquet EMP_PKG, crea dues funcions sobrecarregades anomenades GET_EMPLOYEE:

a) En la especificació del paquet, afegeix les següents funcions:

i) La funció GET_EMPLOYEE que accepta el paràmetre anomenat p_emp_id basat en el tipus employees.employee_id%TYPE. Aquesta funció ha de retornar EMPLOYEES%ROWTYPE.

ii) La funció GET_EMPLOYEE que accepta el paràmetre anomenat p_family_name de tipus employees.last_name%TYPE. Aquesta funció ha de retornar EMPLOYEES%ROWTYPE.

b) Prem Run Script per tornar a crear i compilar el paquet.

c) En el cos del paquet:

i) Fes la primera funció GET_EMPLOYEE per consultar un empleat mitjançant l'identificador del mateix.

ii) Fes la segona funció GET_EMPLOYEE per utilitzar l'operador d'igualtat en el valor subministrat en el paràmetre p_family_name.

d) Prem Run Script per tornar a crear i compilar el paquet.

e) Afegeix un procediment d'utilitat PRINT_EMPLOYEE al paquet EMP_PKG, de la següent forma:

i) El procediment accepta EMPLOYEES%ROWTYPE com a paràmetre.

ii) El procediment mostra el següent per a un empleat en una línia, mitjançant el paquet DBMS_OUTPUT:

- department_id

- employee_id

- first_name

- last_name

- job_id

- salary

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

g) Utilitza un bloc anònim per cridar a la funció EMP_PKG.GET_EMPLOYEE amb un identificador d'empleat 100 i amb cognom 'Joplin'. Utilitza el procediment PRINT_EMPLOYEE per mostrar els resultats per a cada fila tornada.

3. Com la companyia no canvia amb freqüència les seves dades de departament, pots millorar el rendiment de EMP_PKG afegint un procediment públic, INIT_DEPARTMENTS, per omplir una taula PL/SQL privada d'identificadors de departament vàlids. Modifica la funció VALID_DEPTID per utilitzar el contingut de la taula PL/SQL privada amb el fi de validar els valors dels identificadors de departament.

Nota: el script de l'arxiu de solucions sol_04_03.sql conté el codi dels passos a, b i c.

a) En l'especificació del paquet, crea un procediment anomenat INIT_DEPARTMENTS sense paràmetres. Per fer-ho, afegeix el següent a la secció d'especificació del paquet abans de la especificació PRINT_EMPLOYEES:

     PROCEDURE init_departments;

b) En el cos del paquet, implanta el procediment INIT_DEPARTMENTS per emmagatzemar tots els identificadors de departament en una taula d'índex PL/SQL privada anomenada valid_departments que conté valores BOOLEAN.

i) Declara la variable valid_departments i la seva definició de tipus boolean_tab_type abans que tots els procediments del cos.

Introdueix el següent al començament del cos del paquet:

     TYPE boolean_tab_type IS TABLE OF BOOLEAN
     INDEX BY BINARY_INTEGER;
     valid_departments boolean_tab_type;

ii) Utilitza el valor de la columna department_id com a índex per crear l'entrada en la taula de índex per indicar la seva presencia i assignar a l'entrada un valor de TRUE. Introdueix la declaració del procediment INIT_DEPARTMENTS al final del cos del paquet (justament després del procediment print_employees), de la sigüent 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 cos, crea un bloc d'inicialització que cridi al procediment INIT_DEPARTMENTS per inicialitzar la taula, de la següent forma:

     BEGIN
        init_departments;
     END;

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

4. Canvia la funció de processament de validació VALID_DEPTID per utilitzar la taula PL/SQL privada d'identificadors de departament.

a) Modifica la funció VALID_DEPTID per realitzar la validació utilitzant la taula PL/SQL de valors d'identificadors de departament. Prem en Run Script (F5) per crear el paquet. Compila el paquet.

b) Prova el codi cridant a ADD_EMPLOYEE amb el nom James Bond en el departament 15. Què succeïx?

c) Afegeix un nou departament. Especifica 15 como identificador del departament i 'Security' com a nom del departament. Confirma i verifica els canvis.

d) Prova el codi cridant a ADD_EMPLOYEE amb el nom James Bond en el departament 15. Què succeïx?

e) Executa el procediment EMP_PKG.INIT_DEPARTMENTS per actualitzar la taula interna PL/SQL amb les últimes dades del departament.

f) Prova el codi cridant a ADD_EMPLOYEE amb el nom James Bond en el departament 15. Què succeïx?

g) Suprimeox al empleat James Bond y el departament 15 de les seves respectives taules, confirma els canvis i refresca les dades del departament cridant al procediment EMP_PKG.INIT_DEPARTMENTS. Assegurat d'introduir SET SERVEROUTPUT ON abans.

5. Reorganitza els subprogrames en el cos de la especificació del paquet per a que estiguin en seqüència alfabètica.

a) Edita la especificació del paquet i reorganitza els subprogrames de forma alfabètica. Prem en Run Script per tornar a crear la especificació del paquet. Compila l'especificació del paquet. Què succeïx?

b) Edita el cos del paquet i reorganitza tots els subprogrames de forma alfabètica. Prem en Run Script per tornar a crear la especificació del paquet. Recompila l'especificació del paquet. Què succeïx?

c) Corregeix l'error de compilació utilitzant una declaració anticipada en el cos per a la referencia de subprograma adequada. Prem en Run Script per tornar a crear el paquet i, a continuació, recompila el paquet. Què succeïx?

Creació de disparadors

En aquesta pràctica, crearàs disparadors de sentencia i de fila. També crearàs procediments que es cridaran des de els disparadors.

1. Les files de la taula JOBS emmagatzemen els salaris mínims i màxims permesos per als diferents valors de JOB_ID. Escriu un codi per garantir que el salari dels empleats estigui dins del rang permés pel seu tipus de treball, per a operacions de inserció i actualització.

a) Crea un procediment anomenat CHECK_SALARY, de la següent forma:

i) El procediment accepta dos paràmetres, un per a la cadena de l'identificador de treball del empleat i l'altre per al salari.

ii) El procediment utilitza l'identificador de treball per a determinar el salari mínim i màxim per al treball especificat.

iii) Si el paràmetre del salari, mínim i màxim inclosos, no es troba dins del rang de salaris, apareixerà una excepció d'aplicació amb el missatge “Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>.” Substitueix els diferents elements del missatge per els valors que proporcionen els paràmetres i les variables omplerts amb consultes. Guarda l'arxiu.

b) Crea un disparador anomenat CHECK_SALARY_TRG en la taula EMPLOYEES que arranqui davant d'una operació INSERT o UPDATE en cada fila:

i) El disparador ha de cridar al procediment CHECK_SALARY per executar la lògica de negoci.

ii) El disparador ha de transferir el nou identificador de treball i salari als paràmetres de procediment.

2. Prova el disparador CHECK_SAL_TRG utilitzant els següents casos:

a) Utilitza el procediment EMP_PKG.ADD_EMPLOYEE per afegir a la empleada Eleanor Beh al departament 30. Què succeeix? ¿Per què?

b) Actualitza el salari de l'empleat 115 a 2.000 dòlars. En un altre operació d'actualització, canvia l'identificador de treball de l'empleat a HR_REP. Què succeeix en cada cas?

c) Actualitza el salari de l'empleat 115 a 2.800 dòlars. Què succeeix?

3. Actualitza el disparador CHECK_SALARY_TRG per a que arranqui només quan els valors de l'identificador de treball o el salari hagin canviat en realitat.

a) Implanta la regla de negoci utilizant una clàusula WHEN per comprovar si els valors JOB_ID o SALARY han canviat.

Nota: assegurat de que la condició manega NULL en els valors de OLD.column_name si es realitza una operació INSERT; si no es així, la operació d'inserció fallarà.

b) Comprova el disparador executant el procediment EMP_PKG.ADD_EMPLOYEE amb els següents valors de paràmetroes:

- p_first_name: 'Eleanor'

- p_last name: 'Beh'

- p_Email: 'EBEH'

- p_Job: 'IT_PROG'

- p_Sal: 5000

c) Actualitza als empleats amb un treball IT_PROG incrementant el seu salari en 2.000 dòlars. Què succeïx?

d) Actualitza a 9.000 dòlars el salari de Eleanor Beh.

Indicació: utilitza una sentencia UPDATE amb una subconsulta en la clàusula WHERE. Què succeïx?

e) Canvia el treball de Eleanor Beh a ST_MAN utilitzant un altre sentencia UPDATE amb una subconsulta. Què succeïx?

4. Se't demana que evitis que es suprimeixi als empleats durant les hores laborables.

a) Escriu un disparador de sentencia anomenat DELETE_EMP_TRG en la taula EMPLOYEES per evitar que les files es suprimeixin durant hores laborables entre setmana, es a dir, de las 9:00 a.m. a las 6:00 p.m.

b) Intenta suprimir els empleats amb JOB_ID SA_REP que no estiguin assignats a un departamento.

Indicació: empleat Grant amb identificador 178.

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

En aquesta pràctica, implantaràs una regla de negoci senzilla per garantir la integritat de les dades dels salaris dels empleats respecte al rang de salaris vàlids per als seus treballs. Crea un disparador per a aquesta regla. Durant aquest procés, els nous disparadors donaran lloc a un efecte en cascada amb disparadors creats en la secció pràctica de la lliçó anterior. L'efecte en cascada originarà una excepció de taula mutant en la taula JOBS. A continuació, crea un paquet PL/SQL i disparadors addicionals per resoldre el problema de la taula mutant.

1. Els empleats reben un augment de sou automàticament si el salari mínim d'un treball s'augmenta a un valor superior que els seus salaris actuals. Implanta aquest requisit amb un procediment empaquetat al que cridi el disparador de la taula JOBS. Quan intenta actualitzar el salari mínim en la tabla JOBS e intenta actualitzar els salaris dels empleats, el disparador CHECK_SALARY intenta llegir la taula 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.