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

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

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.

    DECLARE
      CURSOR c_dept_cursor IS
        SELECT department_id,department_name
        FROM departments
        WHERE department_id < 100
        ORDER BY department_id;

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.

    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;

c) Declare las variables que contienen los valores recuperados de cada cursor.

Utilice el atributo %TYPE mientras declara las variables.

    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;

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.

    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);

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.

    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;

f) Termine el primer bucle y cierre c_dept_cursor. A continuación, termine la sección ejecutable.

   END LOOP;
   CLOSE c_dept_cursor;
   END;

g) Ejecute el script. La salida de ejemplo es la siguiente: