viernes, 6 de enero de 2017

Cursores Explícitos en SQL

Cursores

Como se ha explicado anteriormente, las sentencias SQL que devuelven una sola fila se pueden incluir en un bloque PL/SQL. Los datos recuperados por la sentencia SQL se almacenan en las variables mediante la cláusula INTO. ¿Cómo las sentencias SQL son procesadas por el servidor Oracle? El servidor de Oracle asigna un área de memoria privada denominada área de contexto para el procesamiento de sentencias SQL. La sentencia SQL se analiza y se procesa en esta área. La información necesaria para el procesamiento y la información obtenida tras el procesamiento se almacenan en esta área. No setiene control sobre esta área, ya que se gestiona internamente por el servidor Oracle. Un cursor es un puntero a la zona de contexto. Sin embargo, este cursor es un cursor implícito y es gestionado automáticamente por el servidor Oracle. Cuando el bloque PL/SQL ejecuta una sentencia SQL, PL/SQL crea un cursor implícito. Existen dos tipos de cursores:
  • Cursor Implícito: se crea y está gestionado por el servidor Oracle. No se tiene acceso a él. El servidor de Oracle crea un cursor como cuando tiene que ejecutar una sentencia SQL.
  • Cursor Explícito: como programador, es posible que desee recuperar varias filas de una tabla de base de datos, En estos casos, se puede declarar cursores explícitamente en función de las necesidades de negocio. Un cursor declarado por los programadores se denomina cursor explícito. Se debe declara un cursor en la sección declarativa de un bloque PL/SQL

Cursores Explíctos

Los cursores explícitos se emplean para realizar consultas SELECT que pueden devolver cero filas, una o más de una fila.
Es necesario declarar un cursor explícito cuando se deben recuperar múltiples filas desde una o varias tablas de la Base de Datos para tener un puntero por cada fila recuperada y trabajar con una fila a la vez. El conjunto de filas retornadas por la query de múltiples filas es llamado Set Activo (Conjunto Activo) y su tamaño es el número de filas que cumplen los criterios de búsqueda de la sentencia SELECT.
Los cursores explícitos pueden ser controlados manualmente en el bloque PL/SQL por el programador. Se deben declarar en la sección Declarativa del bloque PL/SQL.


Control de Cursores Explícitos

Para trabajar con un cursor explícito se requiere:
  • Declarar el cursor con la instrucción DECLARE asignándole un nombre y definiendo la estructura de la consulta que será asociada al cursor.
  • Abrir el cursor con la instrucción OPEN que ejecuta la consulta y reemplaza cualquier variable que es referenciada. Las filas identificadas por la consulta son llamadas al set activo y ahora están disponibles para leerlas.
  • Recuperar los datos desde el cursor con la instrucción FETCH leyendo cada fila recuperada por el cursor.
  • Cerrar el cursor y liberar los recursos con la instrucción CLOSE: liberando así el set activo de filas. Esto hace posible reabrir el cursor para establecer un nuevo set activo.
Un programa PL/SQL abre un cursor, procesa filas devueltas por una consulta y, a continuación, cierra el cursor. El cursor marca la posición actual en el conjunto activo.
1.- La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el set activo y posiciona el cursor en la primera fila.
2.- La sentencia FETCH recupera la fila actual y avanza el cursor a la siguiente fila hasta que no existan más filas o hasta que se cumpla una condición especificada.
3.- La sentencia CLOSE libera el cursor


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Sintaxis
 
CURSOR nombre_cursor IS
       sentencia_select;
        
-- Ejemplo
 
DECLARE
  CURSOR cur_emp IS
  SELECT employee_id, last_name
      FROM employees WHERE department_id =30;
 
DECLARE
  v_locid NUMBER:= 1700;
  CURSOR cur_dept  IS
  SELECT *
      FROM departments WHERE location_id = v_locid;
Declarar el Cursor
En la sintaxis:

  • nombre_cursor: es un identificador PL/SQL que le da un nombre al cursor.
  • sentencia_select: es una instrucción o sentencia SQL que debe ser procesada cuyo resultado quedará en el Cursor.
La sentencia CURSOR está incluida en la sección ejecutable de un bloque PL/SQL En la declaración del cursor explícito la instrucción SELECT no contiene la cláusula INTO porque sólo se está definiendo el cursor, esto es parte de la instrucción FETCH
Una declaración de cursor puede hacer referencia a variables PL/SQL las que deben ser declaradas previamente. El set activo de un cursor es determinado por la sentencia Select en la declaración del cursor. Es obligatorio tener una cláusula INTO para una sentencia Select de un cursor explícito al momento de leer los datos recuperados por el Cursor.
Si se requiere que el procesamiento de las filas sea en un orden específico, se debe usar la cláusula ORDER BY en la query. El cursor puede ser cualquier sentencia SQL válida, incluyendo joins, subqueries y otras.
En el ejemplo de la izquierda, se declara un cursor explícito cur_emp el que obtendrá la identificación y apellidos de los empleado que trabajan en el departamento 30. El ejemplo de la derecha, el valor de la localidad está asignado a la variable v_locid la que es utilizada en la condición de la sentencia del cursor cur_dept. Por lo tanto el cursor obtendrá la información del departamento cuya localidad sea 1700.



1
2
3
4
5
6
7
8
9
10
11
12
13
-- Sintaxis
 
OPEN nombre_cursor;
 
-- Ejemplo
 
DECLARE
  CURSOR cur_emp IS
   SELECT employee_id, last_name
      FROM employees WHERE department_id =30;
...
BEGIN
  OPEN emp_cursor;

Abrir el Cursor

En la sintaxis:
nombre_cursor: es el cursor declarado en la sección de Declaración del bloque PL/SQL.

La sentencia OPEN está incluida en la sección Ejecutable de un bloque PL/SQL y es una sentencia ejecutable que realiza las siguientes operaciones:
  • Dinámicamente se asigna memoria para un área de contexto.
  • Parsea la sentencia SELECT.
  • Se examinan los valores de las variables de entrada.
  • Identifica el conjunto o set activo que satisface el criterio de búsqueda, basándose en los valores de variables y el contenido de las tablas que forman parte de la consulta.
  • Posiciona el puntero en la primera fila del conjunto activo.
    • Una vez abierto el cursor no se puede volver a abrir hasta que no se cierre. En el ejemplo, se abre el cursor cur_emp declarado inicialmente.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    -- Sintaxis
     
    FETCH nombre_cursor INTO lista_de_variables;
    FETCH nombre_cursor INTO registro_PL/SQL;
     
    -- Ejemplo
     
    DECLARE
    CURSOR cur_emp IS
         SELECT employee_id, last_name
            FROM employees
         WHERE department_id =30;
    v_lname       employees.last_name%TYPE;
    v_empno      employees.employee_id%TYPE;
    BEGIN
         OPEN cur_emp;
         FETCH cur_emp INTO v_empno, v_lname;
         DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || v_lname);
    END;
    Obtener Datos del Cursor En la sintaxis: nombre_cursor: es el cursor declarado en la sección de Declaración del bloque PL/SQL. lista_de_variables o registro_PL/SQL: es donde se almacenarán los valores leídos desde el Cursor. Se deben declarar previamente y deben ser compatibles en tipo de dato con las columnas seleccionadas en la consulta. La sentencia FETCH lee los datos desde la fila actual en las variables de salida PL/SQL y avanza el puntero a la siguiente fila en el set activo. Se puede usar el atributo %NOTFOUND para chequear si el conjunto activo se ha recuperado completamente. En el bloque PL/SQL del ejemplo, el cursor cur_emp obtiene la identificación y apellido de los empleados que trabajan en el departamento 30. En la sección de Ejecución se abre el cursor, se lee la primera fila y los valores son almacenados en las variables declaras para posteriormente mostrar la información. Para poder leer todas las filas recuperadas en el cursor, se debe generar una Iteración.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    DECLARE
      CURSOR c_emp_cursor IS
       SELECT employee_id, last_name FROM employees
       WHERE department_id =30;
      v_empno employees.employee_id%TYPE;
      v_lname employees.last_name%TYPE;
    BEGIN
      OPEN c_emp_cursor;
      LOOP
        FETCH c_emp_cursor INTO v_empno, v_lname;
        EXIT WHEN c_emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE( v_empno ||'  '||v_lname); 
      END LOOP;
    END;
    Obtener Datos del Cursor En el ejemplo ahora se utiliza un loop simple para leer todas las filas del cursor. Se utiliza también el atributo de cursor %NOTFOUND para la condición de salida del loop.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    -- Sintaxis
     
    CLOSE nombre_cursor;
     
    -- Ejemplo
     
    DECLARE
    CURSOR cur_emp IS
       SELECT employee_id, last_name
           FROM employees
        WHERE department_id =30;
    lname   employees.last_name%TYPE;
    empno   employees.employee_id%TYPE;
    BEGIN
       OPEN cur_emp;
       FETCH emp_cursor INTO empno, lname;
       DBMS_OUTPUT.PUT_LINE(empno || '   ' || lname);   
       CLOSE cur_emp;
    END;
    Cerrar el Cursor En la sintaxis: nombre_cursor: es el cursor declarado en la sección de Declaración del bloque PL/SQL. La sentencia CLOSE: Deshabilita (cierra) el cursor, libera el área de contexto y el set activo se elimina. Cierra el cursor después de completar el procesamiento de las sentencias FETCH. Un cursor puede ser reabierto sólo si éste está cerrado. Si se leen datos desde un cursor después de que éste se ha cerrado entonces la excepción INVALID_CURSOR se producirá. El número de cursores abiertos por sesión es determinado por el parámetro de la Base de Datos OPEN_CURSORS. Por defecto es 50.

    Atributos para Cursores Explícitos

    Hay cuatro atributos para obtener información del estado de un cursor explícito. Cuando se agrega el nombre del cursor, estos atributos devuelven información útil acerca de la ejecución de una sentencia de manipulación del cursor.
    • %ISOPEN: Atributo de tipo Booleano que retorna TRUE si el cursor se encuentra abierto.
    • %NOTFOUND: Atributo de tipo Booleano que retorna TRUE si el FECTH más reciente no retorna filas.
    • %FOUND: Atributo de tipo Booleano que Retorna TRUE si el FETCH más reciente retorna una fila.
    • %ROWCOUNT: Atributo de tipo numérico que retorna el número total de filas procesadas.

    1
    2
    3
    4
    5
    IF NOT cur_emp%ISOPEN THEN
                 OPEN cur_emp;
    END IF;
    LOOP
      FETCH cur_emp ...
    Atributo %ISOPEN Se pueden leer filas sólo cuando el cursor se encuentra abierto. Usar el atributo de cursor %ISOPEN antes de realizar una lectura permite validar si el cursor se encuentra abierto. Retorna el estado del cursor. TRUE si está abierto y FALSE si no. En el ejemplo, antes de leer las filas del cursor cur_emp se verifica si está abierto o no. En el caso de no estar abierto el cursor se abre.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    DECLARE
    v_empleado   VARCHAR2(60);
    CURSOR cur_emp IS
         SELECT first_name || ' ' || last_name
             FROM employees;
    BEGIN
      OPEN cur_emp;
         LOOP
            FETCH  cur_emp INTO v_empleado;
            EXIT WHEN cur_emp%ROWCOUNT > 10 OR 
                                   cur_emp%NOTFOUND;       
            DBMS_OUTPUT.PUT_LINE(v_empleado);
         END LOOP;
      CLOSE cur_emp;
    END;
    Atributos %ROWCOUNT y %NOTFOUND En el ejemplo, el bloque obtiene a todos los empleados que existen en la tabla employees. La condición para salir del loop de lectura del cursor es que las filas leída sea mayor a 10 o que ya no existan más filas que leer en el cursor.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    DECLARE
      CURSOR cur_emp IS
       SELECT employee_id, last_name
          FROM employees
       WHERE  department_id = 30;
       reg_emp     cur_emp%ROWTYPE;
    BEGIN
      OPEN cur_emp;
      LOOP
        FETCH cur_emp INTO reg_emp;
        EXIT WHEN cur_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(reg_emp.employee_id || ' ' || reg_emp.last_name);   
      END LOOP;
    CLOSE cur_emp;
    END;
    Cursores y Registros Usando el atributo %ROWTYPE se puede definir un registro basado en las columnas seleccionadas en un cursor explícito utilizando el atributo %ROWTYPE, esto significa que los nombres de los campos del registro son los nombres de columnas seleccionadas. Esto es conveniente para procesar las filas del conjunto activo, porque sólo se tienen que hacer una lectura simple en el registro. Por lo tanto, los valores de la fila leída del cursor son almacenados directamente en los campos correspondientes del registro. En el bloque del ejemplo, se define el registro reg_emp basado en las columnas seleccionadas por el cursor cur_emp (identificación y apellido de los empleados del departamento 30). Al leer las filas del cursor los valores son almacenados en los campos del registro declarado. Los nombres de Los campos del registro son los nombres de las columnas obtenidas en el cursor (employee_id y last_name).

    Manejo del Cursor: Loop Simple

    Para poder iterar a través del cursor se puede usar LOOP Simple. Esto permite leer todas las filas (o una cantidad de filas determinadas) del cursor de acuerdo a la condición de salida del loop.

    1
    2
    3
    4
    5
    6
    7
    OPEN nombre_cursor;   
    LOOP       
          FETCH nombre_cursor INTO lista_variables | registro_PL/SQL;
          EXIT WHEN nombre_cursor%NOTFOUND | EXIT WHEN nombre_cursor%ROWCOUNT;
        /* Procesamiento de los registros recuperados y ejecución de sentencias */   
    END LOOP;   
    CLOSE nombre_cursor; 
     
     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    DECLARE
    CURSOR cur_emp IS
       SELECT employee_id, last_name
         FROM employees
        WHERE department_id =30;
    lname   employees.last_name%TYPE;
    empno   employees.employee_id%TYPE;
    BEGIN
      OPEN cur_emp;
      LOOP
           FETCH emp_cursor INTO empno, lname;
           EXIT WHEN cur_emp%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE(empno || '   ' || lname);   
      END LOOP;
    CLOSE cur_emp;
    END;
    Manejo del Cursor: Loop Simple En el bloque PL/SQL del ejemplo, se lee desde el cursor cur_emp la identificación y apellido de los empleados que trabajan en el departamento 30. La información se muestra hasta cuando no existan más filas que leer desde el cursor cur_emp.

    Manejo del Cursor: WHILE LOOP

    La instrucción FETCH aparece dos veces. Para leer todas filas se utiliza WHILE LOOP. Para validar que existan filas en el cursor se utiliza el atributo %FOUND.
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    OPEN nombre_cursor;   
    FETCH nombre_cursor  INTO lista_variables;   
    WHILE nombre_cursor%FOUND
    LOOP      
         /* Procesamiento de los registros recuperados  y ejecución de sentencias*/       
        FETCH nombre_cursor INTO lista_variables;   
    END LOOP;   
    CLOSE nombre_cursor;
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    DECLARE
      CURSOR cur_emp IS
       SELECT employee_id, last_name FROM employees
       WHERE department_id =30;
      empno        employees.employee_id%TYPE;
      lname         employees.last_name%TYPE;
    BEGIN
      OPEN cur_emp;
          FETCH cur_emp INTO empno, lname;
          WHILE cur_emp%FOUND LOOP
                  DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);
                  FETCH emp_cursor INTO empno, lname;
          END LOOP;
      CLOSE cur_emp;
    END;
    Manejo del Cursor: WHILE LOOP En el ejemplo, se hace una primera lectura del cursor cur_emp para poder validar en la condición si existen filas que leer antes de comenzar con la iteración. Por lo tanto mientras existan filas que leer desde el cursor se mostrará la información. Cuando ya no existan más filas que leer termina la iteración del bucle WHILE LOOP y se cierra el cursor.

    Manejo del Cursor: FOR LOOP

    El ciclo FOR LOOP es el método más fácil para procesar cursores explícitos ya que se ejecuta implícitamente las instrucciones OPEN, FETCH, EXIT y CLOSE. El loop finaliza automáticamente cuando la última fila es leída. El registro es declarado implícitamente. Los campos del registro serán las columnas seleccionadas en el cursor. En la sintaxis: nombre_registro : es el nombre del registro declarado implícitamente. nombre_cursor : es un identificador PL/SQL para el cursor declarado previamente.

    1
    2
    3
    FOR nombre_registro IN nombre_cursor LOOP  
     /* Procesamiento de los registros recuperados  y ejecución de sentencias*/
    END LOOP;
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    DECLARE
      CURSOR cur_emp IS
       SELECT employee_id, last_name
           FROM employees
        WHERE department_id =30;
    BEGIN
       FOR reg_emp IN cur_emp LOOP
           DBMS_OUTPUT.PUT_LINE( reg_emp.employee_id || ' ' || reg_emp.last_name);  
       END LOOP;
    END;
    Manejo del Cursor: FOR LOOP En el ejemplo, se lee cada una de las filas del cursor cur_emp y los valores se almacenan en el registro reg_emp cuyos campos serán employee_id y last_name que son las columnas seleccionadas en el cursor. Cuando no existen más filas que leer, el ciclo finaliza y se cierra el cursor. Declarar el registro reg_emp, abrir el cursor cur_emp, leer sus datos, finalizar el loop y cerrar el cursor se ejecutan en forma implícita utilizando FOR LOOP.

    1
    2
    3
    4
    5
    6
    7
    BEGIN
      FOR emp_record IN (SELECT employee_id, last_name FROM employees
                         WHERE department_id =30)
      LOOP
       DBMS_OUTPUT.PUT_LINE( emp_record.employee_id  ||' '||emp_record.last_name);  
       END LOOP;
    END;
    Cursor FOR LOOP usando Subconsultas En este bloque PL/SQL no hay una sección declarativa. La diferencia entre el cursor FOR LOOP utilizando subconsultas y el cursor loop FOR se encuentra en la declaración de cursor. Con cursor FOR loops usando subconsultas, no es necesario declarar el cursor en la sección declarativa. Se tiene que definir la sentencia SELECT que determina el conjunto activo en el loop. El ejemplo el registro emp_record almacena los empleados que trabajan en el departamento 30 los que son obtenidos usando una subconsulta. Luego, a través del loop, se mostrará toda la información almacenada en el registro.

    Cursores con Parámetros

    Se pueden pasar parámetros a un cursor. Esto significa que se puede abrir y cerrar un cursor explícito varias veces en un bloque, devolviendo un set activo diferente en cada ocasión. Para cada ejecución, el cursor se debe haber cerrado con anterioridad para poder ser abierto nuevamente con un nuevo conjunto de parámetros. Cada parámetro formal en la declaración del cursor debe tener su valor correspondiente en la sentencia OPEN. Los valores de los parámetros pasan al cursor cuando se abre y la consulta es ejecutada.
    Es particularmente útil cuando el mismo cursor es referenciado en forma repetida pero con valores diferentes.
    En la sintaxis:
    • nombre_cursor: es un identificador PL/SQL para el cursor declarado.
    • nombre_parámetro: es el nombre de un parámetro.
    • tipo_dato: es el tipo de dato escalar del parámetro.
    • sentencia_select: es una instrucción o sentencia SQL que debe ser procesada cuyo resultado quedará en el Cursor.
    • valor_parámetro: es el valor que se le asigna al parámetro definido en el cursor.
    1
    2
    3
    4
    CURSOR nombre_cursor[(nombre_parámetro tipo_dato, ...)] IS
      sentencia_select;
     
    OPEN  nombre_cursor(valor_parámetro,.....) ;
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    DECLARE
     CURSOR   emp_cursor (p_deptno NUMBER) IS
           SELECT  employee_id, last_name
              FROM  employees
           WHERE   department_id = p_deptno;
    v_empno      employees.employee_id%TYPE;
    v_lname      employees.last_name%TYPE;
    BEGIN
       OPEN emp_cursor (30);
       DBMS_OUTPUT.PUT_LINE('Empleados Depto 30');
          LOOP
              FETCH emp_cursor INTO v_empno, v_lname;
              EXIT WHEN emp_cursor%NOTFOUND;
              DBMS_OUTPUT.PUT_LINE('   ' || rpad(v_empno, 19, ' ') ||' '||v_lname);
          END LOOP;
       CLOSE emp_cursor;
       OPEN emp_cursor (20);
       DBMS_OUTPUT.NEW_LINE();    
       DBMS_OUTPUT.PUT_LINE('Empleados Depto 20');
           LOOP
               FETCH emp_cursor INTO v_empno, v_lname;
               EXIT WHEN emp_cursor%NOTFOUND;
               DBMS_OUTPUT.PUT_LINE('   ' || rpad(v_empno, 19, ' ') ||' '|| v_lname);
           END LOOP;
       CLOSE emp_cursor;
    END;
    Cursores con Parámetros El bloque del ejemplo posee un cursor con parámetro p_deptno que según el valor que se le asigne cuando se abra obtendrá información de los empleados que trabajen en el departamento asignado por parámetro. En este caso se muestran los empleados que trabajan en el departamento 30 y 20.

    Cláusula FOR UPDATE

    Si hay varias sesiones enuna sola base de datos, existe la posibilidad de que las filas de una tabla en particular sean actualizadas después de abrir el cursor. Estos datos actualizados se verán sólo cuando se vuelve a abrir el cursor. Por lo tanto, es mejor tener bloqueadas las filas antes de actualizar o eliminar filas. Puede bloquear las filas con la cláusula FOR UPDATE en la consulta del cursor.
    La cláusula FOR UPDATE es la última cláusula en una sentencia SELECT, incluso después de ORDER BY (si existe). Al consultar varias tablas, se puede utilizar la cláusula FOR UPDATE para limitar el bloqueo de filas a las tablas particulares. FOR UPDATE col_name (s) bloquea filas sólo en tablas que contienen col_name (s).
    En la sintaxis:
    • nombre_cursor: es un identificador PL/SQL para el cursor declarado.
    • columna_referenciada: es una columna en la tabla sobre la cual se realiza la query.
    • NOWAIT: retorna un error del Servidor Oracle si las filas están bloqueadas por otra sesión.
    • WAIT n: especifica el número de segundos para esperar y chequear si las filas están bloqueadas. Si la filas están bloqueadas después de n segundos retorna un error.
    •  
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- Sintaxis
     
     
    CURSOR nombre_cursor
    SELECT  ...
    FROM        ...
    FOR UPDATE [OF column_referenciada][NOWAIT | WAIT n];
     
    -- Ejemplo
     
    DECLARE CURSOR c_emp_cursor IS SELECT employee_id, last_name
                                   FROM  employees WHERE department_id = 80 FOR UPDATE OF salary NOWAIT;

    Cláusula WHERE CURRENT

    Es usada en conjunto con la cláusula FOR UPDATE cuando se utiliza un cursor SELECT FOR UPDATE para referenciar la fila actual en el cursor explícito. Se usa la combinación de ambas cláusulas para actualizar o eliminar la fila actual en la correspondiente tabla de la Base de datos.
    En la sintaxis:
  • nombre_cursor : es el nombre de un cursor declarado. El cursor debe haber sido declarado con la cláusula FOR UPDATE.
    • 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    DECLARE
      empleado  employees%ROWTYPE;
      CURSOR emp_cursor IS
         SELECT *
         FROM employees
         WHERE department_id = 100
         FOR UPDATE;
    BEGIN
      OPEN emp_cursor; -- Se produce el bloqueo
      LOOP
          FETCH emp_cursor INTO empleado;
          EXIT WHEN emp_cursor%NOTFOUND;
          IF empleado.salary < 7000 THEN
              UPDATE employees
              SET salary=salary + salary *.10
              WHERE CURRENT OF emp_cursor;
          END IF;
      END LOOP;
      CLOSE emp_cursor; -- No libera bloqueos
      COMMIT; /*Libera el bloqueo de las filas del cursor FOR UPDATE*/
    END ;
    Cláusula WHERE CURRENT El bloque del ejemplo obtiene las filas completas de los empleados que trabajan en el departamento 100. Al abrir el cursor FOR UPDATE se bloquean todas sus filas en la tabla (las filas que poseen el departamento 100). Después de cerrar el cursor, con la instrucción COMMIT se liberan las filas recuperadas por el cursor en la tabla original.

    Cursores con Subconsultas

    Una subconsulta es una consulta SQL, generalmente entre paréntesis, que está dentro de otra sentencia SQL. Cuando es evaluada, la subconsulta proporciona una valor o set de valores para la consulta externa. Las subconsultas son usadas a menudo en la cláusula WHERE de una sentencia Select. Pueden ser usadas también en una cláusula FROM creando una fuente de dato temporal para la consulta. El bloque del ejemplo, muestra a los empleados que posean un salario menor al salario promedio. (El resultado se muestra en la siguiente página).
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    DECLARE
    CURSOR mi_cursor IS
       SELECT first_name || ' ' || last_name, salary
        FROM employees
        WHERE salary < (SELECT ROUND(AVG(salary))
                                        FROM employees)
        ORDER BY salary, last_name;
    nombre   VARCHAR2(50);
    salario   employees.salary%TYPE;
    BEGIN
       OPEN mi_cursor;
       DBMS_OUTPUT.PUT_LINE('    FUNCIONARIOS CON SALARIO MENOR AL PROMEDIO  ');
       DBMS_OUTPUT.PUT_LINE('    ----------------------------------------  ');
       LOOP
           FETCH mi_cursor INTO nombre, salario;
           EXIT WHEN mi_cursor%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE(rpad(nombre, 25, ' ' ) || ' : ' || TO_CHAR(salario, '$999,999'));
         END LOOP;
      CLOSE mi_cursor;
    END;

    Trabajando con más de un cursor

    Se trabaja con más de un cursor a la vez cuando alguno de los valores recuperado del cursor principal es utilizado como parámetro en otro cursor. Primero se abre el cursor principal cuyos valores servirán como parámetros al siguiente cursor. Obtenidos los valores del primer cursor, se puede abrir el siguiente cursor. Los cursores se deben manejar con loops anidados ya sea utilizando el LOOP Simple o FOR LOOP.
    En el ejemplo, por cada departamento leído (primer cursor) se leen desde el segundo cursor todos los empleados que pertenecen al departamento. Una vez leídos todos los empleados se lee el siguiente departamento para obtener los empleados del nuevo departamento leído y así sucesivamente hasta cuando ya no existan más departamentos que leer desde el cursor principal.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    DECLARE
     CURSOR cur_deptos IS
       SELECT department_id, department_name
         FROM departments;    
     CURSOR cur_emp_depto(deptno NUMBER) IS
       SELECT first_name || ' ' || last_name nombre_emp
         FROM employees
        WHERE department_id = deptno;
    v_total_emp  NUMBER(2);
    BEGIN
      DBMS_OUTPUT.PUT_LINE('    Empleados por Departamento');
      DBMS_OUTPUT.PUT_LINE('===================================');
      DBMS_OUTPUT.NEW_LINE();
      FOR reg_deptos IN cur_deptos LOOP
          DBMS_OUTPUT.PUT_LINE('Departamento: '|| reg_deptos.department_name);    
          DBMS_OUTPUT.PUT_LINE('=====================================');
          v_total_emp := 0;    
              FOR reg_emp_depto IN cur_emp_depto(reg_deptos.department_id) LOOP
                       DBMS_OUTPUT.PUT_LINE(reg_emp_depto.nombre_emp);
                       v_total_emp := v_total_emp + 1;
              END LOOP;
              DBMS_OUTPUT.PUT_LINE('=====================================');
              DBMS_OUTPUT.PUT_LINE('Total de Empleados: ' || v_total_emp);
              DBMS_OUTPUT.NEW_LINE();
      END LOOP;
    END;
     
     
     
     
    Fuentes:  http://catalanseb.blogspot.com.es/2015/05/usando-cursores-explicitos.html
Publicado por en
Etiquetas:

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)