Viernes, 12 Junio 2020 02:30

Cursores Explícitos

Rate this item
(0 votes)

Objetivos:

  • Distinguir entre Cursores Implícitos y Explícitos.
  • Discutir las razones para utilizar los cursores explícitos.
  • Declarar y controlar cursores explícitos.
  • Utilizar bucles simples y cursores FOR LOOP para obtener datos.
  • Declarar y utilizar cursores con parámetros.
  • Bloquear filas con la cláusula FOR UPDATE.
  • Hacer referencia a la fila actual con la cláusula WHERE CURRENT OF.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.


Los Cursores

El servidor Oracle utiliza áreas de trabajo (llamadas zonas privadas SQL) al ejecutar sentencias SQL para almacenar y procesar información. Puede utilizar los cursores explícitos para nombrar una zona privada SQL y acceder a su información almacenada.

El servidor Oracle abre implícitamente un cursor para procesar cada instrucción SQL que no este asociada con un cursor declarado explícitamente.

Tipo

Descripción

Implícito

Son declarados por PL/SQL de forma implícita para cada DML o sentencia SELECT.

Explícito

Son ideales para consultas que devuelven varias filas; son declarados y gestionados por el programador, y se manipulan a través de sentencias específicas en sentencias ejecutables del bloque.

 

Operaciones de los Cursores Explícitos

Se declaran cursores explícitos en PL/SQL cuando se tiene una instrucción SELECT que devuelve varias filas, ya que con el cursor es posible procesar cada fila devuelta.

Se conoce como el active set(conjunto activo) al conjunto de filas devueltas por una consulta de múltiples filas. Su tamaño es el número de filas que coinciden con su criterio de búsqueda.

Funciones de los Cursores Explícitos

  • Puede realizar el procesamiento fila por fila más allá de la primera fila devuelta por una consulta.
  • Mantiene un registro de la fila que se está procesando actualmente.
  • Permite al programador controlar manualmente los cursores explícitos en el bloque PL/SQL.

Manejando Cursores Explícitos

Ahora que tiene una comprensión conceptual de los cursores, revise los pasos para utilizarlos.

1. En la parte declarativa de un bloque PL/SQL, declare el cursor, asignele un nombre y defina la estructura de su consulta.

2. Abra el cursor.

La sentencia OPEN ejecuta la consulta y vincula las variables que se hayan referenciado. Las filas identificadas por la consulta (active set) ahora están disponibles para el FETCH.

3. Obtener (FETCH) los datos del cursor.

En el diagrama de flujo anterior: Luego de cada búsqueda (FETCH), se prueba la existencia de filas en el cursor. Si no hay más filas para procesar, se debe cerrar el cursor.

4. Cierre el cursor.

La sentencia CLOSE libera el active set de filas. Ahora es posible volver a abrir el cursor para establecer un nuevo active set.

Detalles:

1. La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el active set, y posiciona el cursor en la primera fila.

2. La sentencia FETCH recupera la fila actual y avanza el cursor a la siguiente hasta que no haya más filas o hasta que cierta condición se cumpla.

3. La sentencia CLOSE libera el cursor.

Sintaxis:

CURSOR cursor_name IS
     select_statement;


En la Sintaxis:

  • cursor_name: Es un identificador PL/SQL.
  • select_statement: Es una sentencia SELECT sin la cláusula INTO.

En un cursor el active set está determinado por el SELECT al momento de su declaración. En PL/SQL es obligatorio tener una cláusula INTO al usar una sentencia SELECT. Sin embargo, tenga en cuenta que la sentencia SELECT en la declaración de cursor no puede tener dicha cláusula. Esto se debe a que sólo se está definiendo el cursor en la parte declarativa, no recuperando su filas.

Notas:

  • No incluya la cláusula INTO en la declaración del cursor, ya que está se usa más adelante en la sentencia FETCH.
  • Si desea que las filas sean procesadas en algún orden especifico, utilice la cláusula ORDER BY en la consulta.
  • El cursor puede ser cualquier sentencia SELECT válida, incluyendo JOINsUNIONs, subconsultas, y así sucesivamente.

Ejemplos:

DECLARE

    v_dept  NUMBER := 30;

    CURSOR c_emps IS
        SELECT  employee_id,
                last_name,
                salary,
                job_id
        FROM employees
        WHERE department_id = v_dept;

    CURSOR c_dept_sal IS
        SELECT  d.department_name,
               SUM(e.salary)
        FROM employees e, departments d
        WHERE e.department_id = d.department_id
        GROUP BY d.department_name;

/*El ejemplo muestra la declaración de dos cursores: v_dept y c_dept_salc_emps consulta algunas informaciones de los empleados del departamento que contenga la variable v_dept, que en este caso es 30;  c_dept_sal consulta la suma de salarios de los empleados de cada departamento.*/

 

Sentencia OPEN

La sentencia OPEN ejecuta la consulta asociada con el cursor, identifica el active set, y posiciona el puntero en la primera fila del cursor. La sentencia OPEN se incluye en la sección ejecutable del bloque PL/SQL.

OPEN es una sentencia ejecutable que realiza las siguientes operaciones:

1. Dinámicamente asigna la memoria para un área de contexto.

2. Analiza la sentencia SELECT.

3. Vincula las variables de entrada (establece los valores de las variables de entrada mediante la obtención de sus direcciones de memoria).

4. Identifica el conjunto activo (el conjunto de filas que satisfacen los criterios de búsqueda). Las filas en el active set no son asignadas a variables cuando se ejecuta la sentencia OPEN. Más bien, la sentencia FETCH es la que hace ese trabajo.

5. Sitúa el puntero a la primera fila del active set.

Nota: Si una consulta no devuelve ninguna fila cuando se abre el cursor, PL/SQL no lanza una excepción. Puede averiguar el número de filas devueltas por un cursor explícito mediante el uso del atributo <cursor_name>%ROWCOUNT.

Sentencia FETCH

La sentencia FETCH recupera las filas del cursor de una en una. Después de cada FETCH, el cursor avanza a la siguiente fila del active set. Puede utilizar el atributo %NOTFOUND para determinar si todo el active set se ha recuperado.

La sentencia FETCH realiza las siguientes operaciones:

1. Lee y asigna los datos de la fila actual a las variables de PL/SQL.

2. Avanza el puntero a la siguiente fila del active set.

El número de variables en la cláusula INTO de la sentencia FETCH debe ser igual al número de columnas en la sentencia SELECT del cursor; asegúrese de que los tipos de datos sean compatibles y que estén en el orden correcto. Alternativamente, también puede definir un récord para el cursor y referenciarlo en la cláusula INTO del FETCH.

Sentencia CLOSE

La sentencia CLOSE desactiva el cursor, libera el área de contexto, y "in-define" el active set. Es recomendable siempre cerrar el cursor después de completar el proceso. Si es necesario, puede volver a abrir el cursor, pero tenga en cuenta que un cursor puede volver a abrir solamente si está cerrado. Si intenta obtener datos de un cursor cerrado, se produce la excepción: INVALID_CURSOR.

Nota: Aunque es posible terminar el bloque PL/SQL sin cerrar los cursores, es recomendable hacer hábito de cerrar cualquier cursor que se declara explícitamente,  ya que esto libera recursos. Hay un límite máximo en el número de cursores abiertos por sesión, lo cual está determinado por el parámetro OPEN_CURSORS en el archivo de parámetros de Base de Datos. (Por defecto OPEN_CURSORS = 50).

Ejemplos:

SET SERVEROUTPUT ON

DECLARE
     CURSOR c_emp_cursor IS 
          SELECT
                   employee_id, 
                   last_name
          FROM employees
          WHERE employee_id = 111;

     v_empno employees.employee_id%TYPE;
     v_lname employees.last_name%TYPE;
BEGIN
     OPEN c_emp_cursor;   --abre el cursor
     FETCH c_emp_cursor INTO v_empno, v_lname;   --asigna los valores a las variables.
     CLOSE c_emp_cursor;       --cierra el cursor.

     DBMS_OUTPUT.PUT_LINE( v_empno ||'  '||v_lname); 
END;

/*Este es un ejemplo simple de un cursor, en el cual se define un cursor que extrae el código y apellido de un empleado; notar como son asignados los datos extraídos a las variables previamente declaradas.*/

 

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;   --abre el cursor
      LOOP
            FETCH c_emp_cursor INTO v_empno, v_lname;   --asigna los valores a las variables.
            EXIT WHEN c_emp_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE( v_empno ||'  '||v_lname);  
      END LOOP;
      CLOSE c_emp_cursor;       --cierra el cursor.
END;

/*Este ejemplo muestra como usar un cursor que retorne mas de una fila de resultados; notar como se itera el cursor  con la ayuda de un LOOP simple.*/

 

Cursores y Records

Ya ha visto que se puede definir records con la estructura de columnas de una tabla. También puede definir un records basado en la lista de columnas en el SELECT de un cursor explícito. Esto es conveniente para procesar las filas del active set, ya que solo tiene que especificar el nombre del record en la cláusula INTO de la sentencia FETCH, en lugar de un numero x de variable. Esto también asegura que los valores de las filas se cargaran directamente en los campos correspondientes del record.

Ejemplo:

DECLARE
      CURSOR c_emp_cursor IS 
           SELECT
                    employee_id, 
                    last_name,
                    salary,
                    department_id,
                    manager_id
           FROM employees
           WHERE department_id = 30;

      v_emp_rec     c_emp_cursor%ROWTYPE;
BEGIN
      OPEN c_emp_cursor;   --abre el cursor
      LOOP
            FETCH c_emp_cursor INTO v_emp_rec;   --asigna los valores a las variables.
            EXIT WHEN c_emp_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Nombre: '||v_emp_rec.last_name||', Salario: '||v_emp_rec.salary||
                                     ', Departamento: '||v_emp_rec.department_id);
      END LOOP;
      CLOSE c_emp_cursor;       --cierra el cursor.
END;

/*Modificamos el último ejemplo agregandole mas columnas; notar como el record basado en el cursor simplifica el código, evitando tener que declarar tantas variables como columnas en el SELECT.*/


Cursores FOR LOOP

Un Cursor FOR LOOP procesa las filas de un cursor explícito. Se puede considerar como un atajo, esto porque se abre el cursor, una fila es extraída(FETCH) por cada iteración del LOOP, el LOOP termina cuando se procesa la última fila, y el cursor se cierra automáticamente. El LOOP en sí se termina automáticamente al final de la iteración, donde la última fila se recupera.

Sintaxis:

FOR record_name IN cursor_name LOOP
    statement1;
    statement2;
    . . .
END LOOP;


En la sintaxis:

  • record_name:      Es el nombre del record declarado implícitamente.
  • cursor_name:      Es el nombre del cursor declarado previamente.

Directrices:

  • No declare el record que usara en el LOOP; ya que se declara de forma implícita.
  • Puede usar los atributos de cursor durante el LOOP si es necesario.
  • Suministrar los parámetros del cursor, si se requiere, entre paréntesis a continuación del nombre del cursor en la sentencia FOR.

Ejemplo:

DECLARE
      CURSOR c_emp_cursor IS 
           SELECT
                    employee_id, 
                    last_name,
                    salary,
                    department_id,
                    manager_id
           FROM employees
           WHERE department_id = 30;

      v_emp_rec     c_emp_cursor%ROWTYPE;
BEGIN
      FOR i IN c_emp_cursor LOOP
            DBMS_OUTPUT.PUT_LINE('Nombre: '||i.last_name||', Salario: '||i.salary||
                                     ', Departamento: '||i.department_id);  
      END LOOP;
END;

/*El mismo ejemplo ahora usando un FOR LOOP; Notar como se simplifica el código aun mas ya que no hay necesidad de abrir, hacer FETCH ni cerrar el cursor.*/


Atributos de los Cursores Explícitos

Atributo

Tipo

Descripción

%ISOPEN

Boolean

Retorna TRUE si el cursor está abierto.

%NOTFOUND

Boolean

Retorna TRUE si el último FETCH no trajo una fila.

%FOUND

Boolean

Retorna TRUE si el último FETCH trajo una fila; opuesto a %NOTFOUND.

%ROWCOUNT

Number

Cantidad de filas que han sido extraídas(FETCH).

 

Al igual que con los cursores implícitos, hay cuatro atributos que proveen información referente al estado de un cursor. Una vez adjunto al nombre del cursor, estos atributos devuelven información útil acerca de la ejecución del cursor.

Nota: No puede hacer referencia a los atributos de un cursor directamente en una sentencia SQL.

Ejemplo:

DECLARE
      CURSOR c_emp_details IS 
           SELECT
                    e.first_name||' '||e.last_name AS nombre, 
                    e.salary AS salario,
                    d.department_name AS departamento,
                    j.job_title AS empleo,
                    m.first_name||' '||m.last_name AS supervisor,
                    l.city AS ciudad
           FROM employees e, employees m, departments d, locations l, jobs j
           WHERE NVL(e.manager_id, 0) = m.employee_id
           AND e.job_id = j.job_id
           AND e.department_id = d.department_id
           AND d.location_id = l.location_id;

      v_emp_details_rec     c_emp_details%ROWTYPE;
BEGIN
      IF NOT c_emp_details%ISOPEN THEN
          OPEN c_emp_details;
          DBMS_OUTPUT.PUT_LINE('Cursor Abierto');
      END IF;

      LOOP
            FETCH c_emp_details INTO v_emp_details_rec;

            EXIT WHEN c_emp_details%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE('FETCH #: '||c_emp_details%ROWCOUNT);

            DBMS_OUTPUT.PUT_LINE(
                                     'Nombre: '||v_emp_details_rec.nombre||CHR(10)||
                                     'Salario: '||v_emp_details_rec.salario||CHR(10)||
                                     'Departamento: '||v_emp_details_rec.departamento||CHR(10)||
                                     'Empleo: '||v_emp_details_rec.empleo||CHR(10)||
                                     'Supervisor: '||v_emp_details_rec.supervisor||CHR(10)||
                                     'Ciudad: '||v_emp_details_rec.ciudad
                                    );
      END LOOP;

      IF c_emp_details%ISOPEN THEN
          CLOSE c_emp_details;
          DBMS_OUTPUT.PUT_LINE('Cursor Cerrado');
      END IF;
END;

/*En el ejemplo creamos un cursor que contiene informaciones de los empleados y un record basado en ese cursor; notar el uso de los atributos del cursor en la sección de ejecución del bloque.*/

 

Cursores FOR LOOP con Subconsultas

La diferencia entre un cursor FOR LOOP con una subconsulta y un cursor FOR LOOP se encuentra en la declaración de cursor. Si desea usar un cursor FOR LOOP con una subconsulta, no es necesario declarar el cursor en la parte declarativa. Usted tiene que proporcionar la sentencia SELECT que determina el active set en el mismo LOOP.

Nota: No se puede hacer referencia a los atributos del cursor FOR LOOP con una subconsulta, esto debido a que se desconoce el nombre del mismo.

Ejemplo:

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;

/*En el ejemplo un cursor FOR LOOP con una subconsulta; notar como este bloque no necesita la seccion declarativa.*/

 

Cursores Con Parámetros

Puede pasar parámetros a un cursor. Esto significa que puede abrir y cerrar un cursor varias veces y retornar un active set diferente en cada ocasión. Para cada ejecución, el cursor es cerrado y abierto de nuevo con un conjunto de valores diferentes.

Los tipos de datos de los parámetros son los mismos que para las variables escalares, con la diferencia de que al parámetro no se le especifica la precisión(tamaño). Los nombres de los parámetros son para referencia en la consulta del cursor.

Sintaxis:

CURSOR cursor_name[(parameter_name datatype, ...)]
IS
    select_statement;

OPEN    cursor_name(parameter_value,.....);
FOR record_name IN cursor_name(parameter_value,.....) LOOP

En la sintaxis:

cursor_name

Es un identificador del cursor.

parameter_name

Es el nombre del parámetro.

datatype

Es el tipo de dato del parámetro.

select_statement

Es la sentencia SELECT.

La notación de parámetro no ofrece mejor funcionalidad; simplemente le permite especificar valores de entrada  de forma fácil y clara. Esto es particularmente útil cuando se hace referencia repetidamente del mismo cursor.

Ejemplo:

DECLARE
    CURSOR c_emp_details(p_emp_id NUMBER) IS
        SELECT
                e.first_name||' '||e.last_name AS nombre,
                e.salary AS salario,
                d.department_name AS departamento,
                l.city AS ciudad
        FROM employees e, departments d, locations l
        WHERE e.employee_id = p_emp_id
        AND e.department_id = d.department_id
        AND d.location_id = l.location_id;

    CURSOR c_job_dept_details(p_dept_id NUMBER, p_job_id VARCHAR2) IS
        SELECT
                d.department_name AS departamento,
                j.job_title AS empleo,
                COUNT(e.employee_id) AS emp_count,
                SUM(e.salary) AS total_sal
        FROM employees e, departments d, jobs j
        WHERE d.department_id = p_dept_id
        AND j.job_id = p_job_id
        AND e.department_id = d.department_id
        AND e.job_id = j.job_id 
        GROUP BY d.department_name, j.job_title;

    v_emp_details_rec   c_emp_details%ROWTYPE;
BEGIN
    OPEN c_emp_details(121);
    FETCH c_emp_details INTO v_emp_details_rec;
    CLOSE c_emp_details;

    DBMS_OUTPUT.PUT_LINE('Primer Cursor');

    DBMS_OUTPUT.PUT_LINE('Empleado: '||v_emp_details_rec.nombre||CHR(10)||
                             'Salario: '||v_emp_details_rec.salario||CHR(10)||
                             'Departamento: '||v_emp_details_rec.departamento||CHR(10)||
                             'Ciudad: '||v_emp_details_rec.ciudad);

    DBMS_OUTPUT.PUT_LINE('Segundo Cursor');

    FOR i IN c_job_dept_details(50,'ST_MAN') LOOP
        DBMS_OUTPUT.PUT_LINE('Departamento: '||i.departamento||CHR(10)||
                                 'Empleo: '||i.empleo||CHR(10)||
                                 'Cantidad de Empleados: '||i.emp_count||CHR(10)||
                                 'Salario Total: '||i.total_sal);
    END LOOP;
END;

/*El ejemplo muestra el uso de dos cursores con parámetro, el primero recibe el numero de empleado y retorna ciertas informaciones del mismo; el segundo recibe el código de departamento y el código de empleo y retorna la cantidad de empleados y la suma de salarios. Tenga en cuenta que estos cursores pueden ser usados repetidamente en el mismo bloque con parámetros distintos para así generar diferentes resultados.*/


Referenciar y Bloquear la Actual Fila del Cursor

Cláusula FOR UPDATE

Si hay varias sesiones en una Base de Datos, existe la posibilidad de que las filas de una tabla en particular se actualizan después de haber abierto un cursor. De modo que usted ve los datos actualizados sólo cuando vuelve a abrir el cursor. Por lo tanto, es mejor bloquear las filas antes de actualizarlas o eliminarlas. Es posible bloquear las filas con la cláusula FOR UPDATE en la consulta del cursor.

Sintaxis:

SELECT    ...
FROM        ...
FOR UPDATE [OF column_reference][NOWAIT | WAIT n];

 

En la sintaxis:

column_reference

Es una columna de la tabla en la que se realiza la consulta (también se puede utilizar una lista de columnas).

NOWAIT

Devuelve un error de Oracle si las filas están bloqueadas por otra sesión.

La cláusula FOR UPDATE es la última en una sentencia SELECT, incluso después de ORDER BY (si existe). Cuando se desea consultar varias tablas, puede utilizar la cláusula FOR UPDATE para confinar el bloqueo de filas en tablas particulares. FOR UPDATE OF col_name(s) bloquea filas sólo en las tablas que contienen col_name(s).

La sentencia SELECT ... FOR UPDATE identifica las filas que se van a actualizar o eliminar y, a continuación, bloquea cada fila del active set. Esto es útil cuando se desea realizar una actualización en los valores existentes en una fila. En ese caso, se debe asegurar de que la fila no sea cambiada por otra sesión antes de la actualización.

La palabra clave NOWAIT (opcional) le indica al Servidor Oracle no esperar si las filas seleccionadas han sido bloqueadas por otro usuario. En caso de que hayan sido bloqueadas, el control vuelve inmediatamente a tu programa de modo que pueda hacer otro trabajo antes de volver a intentar adquirir el bloqueo. Si se omite la palabra clave NOWAIT, el servidor Oracle espera hasta que las filas estén disponibles.

Ejemplo:

DECLARE
    CURSOR c_emp_cursor IS 
        SELECT employee_id, last_name, salary
        FROM  employees
        WHERE department_id = 80
        FOR UPDATE OF salary NOWAIT; 

Si el servidor de Oracle no puede adquirir el bloqueo de las filas que necesita en una operación SELECT FOR UPDATE, se espera indefinidamente. Utilizar NOWAIT para manejar este tipo de situaciones. Si las filas están bloqueados por otra sesión y se ha especificado NOWAIT, al abrir el cursor se produce un error. Se puede utilizar WAIT en lugar de NOWAIT, y si usa WAIT puede especificar el número de segundos de espera. Si luego de haber transcurrido los n segundos las filas continúan bloqueadas, se devuelve un error.

No es obligatorio especificar una columna en la cláusula FOR UPDATE OF, pero se recomienda para una mejor legibilidad y mantenimiento.

Cláusula WHERE CURRENT OF.

  • Usar cursores para actualizar o eliminar la fila actual.
  • Incluir la cláusula FOR UPDATE en la consulta de cursor para primero bloquear la fila.
  • Utilice la cláusula WHERE CURRENT OF para hacer referencia a la fila actual de un cursor explícito.

La cláusula WHERE CURRENT OF se utiliza en conjunto con la cláusula FOR UPDATE para referirse a la fila actual de un cursor explícito. La cláusula WHERE CURRENT OF se utiliza en las sentencias UPDATE o DELETE; primero es necesario especificar la cláusula FOR UPDATE en la declaración del cursor. Se puede utilizar la combinación de actualización y supresión de un fila actual de la tabla de Base de Datos correspondiente. Esto le permite aplicar las actualizaciones y eliminar la fila que en cuestión, sin la necesidad de hacer referencia explícita del ID de la misma.

Sintaxis:

UPDATE table_name
SET column_name = ...
WHERE CURRENT OF cursor_name;

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

 

Ejemplo:

DECLARE
    CURSOR c_emp IS
        SELECT employee_id, last_name, salary
        FROM  employees
        WHERE employee_id = 100
        FOR UPDATE OF salary    WAIT 180;
    v_emp_rec c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    FETCH c_emp INTO v_emp_rec;

    UPDATE employees
    SET salary = 30000
    WHERE CURRENT OF c_emp;

    COMMIT;

    CLOSE c_emp;
END;

/*Este SCRIPT tratara de bloquear para luego actualizar el salario del registro con el employee_id = 100 de la tabla employees, en caso de ya estar bloqueado por otro usuario, el bloque esperaría máximo 3 minutos para que se libere dicho registro, de no ser bloqueado en este tiempo un error se produciría.*/


Fuente: Oracle Database: PL/SQL Fundamentals

Read 2466 times Last modified on Sábado, 11 Julio 2020 14:52

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.