Cursor (databases)
≈ 5 min read
Names in other languages
No names in other languages are held for this entry yet.
Article bodies are English and are not machine-translated. A name in another language is not a translation of the article.
In computer science, a database cursor is a mechanism that enables traversal over the records in a database. Cursors facilitate processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator. Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the sequential processing of rows in a result set. In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, a SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
In computer science, a database cursor is a mechanism that enables traversal over the records in a database. Cursors facilitate processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator. Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the sequential processing of rows in a result set. In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, a SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
Usage
To use cursors in SQL procedures, you need to do the following: Declare a cursor that defines a result set Open the cursor to establish the result set Fetch the data into local variables as needed from the cursor, one row at a time Close the cursor when done To work with cursors you must use the following SQL statements This section introduces the ways the SQL:2003 standard defines how to use cursors in applications in embedded SQL. Not all application bindings for relational database systems adhere to that standard, and some (such as CLI or JDBC) use a different interface. A programmer makes a cursor known to the DBMS by using a DECLARE ... CURSOR statement and assigning the cursor a (compulsory) name: DECLARE cursor_name CURSOR IS SELECT ... FROM ... Before code can access the data, it must open the cursor with the OPEN statement. Directly following a successful opening, the cursor is positioned before the first row in the result set. OPEN cursor_name Applications position cursors on a specific row in the result set with the FETCH statement. A fetch operation transfers the data of the row into the application. FETCH cursor_name INTO ... Once an application has processed all available rows or the fetch operation is to be positioned on a non-existing row (compare scrollable cursors below), the DBMS returns a SQLSTATE '02000' (usually accompanied by an SQLCODE +100) to indicate the end of the result set. The final step involves closing the cursor using the CLOSE statement: CLOSE cursor_name After closing a cursor, a program can open it again, which implies that the DBMS re-evaluates the same query or a different query and builds a new result set. let $displayed-sequence := subsequence($result, $start, $item-count) Where $result is the result of the initial XQuery, $start is the item number to start and $item-count is the number of items to return. Equivalently this can also be done using a predicate: let $displayed-sequence := $result[$start to $end] Where $end is the end sequence. For complete examples see the .
Example
EMPLOYEES TABLE 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;
See also
Iterator
External links
Descriptions from Portland Pattern Repository Berkeley DB Reference Guide: Cursor operations PostgreSQL cursors MySQL's Cursor Documentation FirebirdSQL cursors documentation
Text derived from https://en.wikipedia.org/w/index.php?oldid=1271809520 under CC BY-SA 4.0; modifications have been made.