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:

CREATE OR REPLACE PROCEDURE add_job(
  p_jobid jobs.job_id%type,
  p_jobtitle jobs.job_title%type) IS
BEGIN
  INSERT INTO jobs(job_id, job_title)
  VALUES (p_jobid, p_jobtitle);
  COMMIT;
END;

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:

BEGIN
   add_job('IT_DBA', 'Database Administrador');
END;

SELECT * FROM jobs WHERE job_id = 'IT_DBA';

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.

BEGIN
   add_job('ST_MAN', 'Stock Manager');
END;

Informe de error -
ORA-00001: unique constraint (HR.JOB_ID_PK) violated
ORA-06512: at "HR.ADD_JOB", line 5
ORA-06512: at line 2
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

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:

CREATE OR REPLACE PROCEDURE upd_job(
  p_jobid jobs.job_id%type,
  p_jobtitle jobs.job_title%type) IS
BEGIN
  UPDATE jobs
  SET job_title =  p_jobtitle
  WHERE job_id = p_jobid;
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20202, 'No job updated');
  END IF;
  COMMIT;
END;  

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:

BEGIN
   upd_job('IT_DBA', 'Data Administrador');
END;

SELECT * FROM jobs WHERE job_id = 'IT_DBA';

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.

BEGIN
   upd_job('IT_WEB', 'Web Master');
END;

Informe de error -
ORA-20202: No job updated
ORA-06512: at "HR.UPD_JOB", line 9
ORA-06512: at line 2

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:

CREATE OR REPLACE PROCEDURE del_job(
  p_jobid jobs.job_id%type) IS
BEGIN
  DELETE jobs
  WHERE job_id = p_jobid;
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20203, 'No jobs deleted');
  END IF;
  COMMIT;
END;

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:

BEGIN
   del_job('IT_DBA');
END;

SELECT * FROM jobs WHERE job_id = 'IT_DBA';

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:

BEGIN
   del_job('IT_WEB');
END;

Informe de error -
ORA-20203: No jobs deleted
ORA-06512: at "HR.DEL_JOB", line 7
ORA-06512: at line 2

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.

CREATE OR REPLACE PROCEDURE get_employee(
  p_empid IN employees.employee_id%type,
  p_sal OUT employees.salary%type,
  p_job OUT employees.job_id%type) IS
BEGIN
  SELECT salary, job_id
  INTO p_sal, p_job
  FROM employees
  WHERE employee_id = p_empid;  
END get_employee;  

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:

VARIABLE v_salary NUMBER
VARIABLE v_job VARCHAR2(15)

EXECUTE get_employee(120, :v_salary, :v_job)

PRINT v_salary v_job

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:

EXECUTE get_employee(300, :v_salary, :v_job)

Informe de error -
ORA-01403: no data found
ORA-06512: at "HR.GET_EMPLOYEE", line 6
ORA-06512: at line 1
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.