Principles and usage of SQL Server cursor-reprint

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. 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 filesProgramThe 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 relational databases Management System Essentially, it is 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 a cursor to process a single record.

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 precisely because the cursor acts as a collection-oriented database. Management System and line-oriented Program Design The two data processing methods can communicate with each other.

1.2 cursor type

Ms SQL Server supports three types of cursors:Transact_ SQL cursor,API Server CursorAnd Customer Cursor.

(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 tags. 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 in Server The client cursor is also called the 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 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 useFetch 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 backend database at a time. In most cases, what we want to do is to extract the first record from the database until it ends. Therefore, we usually need to put the statement for data extraction by the cursor in a loop until all 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 has encountered a problem.QuestionIn this way, you 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 love it.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 (see the following example). 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 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.

--Declared 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 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 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

FetchPriorFrom 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, the cursor can only be moved forward), and 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

Although the SQL statement-based background database currently supports roughly the same language, the support for cursors is somewhat different, for example, the 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

WhereCurrentCustomercursor;

Delete the current row as follows:

Delete from customer

WhereCurrentCustomercursor;

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"