SESION 04

Utilizar cursores
SQL
 La invocación directa, o SQL interactivo, es un método de acceso de datos
que soporta la ejecución ad hoc de instrucciones SQL, usualmente a través
de algún tipo de aplicación de cliente.
 Por ejemplo, se puede utilizar SQL Server Management Studio para interactuar
directamente con una base de datos SQL. Sin embargo, la invocación
directa generalmente representa solamente un pequeño porcentaje de todo
el uso de una base de datos.
 Un método mucho más común que se utiliza para acceder a las bases de
datos SQL es el SQL incrustado, un modelo de acceso de datos en el cual las
instrucciones SQL están incrustadas en una aplicación de lenguaje de
programación, por ejemplo C, Java y COBOL.
 Para soportar SQL incrustado, el estándar SQL permite declarar cursores que
actúan como señaladores para especificar filas de datos en los resultados de
la consulta.
Entender los cursores SQL

 Una de las características que definen a SQL es el hecho de que los datos en una base
de datos SQL pueden ser manejados en conjuntos. De hecho, a menudo se hace
referencia a los resultados de una consulta arrojados por las instrucciones SELECT como
conjuntos de resultados. Cada uno de estos conjuntos de resultados está conformado
por una o más filas extraídas desde una o más tablas.
 Cuando se trabaja con los datos SQL de forma interactiva, tener los datos arrojados en
conjuntos raramente representa un problema debido a que normalmente es posible
desplazarse a través de los resultados de la consulta para encontrar la información que
se necesita.
 Si el tamaño de los resultados es demasiado extenso para desplazarse fácilmente, es
posible hacer más preciso el enfoque de la expresión de búsqueda para arrojar un
conjunto de resultados más manejable. Sin embargo, la mayoría de los accesos de
datos se hace a través de medios diferentes a la invocación directa.
 Un cursor funciona como un señalador que permite al lenguaje de
programación de aplicación tratar a los resultados de la consulta una fila
a la vez, de manera muy parecida a la que estos lenguajes de
programación manejan los registros desde archivos de datos
tradicionales.
 A pesar de que el cursor puede recorrer todas las filas de los resultados
de la consulta, se enfoca solamente en una fila a la vez. Un cursor aun así
arroja un conjunto de resultados completo, pero permite al lenguaje de
programación convocar solamente una fila de ese conjunto.
EJEMPLO:

 Por ejemplo, supongamos que los resultados de la consulta se derivan de

la siguiente instrucción SELECT:

SELECT NOMBRE_INTERPRETE,
LUGAR_DE_NACIMIENTO
FROM INTERPRETES;

 Los resultados de la consulta de esta instrucción arrojarán filas de la tabla

INTERPRETES, que incluyen la columna NOMBRE_INTERPRETE y la columna
LUGAR_DE_NACIMIENTO. Sin embargo, el lenguaje de programación de
aplicación puede tratar solamente con una fila a la vez, por lo que el
cursor se declara como una instrucción SQL incrustada dentro del
lenguaje de programación de aplicación.
Declarar y abrir
cursores SQL
 La mayoría de los lenguajes de programación de aplicación soportan el
uso de cursores para recuperar datos de una base de datos SQL.
 El lenguaje del cursor está incrustado en el código de programación de
una forma muy parecida a la que se incrustaría cualquier instrucción SQL.
Cuando se utiliza un cursor en un lenguaje de programación, primero se
debe declarar el cursor (similar a como se declararía una variable) y
luego utilizar el nombre de la instrucción (el nombre que se le asignó al
cursor) en otras instrucciones SQL incrustadas para abrir el cursor,
recuperar filas individuales a través del cursor y cerrar el cursor.
 Las siguientes descripciones proporcionan una idea general de estas
cuatro instrucciones:
 DECLARE CURSOR Declara el cursor SQL al definir el nombre del cursor, sus
características y una expresión de consulta que es invocada cuando se abre
el cursor.
 OPEN Abre el cursor e invoca la expresión de consulta, haciendo que los
resultados de consulta estén disponibles para las instrucciones FETCH.
 FETCH Recupera datos en las variables que pasan los datos al lenguaje de
programación host o a otras instrucciones SQL incrustadas.
 CLOSE Cierra el cursor. Una vez que el cursor es cerrado, no pueden
recuperarse datos de los resultados de la consulta del cursor.
Declarar un
cursor
 La primera instrucción que veremos será la instrucción DECLARE CURSOR.
El cursor debe ser declarado antes de que pueda utilizarse para
recuperar datos. Se puede declarar un cursor en cualquier punto del
código de aplicación, siempre y cuando sea declarado antes de que se
haga referencia al cursor por medio de cualquier otra instrucción.
 Muchos programadores prefieren declarar todos los cursores y variables
al inicio del programa para que se conserven juntas todas las
instrucciones. Por lo tanto, se puede hacer referencia a los cursores y
variables en cualquier punto del programa.
 La sintaxis para una instrucción de cursor incluye muchos elementos,
como se muestra en la siguiente sintaxis:

DECLARE <nombre del cursor>

[ SENSITIVE | INSENSITIVE | ASENSITIVE ]
[ SCROLL | NO SCROLL ] CURSOR
[ WITH HOLD | WITHOUT HOLD ]
[ WITH RETURN | WITHOUT RETURN ]
FOR <expresión de consulta>
[ ORDER BY <especificación del tipo> ]
[ FOR { READ ONLY | UPDATE [ OF <lista de la columna> ] } ]
Trabajar con
elementos
opcionales de la
sintaxis
Sensibilidad del cursor|

 El primer elemento opcional de la instrucción DECLARE CURSOR que

veremos es la sensibilidad del cursor, que se representa en la siguiente
sintaxis:

[ SENSITIVE | INSENSITIVE | ASENSITIVE ]

 La sensibilidad del cursor está relacionada con las instrucciones fuera del
cursor que afectan a las mismas filas que aquellas arrojadas por el cursor.
EJEMPLO

 supongamos que el cursor arroja filas de la tabla CDS_EN_EXISTENCIA.

Mientras el cursor está abierto, otra instrucción dentro de la misma
transacción elimina algunas de las mismas filas en la tabla
CDS_EN_EXISTENCIA que fueron arrojadas por el cursor. De la sensibilidad
del cursor dependerá si es que el cursor puede o no captar estas
eliminaciones.
Capacidad de desplazamiento del
cursor

 El siguiente elemento opcional en la instrucción DECLARE CURSOR que

veremos es la capacidad de desplazamiento del cursor, como muestra la
siguiente sintaxis:
[ SCROLL | NO SCROLL ]
 El desplazamiento está directamente relacionado con la instrucción FETCH y
las opciones que dicha instrucción puede utilizar para recuperar los datos.
 Si se especifica la opción SCROLL, la instrucción FETCH puede ser definida con
una de muchas opciones que extienda su habilidad para moverse a través
de los resultados de la consulta y arrojar filas específicas.
 La opción SCROLL permite a la instrucción FETCH brincar a través de los
resultados de la consulta según sea necesario para recuperar la fila
específica.
 Si se especifica NO SCROLL en la instrucción del cursor, la instrucción FETCH no
puede utilizar las opciones de desplazamiento adicionales y solamente puede
recuperar la siguiente fila disponible de los resultados de la consulta.
 Si no se especifica ninguna opción, se toma de modo predeterminado NO
SCROLL.
Capacidad para mantener abierto el
cursor

 El siguiente elemento que veremos en la sintaxis DECLARE CURSOR está

relacionado con la capacidad para mantener abierto el cursor, como se
muestra en la siguiente sintaxis:
[ WITH HOLD | WITHOUT HOLD ]

 La capacidad para mantener abierto el cursor hace referencia a una

característica en los cursores que está relacionada con la condición de
cerrar o no un cursor automáticamente cuando la transacción en la que
el cursor fue abierto es completada. Una transacción es una unidad
atómica de trabajo.
 Esto significa que todas las instrucciones dentro de la transacción deben
ser exitosas o ninguna de ellas podrá tener algún efecto.
 SQL proporciona dos opciones a elegir al activar la definición de la
capacidad para mantener abierto el cursor: WITH HOLD y WITHOUT HOLD.
 Si se especifica WITH HOLD, el cursor permanecerá abierto después de
completar la transacción, hasta que el usuario explícitamente lo cierre.
 Si se especifica WITHOUT HOLD, el cursor será cerrado automáticamente
cuando la transacción sea completada.
 Si no se especifica ninguna de estas opciones, se toma de manera
preestablecida WITHOUT HOLD y el cursor es cerrado automáticamente.
Capacidad del cursor para arrojar
resultados

 La capacidad del cursor para arrojar resultados es la siguiente opción

que veremos en la definición de la instrucción del cursor, y utiliza la
siguiente sintaxis:
[ WITH RETURN | WITHOUT RETURN ]

 Las opciones para arrojar resultados aplican solamente a los cursores que
son abiertos en un procedimiento invocado por SQL.
 Un procedimiento invocado por SQL es un tipo de rutina que es invocado
utilizando la instrucción EXEC.
 La instrucción EXEC es una instrucción SQL que invoca procedimientos y
permite pasar valores de parámetros a esos procedimientos.
 SQL soporta dos opciones del cursor para arrojar resultados WITH RETURN y
WITHOUT RETURN.
 Si se especifica WITH RETURN, el cursor es considerado un cursor del
conjunto de resultados. Por lo tanto, si se abre el cursor dentro de un
procedimiento invocado por SQL, el conjunto de resultados del cursor es
arrojado hacia lo que haya invocado el procedimiento, que puede ser
otra rutina invocada por SQL o un programa de lenguaje host.
 Si se especifica WITHOUT RETURN, el conjunto de resultados del cursor es
arrojado de la forma normal, sea abierto o no con un procedimiento
invocado por SQL.
 Si no se especifica ninguna opción, se asume de manera preestablecida
WITHOUT RETURN.
Capacidad de ordenamiento del
cursor

 La instrucción DECLARE CURSOR incluye una cláusula opcional ORDER BY,

como muestra la siguiente sintaxis:

[ ORDER BY <especificación de clasificación> ]

 La cláusula ORDER BY permite clasificar los resultados de la consulta

arrojados por la especificación de la consulta.
 En la cláusula se puede especificar cuáles columnas forman la base para
clasificar las filas.
 Se debe tener cuidado respecto al rendimiento debido que un cursor
con una cláusula ORDER BY puede forzar al motor SQL a recuperar y
clasificar el conjunto completo de resultados antes de que la primera fila
pueda ser arrojada, y esto puede ser desastroso para el rendimiento de
conjuntos con resultados muy grandes.
 Si se utiliza una cláusula ORDER BY, la instrucción SELECT del cursor no
puede contener una cláusula GROUP BY.
Capacidad de actualización del
cursor

 El último elemento opcional de la instrucción DECLARE CURSOR que

veremos es la capacidad de actualización del cursor, como se muestra
en la siguiente sintaxis:
[ FOR { READ ONLY | UPDATE [ OF <lista de la columna> ] } ]

 La capacidad de actualización del cursor se refiere a la habilidad de

utilizar una instrucción UPDATE o DELETE para modificar los datos arrojados
por la instrucción SELECT del cursor.
 Como puede verse en la sintaxis, se debe utilizar la palabra clave FOR
junto con la opción READ ONLY o UPDATE.
 Si se especifica READ ONLY, no es posible ejecutar una instrucción UPDATE
o DELETE para los resultados de la consulta arrojados por la instrucción
SELECT del cursor.
 Por otro lado, si se especifica UPDATE, sí es posible ejecutar las
instrucciones.
 Si no se especifica ninguna opción, se toma UPDATE de forma
predeterminada, a menos que otra opción la sobrescriba.
 La opción UPDATE también permite especificar cuáles columnas pueden
ser actualizadas en la tabla subyacente. Para hacer esto debe incluirse la
palabra clave OF, seguida de uno o más nombres de columna.
Crear una instrucción de cursor

 Ahora que hemos visto cada componente de la instrucción DECLARE

CURSOR, demos un vistazo a algunos ejemplos que pueden ayudar a
ilustrar cómo declarar un cursor.
 Para estos ejemplos utilizaremos la tabla INVENTARIO_CD.
 El primer ejemplo que veremos es una instrucción de cursor básica que
incluye solamente los elementos obligatorios más una cláusula ORDER BY,
como se muestra en la siguiente instrucción DECLARE CURSOR:

DECLARE CD_1 CURSOR

FOR
SELECT *
FROM INVENTARIO_CD
ORDER BY TITULO_CD;
 En el segundo ejemplo vamos añadir la palabra clave SCROLL y la
clausula FOR READ ONLY.

DECLARE CD_2 SCROLL CURSOR

FOR
SELECT *
FROM INVENTARIO_CD
ORDER BY TITULO_CD
FOR READ ONLY;
 El siguiente tipo de instrucción de sólo lectura que veremos también
incluye la palabra clave INSENSITIVE, como muestra el siguiente ejemplo:

DECLARE CD_3 SCROLL INSENSITIVE CURSOR

FOR
SELECT *
FROM INVENTARIO_CD
ORDER BY TITULO_CD
FOR READ ONLY;
 Ahora demos un vistazo a un cursor actualizable. En la siguiente
instrucción de cursor, la instrucción SELECT arroja una vez más todas las
filas y columnas para la tabla INVENTARIO_CD:

DECLARE CD_4 CURSOR

FOR
SELECT *
FROM INVENTARIO_CD
FOR UPDATE;
 Si se desea que el cursor sea actualizable solamente para cierta
columna, se deberá incluir la cláusula FOR UPDATE, junto con el nombre
de la columna, como se muestran en el siguiente ejemplo:

DECLARE CD_5 CURSOR

FOR
SELECT *
FROM INVENTARIO_CD
FOR UPDATE OF DISCO_COMPACTO;
Abrir y cerrar un
cursor
Abrir cursor

 El proceso para abrir un cursor es bastante sencillo. Solamente es

necesario proporcionar la palabra clave OPEN y el nombre del cursor,
como se muestra en la siguiente sintaxis:

OPEN <nombre del cursor>

 Por ejemplo, para abrir el cursor CD_1 se invoca la siguiente instrucción

SQL:

OPEN CD_1;
Cerrar cursor

 Una vez que se ha terminado de utilizar el cursor, éste deberá cerrarse

para que puedan liberarse los recursos del sistema. Para cerrar un cursor
se puede utilizar la instrucción CLOSE, como se muestra en la siguiente
sintaxis:

CLOSE <nombre del cursor>

 La instrucción CLOSE no realiza otra función que cerrar el cursor, lo que

significa que los resultados de la consulta de la instrucción SELECT del
cursor son liberados. Por ejemplo, para cerrar el cursor CD_1 se utiliza la
siguiente instrucción SQL:

CLOSE CD_1;
Recuperar datos
desde un cursor
 Para poder hacer eso se debe utilizar una instrucción FETCH.
 La instrucción FETCH la que identifica las filas individuales dentro de ese
conjunto de datos y extrae los valores individuales de esas filas, que
pasan entonces a las variables host.
 Una variable host es un tipo de parámetro que pasa un valor al lenguaje
host.
 Una o más instrucciones FETCH pueden ser ejecutadas mientras un cursor
está abierto. Cada instrucción apunta a una fila específica en los
resultados de la consulta, y los valores son entonces extraídos de esas filas
 La siguiente sintaxis muestra los elementos básicos que conforman la
instrucción FETCH:

FETCH [ [ <orientación para búsqueda> ] FROM ]

<nombre del cursor> INTO <variables host>
 SQL soporta seis opciones de orientación para búsqueda que identifican
cuál fila es seleccionada de los resultados de la consulta del cursor.
 NEXT: Recupera la siguiente fila de los resultados de la consulta. Si se utiliza
NEXT en la primera instrucción FETCH después de que se abre el cursor, será
arrojada la primera fila en los resultados de la consulta. Una segunda
instrucción FETCH NEXT arrojará la segunda fila.
 PRIOR: Recupera directamente la fila anterior a la última que se había
recuperado. Si se utiliza PRIOR en la primera instrucción FETCH después de abrir
el cursor, ninguna fila será arrojada debido a que ninguna fila precede a la
primera fila.
 FIRST: Recupera la primera fila de los resultados de la consulta del cursor, sin
importar cuántas instrucciones FETCH hayan sido ejecutadas desde que se
abrió el cursor.
 LAST: Recupera la última fila de los resultados de la consulta del cursor, sin importar
cuántas instrucciones FETCH hayan sido ejecutadas desde que se abrió el cursor.
 ABSOLUTE <valor>: Recupera la fila especificada por el marcador de posición
<valor>. El valor debe ser un numérico exacto, a pesar de que puede ser derivado
de una variable host. El numérico identifica cuál fila es arrojada por la instrucción
FETCH. Por ejemplo, ABSOLUTE 1 arroja la primera fila, ABSOLUTE 2 arroja la segunda
fila y ABSOLUTE -1 arroja la última fila.
 RELATIVE <valor>: Recupera la fila especificada por el marcador de posición
<valor>, relativo a la posición actual del cursor. Si se utiliza RELATIVE en la primera
instrucción FETCH después de abrir el cursor, RELATIVE 1 arroja la primera fila de los
resultados de la consulta del cursor, y RELATIVE -1 arroja la última fila. Sin embargo, si
el cursor no está al comienzo de los resultados de la consulta, como lo está cuando
se abre el cursor por primera vez, RELATIVE 1 y RELATIVE -1 arrojan filas relativas a la
posición del cursor donde se quedó después de la última instrucción FETCH
ejecutada.
 La primera instrucción FETCH que veremos utiliza la opción de orientación
para búsqueda NEXT para recuperar una fila del cursor CD_2:

FETCH NEXT
FROM CD_2;

 La siguiente instrucción FETCH utiliza la orientación para búsqueda

ABSOLUTE:

FETCH ABSOLUTE 5
FROM CD_2;
 La siguiente instrucción FETCH utiliza la orientación para búsqueda FIRST:

FETCH FIRST
FROM CD_2;

 La siguiente instrucción FETCH utiliza la orientación para búsqueda

RELATIVE:

FETCH RELATIVE 10
FROM CD_2;
 La siguiente instrucción FETCH utiliza la orientación para búsqueda LAST:

FETCH LAST
FROM CD_2;

 La siguiente instrucción FETCH utiliza la orientación para búsqueda PRIOR:

FETCH PRIOR
FROM CD_2;
Utilizar
instrucciones
UPDATE y DELETE
posicionadas
 Para hacerlo se debe utiliza una instrucción UPDATE o DELETE
posicionada.
 Las instrucciones UPDATE y DELETE posicionadas contienen una cláusula
especial WHERE que hace referencia al cursor abierto.
 Demos un vistazo a cada una de estas dos instrucciones para mostrar
cómo pueden utilizarse para modificar los datos arrojados por el cursor.
Utilizar la instrucción UPDATE
posicionada

 La instrucción UPDATE posicionada es, en su mayor parte, igual que una

instrucción UPDATE regular, excepto que ésta requiere una cláusula
especial WHERE, como se muestra en la siguiente sintaxis:

UPDATE <nombre de la tabla>

SET <lista de conjunto>
WHERE CURRENT OF <nombre del cursor>
 Demos un vistazo a un ejemplo para demostrar cómo funciona esto. El
siguiente conjunto de instrucciones SQL declara el cursor CD_4, abre ese
cursor, busca una fila desde los resultados de la consulta del cursor,
actualiza esa fila y luego cierra el cursor:

OPEN CD_4_1;
DECLARE CD_4_1 CURSOR
FETCH CD_4_1
FOR
UPDATE INVENTARIO_CD
SELECT *
SET EXISTENCIA_CD= EXISTENCIA_CD * 2
FROM INVENTARIO_CD
WHERE CURRENT OF CD_4_1;
FOR UPDATE;
CLOSE CD_4_1;
 Una instrucción DELETE posicionada utiliza la siguiente sintaxis:

DELETE <nombre de la tabla>

WHERE CURRENT OF <nombre del cursor>
 Las siguientes instrucciones SQL declaran el cursor CD_4, abren el cursor,
arrojan una fila desde el cursor, eliminan esa fila y cierran el cursor:

DECLARE CD_4_2 CURSOR

FOR
SELECT *
FROM INVENTARIO_CD
FOR UPDATE;
OPEN CD_4_2;
FETCH CD_4_2
DELETE INVENTARIO_CD
WHERE CURRENT OF CD_4_2;
CLOSE CD_4_2;
EJERCICIOS CON CURSORES

 Se creará un cursor que nos muestre toda la información de la tabla

discos_compactos el cual será ordenado en forma ascendente.

 Deberá de realizar los 6 tipos de FETCH, para lo cual se deja a libre

elección los valores a ingresar.
EJERCICIOS CON CURSORES

 Al cursor anterior deberá de incluir la instrucción SCROLL e INSENSITIVE

 Realizar un cursor que utilice la instrucción UPDATE en la tabla

discos_compactos en donde se multiple por dos el numero de existencia.

 Realice la actualización del cursor anterior en donde usted ingrese el

valor que usted desee