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

Revisió del 13:17, 15 març 2018

Solución 7-1: Uso de Cursores Explícitos

En esta lección, realizará dos ejercicios:

• Primero, utilizará un cursor explícito para procesar un número de filas de una tabla y rellenar otra tabla con los resultados mediante un bucle FOR de cursor.
• Después, escribirá un bloque PL/SQL que procese la información con dos cursores, incluido uno que utilice un parámetro.

1) Cree un bloque PL/SQL que realice las siguientes acciones:

a) En la sección de declaraciones, declare e inicialice una variable llamada v_deptno de tipo NUMBER. Asigne un valor de identificador de departamento válido (consulte los valores en la tabla del paso d).

DECLARE
  v_deptno NUMBER := 10;

b) Declare un cursor llamado c_emp_cursor, que recupere last_name, salary y manager_id de los empleados que trabajan en el departamento especificado en v_deptno.

CURSOR c_emp_cursor IS
  SELECT last_name, salary,manager_id
  FROM employees
  WHERE department_id = v_deptno;

c) En la sección ejecutable, utilice el bucle FOR de cursor para realizar operaciones en los datos recuperados. Si el salario del empleado es menor que 5.000 y si el identificador de superior es 101 o 124, aparece el mensaje “<<last_name>> Due for a raise”. De lo contrario, aparece el mensaje “<<last_name>> Not Due for a raise”.

BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    IF emp_record.salary < 5000 AND (emp_record.manager_id=101OR emp_record.manager_id=124) THEN
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
    ELSE
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
    END IF;
   END LOOP;
END;

d) Pruebe el bloque PL/SQL para los siguientes casos:

   Department ID      Message
    10                Whalen Due for a raise
    20                Hartstein Not Due for a raise
                      Fay Not Due for a raise
    50                Weiss Not Due for a raise
                      Fripp Not Due for a raise
                      Kaufling Not Due for a raise
                      Vollman Not Due for a raise. . .
                      . . .
                      OConnell Due for a raise
                      Grant Due for a raise
    80                Russell Not Due for a raise
                      Partners Not Due for a raise
                      Errazuriz Not Due for a raise
                      Cambrault Not Due for a raise
                      . . .
                      Livingston Not Due for a raise
                      Johnson Not Due for a raise
</source>
Solució:

DECLARE
  v_deptno NUMBER := 10;
  CURSOR c_emp_cursor IS
    SELECT last_name, salary,manager_id
    FROM employees
    WHERE department_id = v_deptno;
BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    IF emp_record.salary < 5000 AND (emp_record.manager_id=101OR emp_record.manager_id=124) THEN
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
    ELSE
      DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
    END IF;
  END LOOP;
END;
<source lang="SQL">

2) A continuación, escriba un bloque PL/SQL que declare y utilice dos cursores: uno sin parámetro y otro con parámetro. El primer cursor recupera el número de departamento y el nombre del departamento de la tabla departments para todos los departamentos cuyos números de identificador sean menores que 100. El segundo cursor recibe el número de departamento como parámetro y recupera los detalles de los empleados que trabajan en ese departamento y cuyo employee_id sea menor que 120.

:a) Declare un cursor c_dept_cursor para recuperar department_id y department_name para los departamentos con department_id menor que 100. Ordene por department_id.
<source lang="SQL">
DECLARE
  CURSOR c_dept_cursor IS
    SELECT department_id,department_name
    FROM departments
    WHERE department_id < 100
    ORDER BY department_id;
</source>
:b) Declare otro cursor c_emp_cursor que tome el número de departamento como parámetro y recupere los siguientes datos de la tabla employees: last_name, job_id, hire_date y salary de los empleados que trabajan en ese departamento, con employee_id menor que 120.
<source lang="SQL">
CURSOR c_emp_cursor(v_deptno NUMBER) IS
  SELECT last_name,job_id,hire_date,salary
  FROM employees
  WHERE department_id = v_deptno AND employee_id < 120;
</source>
:c) Declare las variables que contienen los valores recuperados de cada cursor.
:Utilice el atributo %TYPE mientras declara las variables.
<source lang="SQL">
v_current_deptno departments.department_id%TYPE;
v_current_dname departments.department_name%TYPE;
v_ename employees.last_name%TYPE;
v_job employees.job_id%TYPE;
v_hiredate employees.hire_date%TYPE;
v_sal employees.salary%TYPE;
</source>
:d) Abra c_dept_cursor, utilice un bucle simple y recupere los valores en las variables declaradas. Muestre el número y el nombre de departamento. Utilice el atributo de cursor adecuado para salir del bucle.
<source lang="SQL">
BEGIN
  OPEN c_dept_cursor;
  LOOP
    FETCH c_dept_cursor INTO v_current_deptno, v_current_dname;
    EXIT WHEN c_dept_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname);
</source>
:e) Abra c_emp_cursor transfiriendo el número de departamento actual como parámetro. Inicie otro bucle y recupere los valores de emp_cursor en las variables e imprima todos los detalles recuperados de la tabla employees.

:'''Nota'''
:* Compruebe si c_emp_cursor ya está abierto antes de abrirlo.
:* Utilice el atributo de cursor adecuado para la condición de salida.
:* Cuando termine el bucle, imprima una línea después de haber mostrado los detalles de cada departamento y cierre c_emp_cursor.
<source lang="SQL">
IF c_emp_cursor%ISOPEN THEN
  CLOSE c_emp_cursor;
END IF;
OPEN c_emp_cursor (v_current_deptno);
LOOP
  FETCH c_emp_cursor INTO v_ename,v_job,v_hiredate,v_sal;
  EXIT WHEN c_emp_cursor%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE (v_ename || ' ' || v_job || ' ' || v_hiredate || ' ' || v_sal);
END LOOP;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------');
CLOSE c_emp_cursor;
</source>
:f) Termine el primer bucle y cierre c_dept_cursor. A continuación, termine la sección ejecutable.
<source lang="SQL">
END LOOP;
  CLOSE c_dept_cursor;
END;
</source>
:g) Ejecute el script. La salida de ejemplo es la siguiente:

[[Imatge:M2UF3_SOL_Cursos_1.png |500px|center| Solucions Cursors]]

</source>
Solució:
DECLARE
  CURSOR c_dept_cursor IS
    SELECT department_id,department_name
    FROM departments
    WHERE department_id < 100
    ORDER BY department_id;
  v_current_deptno departments.department_id%TYPE;
  v_current_dname departments.department_name%TYPE;
  v_ename employees.last_name%TYPE;
  v_job employees.job_id%TYPE;
  v_hiredate employees.hire_date%TYPE;
  v_sal employees.salary%TYPE;
BEGIN
  OPEN c_dept_cursor;
  LOOP
    FETCH c_dept_cursor INTO v_current_deptno, v_current_dname;
    EXIT WHEN c_dept_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname);
    IF c_emp_cursor%ISOPEN THEN
      CLOSE c_emp_cursor;
    END IF;
    OPEN c_emp_cursor (v_current_deptno);
    LOOP
      FETCH c_emp_cursor INTO v_ename,v_job,v_hiredate,v_sal;
      EXIT WHEN c_emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (v_ename || ' ' || v_job || ' ' || v_hiredate || ' ' || v_sal);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------');
    CLOSE c_emp_cursor;
  END LOOP;
  CLOSE c_dept_cursor;
END;
</source>

==Solución 7-2: Uso de Cursores Explícitos==

Si tiene tiempo, realice el siguiente ejercicio opcional. Creará un bloque PL/SQL que utiliza un cursor explícito para determinar los n salarios más altos de los empleados.

1) Ejecute el script lab_07-2.sql para crear una nueva tabla top_salaries para almacenar los salarios de los empleados.

2) En la sección de declaraciones, declare la variable v_num del tipo NUMBER que contenga el número n que representa los n salarios más altos de la tabla employees. Por ejemplo, para ver los cinco salarios principales, introduzca 5.
Declare otra variable sal del tipo employees.salary. Declare un cursor, c_emp_cursor, que recupere los salarios de los empleados en orden descendente.
Recuerde que los salarios no deben estar duplicados.
<pre>
    DECLARE
      v_num NUMBER(3) := 5;
      v_sal employees.salary%TYPE;
      CURSOR c_emp_cursor IS
        SELECT salary
        FROM employees
        ORDER BY salary DESC;

3) En la sección ejecutable, abra el bucle, recupere los n salarios principales e insértelos en la tabla top_salaries. Puede utilizar un bucle simple para realizar operaciones con los datos. Además, utilice los atributos %ROWCOUNT y %FOUND para la condición de salida.

Nota: asegúrese de agregar una condición de salida para evitar un bucle infinito.

    BEGIN
      OPEN c_emp_cursor;
      FETCH c_emp_cursor INTO v_sal;
      WHILE c_emp_cursor%ROWCOUNT <= v_num AND c_emp_cursor%FOUND
      LOOP
        INSERT INTO top_salaries (salary)
        VALUES (v_sal);
        FETCH c_emp_cursor INTO v_sal;
      END LOOP;
      CLOSE c_emp_cursor;
    END;

4) Después de insertarlos en la tabla top_salaries, muestre las filas con una sentencia SELECT. La salida que se muestra representa los cinco salarios más altos de la tabla employees.

    /
    SELECT * FROM top_salaries;

La salida de ejemplo es la siguiente:

5) Pruebe distintos casos especiales, como v_num = 0 o donde v_num es mayor que el número de empleados de la tabla employees. Vacíe la tabla top_salaries después de cada prueba.