Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: PL-SQL-2»
(→Ús de paquets proporcionats per Oracle) |
(→Ús de paquets proporcionats per Oracle) |
||
Línia 194: | Línia 194: | ||
identificadores de departamento. Por último, reorganizará los subprogramas en el cuerpo | identificadores de departamento. Por último, reorganizará los subprogramas en el cuerpo | ||
y la especificación del paquete para que estén en secuencia alfabética. | y la especificación del paquete para que estén en secuencia alfabética. | ||
+ | |||
'''1.''' Modifique el código del paquete EMP_PKG que ha creado en la Práctica 4, paso 2 y | '''1.''' Modifique el código del paquete EMP_PKG que ha creado en la Práctica 4, paso 2 y | ||
sobrecargue el procedimiento ADD_EMPLOYEE. | sobrecargue el procedimiento ADD_EMPLOYEE. | ||
− | a) En la especificación del paquete, agregue un nuevo procedimiento denominado | + | |
+ | :a) En la especificación del paquete, agregue un nuevo procedimiento denominado | ||
ADD_EMPLOYEE, que acepte los tres parámetros siguientes: | ADD_EMPLOYEE, que acepte los tres parámetros siguientes: | ||
− | i) First name | + | |
− | ii) Last name | + | ::i) First name |
− | iii) Department ID | + | ::ii) Last name |
− | b) Haga clic en Run Script (F5) para crear y compilar el paquete. | + | ::iii) Department ID |
− | c) Implante el nuevo procedimiento ADD_EMPLOYEE en el cuerpo del paquete, de | + | |
+ | :b) Haga clic en Run Script (F5) para crear y compilar el paquete. | ||
+ | |||
+ | :c) Implante el nuevo procedimiento ADD_EMPLOYEE en el cuerpo del paquete, de | ||
la siguiente forma: | la siguiente forma: | ||
− | i) Formatee la dirección de correo electrónico en caracteres en mayúscula, | + | |
+ | ::i) Formatee la dirección de correo electrónico en caracteres en mayúscula, | ||
utilizando la primera letra del nombre concatenado con las siete primeras | utilizando la primera letra del nombre concatenado con las siete primeras | ||
letras del apellido. | letras del apellido. | ||
− | ii) El procedimiento llamará al procedimiento ADD_EMPLOYEE existente para | + | ::ii) El procedimiento llamará al procedimiento ADD_EMPLOYEE existente para |
realizar la operación INSERT real utilizando los parámetros y el correo | realizar la operación INSERT real utilizando los parámetros y el correo | ||
electrónico formateado para proporcionar los valores. | electrónico formateado para proporcionar los valores. | ||
− | iii) Haga clic en Run Script para crear el paquete. Compile el paquete. | + | ::iii) Haga clic en Run Script para crear el paquete. Compile el paquete. |
− | d) Llame al nuevo procedimiento ADD_EMPLOYEE utilizando el nombre Samuel | + | |
+ | :d) Llame al nuevo procedimiento ADD_EMPLOYEE utilizando el nombre Samuel | ||
Joplin para agregarlo al departamento 30. | Joplin para agregarlo al departamento 30. | ||
− | e) Confirme que el nuevo empleado se ha agregado a la tabla EMPLOYEES. | + | |
− | 2 | + | :e) Confirme que el nuevo empleado se ha agregado a la tabla EMPLOYEES. |
+ | |||
+ | '''2.''' En el paquete EMP_PKG, cree dos funciones sobrecargadas denominadas | ||
GET_EMPLOYEE: | GET_EMPLOYEE: | ||
− | a) En la especificación del paquete, agregue las siguientes funciones: | + | |
− | i) La función GET_EMPLOYEE que acepta el parámetro denominado | + | :a) En la especificación del paquete, agregue las siguientes funciones: |
+ | |||
+ | ::i) La función GET_EMPLOYEE que acepta el parámetro denominado | ||
p_emp_id basado en el tipo employees.employee_id%TYPE. Esta | p_emp_id basado en el tipo employees.employee_id%TYPE. Esta | ||
función debe devolver EMPLOYEES%ROWTYPE. | función debe devolver EMPLOYEES%ROWTYPE. | ||
− | ii) La función GET_EMPLOYEE que acepta el parámetro denominado | + | |
+ | ::ii) La función GET_EMPLOYEE que acepta el parámetro denominado | ||
p_family_name de tipo employees.last_name%TYPE. Esta función | p_family_name de tipo employees.last_name%TYPE. Esta función | ||
debe devolver EMPLOYEES%ROWTYPE. | debe devolver EMPLOYEES%ROWTYPE. | ||
− | b) Haga clic en Run Script para volver a crear y compilar el paquete. | + | :b) Haga clic en Run Script para volver a crear y compilar el paquete. |
− | c) En el cuerpo del paquete: | + | |
− | i) Implante la primera función GET_EMPLOYEE para consultar un empleado | + | :c) En el cuerpo del paquete: |
+ | |||
+ | ::i) Implante la primera función GET_EMPLOYEE para consultar un empleado | ||
mediante el identificador del mismo. | mediante el identificador del mismo. | ||
− | ii) Implante la segunda función GET_EMPLOYEE para utilizar el operador de | + | |
+ | ::ii) Implante la segunda función GET_EMPLOYEE para utilizar el operador de | ||
igualdad en el valor suministrado en el parámetro p_family_name. | igualdad en el valor suministrado en el parámetro p_family_name. | ||
− | d) Haga clic en Run Script para volver a crear y compilar el paquete. | + | |
− | e) Agregue un procedimiento de utilidad PRINT_EMPLOYEE al paquete EMP_PKG, | + | :d) Haga clic en Run Script para volver a crear y compilar el paquete. |
+ | |||
+ | :e) Agregue un procedimiento de utilidad PRINT_EMPLOYEE al paquete EMP_PKG, | ||
de la siguiente forma: | de la siguiente forma: | ||
− | i) El procedimiento acepta EMPLOYEES%ROWTYPE como parámetro. | + | |
− | ii) El procedimiento muestra lo siguiente para un empleado en una línea, | + | ::i) El procedimiento acepta EMPLOYEES%ROWTYPE como parámetro. |
+ | ::ii) El procedimiento muestra lo siguiente para un empleado en una línea, | ||
mediante el paquete DBMS_OUTPUT: | mediante el paquete DBMS_OUTPUT: | ||
− | + | ||
− | - | + | :::- department_id |
− | - | + | :::- employee_id |
− | - | + | :::- first_name |
− | - | + | :::- last_name |
− | - | + | :::- job_id |
− | + | :::- salary | |
− | + | ||
− | + | :f) Haga clic en Run Script (F5) para crear y compilar el paquete. | |
− | + | ||
− | job_id | + | :g) Utilice un bloque anónimo para llamar a la función EMP_PKG.GET_EMPLOYEE |
− | salary | ||
− | f) Haga clic en Run Script (F5) para crear y compilar el paquete. | ||
− | g) Utilice un bloque anónimo para llamar a la función EMP_PKG.GET_EMPLOYEE | ||
con un identificador de empleado 100 y con apellido 'Joplin'. Utilice el | con un identificador de empleado 100 y con apellido 'Joplin'. Utilice el | ||
procedimiento PRINT_EMPLOYEE para mostrar los resultados para cada fila | procedimiento PRINT_EMPLOYEE para mostrar los resultados para cada fila | ||
devuelta. | devuelta. | ||
− | 3 | + | |
+ | '''3.''' Como la compañía no cambia con frecuencia sus datos de departamento, puede | ||
mejorar el rendimiento de EMP_PKG agregando un procedimiento público, | mejorar el rendimiento de EMP_PKG agregando un procedimiento público, | ||
INIT_DEPARTMENTS, para rellenar una tabla PL/SQL privada de identificadores de | INIT_DEPARTMENTS, para rellenar una tabla PL/SQL privada de identificadores de | ||
Línia 259: | Línia 275: | ||
contenido de la tabla PL/SQL privada con el fin de validar los valores de los | contenido de la tabla PL/SQL privada con el fin de validar los valores de los | ||
identificadores de departamento. | identificadores de departamento. | ||
− | Nota: el script del archivo de soluciones sol_04_03.sql contiene el código de los | + | |
− | pasos a, b y c. | + | '''Nota:''' el script del archivo de soluciones sol_04_03.sql contiene el código de los pasos a, b y c. |
− | a) En la especificación del paquete, cree un procedimiento denominado | + | |
+ | :a) En la especificación del paquete, cree un procedimiento denominado | ||
INIT_DEPARTMENTS sin parámetros. Para ello, agregue lo siguiente a la | INIT_DEPARTMENTS sin parámetros. Para ello, agregue lo siguiente a la | ||
sección de especificación del paquete antes de la especificación | sección de especificación del paquete antes de la especificación | ||
PRINT_EMPLOYEES: | PRINT_EMPLOYEES: | ||
PROCEDURE init_departments; | PROCEDURE init_departments; | ||
− | b) En el cuerpo del paquete, implante el procedimiento INIT_DEPARTMENTS para | + | |
+ | :b) En el cuerpo del paquete, implante el procedimiento INIT_DEPARTMENTS para | ||
almacenar todos los identificadores de departamento en una tabla de índice PL/SQL | almacenar todos los identificadores de departamento en una tabla de índice PL/SQL | ||
privada denominada valid_departments que contiene valores BOOLEAN. | privada denominada valid_departments que contiene valores BOOLEAN. | ||
− | i) Declare la variable valid_departments y su definición de tipo | + | ::i) Declare la variable valid_departments y su definición de tipo |
boolean_tab_type antes que todos los procedimientos del cuerpo. | boolean_tab_type antes que todos los procedimientos del cuerpo. | ||
Introduzca lo siguiente al comienzo del cuerpo del paquete: | Introduzca lo siguiente al comienzo del cuerpo del paquete: | ||
Línia 276: | Línia 294: | ||
INDEX BY BINARY_INTEGER; | INDEX BY BINARY_INTEGER; | ||
valid_departments boolean_tab_type; | valid_departments boolean_tab_type; | ||
− | ii) Utilice el valor de la columna department_id como índice para crear la | + | ::ii) Utilice el valor de la columna department_id como índice para crear la |
entrada en la tabla de índice para indicar su presencia y asignar a la entrada un | entrada en la tabla de índice para indicar su presencia y asignar a la entrada un | ||
valor de TRUE. Introduzca la declaración del procedimiento | valor de TRUE. Introduzca la declaración del procedimiento | ||
Línia 288: | Línia 306: | ||
END LOOP; | END LOOP; | ||
END; | END; | ||
− | c) En el cuerpo, cree un bloque de inicialización que llame al procedimiento | + | |
+ | ::c) En el cuerpo, cree un bloque de inicialización que llame al procedimiento | ||
INIT_DEPARTMENTS para inicializar la tabla, de la siguiente forma: | INIT_DEPARTMENTS para inicializar la tabla, de la siguiente forma: | ||
BEGIN | BEGIN | ||
init_departments; | init_departments; | ||
END; | END; | ||
− | d) Haga clic en Run Script (F5) para crear y compilar el paquete. | + | |
− | 4 | + | ::d) Haga clic en Run Script (F5) para crear y compilar el paquete. |
− | tabla PL/SQL privada de identificadores de departamento. | + | |
− | a) Modifique la función VALID_DEPTID para realizar la validación utilizando la | + | '''4.''' Cambie la función de procesamiento de validación VALID_DEPTID para utilizar la tabla PL/SQL privada de identificadores de departamento. |
+ | |||
+ | :a) Modifique la función VALID_DEPTID para realizar la validación utilizando la | ||
tabla PL/SQL de valores de identificadores de departamento. Haga clic en Run | tabla PL/SQL de valores de identificadores de departamento. Haga clic en Run | ||
Script (F5) para crear el paquete. Compile el paquete. | Script (F5) para crear el paquete. Compile el paquete. | ||
− | b) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en | + | |
+ | :b) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en | ||
el departamento 15. ¿Qué sucede? | el departamento 15. ¿Qué sucede? | ||
− | c) Inserte un nuevo departamento. Especifique 15 como identificador del | + | |
+ | :c) Inserte un nuevo departamento. Especifique 15 como identificador del | ||
departamento y 'Security' como nombre del departamento. Confirme y | departamento y 'Security' como nombre del departamento. Confirme y | ||
verifique los cambios. | verifique los cambios. | ||
− | d) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en | + | |
+ | :d) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en | ||
el departamento 15. ¿Qué sucede? | el departamento 15. ¿Qué sucede? | ||
− | e) Ejecute el procedimiento EMP_PKG.INIT_DEPARTMENTS para actualizar la | + | |
+ | :e) Ejecute el procedimiento EMP_PKG.INIT_DEPARTMENTS para actualizar la | ||
tabla interna PL/SQL con los últimos datos del departamento. | tabla interna PL/SQL con los últimos datos del departamento. | ||
− | f) Pruebe el código llamando a ADD_EMPLOYEE con el nombre de empleado | + | |
+ | :f) Pruebe el código llamando a ADD_EMPLOYEE con el nombre de empleado | ||
James Bond, que trabaja en el departamento 15. ¿Qué sucede? | James Bond, que trabaja en el departamento 15. ¿Qué sucede? | ||
− | g) Suprima al empleado James Bond y el departamento 15 de sus respectivas | + | :g) Suprima al empleado James Bond y el departamento 15 de sus respectivas |
tablas, confirme los cambios y refresque los datos del departamento llamando al | tablas, confirme los cambios y refresque los datos del departamento llamando al | ||
procedimiento EMP_PKG.INIT_DEPARTMENTS. Asegúrese de introducir SET | procedimiento EMP_PKG.INIT_DEPARTMENTS. Asegúrese de introducir SET | ||
SERVEROUTPUT ON antes. | SERVEROUTPUT ON antes. | ||
− | 5 | + | |
− | estén en secuencia alfabética. | + | '''5.''' Reorganice los subprogramas en el cuerpo de la especificación del paquete para que estén en secuencia alfabética. |
− | a) Edite la especificación del paquete y reorganice los subprogramas de forma | + | |
+ | :a) Edite la especificación del paquete y reorganice los subprogramas de forma | ||
alfabética. Haga clic en Run Script para volver a crear la especificación del | alfabética. Haga clic en Run Script para volver a crear la especificación del | ||
paquete. Compile la especificación del paquete. ¿Qué sucede? | paquete. Compile la especificación del paquete. ¿Qué sucede? | ||
− | b) Edite el cuerpo del paquete y reorganice todos los subprogramas de forma | + | |
+ | :b) Edite el cuerpo del paquete y reorganice todos los subprogramas de forma | ||
alfabética. Haga clic en Run Script para volver a crear la especificación del | alfabética. Haga clic en Run Script para volver a crear la especificación del | ||
paquete. Recompile la especificación del paquete. ¿Qué sucede? | paquete. Recompile la especificación del paquete. ¿Qué sucede? | ||
− | c) Corrija el error de compilación utilizando una declaración anticipada en el cuerpo | + | |
− | para la referencia de subprograma adecuada. Haga clic en Run Script para volver | + | :c) Corrija el error de compilación utilizando una declaración anticipada en el cuerpo para la referencia de subprograma adecuada. Haga clic en Run Script para volver a crear el paquete y, a continuación, recompile el paquete. ¿Qué sucede? |
− | a crear el paquete y, a continuación, recompile el paquete. ¿Qué sucede? |
Revisió del 08:41, 20 abr 2016
Contingut
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 verificar que s'ha afegit el nou empleat.
Ús de paquets proporcionats per Oracle
En esta práctica, modificará el código del paquete EMP_PKG que ha creado anteriormente y, a continuación, sobrecargará el procedimiento ADD_EMPLOYEE. A continuación, creará dos funciones sobrecargadas denominadas GET_EMPLOYEE en el paquete EMP_PKG. También agregará un procedimiento público a EMP_PKG para rellenar una tabla PL/SQL privada de identificadores de departamento válidos, además de modificar la función VALID_DEPTID para utilizar el contenido de la tabla PL/SQL privada con el fin de validar los valores de identificador válidos. También cambiará la función de procesamiento de validación VALID_DEPTID para utilizar la tabla PL/SQL privada de identificadores de departamento. Por último, reorganizará los subprogramas en el cuerpo y la especificación del paquete para que estén en secuencia alfabética.
1. Modifique el código del paquete EMP_PKG que ha creado en la Práctica 4, paso 2 y sobrecargue el procedimiento ADD_EMPLOYEE.
- a) En la especificación del paquete, agregue un nuevo procedimiento denominado
ADD_EMPLOYEE, que acepte los tres parámetros siguientes:
- i) First name
- ii) Last name
- iii) Department ID
- b) Haga clic en Run Script (F5) para crear y compilar el paquete.
- c) Implante el nuevo procedimiento ADD_EMPLOYEE en el cuerpo del paquete, de
la siguiente forma:
- i) Formatee la dirección de correo electrónico en caracteres en mayúscula,
utilizando la primera letra del nombre concatenado con las siete primeras letras del apellido.
- ii) El procedimiento llamará al procedimiento ADD_EMPLOYEE existente para
realizar la operación INSERT real utilizando los parámetros y el correo electrónico formateado para proporcionar los valores.
- iii) Haga clic en Run Script para crear el paquete. Compile el paquete.
- d) Llame al nuevo procedimiento ADD_EMPLOYEE utilizando el nombre Samuel
Joplin para agregarlo al departamento 30.
- e) Confirme que el nuevo empleado se ha agregado a la tabla EMPLOYEES.
2. En el paquete EMP_PKG, cree dos funciones sobrecargadas denominadas GET_EMPLOYEE:
- a) En la especificación del paquete, agregue las siguientes funciones:
- i) La función GET_EMPLOYEE que acepta el parámetro denominado
p_emp_id basado en el tipo employees.employee_id%TYPE. Esta función debe devolver EMPLOYEES%ROWTYPE.
- ii) La función GET_EMPLOYEE que acepta el parámetro denominado
p_family_name de tipo employees.last_name%TYPE. Esta función debe devolver EMPLOYEES%ROWTYPE.
- b) Haga clic en Run Script para volver a crear y compilar el paquete.
- c) En el cuerpo del paquete:
- i) Implante la primera función GET_EMPLOYEE para consultar un empleado
mediante el identificador del mismo.
- ii) Implante la segunda función GET_EMPLOYEE para utilizar el operador de
igualdad en el valor suministrado en el parámetro p_family_name.
- d) Haga clic en Run Script para volver a crear y compilar el paquete.
- e) Agregue un procedimiento de utilidad PRINT_EMPLOYEE al paquete EMP_PKG,
de la siguiente forma:
- i) El procedimiento acepta EMPLOYEES%ROWTYPE como parámetro.
- ii) El procedimiento muestra lo siguiente para un empleado en una línea,
mediante el paquete DBMS_OUTPUT:
- - department_id
- - employee_id
- - first_name
- - last_name
- - job_id
- - salary
- f) Haga clic en Run Script (F5) para crear y compilar el paquete.
- g) Utilice un bloque anónimo para llamar a la función EMP_PKG.GET_EMPLOYEE
con un identificador de empleado 100 y con apellido 'Joplin'. Utilice el procedimiento PRINT_EMPLOYEE para mostrar los resultados para cada fila devuelta.
3. Como la compañía no cambia con frecuencia sus datos de departamento, puede mejorar el rendimiento de EMP_PKG agregando un procedimiento público, INIT_DEPARTMENTS, para rellenar una tabla PL/SQL privada de identificadores de departamento válidos. Modifique la función VALID_DEPTID para utilizar el contenido de la tabla PL/SQL privada con el fin de validar los valores de los identificadores de departamento.
Nota: el script del archivo de soluciones sol_04_03.sql contiene el código de los pasos a, b y c.
- a) En la especificación del paquete, cree un procedimiento denominado
INIT_DEPARTMENTS sin parámetros. Para ello, agregue lo siguiente a la sección de especificación del paquete antes de la especificación PRINT_EMPLOYEES: PROCEDURE init_departments;
- b) En el cuerpo del paquete, implante el procedimiento INIT_DEPARTMENTS para
almacenar todos los identificadores de departamento en una tabla de índice PL/SQL privada denominada valid_departments que contiene valores BOOLEAN.
- i) Declare la variable valid_departments y su definición de tipo
boolean_tab_type antes que todos los procedimientos del cuerpo. Introduzca lo siguiente al comienzo del cuerpo del paquete: TYPE boolean_tab_type IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER; valid_departments boolean_tab_type;
- ii) Utilice el valor de la columna department_id como índice para crear la
entrada en la tabla de índice para indicar su presencia y asignar a la entrada un valor de TRUE. Introduzca la declaración del procedimiento INIT_DEPARTMENTS al final del cuerpo del paquete (justo después del procedimiento print_employees), de la siguiente 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 cuerpo, cree un bloque de inicialización que llame al procedimiento
INIT_DEPARTMENTS para inicializar la tabla, de la siguiente forma: BEGIN init_departments; END;
- d) Haga clic en Run Script (F5) para crear y compilar el paquete.
4. Cambie la función de procesamiento de validación VALID_DEPTID para utilizar la tabla PL/SQL privada de identificadores de departamento.
- a) Modifique la función VALID_DEPTID para realizar la validación utilizando la
tabla PL/SQL de valores de identificadores de departamento. Haga clic en Run Script (F5) para crear el paquete. Compile el paquete.
- b) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en
el departamento 15. ¿Qué sucede?
- c) Inserte un nuevo departamento. Especifique 15 como identificador del
departamento y 'Security' como nombre del departamento. Confirme y verifique los cambios.
- d) Pruebe el código llamando a ADD_EMPLOYEE con el nombre James Bond en
el departamento 15. ¿Qué sucede?
- e) Ejecute el procedimiento EMP_PKG.INIT_DEPARTMENTS para actualizar la
tabla interna PL/SQL con los últimos datos del departamento.
- f) Pruebe el código llamando a ADD_EMPLOYEE con el nombre de empleado
James Bond, que trabaja en el departamento 15. ¿Qué sucede?
- g) Suprima al empleado James Bond y el departamento 15 de sus respectivas
tablas, confirme los cambios y refresque los datos del departamento llamando al procedimiento EMP_PKG.INIT_DEPARTMENTS. Asegúrese de introducir SET SERVEROUTPUT ON antes.
5. Reorganice los subprogramas en el cuerpo de la especificación del paquete para que estén en secuencia alfabética.
- a) Edite la especificación del paquete y reorganice los subprogramas de forma
alfabética. Haga clic en Run Script para volver a crear la especificación del paquete. Compile la especificación del paquete. ¿Qué sucede?
- b) Edite el cuerpo del paquete y reorganice todos los subprogramas de forma
alfabética. Haga clic en Run Script para volver a crear la especificación del paquete. Recompile la especificación del paquete. ¿Qué sucede?
- c) Corrija el error de compilación utilizando una declaración anticipada en el cuerpo para la referencia de subprograma adecuada. Haga clic en Run Script para volver a crear el paquete y, a continuación, recompile el paquete. ¿Qué sucede?