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