Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the principle and usage of SQL cursors". In daily operation, I believe that many people have doubts about the principle and usage of SQL cursors. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the principle and usage of SQL cursors". Next, please follow the editor to study!

1.1 advantages of cursors and cursors

Cursors are a very important concept in a database. Cursors provide a flexible means of manipulating data retrieved from a table. In essence, cursors are actually a mechanism to extract one record at a time from a result set that includes multiple data records. Cursors are always associated with a T_SQL select statement because cursors consist of a result set (which can be zero, one, or multiple records retrieved by an associated selection statement) and a cursor location that points to a particular record in the result set. When you decide to process the result set, you must declare a cursor that points to the result set. If you have ever written a program to process a file in C, the cursor is just like the file handle you get when you open the file. As long as the file is opened successfully, the file handle can represent the file. The truth is the same for cursors. The visible cursor can process the result set from the underlying table in a similar way to the traditional program reading the flat file, thus presenting the data in the table to the program in the form of a flat file.

We know that relational database management systems are collection-oriented in nature, and there is no expression in MS SQL SERVER to describe a single record in a table, unless you use the where clause to restrict that only one record is selected. Therefore, we must use cursors to process data for a single record.

Thus, cursors allow applications to perform the same or different operations on each row of the row result set returned by the query statement select, rather than on the entire result set at once; it also provides the ability to delete or update data in the table based on the cursor location Moreover, it is the cursor that connects the collection-oriented database management system and line-oriented programming, so that the two data processing methods can communicate.

1.2 types of cursors

MS SQL SERVER supports three types of cursors: Transact_SQL cursors, API server cursors, and client cursors.

(1) Transact_SQL cursor

Transact_SQL cursors are defined by DECLARE CURSOR syntax and are mainly used in Transact_SQL scripts, stored procedures, and triggers. Transact_SQL cursors are mainly 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, and triggers. Transact_SQL cursors do not support extracting blocks or multiple rows of data.

(2) API cursor

API cursors support the use of cursor functions in OLE DB, ODBC and DB_library, mainly on servers. Each time a client application calls the API cursor function, the OLE DB provider of the MS SQL SEVER, the ODBC driver, or the dynamic link library (DLL) of the DB_library passes these client requests to the server to process the API cursor.

(3) customer cursor

Customer cursors are mainly used when caching result sets on the client. In the customer cursor, a default result set is used to cache the entire result set on the client. Customer cursors only support static cursors, not dynamic cursors. Because server cursors do not support all Transact-SQL statements or batches, client cursors are often used only as helpers to server cursors. Because in general, server cursors can support most cursor operations.

Because API cursors and Transact-SQL cursors are used on the server side, they are called server cursors and background cursors, while client cursors are called foreground cursors. In this chapter we focus on server (background) cursors.

Select count (id) from info

Select * from info

-- clear all records

Truncate table info

Declare @ I int

Set @ iTunes 1

While @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.