Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T2»

Revisió del 20:44, 23 abr 2016

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.

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
    /