SQL Server cursor principles and how to use them

During database development, when you retrieve data that is only a single record, the transaction statement code that you write tends to use the Select INSERT statement. But we often encounter situations where one record is read from one result set to another. So how do we solve this problem? Cursors provide us with a very good solution.

1.1 Advantages of cursors and cursors

In a database, cursors are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. cursors are always associated with a T_SQL selection statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record. when you decide to process a result set, you must declare a cursor that points to the result set. If you have ever written a program that handles a file in C, the cursor is just like the file handle you get when you open the file, and the file handle can represent the file as long as the file opens successfully. For cursors, the rationale is the same. Visible cursors are capable of processing the result set from the underlying table in a manner similar to that of a traditional program reading a flat file, rendering the data in the table to the program as a flat file.

We know that relational database management systems are essentially set-oriented, and in MS SQL SERVER there is no representation of a single record in a table, unless a WHERE clause is used to restrict only one record from being selected. So we have to use cursors to handle single-record-oriented data processing.

Thus, cursors allow the application to perform the same or different operations on each row of the rows result set returned by the query statement Select, rather than one operation for the entire result set at a time, and it also provides the ability to delete or update the data in the table based on the cursor position; It is the cursor that is a collection-oriented database management system and the line-oriented programming of the two linked together, so that two data processing methods can communicate.

1.2 Cursor Types

MS SQL Server supports three types of cursors:transact_sql cursors ,API server Cursors , and client cursors .

(1) Transact_sql cursors

TRANSACT_SQL cursors are defined by the DECLARE CURSOR syntax and are primarily used in transact_sql scripts, stored procedures, and triggers. Transact_sql cursors are primarily used on the server and are managed by TRANSACT_SQL statements sent from the client to the server or Transact_sql in batches, stored procedures, triggers. Transact_sql cursors do not support extracting data blocks or multiple rows of data.

(2) API cursors

API cursors support the use of cursor functions in OLE DB, ODBC, and Db_library, primarily on servers. Each time the client application invokes an API cursor function, the MS SQL SEVER OLE DB provider, ODBC Drive, or db_library dynamic-link library (DLL) will route these client requests to the server to process the API cursors.

(3) Customer cursors

A client cursor is used primarily when a result set is cached on the client. In a customer cursor, there is a default result set that 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 batches, client cursors are often used only as a helper for server cursors. Because in general, server cursors can support the vast majority of cursor operations.

Because API cursors and Transact-SQL cursors are used on the server side, they are called server cursors, also known as background cursors, and client cursors are called foreground cursors. In this chapter we mainly describe the 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 @[email protected]+1

End

1.3 Cursor operation

There are four basic steps to using cursors: declaring cursors , opening cursors , extracting data , and closing cursors .

declaring cursors

As with other types of variables, it should be declared first before a cursor is used. The declaration of a cursor consists of two parts: 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 number and balance of a customer in Beijing, you can write the following code:

DECLARE Customercursor CURSOR for

SELECT acct_no,name,balance

From customer

WHERE province= "Beijing";

One thing to note in a cursor declaration is that, like the declarations of other variables, this piece of code that declares a cursor is not executed, and you cannot set the breakpoint at debug to this line of code, or use the If ... END If statement to declare two cursors of the same name, such as the following code is wrong.

IF is_prov= "Beijing" then

DECLARE Customercursor CURSOR for

SELECT acct_no,name,balance

From customer

WHERE province= "Beijing";

ELSE

DECLARE Customercursor CURSOR for

SELECT acct_no,name,balance

From customer

WHERE province〈〉 "Beijing";

END IF

Open cursor

After you declare a cursor, you must open it before you make other operations. Opening a cursor is the execution of a SQL statement associated with it, such as opening a cursor declared in the previous example, we just need to type:

OPEN Customercursor;

Because opening a cursor is an operation on a database for some SQL SELECT, it will take some time, depending on the system performance you use and the complexity of the statement. If it takes a long time to execute, consider changing the mouse displayed on the screen to hourglass.

Extracting data

When you open a cursor with an open statement and execute a query in the database, you cannot immediately take advantage of the data in the query result set. You must use a FETCH statement to obtain the data . A fetch statement can put a record in the programmer-specified variable at a time . in fact, the FETCH statement is the core used by the cursor . In DataWindow and Datastore, after executing the retrieve () function, all the results of the query can be obtained, and using cursors, we can only get the query results on a record-by-article.

Once a cursor has been declared and opened, we can put the data into any variable. In a FETCH statement, you can specify the name of the cursor and the name of the target variable. The following example:

FETCH Custmercur-sor

into: ls_acct_no,

: Ls_name,

: ll_balance;

Syntactically speaking, the above is a valid statement to fetch data, but in general we should include other parts with cursors. As we mentioned earlier, cursors can only fetch one record from the background database at a time, and in most cases we want to start extracting from the first record in the database until the end. So we usually put the cursor to extract the data of the statement in a loop body, until the result set of all the data extracted, jump out of the loop circle . by detecting SQLCA. the value of the Sql-code to see if the last fetch statement was successful. In general, when the Sqlcode value of 0 o'clock indicates that everything is OK, 100 means that the end of the result set has been taken, and the other values indicate that the operation is out of the question 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 many structures in the loop body, and the most common one is mentioned here. Some programmers prefer to put a fetch statement in front of the loop body, put another fetch statement in the loop, and detect if Sqlca.sqlcode is 100 (see example below). But to do so, the maintenance needs to modify two fetch statements at the same time, a little more trouble.

Close Cursors

At the end of the cursor operation, do not forget to close the cursor, which is a good programming practice to allow the system to release the resources that the cursor occupies. The statement to close the cursor is simple:

CLOSE Customercursor;

Using the WHERE clause

We can dynamically define the parameters of the WHERE clause in the cursor, for example, in this case we have directly defined the query province is Beijing's record, but perhaps in the application we want to use a drop-down list box, by the user to select the province to query, what should we do?

As we mentioned earlier, the function of the Declare statement is simply to define a cursor in which the cursor is actually executed in the open statement. With this in view, we can easily implement this function by adding variables as arguments in the DECLARE WHERE clause as follows:

DECLARE Customercursor CURSOR for

Selcect acct_no,name,balance

From customer

WHERE province=:ls_province;

Define the value of the Ls_province

OPEN Customercursor;

Types of Cursors

As with other variables, we can also define the type of access for the cursor: Global, shared, instance, or local, and the naming conventions for cursor variables are recommended for other variables as well.

-- declaring cursors

Declare my_cursor cursor keyset for SELECT * FROM info

-- Delete cursor resource

Deallocate my_cursor

-- Open the cursor, which is valid until the cursor is closed or deleted

Open My_cursor

-- Close cursor

Close My_cursor

-- declaring local variables

declare @id int, @name varchar, @address varchar (20)

-- Locate the record at the specified location

Fetch absolute 56488 from My_cursor to @id, @name, @address

Select @id as ID, @name as name, @address as address

--position records relative to the current record

Fetch relative-88 from My_cursor to @id, @name, @address

Select @id as ID, @name as name, @address as address

-- Navigate to the previous entry in the current record

Fetch Prior from My_cursor to @id, @name, @address

Select @id as ID, @name as name, @address as address

-- navigates to the current record after a

FETCH NEXT from My_cursor to @id, @name, @address

Select @id as ID, @name as name, @address as address

-- position to first record

Fetch first from my_cursor to @id, @name, @address

Select @id as ID, @name as name, @address as address

-- position to tail record

Fetch last from My_cursor into @id, @name, @address

Select @id as ID, @name as name, @address as address

Example :

Use Database1

DECLARE my_cursor cursor scroll dynamic

/**//*Scroll indicates that the cursor pointer can be moved (otherwise only forward), anddynamic means 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 to @pname

While(@ @fetch_status=0)

Begin

print ' Product Name: ' + @pname

FETCH NEXT from My_cursor to @pname

End

Fetch first from My_cursor to @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

Advanced Tips for 1.4 cursors

Although the current SQL statement-based background database supports roughly the same language, there are some differences in support for cursors, such as support for scrolling cursors. The so-called scrolling cursor is where the programmer can specify that the cursor scrolls in either direction. In Informix, you can even roll a cursor to the beginning or end of a result set, using the same statements as fetch First,fetch last, fetch prior, and fetch NEXT. When a programmer uses a FETCH statement, it defaults to fetch NEXT. Because scrolling is implemented in the background of the database, scrolling cursors provide great convenience for user programming.

Another difference to the cursor support is the modifiable cursor. The use of these cursors refers to read-only cursors, whereas databases such as Oracle, Sybase, and others support modifiable cursors. With such a database, you can modify or delete the row where the current cursor is located. For example, to modify the balance of the user of the row of the current cursor, we can do the following:

UPDATE Customer

SET balance=1000

WHERE Current of customercursor;

The operation to delete the current row is as follows:

DELETE from Customer

WHERE Current of customercursor;

However, if the database you are currently using is Sybase, you need to modify the parameters of the database and set the cursor modifiable value to 1 to perform this operation. This assignment is available before and after connecting to the database.

SQLCA. dbparm= "Cursor update=1"

SQL Server cursor principles and how to use them