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 (job_title) a partir del identificador (job_id).

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.

Treballar amb paquets

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?

Ús de paquets proporcionats per Oracle

Autoritzar al usuari HR a emprar el paquet UTL_FILE:

  SQL> connect sys as sysdba;

  SQL> grant execute on sys.utl_file to hr;

En aquesta pràctica, utilitzarà el paquet UTL_FILE per generar un informe d'arxiu de text dels empleats de cada departament. Primer cal crear i executar un procediment anomenat EMPLOYEE_REPORT, que generà un informe d'empleats en un arxiu del sistema operatiu utilitzant el paquet UTL_FILE. Aquest informe generarà una llista dels empleats que tenen un salari superior al salari mig del seu departament. Finalment, es mostrara el contingut de l'arxiu de text per pantalla.

1. Crea un procediment anomenat EMPLOYEE_REPORT, que generi un informe d'empleats en un arxiu del sistema operatiu utilitzant el paquet UTL_FILE.

Aquest informe generarà una llista dels empleats que tenen un salari superior al salari mig del seu departament.

a) El procediment rebrà dos paràmetres. El primer és el directori de sortida. El segon és el nom de l'arxiu de text.

Nota: Utilitza el valor de la ubicació del directori UTL_FILE. Agrega una secció per manegar les excepcions que es poden produir al utilitzar el paquet UTL_FILE.

b) Crea i compila el procediment.

2. Crida al procediment amb els dos arguments següents:

a) Utilitza REPORTS_DIR com alies per l'objecte de directori com a primer paràmtre.

b) Utilitza sal_rpt.txt com a segon paràmetre.

3. Per mostrar el contingut de l'arxiu, procedeix de la següent forma:

a) Fes clic dues vegades en la icona Terminal de l'escriptori. Es mostrarà la finestra Terminal.

b) Situat en el directori on es trobi l'arxiu generat (/home/oracle/labs/plpu/reports),

Nota: pots utilitzar l'ordre pwd per mostrar el directori actual.

c) Llista el contingut del directori (ordre ls).

d) Obre l'arxiu transferit sal_rpt.txt, amb l'editor que vulguis.

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à subjecta a canvis i obtindrà una excepció de taula mutant que es resol creant u nou paquet i disparadors addicionals.

a) Actualitza el paquet EMP_PKG (que vas actualitzà per última vegada en la pràctica 8), de la següent forma:

i) Agrega un procediment anomenat SET_SALARY, que actualitzi els salaris dels empleats.

ii) El procediment SET_SALARY accepta els dos paràmetres següents: el identificador de treball de aquells salaris que pot ser s'hagin d'actualitzar i el nou salari mínim per a l'identificador del treball.

b) Crea un disparador de fila anomenat UPD_MINSALARY_TRG en la taula JOBS, que cridi al procediment EMP_PKG.SET_SALARY quan el salari mínim de la taula JOBS s'actualitzi per a un identificador de treball especificat.

c) Escriu una consulta per mostrar l'identificador d'empleat, el cognom, l'identificador de treball, el salari actual i el salari mínim per als empleats que siguin programadors, es a dir, el seu JOB_ID es 'IT_PROG'. A continuació, actualitza el salari mínim en la taula JOBS per a augmentar-lo en 1.000 dòlars. Què succeïx?

2. Per resoldre el problema de la taula mutant, crea JOBS_PKG per mantenir en memòria una copia de les files de la taula JOBS. A continuació, modifica el procediment CHECK_SALARY per utilitzar les dades del paquet en lloc d'emetre una consulta en una taula mutant per evitar la excepció. Hauràs de crear crear un disparador de sentencia BEFORE INSERT OR UPDATE en la taula EMPLOYEES per inicialitzar l'estat del paquet JOBS_PKG abans de que arranqui el disparador de fila CHECK_SALARY.

a) Crea un nou paquet anomenat JOBS_PKG amb la següent especificació:

    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) Implanta el cos de JOBS_PKG, com es detalla a continuació:

i) Declara una taula d'índex PL/SQL privada anomenada jobs_tab_type indexada per un tipus de cadena basada en JOBS.JOB_ID%TYPE.

ii) Declara una variable privada anomendad jobstab basada en jobs_tab_type.

iii) El procediment INITIALIZE llegeix les files en la taula JOBS amb un bucle de cursor y utilitza el valor JOB_ID per al índex jobstab que se li assigni a la fila corresponent.

iv) La funció GET_MINSALARY utilitza un paràmetre p_jobid com a índex per a jobstab i retorna min_salary per a aquest element.

v) La funció GET_MAXSALARY utilitza un paràmetre p_jobid com a índex per a jobstab y retorna max_salary per a aquest element.

vi) El procediment SET_MINSALARY utilitza su p_jobid com a índex per a jobstab amb el fi de definir el camp min_salary del seu element en el valor del paràmetre min_salary.

vii) El procediment SET_MAXSALARY utilitza su p_jobid com a índex per a jobstab amb el fi de definir el camp max_salary del seu element en el valor del paràmetre max_salary.

c) Copia el procediment CHECK_SALARY de la pràctica 10, exercici 1a, i modifica el codi substituint la consulta de la taula JOBS amb sentencies per definir les variables locals minsal y maxsal amb valors de les dades JOBS_PKG cridant a les funcions GET_*SALARY adequades. Aquest pas ha d'eliminar l'excepció de disparador mutant.

d) Implanta un disparador de sentencia BEFORE INSERT OR UPDATE anomenat INIT_JOBPKG_TRG que utilitzi la sintaxis CALL per cridar al procediment JOBS_PKG.INITIALIZE, amb la finalitat de garantir que l'estat del paquet sigui actual abans de que es realitzin les operacions DML.

e) Prova els canvis de codi executant la consulta per mostrar els empleats que son programadors i, a continuació, emet una sentencia de actualització per augmentar el salari mínim del tipus de treball IT_PROG en 1.000 en la taula JOBS. Després d'això, realitza una consulta dels empleats amb el tipus de treball IT_PROG per comprovar els canvis resultants. Els salaris de què empleats s'han definit en el mínim per als seus treballs?

3. Donat que CHECK_SALARY_TRG arranca el procediment CHECK_SALARY, abans d'afeir o actualitzar un empleat, has de comprovar si encara funciona com s'esperava.

a) Prova'l agregant un nou empleat mitjançant EMP_PKG.ADD_EMPLOYEE amb els següents paràmetres: (‘Steve’,‘Morse’, ‘SMORSE’, and sal => 6500). Què succeïx?

b) Per corregir el problema trobat al afegir o actualitzar un empleat:

i) Crea un disparador de sentencia BEFORE INSERT OR UPDATE anomenat EMPLOYEE_INITJOBS_TRG en la taula EMPLOYEES que cridi al procediment JOBS_PKG.INITIALIZE.

ii) Utilitza la sintaxis CALL en el cos del disparador.

c) Prova el disparador afegint l'empleat Steve Morse de nou. Confirma el registre afegint en la taula EMPLOYEES mostrant l'identificador d'empleat, el nom i el cognom, el salari, l'identificador de treball i el identificador de departament.

Solucions exercicis

• Creació de procediments

• Creació de funcions i depuració de subprogrames

• Creació i ús de paquets

• Treballar amb paquets
• Creació de disparadors