Cursor (CURSOR)

Principle: The cursor is to extract data according to the specified requirements of the corresponding data set, and then data processing.

The concept of cursors

1.1 Cursors (cursor)

It enables the user to access the result set returned by SQL Server on a row-by-line basis.

One of the main reasons for using cursors is to convert the set operation to a single record processing mode.

When data is retrieved from a database in SQL language, the result is placed in an area of memory, and the result is often a collection of multiple records.

The cursor mechanism allows users to access these records row-by-line within SQL Server, displaying and processing these records at the user's own will.

1.2SQL language and main language have different data processing methods

(1). The SQL language is set-oriented, An SQL statement can, in principle, produce or process multiple records (2). The main language is record-oriented, a set of primary variables can only hold one record at a time only using the main variable does not fully meet the requirements of the SQL statement output data to the application, Embedded SQL introduced the concept of cursors to coordinate the two different processing methods. A cursor is a data buffer opened by the system for the user, which holds the results of the execution of the SQL statement, and each cursor area has a name.

Advantages of 1.3 Cursors

The following advantages are obtained from the cursor definition, which makes the cursor play an important role in the actual application:

1) allows the program to perform the same or different operations on each row in the rowset returned by the query statement select, rather than performing the same operation on the entire rowset.

2) provides the ability to delete and update rows in a table based on the cursor location.

3) cursors actually serve as a bridge between the set-oriented database management system (RDBMS) and the line-oriented program design, which enables the two processing methods to communicate through cursors.

Use of 1.4 Cursors

Speaking of the advantages of this multi-cursor, we are now going to reveal the mysterious veil of the cursor in person.

The order in which cursors are used: reputation cursors, open cursors, read data, close cursors, delete cursors.

exec SQL DECLARE cursorname CURSOR forexec sql OPEN cursornameexec sql FETCH cursorname into EXEC SQL CLOSE Cursorname types of SQL statements that do not have cursors:1. Descriptive statement declare2. Data definition statement CREATE,ALTER,DROP3. Data Control Statement grant,revoke4. Query result is a single record SELECT statement Unique5 appears in the where query. Non-current form of UPDATE statement 6. Non-current form of DELETE statement 7.INSERT statement    One, descriptive statement is designed to describe the main variable in embedded SQL, Sqlca and other settings. Describes the main variable 1. EXEC SQL BEGIN DECLARE section;2.exec SQL END DECLARE Section; The two statements must be paired, equal to one parenthesis, and the middle of the two statements is the description of the main variable. Description Sqlca3.exec SQL INCLUDE SQLCA Two, data definition statements For example: Creating a "student" table studentexec SQL CREATE TABLE Student (Sno CHAR (5) Not NULL Unique,sname char (+), Ssex char (1), Sage int,sdept char (15)), the primary variable is not allowed in the data definition statement. Third, the data control statements for example: Grant the query Student table permissions to the user u1exec SQL GRANT SELECT on the table Student to U1; four, the query result is a single record of the SELECT statement statement format: EXEC SQL SELECT [all| distinct]< target expression >[,< target expression; into < main variable >[< indicator variable >][,< main variable >[< indicator variable;]] ... From < table name or view name >[,< table name or view name;] ... [WHERE < conditional expressions;] [GROUP by < column name 1>[having< conditional expression;]] [ORDER by < column name 2>[asc| DESC]]; For the expansion of the interactive SELECT statement, an INTO clause is introduced, and the qualifying records found from the database are placed in the main variables indicated in the INTO clause. Precautions for use: 1. Use the main variable, which can only appear in the INTO clause, the conditional expression of the WHERE clause, and the conditional expression of the having phrase 2. The indicator variable is used to indicate that the variable can only be used in the INTO clause. If the main variable in the INTO clause is followed by aIndicates a variable, when the query concludes that a data item is NULL, the system automatically resets the indicator variable following the corresponding primary variable to a negative value, but does not perform an assignment to the primary variable, that is, the primary variable still retains the value before the SQL statement execution. When the value of the indicator variable is found to be negative, the value of the primary variable should be considered null regardless of the primary variable value. 3. The result of the query is an empty set. If there are no records in the database that meet the criteria, that is, if the query result is empty, the DBMS will set the value of Sqlcode to 1004. The query results are multiple records, the program error occurs, and the DBMS returns an error message in the SQLCA. The SELECT statement should use cursors in all cases, from the standpoint of increasing the data independence of the application. The UPDATE statement in non-current form uses the main variable: appears in the SET clause, WHERE clause. Use indicator variable: appears in the SET clause. A non-current form of an UPDATE statement can manipulate multiple tuples.        the DELETE statement in non-current form uses the main variable: appears in the WHERE clause. A non-current form of DELETE statement can manipulate multiple tuples. The INSERT statement in non-current form uses the main variable: appears in the values clause using an INDICATOR variable: INSERT statements that appear in the VALUES clause non-current form can enter only one tuple at a time. SQL statements that must use Cursors           1. The query result is a SELECT statement for multiple records. UPDATE statement in 2.CURRENT form. Delete statement in 3.CURRENT form.                        one, the query results for multiple record SELECT statements using the cursor step: 1. Description cursor 2. Open cursor 3. Move the cursor pointer, and then take the current record 4. Close cursor   1. Description CURSOR: Use the DECLARE statement. Statement format: EXEC SQL DECLARE < cursor name > cursor for <select statement >; feature: A descriptive statement that the DBMS does not perform the query operation specified by SELECT. 2. Open cursor: Use OPEN statement, statement format: EXEC SQL open < cursor name >; function: Open cursor actually executes the corresponding SELECT statement, takes all records satisfying the query condition from the specified table into the buffer, the cursor is active, The pointer points to the first record in the query result set. 3. Move the cursor pointer, and then take the current record. Using the FETCH statement, statement format: EXEC SQL FETCH [[next| prior| First| Last]from] < cursor name > into < main variable >[< indicator variable >][,< main variable >[< indicator variable;]] ... ; function: Specifies the direction to push the cursor pointer and then takes the current record in the buffer out to the main variable for further processing by the master language; next| prior| First| Last: Specifies how the cursor pointer is pushed. Next: Push forward a record. PRIOR: Rewind a record backward. First: Push to record one. Last: Push to the final record. The default value is next. Description:   1. The primary variable must have a one by one correspondence with the target column expression in the SELECT statement.   2.fetch statements are typically used in a looping structure and are processed by iterating through the rows in the result set by executing a FETCH statement.   3. To further facilitate user processing of data, now some of the correlation coefficientsThe Library management system expands the FETCH statement to allow the user to move the cursor pointer in any direction in any step. 4. Close cursor: Use the CLOSE statement, statement format: EXEC SQL close < cursor name >; function: Closes the cursor, freeing the buffer used by the result set and other resources. Note: When the cursor is closed, it is no longer associated with the original query result set, and the closed cursor can be opened again, associated with the new query results. Second, the current form of the UPDATE statement and DELETE statement: is a collection-oriented operation; modify or delete all records that meet the criteria at once. If you want to modify or delete only one of these records: Use a SELECT statement with a cursor to isolate all records that satisfy the condition, and further identify the records to be modified or deleted, and modify or delete them using the current form of the UPDATE statement and the DELETE statement statement. Steps: (1). DECLARE, indicating the cursor (2). Open, the cursor is opened, and all records satisfying the query criteria are taken from the specified table to the buffer (3). FETCH, advances the cursor pointer, and takes the current record out of the buffer and sends it to the main variable (4). Check if the record is a record to be modified or deleted, and then process it (5). Repeat steps (3) and (4) to determine and process the rows of the result set (6). Close, closes the cursor, freeing the buffer and other resources occupied by the result set. Describe the cursor for the UPDATE statement: Use the Declare statement with the for update of < column name > phrase. Statement formats: EXEC SQL DECLARE < cursor names > cursor for <select statements > for update of < column names >;for UPDATE of < column names > phrases are used to indicate the number of retrieved It is modifiable on the specified column so that the DBMS can control concurrency. Describes the cursor for the DELETE statement, using the Declare statement with the for delete phrase. Statement format: EXEC SQL DECLARE < cursor name > cursor for <select statement > for UPDATE; The for update phrase prompts the DBMS for concurrency control.   Modify or delete the current record: statement format: <update statement > WHERE CURRENT of < cursor name >;<delete statement > The WHERE CURRENT of < cursor name >. WHERE Current of < cursor name > clause indicates that the most recent fetch in the cursor was modified or deletedThe record. You cannot use the current form of an UPDATE statement and a DELETE statement when the SELECT statement in the cursor definition has a union or ORDER BY clause, or if the SELECT statement is equivalent to defining a non-updatable view.

Partly transferred from: http://blog.sina.com.cn/s/blog_9c618eb9010158b8.html

Cursor (CURSOR)