Usage and principle of cursor

During database development, when the data you retrieve is only one record, the transaction statements you writeCodeSelect Insert statements are usually used. However, we often encounter this situation, that is, reading a record from a result set one by one. How can this problem be solved? Cursors provide us with an excellent solution.
1.1 advantages of cursors and cursors

In databases, cursor is a very important concept. Cursors provide a flexible way to operate the data retrieved from a table. In essence, cursors are actually a combination of multiple data records.
The mechanism for extracting a record at a time in the result set. The cursor always corresponds to a t_ SQL statement.
The selection statement is associated because the cursor consists of the result set (which can be zero, one or multiple records retrieved by the relevant selection statement) and the cursor position pointing to a specific record in the result set. When you decide to process the result set
You must declare a cursor pointing to the result set. If C
The language used to process files.ProgramThe cursor is the same as the file handle you get when opening the file. If the file is opened successfully, the file handle can represent the file. For a cursor, the principle is the same
. The visible cursor can process the result set from the basic table in a similar way as the traditional program reads the flat file, so as to present the data in the table to the program in the form of a flat file.

We know that the relational database management system is actually set-oriented. In ms SQL Server, there is no way to describe a single record in a table unless where
Clause to limit that only one record is selected. Therefore, we must use the cursor to perform the operation? BR>
The cursor allows the application to select the query statement.
Each row in the returned row result set performs the same or different operations, instead of performing the same operation on the entire result set at a time; it also provides the ability to delete or update table data based on the cursor position. Moreover,
It is precisely because the cursor links the collection-oriented database management system with row-oriented programming, so that the two data processing methods can be communicated.

1.2 cursor type
Ms SQL Server supports three types of cursors: transact_ SQL, API server, and customer cursors.
(1) transact_ SQL cursor

Transact_ SQL cursor is defined by the declare cursor syntax and mainly used in transact_ SQL
Scripts, stored procedures, and triggers. Transact_ SQL cursor is mainly used on the server and is sent from the client to the server's transact_ SQL
Statement or transact_ SQL in the batch processing, stored procedure, and trigger for management. Transact_ SQL cursor does not support extracting data blocks or multiple rows.
(2) API cursor

API cursors support using cursors in ole db, ODBC, and db_library, and are mainly used on servers. Each time a client application calls an API
Cursor function, ms SQL Sever's OLE DB provider, ODBC drive, or db_library dynamic link library (DLL)
All these client requests are sent to the server to process the API cursor.
(3) Customer cursor

Client cursors are used only when result sets are cached on the client. In a client cursor, a default result set is used to cache the entire result set on the client. Client cursors only support static cursors rather than Dynamic Games
. Because server cursors do not support all Transact-SQL statements
Therefore, client cursors are often used only as an aid for server cursors. Generally, server cursors support the vast majority of cursors.
Because API cursor and transact-SQL cursor are used on the server side, they are also called server cursor and background cursor, while client cursor is called foreground cursor. In this chapter, we will focus on server (background) cursors.

Select count (ID) from Info

Select * from Info

-- Clear all records
Truncate table info

Declare @ I int
Set @ I = 1
While @ I <1000000
Begin
Insert into info values ('Justin '+ STR (@ I), 'shenzhen' + STR (@ I ))
Set @ I = @ I + 1
End

1.3 cursor operation
There are four basic steps to use a cursor: declare the cursor, open the cursor, extract data, and close the cursor.
Declared cursor
Like other types of variables, declare a cursor before using it. The declaration of a cursor includes the name of the cursor and the SQL statement used by the cursor. To declare a cursor called cus-tomercursor to query the name, account, and balance of a customer whose address is located in Beijing, you can write the following code:
Declare mermercursor cursor
Select acct_no, name, balance
From customer
Where province = "Beijing ";
In the Declaration of the cursor, it is worth noting that, like the declaration of other variables, the code line that declares the cursor is not executed, you cannot set the breakpoint for debugging on this line of code or use if... the end if statement is used to declare two cursors with the same name. The following code is incorrect.
If is_prov = "Beijing" then
Declare mermercursor cursor
Select acct_no, name, balance
From customer
Where province = "Beijing ";
Else
Declare mermercursor cursor
Select acct_no, name, balance
From customer
Where province <> "Beijing ";
End if
Open cursor
The cursor must be opened before other operations are performed. To open a cursor is to execute an SQL statement related to it. For example, to open a cursor declared in the previous example, you only need to type:
Open customercursor;
Opening a cursor is a SQL Select Operation on the database. It takes a while, depending on the system performance and the complexity of the statement. If the execution takes a long time, you can change the mouse displayed on the screen to Hourglass.
Extract data
When
After a cursor is opened with an open statement and a query is executed in the database, you cannot use the data in the query result set immediately. You must use the fetch statement to obtain data. One fetch statement can be used once.
To put a record into the variable specified by the programmer. In fact, the fetch statement is the core of the cursor. In datawindow and datastore, The retrieve
() After the function, all the query results can be obtained. With the cursor, we can only obtain the query results one by one.
After a cursor has been declared and opened, we can put the data into any variable. In the fetch statement, you can specify the cursor name and the target variable name. For example:
Fetch custmercur-sor
Into: ls_acct_no,
: Ls_name,
: Ll_balance;
Slave
In terms of syntax, the above is a legal statement for getting data, but generally we should use a cursor to include other parts. As we mentioned earlier, the cursor can only get one entry from the background database at a time.
In most cases, we want to extract from the first record in the database until the end. Therefore, we usually need to put the statement for the cursor to extract data in a loop
If all the data in the set is extracted, the circle jumps out. By checking the value of the SQLCA.SQL-CODE, you can know whether the last fetch statement was successful. Generally, when the sqlcode value is
0 indicates that everything is normal, 100 indicates that the result set has been obtained at the end, and other values indicate that the operation is faulty, so we can write the following code:
Lb_continue = true
Ll_total = 0
Do While lb_continue
Fetch customercur-sor
Into: ls_acct_no,
: Ls_name,
: Ll_balance;
If sqlca. sqlcode = 0 then
Ll_total + = ll_balance
Else
Lb_continue = false
End if
Loop
There are multiple types of cyclic bodies, which are the most common. Some programmers like to place a fetch statement in front of the loop body, place another fetch statement in the loop body, and check whether sqlca. sqlcode is 100. However, in this case, you need to modify two fetch statements at the same time during maintenance, which is a little troublesome.
Close cursor
Do not forget to close the cursor at the end of the cursor operation. This is a good programming habit, so that the system releases the resources occupied by the cursor. The statement to close the cursor is simple:
Close customercursor;
Use the WHERE clause
We can dynamically define the parameters of the WHERE clause in the cursor. For example, in this example, we directly define that the query province is a record of Beijing, however, we may use a drop-down list box in the application to select the province to be queried. What should we do?
As we mentioned earlier, the declare statement only defines a cursor, which is actually executed in the open statement. After understanding this, we can easily implement this function. add variables to the declare where clause as parameters, as shown below:
Declare mermercursor cursor
Selcect acct_no, name, balance
From customer
Where province =: ls_province;
Define the value of ls_province
Open customercursor;
Cursor type
Like other variables, we can also define the access type of the cursor: Global, shared, instance, or local. We recommend that you use the same naming conventions as other variables.
-- Declare a cursor
Declare my_cursor cursor keyset for select * from Info
-- Delete a cursor Resource
Deallocate my_cursor

-- Open the cursor, which is valid before the cursor is closed or deleted
Open my_cursor
-- Close the cursor
Close my_cursor

-- Declare local variables
Declare @ ID int, @ name varchar (20), @ address varchar (20)
-- Locate the record at the specified position
Fetch absolute 56488 from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the relative position record of the current record
Fetch relative-88 from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the previous record
Fetch prior from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the last record
Fetch next from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the first record
Fetch first from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate to the end record
Fetch last from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address

Instance:
Use database1
Declare my_cursor cursor scroll dynamic
/** // * Scroll indicates that the cursor pointer can be moved freely (otherwise, only forward is allowed). Dynamic indicates that the cursor can be read and written (otherwise, the cursor is read-only )*/
For
Select productname from product

Open my_cursor
Declare @ pname sysname
Fetch next from my_cursor into @ pname
While (@ fetch_status = 0)
Begin
Print 'product name: '+ @ pname
Fetch next from my_cursor into @ pname
End
Fetch first from my_cursor into @ pname
Print @ pname
/** // * Update product set productname = 'zzg' where current of my_cursor */
/** // * Delete from product where current of my_cursor */
Close my_cursor
Deallocate my_cursor

1.4 advanced cursor skills

Do
Currently, SQL statements-based background databases support roughly the same language, but there are some differences in the support for cursors, such as the support for Rolling cursors. The so-called scroll cursor means that the programmer can specify the cursor direction
Scroll in any direction. For example, in Informix, you can even roll the cursor to the beginning or end of the result set. The statements used are fetch first and fetch.
Last, fetch prior, and fetch next. When a programmer uses a fetch statement, the default value is fetch.
Next. Since the scroll is implemented in the database background, the scroll cursor provides great convenience for User Programming.
The other difference supported by the cursor is that the cursor can be modified. The usage of the cursor above refers to the read-only cursor, while other databases such as Oracle and Sybase support the modification of the cursor. With such a database, you can modify or delete the row where the current cursor is located. For example, to modify the user balance in the row where the current cursor is located, perform the following operations:
Update customer
Set balance = 1000
Where current of mermercursor;
Delete the current row as follows:
Delete from customer
Where current of mermercursor;
However, if you are currently using Sybase, you must modify the database parameters and set the value of the cursor to 1 to perform the preceding operations. This assignment can be performed before and after the database is connected.
Sqlca. dbparm = "cursor update = 1"
The other content is dynamic cursor, that is, you can dynamically form the SELECT statement of the cursor during the running process. Similar to dynamic use of Embedded SQL in PowerBuilder, dynamicstagin-Garea and other data types are required, which is beyond the scope of this section.