Shulou(Shulou.com)06/01 Report--

Vernier concept

We often encounter this situation in database operations, that is, we read a record one by one from a result set. So how to solve this problem? Cursors provide us with an excellent solution.

Cursor is a data buffer set up by the system for users, which stores the execution results of SQL statements.

Each cursor area has a name. Users can use SQL statements to get records from cursors one by one and assign them to the main variable for further processing by the main language. 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 SQL query 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.

The use of cursors is generally like this, personal understanding: define cursors-- > open cursors-- > Fetch next from into-- > where @ @ fech_status=0 (this similar C language pointer)-- > Fetch next from into-- > end-- > close cursors (close cursor name)-- > Deallocate cursors

Specific examples:

Define cursor Test_Cursor

Declare @ paraA nvarchar (50)-- define parameter declare @ paraB nvarchar (50)-- define parameter declare @ paraC nvarchar (50)-- define parameter declare Test_Cursor cursor local for-- define cursor

Open the cursor and define the result set to be the cursor

-- equivalent to the contents of the table to be traversed select tableA_columnA, tableA_columnB, tableA_columnC from tableA-- is equivalent to pointing a pointer to this header, to the first record of the data table set, open Test_Cursor fetch next from Test_Cursor into@paraA,@paraB,@paraCwhile @ @ fetch_status=0

Loop through the contents of the dataset, doing one by one

Begin insert into tableB (tableB_columnA, tableB_columnB, tableB_columnC) values (@ paraA,@paraB,@paraC) fetch next from Test_Cursor into@paraA,@paraB,@paraCend

Close and close reclaimed memory

Close Test_Cursordeallocate Test_Cursor

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.