M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T1

Soluciones a la Práctica 1-1: Creación, Compilación y Llamada de Procedimientos

En esta práctica, creará y llamará al procedimiento ADD_JOB, además de revisar el resultado. También creará y llamará a un procedimiento denominado UPD_JOB para modificar un trabajo en la tabla JOBS; asimismo, creará y llamará a un procedimiento denominado DEL_JOB para suprimir un trabajo de la tabla JOBS. Por último, creará un procedimiento denominado GET_EMPLOYEE para consultar la tabla EMPLOYEES, lo que devuelve el salario y el identificador de trabajo de un empleado cuando se proporciona el identificador de empleado.

1) Cree, compile y llame al procedimiento ADD_JOB y revise el resultado.

a) Cree un procedimiento denominado ADD_JOB para insertar un nuevo trabajo en la tabla JOBS. Proporcione el identificador y el cargo utilizando dos parámetros.

Nota: puede crear el procedimiento (así como otros objetos) mediante la introducción del código en el área SQL Worksheet y, a continuación, hacer clic en el icono Run Script (F5). De esta forma, se crea y compila el procedimiento. Para saber si el procedimiento contiene o no errores, haga clic en el nombre del mismo en el nodo de procedimientos y, a continuación, seleccione Compile en el menú emergente.

Abra el archivo sol_01_01_a.sql en la carpeta /home/oraclelabs/plpu/solns. Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar el procedimiento. El código y el resultado se muestran de la siguiente forma:

Para ver el procedimiento que acaba de crear, haga clic en el nodo Procedures de Object Navigator. Si no aparece el procedimiento que se acaba de crear, haga clic con el botón derecho en el nodo Procedures y, a continuación, seleccione Refresh en el menú de acceso directo. El nuevo procedimiento se muestra de la siguiente forma:

b) Llame al procedimiento con IT_DBA como identificador de trabajo y Database Administrator como cargo. Consulte la tabla JOBS y vea el resultado.

Ejecute el script /home/oracle/labs/plpu/soln/sol_01_01_b.sql.

El código y el resultado se muestran de la siguiente forma:

c) Llame al procedimiento de nuevo y transfiera un identificador de trabajo ST_MAN y un cargo Stock Manager. ¿Qué sucede? ¿Por qué?

Se ha producido una excepción porque hay una restricción de integridad de clave única en la columna JOB_ID.

2) Cree un procedimiento denominado UPD_JOB para modificar un trabajo en la tabla JOBS.

a) Cree un procedimiento denominado UPD_JOB para actualizar el cargo.

Proporcione el identificador de trabajo y un cargo nuevo utilizando dos parámetros. Incluya el manejo de excepciones necesario si no se ha producido la actualización.

Ejecute el script /home/oracle/labs/plpu/soln/sol_01_02_a.sql.

El código y el resultado se muestran de la siguiente forma:

b) Llame al procedimiento para cambiar el cargo del identificador de trabajo IT_DBA a Data Administrator. Consulte la tabla JOBS y vea el resultado.

Ejecute el script /home/oracle/labs/plpu/soln/sol_01_02_b.sql.

El código y el resultado se muestran de la siguiente forma:

c) Pruebe la sección de manejo de excepciones del procedimiento intentando actualizar un trabajo que no exista. Puede utilizar el identificador de trabajo IT_WEB y el cargo Web Master.

3) Cree un procedimiento denominado DEL_JOB para suprimir un trabajo de la tabla JOBS.

a) Cree un procedimiento denominado DEL_JOB para suprimir un trabajo. Incluya el código de manejo de excepciones necesario si no se ha suprimido ningún trabajo.

Ejecute el script /home/oracle/labs/plpu/soln/sol_01_03_a.sql.

El código y el resultado se muestran de la siguiente forma:

b) Llame al procedimiento mediante el identificador de trabajo IT_DBA. Consulte la tabla JOBS y vea el resultado.

Para llamar al procedimiento y, a continuación, consultar la tabla JOBS, cargue el archivo sol_01_03_b.sql en la carpeta /home/oracle/labs/plpu/solns.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma:

c) Pruebe la sección de manejo de excepciones del procedimiento intentando suprimir un trabajo que no existe. Utilice IT_WEB como identificador de trabajo.

Aparecerá el mensaje que haya incluido en la sección de manejo de excepciones

del procedimiento como salida.

Para llamar al procedimiento y, a continuación, consultar la tabla JOBS, cargue el archivo sol_01_03_c.sql en la carpeta /home/oracle/labs/plpu/solns.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma:

4) Cree un procedimiento denominado GET_EMPLOYEE para consultar la tabla EMPLOYEES, lo que devuelve el salario y el identificador de trabajo de un empleado cuando se proporciona el identificador de empleado.

a) Cree un procedimiento que devuelva un valor de las columnas SALARY y JOB_ID para el identificador de empleado especificado. Elimine los errores de sintaxis, si los hay y, a continuación, recompile el código.

Abra el script /home/oracle/labs/plpu/solns/sol_01_04_a.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para crear y compilar el procedimiento. El código y el resultado se muestran de la siguiente forma:

Nota: Si no aparece el procedimiento que se acaba de crear en Object Navigator, haga clic con el botón derecho en el nodo Procedures de Object Navigator y, a continuación, seleccione Refresh en el menú de acceso directo. En Object Navigator, haga clic con el botón derecho en el nombre del procedimiento y seleccione Compile en el menú de acceso directo. El procedimiento se compila.

b) Ejecute el procedimiento utilizando las variables del host para los dos parámetros OUT: uno para el salario y el otro para el identificador de trabajo. Muestre el salario y el identificador de trabajo para el identificador de empleado 120.

Abra el script /home/oracle/labs/plpu/solns/sol_01_04_b.sql.

Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para llamar al procedimiento. El código y el resultado se muestran de la siguiente forma:

c) Llame al procedimiento de nuevo y transfiera un EMPLOYEE_ID 300. ¿Qué sucede? ¿Por qué?

Ningún empleado de la tabla EMPLOYEES tiene un EMPLOYEE_ID 300. La sentencia SELECT no ha recuperado ningún dato de la base de datos y ha generado un error fatal PL/SQL: NO_DATA_FOUND de la siguiente forma: