Sesión 9

Construcción de cursores

Objetivos de la Sesión

• Definir que es un cursor y para que sirve

• Decidir cuando es conveniente el uso de cursores

• Aplicar cursores para la manipulación masiva de datos

Temas a tratar

• Definición de un cursor• Tipos de cursores. Definición de un cursor

explícito e Implícitos• Desarrollar ejercicios prácticos

Definición de un cursor

Definición de un cursor(1)Definición de un cursor(1)

• Un cursor permite procesar grupospermite procesar grupos

y/o un solo registroy/o un solo registro datos que se obtienen como resultado de una consulta SQL que retorna una o más filas.

• El cursor se almacena en un área de memoria.

Definición de un cursor(2)Definición de un cursor(2)

• Un cursor es un puntero al área de memoria que ORACLE utiliza para procesar una sentencia SELECT. Esta área de memoria recibe el nombre de área de contextoárea de contexto

RESUMEN

Tipos de cursores

Tipos de cursoresTipos de cursores

Cursores implícitos

• Los cursores implícitos son creados por Oracle para manejar alguna instrucción SQL y no son declarados por el programador.

• Este tipo de cursores se utiliza para operaciones Select into,Delete y Update

. Se usan cuando la consulta devuelve un único registro.

Tipos de cursores

Cursores explícitos

• Los cursores explícitos son aquellos que se declaran generalmente por medio de una consulta SQL

• Se utilizan generalmente para procesar consultas que devuelvan más de una registro

Proceso de un cursor explícitoProceso de un cursor explícito

1. Declaración del cursor (DECLARE)2. Abrir el cursor (OPEN)3. Procesar las filas (recoger filas del resultado)

(FETCH)4. Cerrar el cursor (CLOSE)

Declaración del Cursor

• Se da un nombre al cursor y se asocia a una consulta específica.

• Un cursor puede recibir parámetros que son usados en la consulta asociada.

Sintaxis CURSOR nombre_cursor [ (par1 [, par2]…) ] IS

instrucción_SELECT

DECLARARPor ejemplo, los cursores c1 y c2 se pueden declarar como sigue:

DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal>1000 ;

CURSOR c2 RETURN dept%ROWTYPE ISSELECT * from dept WHERE deptno = 10 ;

Abrir el cursorAbrir el cursor

• Al abrir un cursor se ejecuta inmediatamente la consulta e identifica el conjunto resultado, el que consiste de todas las filas que concuerdan con el criterio de selección de éste.

Sintaxis Open nombre_cursor [ (var1 [, var2]…) ]

ABRIR CURSORDECLARE CURSOR c1 IS SELECT ename, job FROM emp

WHERE sal > 3000; …

BEGINBEGIN OPEN c1; …

END;END;

ABRIR CURSOR PASANDO ABRIR CURSOR PASANDO PARÁMETROPARÁMETRO

DECLARE emp_name emp.name%TYPE; salary emp.sal%TYPE; CURSOR c1 (par1 VARCHAR2, par2 NUMBER) IS SELECT…

--Cualquiera de estas sentencias abre el cursor

OPEN c1(emp_name, 3000);OPEN c1(“John”, 1500);OPEN c1(emp_name, salary);

Procesar las filasProcesar las filas

• Devuelve del conjunto de resultados.• Después de cada recuperación el cursor avanza al

siguiente registro

Sintaxis Fetch cursor_name into lista_variablesor Fetch cursor_name into registro_PL/SQL

una fila

RECUPERAR FILAS

Típicamente se utiliza la sentencia FETCH dentro de un conjunto de instrucciones como el siguiente:

LOOP FETCH c1 INTO mi_registro; EXIT WHEN c1%NOTFOUND;-- no encuentra

registros --- se procesa el registroEND LOOP;

PROCESAR CURSOR CON FUNCIÓN PROCESAR CURSOR CON FUNCIÓN

Ejemplo:LOOP FETCH c1 INTO … IF c1%FOUND THEN-- fetch exitoso

…procesos ELSE-- fetch falló; se sale del loop

EXIT; END IF;END LOOP;

Cerrar el cursorCerrar el cursor

1. Cierra el cursor y libera el conjunto de resultados.

2. Una vez que un cursor ya ha sido cerrado, es posible volverlo a abrir sin tener que declararlo otra vez.

3. Sintaxis 4. CLOSE nombre_cursor

Atributos(funciones) de cursores

1. %FOUND: es un atributo booleano, de forma que si la última orden FETCH devuelve una fila nos devuelve el valor TRUE, si no nos devuelve FALSE.

2. %NOTFOUND: se comporta de forma opuesta a %FOUND.

3. %ISOPEN: es también un atributo booleano que se utiliza para asociar si el cursor asociado está abierto o no.

4. %ROWCOUNT: este atributo numérico devuelve el número de filas extraídas por el cursor hasta ese momento.

Atributos de cursores

• Es conveniente declarar el tipo de dato de una variable haciendo referencia indirecta al tipo de dato de una columna de una tabla o al conjunto de registro de una tabla o de un cursor.

• %TYPE: El atributo %TYPE se utiliza para declarar una variable con el mismo tipo que una columna de una tabla o que otra variable definida anteriormente.

• %ROWTYPE: %ROWTYPE es para claves de registro

Se puede declarar un registro basándose en una colección de columnas de una tabla, vista o cursor de la base de datos mediante el atributo %ROWTYPE.

Atributos de cursoresPor ejemplo, si tengo una tabla PAISES declarada como:

CREATE TABLE PAISES(CO_PAIS          NUMBER,DESCRIPCION      VARCHAR2(50),CONTINENTE  VARCHAR2(20) );   

Puedo declarar una variable de tipo registro como PAISES%ROWTYPE; DECLARE

miPAIS PAISES%ROWTYPE;

BEGIN/* Sentencias ... */END;

Proceso de cursoresProceso de cursores

• Cursores implícitos Las órdenes DECLARE, OPEN, FETCH y

CLOSE no son relevantes para este tipo de cursores, aunque si que permiten que se les asigne los atributos de cursor mencionados.

Proceso de cursoresProceso de cursores• Cursores explícitos Por medio de ciclo LOOP.

Debe tenerse cuidado de agregar una condición para salir del ciclo:

OPEN nombre_cursor; LOOP

FETCH nombre_cursor INTO lista_variables; EXIT WHEN nombre_cursor%notfound; /* Procesamiento de los registros recuperados

*/ END LOOP; CLOSE nombre_cursor;

Proceso de cursoresProceso de cursores

• Cursores explícitosPor medio de un ciclo WHILE LOOP. Por medio de un ciclo WHILE LOOP. La instrucción FECTH aparece dos veces:

OPEN nombre_cursor; FETCH nombre_cursor INTO lista_variables; WHILE nombre_cursor%found LOOP

/* Procesamiento de los registros recuperados */

FETCH nombre_cursor INTO lista_variables; END LOOP; CLOSE nombre_cursor;

Proceso de cursoresProceso de cursores

•Cursores explícitos Por medio de un ciclo FOR LOOP.Por medio de un ciclo FOR LOOP.

Es la forma más corta ya que implícitamente se ejecutan las instrucciones OPEN, FECTH y CLOSE.

FOR variable IN nombre_cursor LOOP /* Procesamiento de los registros

recuperados */ END LOOP;

BEGIN

FOR orden IN

(SELECT empno,deptno FROM emp ORDER BY deptno) LOOPDBMS_OUTPUT.PUT_LINE(orden.empno || ' ' || orden.deptno ); END LOOP;END;/

Ejercicio 1: Incluso ya no es necesario declarar el cursor (a partir de Oracle 8i), directamente se puede colocar al frente de la cláusula IN:

Ejercicio 2 Los cursores explicitos también se pueden procesar mediante

ciclosciclosdirectosdirectos sin necesidad de abrirlos ni de hacerles FETCH ni

de cerrarlos explícitamente, estas labores son realizadas por un ciclo FOR así:

DECLARE CURSOR ordenados IS SELECT empno,deptno FROM emp ORDER BY deptno;BEGIN FOR orden IN ordenados LOOP DBMS_OUTPUT.PUT_LINE(orden.empno || ' ' ||

orden.deptno ); END LOOP;END;/

DECLARE CURSOR ordenados IS SELECT empno,deptno FROM emp ORDER BY deptno; codi emp.empno%TYPE; dpti emp.deptno%TYPE;BEGIN OPEN ordenados; LOOP FETCH ordenados INTO codi, dpti; EXIT WHEN ordenados%NOTFOUND; DBMS_OUTPUT.PUT_LINE(codi || ' ' || dpti ); END LOOP; CLOSE ordenados;END;/

Ejercicio 3

Declara el cursor (Crea)

Abre el cursor

Procesar(Recuperar Fila)

Cerrar Cursos

Mostrar los departamentos en forma ordenada

CREATE TABLE areas( codigo varchar(8) PRIMARY KEY, nombre VARCHAR2(15));

INSERT INTO areas VALUES(‘ATC’,’Contabilidad');INSERT INTO areas VALUES(‘ATD,’Administración’);INSERT INTO areas VALUES(‘ATS’, ‘Sistemas’);

Ejercicio 4 Cursores parametrizados

Por ejemplo, el siguiente código crea un cursor para las áreas cuyo código se pasa por parámetro en la orden OPEN:

1. DECLARE2. v_CODIGO AREAS.CODIGO%TYPE;3. v_NOMBRE AREAS.NOMBRE%TYPE;4. CURSOR c_AREAS (v_COD AREAS.CODIGO%TYPE) IS5. SELECT CODIGO, NOMBRE FROM AREAS 6. WHERE CODIGO = v_COD ORDER BY CODIGO;7. BEGIN8. OPEN c_AREAS ('ATC');9. LOOP10. FETCH c_AREAS INTO v_CODIGO, v_NOMBRE;11. EXIT WHEN c_AREAS%NOTFOUND;12. DBMS_OUTPUT.PUT_LINE(v_CODIGO || ' - ' ||v_NOMBRE);13. END LOOP;14. CLOSE c_AREAS;15. END;16. /

Ejercicio 4 Cursores parametrizados

Por ejemplo, el siguiente código crea un cursor para las áreas cuyo código se pasa por parámetro en la orden

OPEN:

Declara el cursor (Crea)

Abre el cursor

Procesar(Recuperar Fila)

Cerrar Cursos

Envía parámetro

1. DECLARE2. CURSOR todos IS SELECT * FROM emp;3. mi_emp emp%ROWTYPE;4. BEGIN 5. OPEN todos;6. LOOP 7. FETCH todos INTO mi_emp;8. /* Procesar sólamente los 2 primeros empleados retornados*/9. EXIT WHEN todos%NOTFOUND OR todos%ROWCOUNT > 2;10. DBMS_OUTPUT.PUT_LINE('Empleado #'||todos%rowcount|| ': '||

mi_emp.empno ||' '|| mi_emp.ename ||' '|| mi_emp.sal );11. END LOOP;12. CLOSE todos;13. END;14. /

Ejemplo 5 :Cuando se declara una variable del tipo de una tabla mediante ROWTYPE, se puede usar con un cursor así:

Empleado #1: 7369 SMITH 800Empleado #2: 7499 ALLEN 2342,56

• CREAR UN CURSOR QUE RETORNE EL NOMBRE DE LOS EMPLEADOS (mayúscula la primera letra), QUE TRABAJAN EN UN DEPARTAMENTO ENVIADO(NOMBRE DE DEPARTAMENTO), TENER EN CUENTA QUE SE DESEA MOSTRAR SOLAMENTE 1 REGISTROS DE TODO EL RESULTADO.

Claúsulas

• WHERE CURRENT OFSi se declara el cursor con la claúsula FOR UPDATE, se puede emplear la claúsula WHERE CURRENT OF en una orden UPDATE o DELETE para hacer referencia a la fila recién extraída por el cursor.

WHERE CURRENT OF cursor

Ejemplo FOR UDPATE

DECLARE-- Este cursor bloquea las columnas dname, locCURSOR c_AllDepartments IS

SELECT * FROM dept FOR UPDATE OF dname, loc

Ejemplo 1 :Ejemplo 1 :Incrementa el sueldo en un 10% de los que Incrementa el sueldo en un 10% de los que pertenecen al departamento 30pertenecen al departamento 30

WHERE CURRENT OFWHERE CURRENT OFDECLARE

CURSOR c_salario ISSELECT sal FROM emp where deptno = 30

FOR UPDATE NOWAIT;

BEGINFOR emp_record IN c_salario LOOP

UPDATE emp SET sal = emp_record.sal * 1.10WHERE CURRENT OF c_salario;

END LOOP;COMMIT;

END;

Ejemplo 2: Agregar un punto al final del continente de cada paísAgregar un punto al final del continente de cada país

CREATE TABLE PAISES(CO_PAIS          NUMBER,DESCRIPCION      VARCHAR2(50),CONTINENTE  VARCHAR2(20) );   

Ejemplo 2: Ejemplo 2: Agregar un Agregar un punto al final punto al final del continente del continente de cada paisde cada pais

DECLARE CURSOR cpaises ISselect CO_PAIS, DESCRIPCION, CONTINENTE from paisesFOR UPDATE;co_pais VARCHAR2(3);descripcion VARCHAR2(50);continente VARCHAR2(25);BEGINOPEN cpaises;FETCH cpaises INTO co_pais, descripcion, continente;WHILE cpaises%foundLOOP UPDATE PAISES SET CONTINENTE = CONTINENTE || '.'WHERE CURRENT OF cpaises;FETCH cpaises INTO co_pais,descripcion,continente;END LOOP; CLOSE cpaises;COMMIT;END;

FOR UPDATE y CURRENT OF

DECLARECURSOR emps IS SELECT ENAME,SAL FROM emp FOR UPDATE;BEGINFOR empleados IN emps LOOP IF empleados.SAL > 1000 THEN DELETE FROM emp WHERE CURRENT OF emps; -- Se refiere a la fila actual ELSE UPDATE emp SET SAL = SAL + 50 WHERE CURRENT OF emps; END IF; END LOOP;END;/Nota: Para poder usar CURRENT OF el cursor debe ser FOR

UPDATE.

• DECLARE• CURSOR emps IS SELECT empno,sal FROM emp FOR

UPDATE;• BEGIN• FOR empleados IN emps LOOP• IF empleados.sal > 1000 THEN• DELETE FROM emp• WHERE CURRENT OF emps; -- Se refiere a la fila actual• ELSE• UPDATE emp SET sal = sal + 50• WHERE CURRENT OF emps;• END IF;• END LOOP;• END;• /

Uso de cursores implícitos

Asociados fundamentalmente a las órdenes INSERT, DELETE, UPDATE.

Sea la tabla animal en una granja:

CREATE TABLE animal( codigo NUMBER(5) PRIMARY KEY, nombre VARCHAR2(20) NOT NULL, tipo CHAR(1) NOT NULL CHECK (tipo IN('c','v','p')), -- c= cerdo, v= vaca, p= pato peso NUMBER(5) NOT NULL);

INSERT INTO animal VALUES (1,'Hello Kitty', 'v', 300);INSERT INTO animal VALUES (2,'Pakita', 'v', 310);INSERT INTO animal VALUES (3,'Padrón', 'c', 126);INSERT INTO animal VALUES (4,'Pamela', 'c', 100);INSERT INTO animal VALUES (5,'Ricky', 'c', 150);INSERT INTO animal VALUES (6,'Comeniños', 'c', 175);INSERT INTO animal VALUES (7,'Comelón', 'c', 650);INSERT INTO animal VALUES (8,'Donald', 'p', 80);

Crear un bloque que muestre un mensaje 'Había más de 5 cerdos gordos” en caso se elimine 5 o mas cerdos con peso superior a 140 kilos y en caso no encuentre ninguno mostrar “No habia cerdos gordos”

Ejercicio: Ir mostrando uno a uno los cerdos que se van borrando.

Crear un bloque que muestre un mensaje

'Había más de 5 cerdos gordos en caso se elimine 5 o mas s cerdos con peso

superior a 140 kilos y en caso contrario “No habia cerdos gordos”

BEGIN DELETE FROM animal WHERE peso > 140 AND tipo='c'; IF SQL%ROWCOUNT > 5 THEN DBMS_OUTPUT.PUT_LINE('Había más de 5 cerdos gordos'); ELSIF SQL%ROWCOUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No había cerdos gordos'); END IF; DBMS_OUTPUT.PUT_LINE('Total de cerdotes eliminados: '||SQL%ROWCOUNT );END;/

Ejercicio: Ir mostrando uno a uno los cerdos que se van borrando.

RESUMEN

Un comando SELECT que devuelva más de una fila requiere el uso de un cursor.Estos cursores se llaman explícitos yexplícitos ya que son

declarados por el programador. (Existen también los cursores implícitos, ver luego.)

Sintaxis:

• CURSORCURSOR <nombre-cursor> -- Se declaraISIS <consulta en SQL>;

• OPENOPEN <nombre-cursor>; -- Para abrirlo• FETCHFETCH <nombre-cursor> INTOINTO <var1 [,var2]...>; -- Para ir tomando los datos• CLOSECLOSE <nombre-cursor>; -- Para cerrarlo

PENSAR

2..- Crear una tabla empleado y descontar su sueldo en 10 soles mediante un cursor. Implícito y Explicito

1..- Crear un cursor que muestre el nombre de los empleados ordenados por su sueldo en forma ascendente.

DECLARE CURSOR ordenados IS SELECT sueldo, cargo FROM emp ORDER BY sueldo DESC; miemp ordenados%ROWTYPE; suma NUMBER(6):= 0;BEGIN OPEN ordenados; LOOP FETCH ordenados INTO miemp; EXIT WHEN ordenados%NOTFOUND OR miemp.cargo= 'Gte.'; suma := suma + miemp.sueldo; END LOOP; CLOSE ordenados; DBMS_OUTPUT.PUT_LINE('Total: ' || suma ); END;/

Ejercicio 6.- Se puede trabajar con variables declaradas de tipo cursortipo cursor:

¿Qué hace este código?

Final Tema

¿Alguna pregunta o alguna duda?

Gracias.

Crear un bloque pl/sql que permita realizar el mantenimiento de la tabla departamento (insert, update ,detete).Se deben declarar variables tipo columna(%type) , las cuales deben estar inicializadas.Para la acción a realizar considerar los siguiente valores:I = Insert, U= update, D= delete. Mostrar la salida exitosa por pantalla según la acción indicada

Crear un bloque PL/SQL que permita insertar un registro en la tabla emp auto generando

su código y utilizando el operador %type.Nota: usar la funcione MAX

• Cursores Los cursores son áreas de trabajo que permiten ejecutar sentencias SQL y procesar la información obtenida de ellos.

Hay dos tipos de cursores: implícitos y explícitos. PL/SQL declara implícitamente un cursor para todas las sentencias de manipulación de datos, incluyendo las consultas que retornan sólo una fila. Para consultas que devuelven más de una fila, es posible declarar explícitamente un cursor que procese las filas en forma individual.

•

Por ejemplo:

DECLARE

CURSOR curs_01 IS

SELECT empno, ename, job FROM emp WHERE deptno=20;

El conjunto de filas retornado se denomina "set de resultados". Su tamaño está determinado por el número de filas que calzan con el criterio de selección de la query que implementa el cursor. Las filas son procesadas de a una cada vez.

En el capítulo dedicado exclusivamente a estos componentes del lenguaje, se detallarán las características de las diferentes modalidades de utilización de los cursores.

•

1.-Crear un bloque PL/SQL que contenga un cursor con sus 4 partes y reciba como parametro un mes(en letras o numero) y retorne todos los campos de lasemana2.-Crear un bloque PL/SQL que contenga un cursor que retorne todos los datos de la pelicula que reciba como parametro la descripcion de genero y la descripcion de clasificación.3.-Crear un cursor que cambia el nombre de pais a Perú a aquellas peliculas cuya cartelera tenga un hora de inicio a la enviada como parametro.