T-SQL cursor, t-SQL

T-SQL cursor, t-SQL

Ø cursor
A cursor can process a select result set or return a result after processing the record set.
Create PROCEDURE [dbo]. [region] @ DevName varchar (2000), @ starttime varchar (200) asbeginset nocount on; -- Set @ DevName = 'electric meter 1 (DTL1110321471), dianbiao $ DTL1110321471; electric Meter 2, dianbiao $ DTL1110321470; 'Clare @ pointID varchar (300) Declare @ pointName varchar (300) Create table # tempTable (pointName varchar (200), pointValue float) create table # tempTables (pointID varchar (2000), pointName varchar (2000) declare @ temp table (col varchar (2000) -- set @ str = replace (@ str, '|', ',') while (len (@ DevName)> 0) begininsert into @ tempselect left (@ DevName, charindex (';', @ DevName)-1) set @ DevName = stuff (@ DevName, 1, charindex (';', @ DevName), '') enddeclare @ name varchar (2000) declare @ value varchar (2000) declare cur cursor for select * from @ tempopen cur -- open the cursor fetch next from cur into @ name while @ FETCH_STATUS = 0 -- determine whether data is successfully obtained begin declare @ str1 varchar (2000) declare @ str2 varchar (2000) select @ str1 = left (@ name, charindex (',', @ name)-1) select @ str2 = substring (@ name, charindex (',', @ name) + 1, LEN (@ name) insert into # tempTables values (@ str2, @ str1) fetch next from cur into @ name endclose cur -- close the cursor deallocate curDeclare @ starttimes varchar (200) Declare @ endtime varchar (200) select @ starttimes = dateadd (day,-datepart (weekday, @ starttime) + 1, @ starttime) -- start time select @ endtime = dateadd (day, 7-datepart (weekday, @ starttime) + 2, @ starttime) -- End Time: Declare Cur cursor forselect pointID, pointName from # tempTablesopen Curfetch next from cur into @ pointID, @ pointName while @ FETCH_STATUS = 0 -- determine whether data is successfully obtained begincreate table # temp (inValue float) declare @ inValue floatDeclare @ SQL varchar (8000) set @ SQL = 'insert into # temp select round (SUM (incrementValue), 2) as incrementValue from TAB_DAY _ '+ @ pointID + 'where GetValueTime> ''' + @ starttimes + ''' and GetValueTime <''' + @ endtime + ''' exec (@ SQL) select @ inValue = inValue from # tempinsert into # tempTable (pointName, pointValue) values (@ pointName, @ inValue) drop table # temp fetch next from cur into @ pointID, @ pointName endclose cur -- close the cursor deallocate curselect * from # tempTable drop table # tempTablesEND
1. cursors are actually a mechanism for extracting one record at a time from the result set of multiple data records. The cursor can be completed:
# Allow locating specific rows in the result set
# Retrieving one or more rows of data from the current location of the result set
# Modify the current position of the result set
The cursor is used to operate the record set one by one, so it adds burden to the server. Generally, the cursor is used only when complicated result sets are operated. SQL Server 2005 has three types of cursors: T-SQL cursors, API cursors, and client cursors.
 
2. Basic cursor operations
The basic operations of a cursor include defining the cursor, opening the cursor, reading the cursor cyclically, closing the cursor, and deleting the cursor.
A. Define A cursor
Declare cursor_name -- cursor name cursor [local | global] -- global and local [forward only | scroll] -- cursor scroll mode [read_only | scroll_locks | optimistic] -- Read mode for select_statements -- query statement [for update | of column_name...] -- modify a field
Parameters:
Forward only | scroll: the previous parameter. The cursor can only be moved backward. The latter parameter can be moved freely.
Read_only: Read-Only cursor
Scroll_locks: the cursor is locked. When the cursor is read, the database locks the record so that the cursor can complete operations on the record.
Optimistic: this parameter does not lock the cursor. At this time, if the record is read into the cursor, the cursor will not be updated or deleted more
 
B. Open the cursor.
Open cursor_name;
After the cursor is opened, you can use the global variable @ cursor_rows to display the number of read records.
 
C. Search cursor
Fetch cursor_name;
The retrieval method is as follows:
Fetch first; read the first line
Fetch next; read the next row
Fetch prior; read the previous row
Fetch last; read the last row
Fetch absolute n; read a row
If n is a positive integer, the nth record is read.
If n is negative, the nth record is extracted from the reciprocal.
If n is, no records are read.
Fetch pelative n
If n is a positive integer, the nth record after the last read is read.
If n is negative, the nth record before the last read is read.
If n is, the last read record is read.
 
D. Close the cursor.
Close cursor_name;
 
E. delete a cursor
Deallocate cursor_name;
 
3. cursor operation example
-- Create a cursor declare cursor_stu cursor scroll forselect id, name, age from student; -- open the cursor open cursor_stu; -- store the read value declare @ id int, @ name nvarchar (20 ), @ age varchar (20); -- read the first record fetch first from cursor_stu into @ id, @ name, @ age; -- cyclically read the cursor record print 'the data read is as follows :'; -- global variable while (@ fetch_status = 0) beginprint 'No.:' + convert (char (5), @ id) + ', name:' + @ name + ', type: '+ @ age; -- continue reading the next record fetch next from cursor_stu into @ id, @ name, @ age; end -- close the cursor close area_cursor; -- delete the cursor -- deallocate area_cursor;