Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: PL-SQL-2»
(→Creació de paquets) |
(→Creació de paquets) |
||
Línia 171: | Línia 171: | ||
'''2.''' Crea i crida un paquet que contingui construccions públiques i privades. | '''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 | + | :a) Crea una especificació del paquet i un cos del paquet anomenats EMP_PKG, que contingui els següents procediments i funció creats anteriorment: |
− | contingui els següents procediments i funció creats anteriorment: | ||
− | i) Procediment ADD_EMPLOYEE com a construcció pública | + | ::i) Procediment ADD_EMPLOYEE com a construcció pública |
− | ii) Procedimient GET_EMPLOYEE com a construcció pública | + | ::ii) Procedimient GET_EMPLOYEE com a construcció pública |
− | iii) Funció VALID_DEPTID com a construcció privada | + | ::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 vereficar que s'ha afegit el nou empleat. |
Revisió del 17:22, 19 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
- - 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 vereficar que s'ha afegit el nou empleat.