¿Qué es un cursor en Oracle y cuáles son ejemplos prácticos?

En el mundo de las bases de datos, Oracle es uno de los sistemas gestores más populares y ampliamente utilizados. Una de las características clave de Oracle es su lenguaje de programación PL/SQL, que permite escribir código procedural dentro de la base de datos. Entre las muchas funcionalidades de PL/SQL, el uso de cursos en Oracle es una de las más importantes y útiles. En este tutorial, exploraremos qué es un cursor in oracle y cómo se utilizan en la práctica.

Definición de cursor en Oracle

En el contexto de Oracle y PL/SQL, un cursor se define como un objeto que permite recuperar y manipular conjuntos de datos de una base de datos. Un cursor actúa como un puntero o una referencia a un conjunto de resultados, permitiendo a los programadores acceder a los datos fila por fila, realizar operaciones y realizar procesamientos adicionales.

Existen dos tipos de cursos en PL/SQL: los cursos implícitos y los cursos explícitos. Los cursos implícitos son creados automáticamente por Oracle al ejecutar ciertas sentencias SQL, como SELECT INTO, INSERT, UPDATE y DELETE. Estos cursos son gestionados internamente por Oracle y no requieren intervención directa del programador para su ciclo de ejecución.

Tipos de cursores en PL/SQL

En PL/SQL, existen dos tipos de cursores: los cursor implícitos y los cursores explícitos. Cada uno tiene sus propias características y se utilizan en diferentes situaciones.

Los cursos implícitos son creados automáticamente por Oracle al ejecutar ciertas sentencias SQL, como SELECT INTO, INSERT, UPDATE y DELETE. Estos cursos se generan internamente por Oracle y no requieren que el programador declare, abra o cierre explícitamente el cursor. Además, Oracle se encarga de gestionar el ciclo de ejecución del cursor implícito.

Por otro lado, los cursores explícitos son aquellos que el programador declara, abre y cierra explícitamente. Se definen en la sección de declaración del bloque o en la especificación del paquete y se utilizan cuando se necesita un mayor control sobre el ciclo de ejecución del cursor. Esto es especialmente útil cuando se desea obtener más de una fila de resultados o cuando se realizan operaciones más complejas.

Los cursores explícitos se declaran especificando un nombre para el cursor y una consulta que se utilizará para obtener los datos. Antes de poder obtener filas del cursor, es necesario cursor open y, una vez se han obtenido todas las filas necesarias, se debe cerrar el cursor. Esto garantiza una gestión adecuada de los recursos y un mejor rendimiento.

Cursores implícitos en Oracle

Los cursos implícitos en Oracle son aquellos que se crean automáticamente por el sistema al ejecutar ciertas sentencias SQL como SELECT INTO, INSERT, UPDATE y DELETE. Estos cursos son gestionados internamente por Oracle y no requieren la intervención directa del programador para su ciclo de ejecución.

El uso de cursos implícitos es conveniente cuando se desea recuperar un único resultado o cuando se realizan operaciones sencillas que no requieren un manejo complejo del cursor. Por ejemplo, al utilizar la sentencia SELECT INTO para asignar el valor de una columna a una variable, Oracle crea automáticamente un cursor implícito para obtener ese único resultado y asignarlo a la variable especificada.

Sin embargo, los cursos implícitos no son la opción más elegante cuando la consulta devuelve cero o múltiples filas. En estos casos, es preferible utilizar cursos explícitos para tener un mayor control sobre el ciclo de ejecución del cursor y poder tratar adecuadamente todas las filas resultantes.

Cursores explícitos en Oracle

Los cursos explícitos en Oracle son aquellos que se declaran explícitamente por el programador en la sección de declaración del bloque o en la especificación del paquete. A diferencia de los cursos implícitos, los cursos explícitos brindan al programador un mayor control sobre el ciclo de ejecución y el manejo de los resultados.

Para utilizar un cursor explícito, se debe especificar un nombre para el cursor y una consulta que se utilizará para obtener los datos. Antes de poder obtener filas del cursor, es necesario open el cursor utilizando la instrucción OPEN. Una vez se han obtenido todas las filas necesarias, se debe cerrar el cursor utilizando la instrucción CLOSE. Esto asegura una gestión adecuada de los recursos y un mejor rendimiento del código.

Además de la apertura y cierre del cursor, los cursos explícitos también ofrecen diferentes atributos que proporcionan información sobre el estado del cursor y el número de filas obtenidas. Algunos de estos atributos incluyen:

• %ISOPEN: Devuelve true si el cursor está abierto.
• %FOUND: Devuelve true si se encontró al menos una fila.
• %NOTFOUND: Devuelve true si no se encontró ninguna fila.
• %ROWCOUNT: Devuelve el número de filas obtenidas hasta el momento.

Estos atributos permiten al programador tomar decisiones y realizar acciones en función del estado y los resultados del cursor. Por ejemplo, se pueden realizar operaciones adicionales en caso de que se encuentren o no resultados, o se puede obtener el número total de filas afectadas por una operación.

Un ejemplo práctico del uso de cursos explícitos en Oracle es actualizar los límites de crédito de los clientes según las ventas y asignar nuevos límites desde un presupuesto dado. Con un cursor explícito, se puede seleccionar las ventas de los clientes y, a través de un bucle, actualizar los límites de crédito en función de los resultados de la consulta.

Ciclo de ejecución de un cursor explícito

El ciclo de ejecución de un cursor explícito consta de varias etapas que deben seguirse para garantizar un procesamiento adecuado de los resultados. Las etapas son las siguientes:

1. Declaración del cursor: En esta etapa, se declara el cursor explícito especificando su nombre y la consulta que se utilizará para obtener los datos.
2. Apertura del cursor: Antes de poder obtener filas del cursor, es necesario open cursor utilizando la instrucción OPEN. Esto prepara el cursor para recuperar filas de datos.
3. Recuperación de filas: Una vez que el cursor está abierto, se pueden recuperar filas de datos utilizando la instrucción FETCH. Cada vez que se llama a FETCH, se obtiene una nueva fila de resultados.
4. Procesamiento de filas: Después de recuperar una fila de datos, se puede realizar cualquier procesamiento adicional necesario. Esto puede incluir cálculos, actualizaciones en la base de datos u otras operaciones.
5. Comprobación de condiciones: Durante el procesamiento de filas, es posible que se deban realizar comprobaciones adicionales utilizando los atributos del cursor (%FOUND, %NOTFOUND, %ROWCOUNT) para tomar decisiones basadas en el estado y los resultados del cursor.
6. Cierre del cursor: Una vez que se han obtenido todas las filas necesarias y se ha completado el procesamiento, se debe cerrar el cursor utilizando la instrucción CLOSE. Esto libera los recursos utilizados por el cursor y mejora el rendimiento del código.

Este ciclo de ejecución se repite hasta que se han recuperado todas las filas necesarias y se ha completado el procesamiento. Es importante seguir este flujo de trabajo de manera adecuada para evitar errores y garantizar el correcto manejo de los datos del cursor explícito.

Atributos de un cursor explícito

Los cursos explícitos en Oracle tienen varios atributos que proporcionan información útil sobre el estado del cursor y el número de filas obtenidas. Algunos de los atributos más comunes incluyen:

• %ISOPEN: Este atributo devuelve true si el cursor está abierto, es decir, si se ha ejecutado la instrucción OPEN. Si el cursor está cerrado, devuelve false.
• %FOUND: Devuelve true si se ha encontrado al menos una fila de datos durante la recuperación de filas. Si no se encuentra ninguna fila, devuelve false.
• %NOTFOUND: A diferencia de %FOUND, este atributo devuelve true si no se ha encontrado ninguna fila durante la recuperación de filas. Si se encuentra alguna fila, devuelve false.
• %ROWCOUNT: Devuelve el número de filas recuperadas hasta el momento desde que se abrió el cursor. Se puede utilizar este atributo para realizar un seguimiento del progreso y el número total de filas obtenidas.

Estos atributos son especialmente útiles para controlar el flujo del programa y tomar decisiones basadas en el estado y los resultados del cursor. Por ejemplo, se pueden utilizar para realizar operaciones adicionales cuando se encuentra una fila específica, verificar si se han obtenido todas las filas necesarias o realizar acciones diferentes según el número total de filas afectadas.

Es importante tener en cuenta que estos atributos solo están disponibles para cursos explícitos y no se aplican a los cursos implícitos que se generan automáticamente por Oracle.

En Oracle, un cursor se utiliza para procesar un conjunto de filas devueltas por una consulta SQL. Los cursos en Oracle son útiles cuando se necesita recorrer un conjunto de resultados fila por fila. Un ejemplo simple de un cursor en Oracle sería:

sql
DECLARE
— Declaración del cursor
CURSOR c_empleados IS
SELECT * FROM empleados;
BEGIN
— Abrir el cursor
OPEN c_empleados;

— Recorrer las filas
LOOP
— Obtener los datos de cada fila
FETCH c_empleados INTO v_id, v_nombre;

— Salir del bucle si no hay más filas
EXIT WHEN c_empleados%NOTFOUND;

— Procesar los datos
DBMS_OUTPUT.PUT_LINE(‘ID: ‘ || v_id || ‘, Nombre: ‘ || v_nombre);
END LOOP;

— Cerrar el cursor
CLOSE c_empleados;
END;

En este ejemplo, se declara un cursor en Oracle llamado c_empleados que recupera todas las columnas de la tabla empleados. Luego se abre el cursor, se recorren las filas recuperadas y se procesan los datos fila por fila. Finalmente, se cierra el cursor después de completar el procesamiento.

Los cursos en Oracle con ejemplos son una parte fundamental de la programación en PL/SQL, ya que permiten manejar eficientemente conjuntos de datos y realizar operaciones detalladas sobre ellos. A través de la declaración, apertura, recorrido y cierre de un cursor en Oracle, los desarrolladores pueden implementar lógica de negocio compleja de manera estructurada y eficaz. Oracle open cursor y pl sql cursor son conceptos clave a entender para trabajar con bases de datos Oracle de manera efectiva.

Ejemplo de uso de cursores en PL/SQL

A continuación, te mostraré un ejemplo práctico de cómo utilizar cursos en PL/SQL para actualizar los límites de crédito de los clientes según las ventas y asignar nuevos límites desde un presupuesto dado. Este ejemplo asume que tienes una tabla llamada «clientes» con las columnas «id_cliente», «nombre», «ventas_anuales» y «limite_credito».

sql
DECLARE
CURSOR c_clientes IS
SELECT id_cliente, ventas_anuales
FROM clientes
FOR UPDATE OF limite_credito;

v_presupuesto_total NUMBER := 1000000;
v_disponible NUMBER := v_presupuesto_total;
v_id_cliente clientes.id_cliente%TYPE;
v_ventas_anuales clientes.ventas_anuales%TYPE;
v_nuevo_limite clientes.limite_credito%TYPE;
BEGIN
OPEN c_clientes;

LOOP
FETCH c_clientes INTO v_id_cliente, v_ventas_anuales;
EXIT WHEN c_clientes%NOTFOUND;

IF v_disponible >= v_ventas_anuales THEN
v_nuevo_limite := v_ventas_anuales * 0.9; — Reducción del 10%
v_disponible := v_disponible – v_ventas_anuales;
ELSE
v_nuevo_limite := v_disponible * 0.9; — Reducción del 10% del presupuesto disponible
v_disponible := 0;
END IF;

UPDATE clientes
SET limite_credito = v_nuevo_limite
WHERE CURRENT OF c_clientes;

COMMIT; — Guardar los cambios en cada iteración

DBMS_OUTPUT.PUT_LINE(‘Cliente ‘ || v_id_cliente || ‘: Nuevo límite de crédito = ‘ || v_nuevo_limite);
END LOOP;

CLOSE c_clientes;
END;
/

En este ejemplo, se declara un cursor explícito llamado «c_clientes» que selecciona los id de clientes y sus ventas anuales de la tabla «clientes». El cursor utiliza la cláusula «FOR UPDATE OF limite_credito» para bloquear las filas seleccionadas y permitir la realización de actualizaciones.

Luego, se declara una variable «v_presupuesto_total» que representa el presupuesto disponible para modificar los límites de crédito. La variable «v_disponible» se inicializa con el valor del presupuesto total.

A continuación, se abre el cursor y se inicia un bucle para recorrer las filas resultantes. Se utiliza la instrucción FETCH para obtener el id de cliente y las ventas anuales de cada fila.

Dentro del bucle, se realiza una verificación para determinar si hay presupuesto disponible para reducir el límite de crédito del cliente. Si hay suficiente presupuesto, se calcula el nuevo límite reduciendo el 10% de las ventas anuales. En caso contrario, se calcula el nuevo límite utilizando el presupuesto disponible restante.

A continuación, se utiliza la instrucción UPDATE para actualizar el campo «limite_credito» de la tabla «clientes» para el cliente actual mediante la cláusula WHERE CURRENT OF. Esto garantiza que se actualice la fila correcta.

Después de cada actualización, se realiza un COMMIT para guardar los cambios hasta ese punto.

Finalmente, se cierra el cursor y se muestra la información del cliente y su nuevo límite de crédito utilizando la función DBMS_OUTPUT.PUT_LINE.

Este ejemplo ilustra cómo utilizar cursos en PL/SQL para realizar operaciones más complejas y controlar el ciclo de ejecución del cursor de manera explícita.