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

Soluciones a la Práctica 2-1: Creación de Funciones

En esta práctica, creará, compilará y utilizará las funciones almacenadas y un procedimiento.

1) Cree y llame a la función GET_JOB para devolver un cargo.

a) Cree y compile la función denominada GET_JOB para devolver un cargo (job_title) a partir del identificador (job_id).

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

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

    CREATE OR REPLACE FUNCTION get_job (p_jobid IN jobs.job_id%type)
     RETURN jobs.job_title%type IS
      v_title jobs.job_title%type;
    BEGIN
      SELECT job_title
      INTO v_title
      FROM jobs
      WHERE job_id = p_jobid;
      RETURN v_title;
    END get_job;
    /

b) Cree una variable de host VARCHAR2 denominada b_title, que permita una longitud de 35 caracteres. Llame a la función con identificador de trabajo SA_REP para que devuelva el valor de la variable del host y, a continuación, imprima la variable de host para ver el resultado.

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

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

    VARIABLE b_title VARCHAR2(35)
    EXECUTE :b_title := get_job ('SA_REP');
    PRINT b_title

2) Cree una función denominada GET_ANNUAL_COMP para devolver el salario anual de un empleado calculado a partir del salario mensual y la comisión transferidos como parámetros.

a) Cree la función GET_ANNUAL_COMP, que acepta valores de parámetros del salario mensual y la comisión. Uno o ambos valores transferidos pueden ser NULL, pero la función deberá devolver un salario anual no NULL. Utilice la siguiente fórmula básica para calcular el salario anual:

    (salary*12) + (commission_pct*salary*12)

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

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

    CREATE OR REPLACE FUNCTION get_annual_comp(
       p_sal IN employees.salary%TYPE,
       p_comm IN employees.commission_pct%TYPE)
      RETURN NUMBER IS
    BEGIN
      RETURN (NVL(p_sal,0) * 12 + (NVL(p_comm,0) * nvl(p_sal,0)* 12));
    END get_annual_comp;
    /

b) Utilice la función en una sentencia SELECT en la tabla EMPLOYEES para los empleados del departamento 30.

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

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

    SELECT employee_id, last_name, 
           get_annual_comp(salary,commission_pct) "Annual Compensation"
    FROM employees
    WHERE department_id=30
    /

3) Cree un procedimiento, ADD_EMPLOYEE, para insertar un nuevo empleado en la tabla EMPLOYEES. El procedimiento llamará a una función VALID_DEPTID para comprobar si el identificador (ID) de departamento especificado para el nuevo empleado existe en la tabla DEPARTMENTS.

a) Cree una función denominada VALID_DEPTID para validar el identificador de departamento especificado y devolver un valor BOOLEAN TRUE si existe el departamento.

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

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

    CREATE OR REPLACE FUNCTION valid_deptid(
      p_deptid IN departments.department_id%TYPE)
      RETURN BOOLEAN IS
      v_dummy PLS_INTEGER;
    BEGIN
      SELECT 1
      INTO v_dummy
      FROM departments
      WHERE department_id = p_deptid;
      RETURN TRUE;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
    END valid_deptid;
    /

b) Cree el procedimiento ADD_EMPLOYEE para agregar un empleado a la tabla EMPLOYEES. La fila se debe agregar a la tabla EMPLOYEES si la función VALID_DEPTID devuelve TRUE; de lo contrario, alertará al usuario con un mensaje adecuado. Proporcione los siguientes parámetros:

- first_name

- last_name

- email

- job: utilice 'SA_REP' como valor por defecto.

- mgr: utilice 145 como valor por defecto.

- sal: utilice 1000 como valor por defecto.

- comm: utilice 0 como valor por defecto.

- deptid: utilice 30 como valor por defecto.

- Utilice la secuencia EMPLOYEES_SEQ para definir la columna employee_id.

- Defina la columna hire_date en TRUNC(SYSDATE).

Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_b.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:

    CREATE OR REPLACE PROCEDURE add_employee(
       p_first_name employees.first_name%TYPE,
       p_last_name employees.last_name%TYPE,
       p_email employees.email%TYPE,
       p_job employees.job_id%TYPE DEFAULT 'SA_REP',
       p_mgr employees.manager_id%TYPE DEFAULT 145,
       p_sal employees.salary%TYPE DEFAULT 1000,
       p_comm employees.commission_pct%TYPE DEFAULT 0,
       p_deptid employees.department_id%TYPE DEFAULT 30)
    IS
    BEGIN
      IF valid_deptid(p_deptid) THEN
         INSERT INTO employees(employee_id, first_name,last_name, email,
                               job_id, manager_id, hire_date, salary,
                               commission_pct, department_id)
         VALUES (employees_seq.NEXTVAL, p_first_name,p_last_name, p_email,
                 p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm, p_deptid);
      ELSE
         RAISE_APPLICATION_ERROR (-20204, 'Invalid department ID. Try again.');
      END IF;
   END add_employee;
   /

c) Llame a ADD_EMPLOYEE para el nombre 'Jane Harris' del departamento 15, dejando otros parámetros con los valores por defecto. ¿Cuál es el resultado?

Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_c.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:

    EXECUTE add_employee('Jane', 'Harris', 'JAHARRIS', p_deptid=> 15)

d) Agregue otro empleado llamado Joe Harris en el departamento 80, dejando los parámetros restantes con sus valores por defecto. ¿Cuál es el resultado?

Abra el script /home/oracle/labs/plpu/solns/sol_02_01_03_d.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:

    EXECUTE add_employee('Joe', 'Harris', 'JAHARRIS',p_deptid=> 80)