Cursor PL / SQL

Resumen: en este tutorial, aprenderá sobre el cursor PL / SQL y su uso.

Un cursor es un puntero que apunta al resultado de una consulta. PL / SQL tiene dos tipos de cursores: cursores implícitos y cursores explícitos.

Cursores implícitos

Siempre que Oracle ejecuta una instrucción SQL como SELECT INTO, INSERT, UPDATE y DELETE, crea automáticamente un cursor implícito.

Oracle administra internamente todo el ciclo de ejecución de los cursores implícitos y revela solo la información y los estados del cursor, como SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND y SQL%NOTFOUND.

El cursor implícito no es elegante cuando la consulta devuelve cero o varias filas que causan la excepción NO_DATA_FOUND o TOO_MANY_ROWS respectivamente.

Cursores explícitos

Un cursor explícito es una SELECT declaración declarada explícitamente en la sección de declaración del bloque actual o una especificación de paquete.

Para un cursor explícito, tiene cont recorre su ciclo de ejecución desde OPEN, FETCH y CLOSE.

Oracle define un ciclo de ejecución que ejecuta una instrucción SQL y le asocia un cursor.

La siguiente ilustración muestra el ciclo de ejecución de un cursor explícito:

Examinemos cada paso en detalle.

Declarar un cursor

Antes de usar un cursor explícito, debe declararlo en la sección de declaración de un bloque o paquete de la siguiente manera:

En esta sintaxis :

• Primero, especifique el nombre del cursor después de la CURSOR palabra clave.
• En segundo lugar, defina una consulta para obtener datos después de la palabra clave IS.

Abrir un cursor

Antes de comenzar a buscar filas desde el cursor, debe abrirlo. Para abrir un cursor, utilice la siguiente sintaxis:

En esta sintaxis, cursor_name es el nombre del cursor declarado en la sección de declaración.

Cuando abre un cursor, Oracle analiza la consulta, vincula las variables y ejecuta la declaración SQL asociada.

Oracle también determina un plan de ejecución , asocia las variables del lenguaje principal y los parámetros del cursor con los marcadores de posición en la instrucción SQL, determina el conjunto de resultados y coloca el cursor en la primera fila del conjunto de resultados.

Más sobre el cursor parametrizado en el siguiente tutorial.

Obtener de un cursor

La instrucción FETCH coloca el contenido de la fila actual en variables. La sintaxis de la instrucción FETCH es la siguiente:

Para recuperar todas las filas en un conjunto de resultados, necesita para recuperar cada fila hasta la última.

Cerrar un cursor

Después de obtener todas las filas, debe cerrar el cursor con CLOSE declaración:

Cerrar un cursor indica a Oracle que libere la memoria asignada en el momento apropiado.

Si declara un cursor en un bloque, procedimiento o función anónimos, el cursor se cerrará automáticamente cuando finalice la ejecución de estos objetos.

Sin embargo, debe cerrar explícitamente los cursores basados en paquetes. Tenga en cuenta que si cierra un cursor que aún no se ha abierto, Oracle generará una INVALID_CURSOR excepción.

Atributos explícitos del cursor

Un cursor tiene cuatro atributos a los que puede hacer referencia en el siguiente formato:

donde cursor_name es el nombre del cursor explícito.

1)% ISOPEN

Este atributo es TRUE si el cursor está abierto o FALSE si no lo es.

2)% ENCONTRADO

Este atributo tiene cuatro valores:

• NULL antes de la primera búsqueda
• TRUE si un registro se obtuvo correctamente
• FALSE si no se devuelve ninguna fila
• INVALID_CURSOR si el cursor no está abierto

3)% NOTFOUND

Este atributo tiene cuatro valores:

• NULL antes de la primera recuperación
• FALSE si se obtuvo un registro correctamente cuidadosamente
• TRUE si no se devuelve ninguna fila
• INVALID_CURSOR si el cursor no está abierto

3)% ROWCOUNT

El atributo %ROWCOUNT devuelve el número de filas extraídas del cursor. Si el cursor no está abierto, este atributo devuelve INVALID_CURSOR.

Ejemplo de cursor PL / SQL

Usaremos las tablas orders y order_items de la base de datos de muestra para la demostración.

La siguiente declaración crea una vista que devuelve los ingresos por ventas de los clientes:

Los valores de la columna de crédito son el 5% de los ingresos por ventas totales.

Suponga que necesita desarrollar un bloquear eso:

1. Restablecer los límites de crédito de todos los clientes a cero.
2. Obtener clientes clasificados por ventas en orden descendente y darles nuevos límites de crédito a partir de un presupuesto de 1 millón.

El siguiente bloque anónimo ilustra la lógica:

En la sección de declaración, declaramos tres variables.

La primera es l_budget cuyo valor inicial es 1,000,000.

La segunda variable es una variable de cursor explícita llamada c_sales cuyo declaración recupera datos de la sales vista:

La tercera variable es registro basado en cursor llamado c_sales.

En la sección de ejecución, realizamos lo siguiente:

• Primero, restablecemos los límites de crédito de todos los clientes a cero mediante una UPDATE declaración.
• En segundo lugar, abra el c_sales cursor.
• En tercer lugar, recupere cada fila del cursor. En cada iteración del ciclo, actualizamos el límite de crédito y reducimos el presupuesto. El ciclo termina cuando no hay fila para buscar o se agota el presupuesto.
• Finalmente, cierre el cursor.

La siguiente consulta recupera datos del customers tabla para verificar la actualización:

Resultado:

Como puede ver claramente en el resultado, solo los primeros clientes tienen los límites de crédito. Si suma todos los límites de crédito, el total debería ser 1 millón como se muestra a continuación:

Ahora , debe comprender los cursores PL / SQL, incluidos los cursores implícitos y explícitos, y cómo usarlos de manera efectiva para procesar datos, fila por fila, desde una tabla.