Cursor (bases de datos) - Cursor (databases)
En informática , un cursor de base de datos es una estructura de control que permite atravesar los registros de una base de datos. Los cursores facilitan el procesamiento posterior junto con el recorrido, como la recuperación, adición y eliminación de registros de la base de datos. La característica del cursor de la base de datos del recorrido hace que los cursores sean similares al concepto del lenguaje de programación de iterador .
Los programadores de bases de datos utilizan cursores para procesar filas individuales devueltas por consultas del sistema de base de datos . Los cursores permiten la manipulación de conjuntos de resultados completos a la vez. En este escenario, un cursor habilita el procesamiento secuencial de filas en un conjunto de resultados.
En los procedimientos de SQL, un cursor permite definir un conjunto de resultados (un conjunto de filas de datos) y realizar una lógica compleja fila por fila. Al usar la misma mecánica, un procedimiento SQL también puede definir un conjunto de resultados y devolverlo directamente al llamador del procedimiento SQL oa una aplicación cliente.
Un cursor puede verse como un puntero a una fila en un conjunto de filas. El cursor solo puede hacer referencia a una fila a la vez, pero puede moverse a otras filas del conjunto de resultados según sea necesario.
Uso
Para utilizar cursores en procedimientos SQL, debe hacer lo siguiente:
- Declare un cursor que define un conjunto de resultados.
- Abra el cursor para establecer el conjunto de resultados.
- Obtenga los datos en variables locales según sea necesario desde el cursor, una fila a la vez.
- Cierre el cursor cuando termine.
Para trabajar con cursores, debe utilizar las siguientes instrucciones SQL
Esta sección presenta las formas en que el estándar SQL: 2003 define cómo utilizar cursores en aplicaciones en SQL incorporado. No todos los enlaces de aplicaciones para sistemas de bases de datos relacionales se adhieren a ese estándar y algunos (como CLI o JDBC ) utilizan una interfaz diferente.
Un programador hace que un cursor sea conocido por el DBMS usando una declaración DECLARE... CURSORy asignando al cursor un nombre (obligatorio):
DECLARE cursor_name CURSOR IS SELECT ... FROM ...
Antes de que el código pueda acceder a los datos, debe abrir el cursor con la OPENdeclaración. Inmediatamente después de una apertura exitosa, el cursor se coloca antes de la primera fila en el conjunto de resultados.
OPEN cursor_name
Las aplicaciones colocan los cursores en una fila específica en el conjunto de resultados con la FETCHinstrucción. Una operación de búsqueda transfiere los datos de la fila a la aplicación.
FETCH cursor_name INTO ...
Una vez que una aplicación ha procesado todas las filas disponibles o la operación de búsqueda se debe colocar en una fila no existente (compare los cursores desplazables a continuación), el DBMS devuelve un SQLSTATE '02000' (generalmente acompañado de un SQLCODE +100) para indicar el final. del conjunto de resultados.
El paso final implica cerrar el cursor usando la CLOSEdeclaración:
CLOSE cursor_name
Después de cerrar un cursor, un programa puede abrirlo nuevamente, lo que implica que el DBMS reevalúa la misma consulta o una consulta diferente y genera un nuevo conjunto de resultados.
Cursores desplazables
Los programadores pueden declarar cursores como desplazables o no desplazables. La capacidad de desplazamiento indica la dirección en la que se puede mover un cursor.
Con un cursor no desplazable (o solo hacia adelante ), puede FETCHcada fila como máximo una vez y el cursor se mueve automáticamente a la siguiente fila. Después de buscar la última fila, si vuelve a buscar, colocará el cursor después de la última fila y obtendrá el siguiente código: SQLSTATE 02000 (SQLCODE +100).
Un programa puede colocar un cursor desplazable en cualquier lugar del conjunto de resultados utilizando la FETCHinstrucción SQL. La palabra clave SCROLL debe especificarse al declarar el cursor. El valor predeterminado es NO SCROLL, aunque los enlaces de idiomas diferentes como JDBC pueden aplicar un valor predeterminado diferente.
DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT ... FROM ...
La posición de destino de un cursor desplazable se puede especificar relativamente (desde la posición actual del cursor) o absolutamente (desde el principio del conjunto de resultados).
FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM cursor_name
FETCH ABSOLUTE n FROM cursor_name
FETCH RELATIVE n FROM cursor_name;
Los cursores desplazables pueden acceder potencialmente a la misma fila en el conjunto de resultados varias veces. Por lo tanto, las modificaciones de datos (operaciones de inserción, actualización, eliminación) de otras transacciones podrían afectar el conjunto de resultados. Un cursor puede ser SENSIBLE o INSENSIBLE a tales modificaciones de datos. Un cursor sensible recoge modificaciones de datos que afectan el conjunto de resultados del cursor, y un cursor insensible no. Además, un cursor puede ser INSENSIBLE, en cuyo caso el DBMS intenta aplicar la sensibilidad tanto como sea posible.
"CON MANTENIMIENTO"
Los cursores generalmente se cierran automáticamente al final de una transacción, es decir, cuando ocurre un COMMIT o ROLLBACK (o una terminación implícita de la transacción). Ese comportamiento se puede cambiar si el cursor se declara usando la cláusula WITH HOLD (el valor predeterminado es WITHOUT HOLD). Un cursor sujetable se mantiene abierto sobre COMMIT y cerrado sobre ROLLBACK. (Algunos DBMS se desvían de este comportamiento estándar y también mantienen abiertos los cursores sostenibles sobre ROLLBACK).
DECLARE cursor_name CURSOR WITH HOLD FOR SELECT .... FROM ....
Cuando ocurre un COMMIT, un cursor sujetable se coloca antes de la siguiente fila. Por lo tanto, una instrucción UPDATE posicionada o DELETE posicionada solo tendrá éxito después de que una operación FETCH haya ocurrido primero en la transacción.
Tenga en cuenta que JDBC define los cursores como retenibles por defecto. Esto se hace porque JDBC también activa la confirmación automática por defecto.
Declaraciones de actualización / eliminación posicionadas
Los cursores no solo se pueden usar para obtener datos del DBMS en una aplicación, sino también para identificar una fila en una tabla para actualizarla o eliminarla. El estándar SQL: 2003 define las sentencias SQL de actualización posicionada y eliminación posicionada para ese propósito. Tales declaraciones no usan una cláusula WHERE regular con predicados. En cambio, un cursor identifica la fila. El cursor debe estar abierto y ya posicionado en una fila mediante FETCHdeclaración.
UPDATE table_name SET ... WHERE CURRENT OF cursor_name
DELETE FROM table_name WHERE CURRENT OF cursor_name
El cursor debe operar en un conjunto de resultados actualizable para ejecutar con éxito una declaración de actualización o eliminación posicionada. De lo contrario, el DBMS no sabría cómo aplicar los cambios de datos a las tablas subyacentes a las que se hace referencia en el cursor.
Cursores en transacciones distribuidas
El uso de cursores en transacciones distribuidas ( entornos X / Open XA ), que se controlan mediante un monitor de transacciones, no es diferente de los cursores en transacciones no distribuidas.
Sin embargo, hay que prestar atención al usar cursores sostenibles . Las conexiones pueden ser utilizadas por diferentes aplicaciones. Por lo tanto, una vez finalizada y confirmada una transacción, una transacción posterior (que se ejecuta en una aplicación diferente) podría heredar los cursores retenibles existentes. Por lo tanto, un desarrollador de aplicaciones debe estar al tanto de esa situación.
Cursores en XQuery
El lenguaje XQuery permite la creación de cursores utilizando la función subsecuencia () .
El formato es:
let $displayed-sequence := subsequence($result, $start, $item-count)
Donde $ result es el resultado de la XQuery inicial, $ start es el número de artículo para comenzar y $ item-count es el número de artículos para devolver.
De manera equivalente, esto también se puede hacer usando un predicado:
let $displayed-sequence := $result[$start to $end]
¿Dónde $endestá la secuencia final?
Para obtener ejemplos completos, consulte XQuery / Searching, Paging and Sorting # Paging en Wikilibros.
Desventajas de los cursores
La siguiente información puede variar según el sistema de base de datos específico.
Obtener una fila del cursor puede resultar en un viaje de ida y vuelta de la red cada vez. Esto usa mucho más ancho de banda de red del que normalmente se necesitaría para la ejecución de una sola instrucción SQL como DELETE. Los viajes de ida y vuelta repetidos de la red pueden reducir considerablemente la velocidad de la operación con el cursor. Algunos DBMS intentan reducir este efecto mediante la recuperación de bloques. La recuperación de bloques implica que se envían varias filas juntas desde el servidor al cliente. El cliente almacena un bloque completo de filas en un búfer local y recupera las filas desde allí hasta que se agota ese búfer.
Los cursores asignan recursos en el servidor, como bloqueos , paquetes, procesos y almacenamiento temporal. Por ejemplo, Microsoft SQL Server implementa cursores creando una tabla temporal y llenándola con el conjunto de resultados de la consulta. Si un cursor no está correctamente cerrado ( desasignado ), los recursos no se liberarán hasta que se cierre la sesión SQL (conexión). Este desperdicio de recursos en el servidor puede provocar degradaciones y fallas en el rendimiento.
Ejemplo
TABLA DE EMPLEADOS
SQL> desc EMPLOYEES_DETAILS;
Name Null? Type
----------------------------------------- -------- --------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(30)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SAMPLE CURSOR KNOWN AS EE
CREATE OR REPLACE
PROCEDURE EE AS
BEGIN
DECLARE
v_employeeID EMPLOYEES_DETAILS.EMPLOYEE_ID%TYPE;
v_FirstName EMPLOYEES_DETAILS.FIRST_NAME%TYPE;
v_LASTName EMPLOYEES_DETAILS.LAST_NAME%TYPE;
v_JOB_ID EMPLOYEES_DETAILS.JOB_ID%TYPE:= 'IT_PROG';
Cursor c_EMPLOYEES_DETAILS IS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES_DETAILS
WHERE JOB_ID ='v_JOB_ID';
BEGIN
OPEN c_EMPLOYEES_DETAILS;
LOOP
FETCH c_EMPLOYEES_DETAILS INTO v_employeeID,v_FirstName,v_LASTName;
DBMS_OUTPUT.put_line(v_employeeID);
DBMS_OUTPUT.put_line(v_FirstName);
DBMS_OUTPUT.put_line(v_LASTName);
EXIT WHEN c_EMPLOYEES_DETAILS%NOTFOUND;
END LOOP;
CLOSE c_EMPLOYEES_DETAILS;
END;
END;
Ver también
Referencias
- Christopher J. Fecha : Base de datos en profundidad , O'Reilly & Associates, ISBN 0-596-10012-4
- Thomas M. Connolly , Carolyn E. Begg : Sistemas de bases de datos , Addison-Wesley, ISBN 0-321-21025-5
- Ramiz Elmasri , Shamkant B. Navathe : Fundamentos de los sistemas de bases de datos , Addison-Wesley, ISBN 0-201-54263-3
- Neil Matthew , Richard Stones : bases de datos principiantes con PostgreSQL: de principiante a profesional , Apress, ISBN 1-59059-478-9
- Thomas Kyte : Experto uno a uno: Oracle , Apress, ISBN 1-59059-525-4
- Kevin Loney: Oracle Database 10g: The Complete Reference , Oracle Press, ISBN 0-07-225351-7