SQL cursor principle and usage
During database development, when the data you retrieve is only one record, the transaction statements you write Code Select 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. A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records. A cursor is always associated with a t_ SQL selection statement because it is a result set (it can be zero or one or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record. When processing a result set, a cursor pointing to the result set must be declared. If you have written in C to process files Program The 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 you use the 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 perform the same or different operations on each row in the row result set returned by the SELECT statement, rather than 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. In addition, it is the combination of the cursor as a collection-oriented database management system and row-oriented programming, enable communication between the two data processing methods. 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 is mainly used in transact_ SQL scripts, stored procedures, and triggers. Transact_ SQL cursors are mainly used on servers. transact_ SQL statements sent from clients to servers or transact_ SQL statements in batch processing, stored procedures, and triggers are managed. 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 client application calls the 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 cursors. Because server cursors do not support all Transact-SQL statements or batch processing, 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 infoselect * from info -- clear all records
Truncate table infodeclare @ I int
Set @ I = 1
While @ I <1000000
Begin
Insert into info values ('Justin '+ STR (@ I), 'shenzhen' + STR (@ I ))
Set @ I = @ I + 1
End1.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 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. A fetch statement can put a record into a variable specified by the programmer at a time. In fact, the fetch statement is the core of the cursor. In datawindow and datastore, after the retrieve () function is executed, 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;
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, a cursor can only retrieve one record from the background database at a time. In most cases, what we want to do is to extract the first record from the database, until the end. Therefore, we usually need to put the statements for data extraction from the cursor in a loop until all the data in the result set is extracted and jumps out of the loop. 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, it indicates that everything is normal. 100 indicates that it has reached the end of the result set, and other values indicate that the operation is faulty, so that 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 productopen 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_cursor1.4 advanced cursor skills although SQL-based background databases currently support roughly the same language, there are some differences in the support for cursors, such as support for Rolling cursors. The so-called scroll cursor means that the programmer can specify the cursor to scroll forward 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, 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.
