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:

c)

d)

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.