T-SQL---Cursors

T-SQL

T-SQL---Cursors

T-SQL----system tables

T-SQL---stored procedures

Objective

Although the orm,nosql to the data now reduces the number of T-SQL programming, but T-SQL this should be the master of every programmer, the next two weeks are ready to put this knowledge point system summary.

What is a cursor

Cursors can either process the result set of a select or do not require all processing, returning a result after processing the recordset.

What cursors can do

A cursor is actually a mechanism that extracts one record at a time from the result set of multiple data records. Cursors can be completed:

# allow targeting to specific rows in the result set

# retrieves one or more rows of data from the current position of the result set

# supports modification of the current position in the result set

Because cursors are the operations of a recordset, this adds to the burden on the server, typically using cursors in cases where complex result sets are manipulated. SQL Server 2005 has three types of cursors: T-SQL cursors, API cursors, and client cursors.

Operation of Cursors

The basic operations of cursors are to define cursors, open cursors, iterate through cursors, close cursors, and delete cursors.

A. Defining cursors

DECLARE cursor_name--cursor name CURSOR [LOCAL | global]-Global, local [forward only | scroll]--cursor scrolling mode [read_only | scroll_locks | optim Istic]--read mode for select_statements--query statement [for update | of column_name ...]--Modify field

Parameters:

Forward Only | Scroll: The previous parameter, the cursor can only be moved backwards, after a parameter, the cursor may move freely

READ_ONLY: Read-only Cursors

Scroll_locks: Cursor lock, when the cursor is read, the database locks the record so that the cursor completes the operation on the record

Optimistic: This parameter does not lock the cursor, and if the record is read into the cursor, updating or deleting the cursor will not exceed

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 record bars

C. Retrieving cursors

FETCH cursor_name;

The search method is as follows:

Fetch first; Read the first line

FETCH Next; Read Next line

Fetch prior; Read previous line

Fetch last; Read last line

Fetch absolute N; Read a row

If n is a positive integer, the nth record is read

If n is negative, the countdown fetches the nth record

If n is, no record is read

Fetch pelative N

If n is a positive integer, the nth record after the last read of the record is read

If n is negative, the nth record before the last read of the record is read

If n is, the last read record is read

D. Close the cursor

Close cursor_name;

E, delete cursors

DEALLOCATE cursor_name;

Instance

--a working instance of Create PROCEDURE [dbo]. [Weektotalpowerbingtubyweek] @DevName varchar ( -), @starttime varchar ( $) as BEGIN SET NOCOUNT on; --set @DevName ='meter 1 (DTL1110321471), dianbiao$dtl1110321471; meter 2 (DTL1110321470), dianbiao$dtl1110321470;'Declare @pointID varchar ( -) Declare @pointName varchar ( -) Create table #tempTable (pointname varchar ( $), Pointvaluefloat) CREATE TABLE #tempTables (Pointid varchar ( -), Pointname varchar ( -)) Declare @temp table (col varchar ( -))          --Set@str =replace (@str,'|',',')           while(Len (@DevName) >0) begin insert INTO @tempSelectLeft (@DevName, CHARINDEX (';', @DevName)-1)          Set@DevName =stuff (@DevName,1, CHARINDEX (';', @DevName),"') End declare @name varchar ( -) declare @value varchar ( -) DECLARE cur cursor for  Select* from@temp Open cur--Open cursor FETCH next fromcur into @name while@ @FETCH_STATUS =0--determine if data was successfully obtained begin DECLARE @str1 varchar ( -) declare @str2 varchar ( -)                       Select@str1 =left (@name, CHARINDEX (',', @name)-1)                       Select@str2 =substring (@name, CHARINDEX (',', @name) +1, LEN (@name)) insert into #tempTables values (@str2, @str1) fetch next  fromcur into @name end close cur--close the cursor deallocate cur Declare @starttimes varchar ( $) Declare @endtime varchar ( $)          Select@starttimes =dateadd (Day,-datepart (Weekday, @starttime) +1, @starttime)--Start TimeSelect@endtime =dateadd (Day,7-datepart (Weekday, @starttime) +2, @starttime)--End time Declare Cur cursor for          SelectPointid,pointname from#tempTables Open Cur fetch next fromcur into @pointID, @pointName while@ @FETCH_STATUS =0--determine if the data was successfully obtained begin CREATE TABLE #temp ( Invaluefloat) DECLARE @inValuefloatDeclare @sql varchar (8000) Set @sql='INSERT INTO #temp select Round (SUM (Incrementvalue), 2) as Incrementvalue from Tab_day_'[Email protected]+'                                whereGetvaluetime >" "[Email protected]+" "< Getvaluetime" "[Email protected]+" '"  exec (@sql)Select@inValue =invalue from#temp INSERT INTO #tempTable (Pointname,pointvalue) VALUES (@pointName, @inValue) DROP table #temp FETCH next fromcur into @pointID, @pointName end Close cur--close the cursor deallocate curSelect* from#tempTable drop table #tempTable drop table #tempTables END

T-SQL---Cursors