Cursores

Cursores - ORACLE

Los cursores son punteros a unas zonas de memoria llamadas reas de contexto en las cuales se almacena informacin acerca del resultado de una consulta, as como el conjunto de registros procesados y no procesados.

Mediante este puntero (cursor), un programa PL/SQL puede controlar el rea de contexto y manejar el conjunto de registros devueltos por una consulta.

tiles para las consultas que devuelven ms de una fila.

Uso de cursores

Para poder utilizar un cursor debemos hacer cuatro pasos:

1. Declaracin del cursor

2. Apertura del cursor

3. Recogida de los resultados en variables PL/SQL

4. Cierre del cursor

Para declarar un cursor se utiliza la siguiente sintaxis:

CURSOR IS

Para poder abrir un cursor utilizaremos:OPEN

Para recoger los datos que devuelve un cursor utilizaremos la orden FETCH de la siguiente manera:

FETCH INTO {|}

Despus de utilizar el FETCH se incrementa el puntero del conjunto activo para que apunte al siguiente registro, de tal manera que en un bucle, cada FETCH devolver filas sucesivas del conjunto activo. El atributo de cursores %NOTFOUND se utiliza para saber cuando se ha terminado de recorrer el conjunto activo.

Para cerrar un cursor utilizaremos:

CLOSE ;

Los cursores tienen cuatro atributos:

%FOUND devuelve TRUE si la ltima instruccin FETCH devolvi una fila

%NOTFOUND es la contraria a %FOUND

%ISOPEN nos indica si el cursor est abierto

%ROWCOUNT nos dice el nmero de registros extrados por el cursor hasta el momento.Cursores parametrizados

Permiten utilizar la orden OPEN para enviar las variables de acoplamiento de un cursor.

Por ejemplo, el siguiente cdigo crea un cursor para las reas cuyo cdigo se pasa por parmetro en la orden OPEN:

Crear las siguientes tablas:

create table oficina(

oficina number(2) constraint pk_oficina primary key,ciudad varchar2(20),

region varchar2(20),

dir number(3),objetivo number(7),

ventas number(7));

create table empleado (

numemp number(5) constraint pkemp primary key, nombre varchar2(30),

edad number(3),

oficina number(2),

titulo varchar2(30),

contrato date,

jefe number(3),

cuota number(7),

ventas number(7),

constraint fk_oficina foreign key (oficina) references oficina (oficina));

Insertar los siguientes registros:begin

insert into oficina values(11, 'Santiago', 'Metropolitana', 106, 575000, 693000);

insert into oficina values(12, 'Arica', 'Primera', 104, 8000000, 735000);

insert into oficina values(13, 'Coquimbo', 'Cuarta', 105, 350000, 368000);

insert into oficina values(14, 'Valparaso', 'Quinta', 108, 725000, 836000);insert into oficina values(15, 'Rancagua', 'Sexta', 108, 850000, 980000);

insert into oficina values(16, 'Talca', 'Sptima', 106, 950000, 990000);

insert into oficina values(17, 'Valdivia', 'Dcima', 108, 750000, 840000);

insert into oficina values(18, 'Copiapo', 'Tercera', 102, 650000, 740000);

insert into oficina values(19, 'Santiago', 'Metropolitana', 106, 850000, 930000);

insert into oficina values(20, 'Antofagasta', 'Segunda', 109, 550000, 680000);

insert into oficina values(21, 'Temuco', 'Novena', 110, 590000, 720000);

insert into empleado values(101, 'Antoni Viguer',45,12,'Representante','10/10/86',104,300000, 305000);

insert into empleado values(102, 'Alvaro Jaumes',48,21,'Representante','12/10/86',108,350000,474000);

insert into empleado values( 103, 'Juan Robira',29,12,'Representante','03/01/87',104,275000,286000);

insert into empleado values(104, 'Jos Gonzlez',33,12, 'Dir Ventas','02/05/87',106,200000,143000);insert into empleado values(105, 'Vicente Pantal',37,13,'Representante','02/12/88',104,350000,368000);

insert into empleado values(106, 'Luis Antonio',53,11, 'Dir General','06/14/88',108,275000,299000);

insert into empleado values(107, 'Jorge Gutierrez',49,21, 'Representante','11/24/88',108,300000,186000);

insert into empleado values(108, 'Ana Bustamante',62,21, 'Dir Ventas','09/30/89',106,350000,361000);

insert into empleado values(109, 'Maria Sunta',39,11,'Representante','07/12/89',106,300000,392000);

insert into empleado values(110, 'Juan Victor',41,20,'Representante','02/13/90',104,300000,76000);end;

Ejemplo:

DECLAREV_NUMEMP EMPLEADO.NUMEMP%TYPE;V_NOMBRE EMPLEADO.NOMBRE%TYPE;CURSOR C_EMPLEADO (V_NUM EMPLEADO.NUMEMP%TYPE) IS

SELECT NUMEMP, NOMBRE

FROM EMPLEADO

WHERE NUMEMP = V_NUM

ORDER BY NUMEMP;

BEGIN

OPEN C_EMPLEADO (:V_NUM);

LOOP

FETCH C_EMPLEADO INTO V_NUMEMP, V_NOMBRE;

EXIT WHEN C_EMPLEADO %NOTFOUND;

DBMS_OUTPUT.PUT_LINE(V_NUMEMP || ' - ' ||V_NOMBRE);

END LOOP;

CLOSE C_EMPLEADO;

END;

Bucles de cursor FOR

Ya hemos visto como podemos utilizar los cursores para recorrer los resultados de una consulta mediante el uso de bucles LOOP y podemos realizar las mismas operaciones utilizando bucles WHILE. Cuando utilizamos bucles FOR podemos usar un procesamiento mucho ms sencillo con los bucles de cursor FOR, los cuales abren, recuperan los datos y cierran el cursor de forma implcita, haciendo su uso ms sencillo.

Su sintaxis es la siguiente:

FOR IN LOOP

END LOOP;

Ejemplo_1: Muestra todos los empleados ordenados en forma ascendente por edad

DECLARE

CURSOR C_EMPLEADO IS

SELECT NOMBRE,EDADFROM EMPLEADO

ORDER BY EDAD;

BEGIN

FOR VP IN C_EMPLEADO LOOP

DBMS_OUTPUT.PUT_LINE ('Nombre: ' || VP.NOMBRE ||' '|| 'Edad:'|| VP.EDAD);END LOOP;

END;

Ejemplo_2: Cree un cursor que calcule la media de las edades de todos los empleados.

declare

promedio integer;

suma integer:=0;

n integer:=0;

v_edad empleado.edad%type;

cursor media isselect edad from empleado;

begin

open media;

loop

fetch media into v_edad;exit when media%notfound;

suma:=suma+v_edad;n:=n+1;

end loop;

promedio:=suma/n;

v_edad:=promedio;

dbms_output.put_line(v_edad);close media;

end;Ejercicio.

Mostrar nombre,fecha_contrato y ttulo de todos los empleados ordenados por las ventas, utilizando un cursor.

Utilizando un cursor muestre los nombres de los empleados y a que oficina pertenecen de todos aquellos cuyo numemp es nmero par y su cuota es mayor o igual a $300.000, ordenados por cuota en forma descendente.

Recopilador por

arroyopaz@gmail.com

Tambin se puede definir una variable a partir de un campo mediante los atributos %TYPE y %ROWTYPE, con esto damos el tipo y longitud a la variable de otra variable u objeto ya definido.

%TYPE es la que se utiliza normalmente

Nombre del cursor

V_NUM variable de tipo NUMEMP de la tabla empleado

(Es un paso de parmetro)

Consulta

Apertura del cursor, se le enva como paso de parmetro el valor 102

Recoleccin de datos, almacenndolos en las variables V_NUMEMP y V_NOMBRE

Trmino de la recoleccin

Cierre del cursor