Cursores

25 de noviembre de 2010 - 21:28 - Cursores

Los cursores se utilizan en PL/SQL para manejar las sentencias SELECT. Un cursoresta formado por un conjunto de registros devueltos por una instrucción SQL del tipoSELECT. Desde un punto de visto interno a la base de datos Oracle, los cursores son segmentos de memoria utilizados para realizar operaciones con los registros devueltos tras ejecutar una sentencia SELECT.

Se pueden distinguir dos tipos de cursores:

- Cursores implícitos: Se utilizan cuando la sentencia SELECT devuelve un solo registro y su formato es como sigue:

     DECLARE
       lsalario empleados.salario%TYPE;
       ldni empleados.dni%TYPE;
     BEGIN
       SELECT salario, dni
       INTO lsalario, ldni
       FROM empleados
       WHERE nombre = 'Juan'
       AND apellidos = 'Rodrigo Comas';
       /* Resto de sentencias del bloque */
     END;

Nota: Mucha gente considera que las sentencias UPDATE, dentro de un bloquePLSQL, son también cursores implícitos, no obstante, yo prefiero no incluirlas dentro de este concepto.

- Cursores explícitos: Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código PL/SQL.

Un cursor explícito tiene que ser definido previamente como cualquier otra variablePLSQL y debe serle asignado un nombre. Veamos un ejemplo que muestra el DNI y el salario de los trabajadores incluidos en la tabla empleados:

     DECLARE
       CURSOR cemp IS
       SELECT salario, dni
       FROM empleados;
       cepm_rec cemp%ROWTYPE;
     BEGIN
       FOR cemp_rec IN cemp
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

Los cursores explícitos admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor. Por ejemplo:

     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE;
       vnombre VARCHAR2(20);
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       FOR cemp_rec IN cemp(vnombre)
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

En los ejemplos anteriores los cursores se han controlado con la sentencia FOR pero también pueden controlarse mediante el uso de las sentencias OPEN, FETCH yCLOSE. La sentencia OPEN identifica el cursor que se tiene que utilizar. La sentenciaFETCH pone, registro a registro, los valores devueltos por el cursor en las variables correspondientes, variables que pueden estar constituidas por una lista de variables o un registro PLSQL (este es el caso de los ejemplos que incluyo en este artículo). Por último, la sentencia CLOSE cierra el cursor y libera la memoria reservada. Veamos como quedaría nuestro ejemplo utilizando este tipo de sentencias en lugar de utilizar la sentencia FOR:

     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE;
       vnombre VARCHAR2(20);
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       OPEN cemp(vnombre);
       LOOP
         FETCH cemp INTO cemp_rec;
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
         EXIT WHEN cemp%NOTFOUND; -- Último registro.
       END LOOP;
       DBMS_OUTPUT.PUT_LINE
         ('Número de empleados procesados ' || cemp%ROWCOUNT);
       CLOSE cemp;
     END;

Sólo me queda señalar que existe una tercera opción para manejar cursores que a mí, particularmente, no me gusta utilizar pero que no quiero omitir:

     DECLARE
       TYPE ecursor IS REF CURSOR RETURN empleados%ROWTYPE;
       cemp ecursor;
       cepm_rec empleados%ROWTYPE;
     BEGIN
       OPEN cemp FOR SELECT * FROM empleados;
       FOR cemp_rec IN cemp
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

Finalmente sólo mencionar que existen cuatro tipos de atributos que nos permiten controlar la ejecución de un cursor:

- %ISOPEN: Devuelve "true" si el cursor está abierto.
- %FOUND: Devuelve "true" si el registro fue satisfactoriamente procesado.
- %NOTFOUND: Devuelve "true" si el registro no pudo ser procesado. Normalmente esto ocurre cuando ya se han procesado todos los registros devueltos por el cursor.
- %ROWCOUNT: Devuelve el número de registros que han sido procesados hasta ese momento.

PL/SQL
Definición de Cursor

Útiles para las consultas que devuelven más de una fila.
Son declarados y nombrados por el programador, y manipulados por medio de sentencias específicas en las acciones ejecutables del bloque.

PL/SQL
Control de Cursores

1º. Crear un área SQL específica DECLARE
2º. Identificar el juego activo OPEN
3º. Cargar la fila actual en variables FETCH
4º. Si todavía existen filas sin leer, volver a 3º.
5º. Si no existen más filas a leer CLOSE

PL/SQL
Declaración del Cursor

Sintaxis:

No incluya la cláusula INTO en la declaración del cursor.
Si es necesario procesar filas en algún orden, incluya la cláusula ORDER BY.

PL/SQL
Ejemplo de Declaración

PL/SQL
Apertura del Cursor

Sintaxis:

Si la consulta no devuelve ninguna fila, no se producirá ninguna excepción al abrir el cursor.
Para los cursores declarados con la cláusula FOR UPDATE, la sentencia OPEN bloquea estas filas.

PL/SQL
Recuperación de Datos

Sintaxis:

Incluya, en el FETCH, el mismo número de variables que las definidas en el cursor, y en el mismo orden.
Compruebe si el cursor tiene filas.

PL/SQL
Cierre del Cursor

Sintaxis:

Cierre el cursor una vez completado el procesamiento de las filas.
Si es necesario, vuelva a abrir el cursor.
No intente recuperar los datos de un cursor una vez que ha sido cerrado (INVALID_CURSOR).

PL/SQL
Atributos de Cursores

Proporcionan información de estado del cursor:

PL/SQL
El Atributo %ISOPEN

Utilice el atributo de cursor %ISOPEN antes de ejecutar una recuperación para comprobar si el cursor está abierto.
Ejemplo:

PL/SQL
Atributos %NOTFOUND, %ROWCOUNT

Utilice %ROWCOUNT para recuperar un número exacto de filas.
Utilice %NOTFOUND para determinar cuándo salir del bucle de lectura del cursor.
Antes de la primera recuperación, %NOTFOUND es NULL, así que si FETCH no se ejecuta nunca satisfactoriamente, no se saldría nunca del bucle de lectura.

PL/SQL
Ejemplo %NOTFOUND, %ROWCOUNT

Ejemplo:

PL/SQL
Bucles FOR de Cursor

Sintaxis:

Apertura, recuperación y cierre implícitos.
No declare el registro, está declarado implícitamente.

PL/SQL
Cursores con Parámetros

Sintaxis:

Permite abrir un cursor varias veces con un juego activo distinto cada vez.

PL/SQL
Cursores con Parámetros

Cada parámetro formal de la declaración del cursor debe tener un parámetro real correspondiente en la sentencia OPEN.
La sintaxis de los parámetros es:

PL/SQL
Cursores con Parámetros

Transfiera el número de departamento y el cargo a la cláusula WHERE:

PL/SQL
Cláusula FOR UPDATE

Sintaxis:

El bloqueo explícito le permite denegar el acceso mientras dura una transacción.
Bloquee las filas antes de la actualización o supresión.
La cláusula FOR UPDATE es la última cláusula de una sentencia SELECT, incluso después del ORDER BY.
NOWAIT devuelve un error de Oracle si las filas han sido bloqueadas por otra sesión, de lo contrario se espera.

PL/SQL
Cláusula WHERE CURRENT OF

Sintaxis:

Incluya la cláusula FOR UPDATE en la definición del cursor para bloquear las filas.
Especifique WHERE CURRENT OF en la sentencia UPDATE o DELETE para referirse a la fila actual del cursor.

EJERCICIOS RESUELTOS

cusrsor 
set serveroutput on
declare 
cursor nombre is
  select e.first_name, e.last_name, d.department_name
  from employees e join departments d 
  on e.department_id = d.department_id;
  v_nombre employees.first_name%type;
  v_apellido employees.last_name%type;
  c_nomdepto departments.department_name%type;
  cont number (2):=0;
begin
open nombre;
  while cont < 10 loop
  fetch nombre into v_nombre, v_apellido, c_nomdepto;
  dbms_output.put_line ('el nombre dek empleado es: '||v_nombre||'Su apellido es' ||v_apellido||'y su depto es:'||c_nomdepto);
  cont:= cont +1;
  end loop;
close nombre;
end;

  --2
--traiga todos los empleados y el nombre del pais q esten en los deptos: sales 60 e it 80 
set serveroutput on
declare
cursor pais is 
select e.employee_id, c.country_name
from employees e join departments d 
on e.department_id = d.department_id
join locations l 
on d.location_id = l.location_id
join countries c
on l.country_id = c.country_id
where d.department_name in ('Sales','IT');
empleado employees.employee_id%type;
npais countries.country_name%type;
cont number(10):=0;
begin 
open pais;

while cont < 50 loop
  fetch pais into empleado, npais;
  EXIT WHEN pais%NOTFOUND;
  dbms_output.put_line('el id de emlpeado es: '||empleado||'y su pais es '||npais); 
    cont:= cont +1;
  end loop;
close pais;
end;

----

taller  pl
--parte1
--1 evalue cada una de las declaraciones. determina cualles de los temas son legales explique
--a 
	declare
  v_id number (4);
  --si es permitido ya q es la forma correcta de declararlo con su tipo de dato 
--b
  declare 
  v_x, v_y, v_z varchar (10);
  --NO es permitido ya q no se puede declarar mas de una variable con un solo tipo de dato
--c
  declare 
  v_britdate date not null;
  --si porque se esta colocando una restriccion a un campo
--d
  declare v_in_stock boolean := 1;
  --NO porq le esta asignando al tipo de dato booleano el valor no permitido debe colocarce "true" o "false"
---2 en cada una de las siguientes asignaciones, indicar si la declaración de tipo dato es válida y 
--  válide de lo que el resultado será
--a 
  declare 
  v_days_to_go := v_due_date - sysdate;
-- Esta asignacion es valida unicamente si las dos variables con tipo date, si la variable inicial es de tipo "Varchar", se debe convertir al tipo correcto.
-B
 v_sender := USER || ':'|| to_char(v_dept_no);
-- Es permitido, ya que es una cadena de caracteres, pues se esta convirtiendo una variable de tipo number a caracter. 
--C
 v_sum := $100,000 + $250,000;
--Esta asignacion no es permitida ya que el formato de los valores, requiere un formato especial, en cuanto se refiere a "$" y ",", por tal motivo, esta asignacio arrojara un error.

--D
 v_flag := TRUE;
 --Esta asignacion si es valida, ya que anteriormente pudo ser declarada con tipo de dato Boolean.

--E
 v_n1 := v_n2 > (2 * v_n3);
--esta asignacion es valida, ya que a la variable inicial le estoy asignando operaciones matematicas, obviamente declaradas enteriormente con tipo de dato

--F
 v_value := NULL;
--Esta asignacion es permitida, ya que le estoy asignando un valor nulo a la variable.

--3
--Crear un bloque anonimo llamado "My PL/SQL Block Works" 

set serveroutput on
declare
scrip varchar2(100);
begin
scrip:= '&Mensaje';
dbms_output.put_line (scrip);
end;

--4.

set serveroutput on
declare
Numero number(10);
Caracter varchar2 (100);
begin
Caracter:= '&Caracter';
numero:=to_number(substr(caracter, 3, 5));
dbms_output.put_line (Numero);
dbms_output.put_line (caracter);
end;
--fin

Cursores

0 comentarios