Understanding of SQL server midstream targets

I. Problems Encountered

In fact, it is not a big problem O (distinct _ distinct) O: Sometimes we may want to directly process the select result set in batch processing or stored procedures. At this time, we need a database object that allows us to process each row of records one by one.

Ii. cursor Concept

To solve the problem above, we can use a database object called a "cursor.

Cursor can be seen as a data type that can be used to traverse the result set, which is equivalent to a pointer or a subscript in an array. It processes result sets in the following ways:

Locate a row in the result set

Searches for a row or a part of rows from the current result set location.

Modify the data of the current row in the result set

Iii. Usage of cursors (creation, opening, reading, closing, and deletion)

[Create a cursor]

It is a bit like defining various data types, but note that do not add "@" (actually there are also "cursor type variables", which are almost identical to the usage of "cursor, and the @ symbol is used for definition ). The following statements define the cursor:

Declare cursor name cursor [local | global] [forward_only | scroll]

For

Select query statement

Cursors can be divided into local cursors and global cursors. local indicates local cursors and global indicates global cursors (default value, can be omitted ). When forward_only (default value, can be omitted) is specified, the cursor is only in, that is, records can only be extracted from start to end. If you need to jump back and forth between rows, You need to specify scroll.

Use cursor]

Creating a cursor without using it makes no sense. The following is a simple example to demonstrate how to use a cursor after creation:

-- [Create a cursor]

Declare C1 cursor for select xingming from yiren

Declare @ xingming varchar (20)

-- [Open cursor]

Open C1

-- [Read cursor]

Fetch next from C1 into @ xingming -- the feature of while is to write it once first.

While (@ FETCH_STATUS = 0)

Begin

Print 'name: '+ @ xingming

Fetch next from C1 into @ xingming

End

-- Close the cursor]

Close C1

-- [Delete a cursor]

Deallocate C1

Is the usage of the cursor similar to the whle (rs. next () {} in Java? In fact, when rs. next () is executed, one row is directly moved backward in the result set. If it does not reach the end of the result set, the loop body is still executed. The same is true for using a cursor here. When the value of @ FETCH_STATUS is 0, the cursor has not reached the end. When it is not 0, the cursor ends and the loop is exited.

Fetch next from cursor name into variable name list is a fixed method for reading cursor content. When multiple fields are selected in the query statement, you need to assign values to multiple variables during reading. It is written as a list of variable names.

Global cursor and scroll cursor]

The global cursor and scroll cursor are mentioned above. The following is an example:

If (CURSOR_STATUS ('global', 'cursor _ 2 ')! =-3) deallocate CURSOR_2

Declare CURSOR_2 cursor scroll -- Global scroll cursor

For select xingming, nicheng, xingbie from yiren

-- The First T-SQL batch starts

Open CURSOR_2

Declare @ seq int,

@ Xingming varchar (20), @ nicheng varchar (50), @ xingbie nchar

Set @ seq = 4

Fetch absolute @ seq from CURSOR_2 into @ xingming, @ nicheng, @ xingbie

If (@ FETCH_STATUS = 0)

Begin

Print 'did' + cast (@ seq as varchar) + 'artist: '+ @ xingming

Print case @ xingbie when 'male' then' other 'when' female 'then' her 'end

+ 'Nickname:' + @ nicheng

End

Close CURSOR_2

Go

-- The second T-SQL batch starts

Open CURSOR_2

Declare @ seq int,

@ Xingming varchar (20), @ nicheng varchar (50), @ xingbie nchar

Set @ seq = 5 -- divided into two batches. You need to define @ seq again

Fetch absolute @ seq from CURSOR_2 into @ xingming, @ nicheng, @ xingbie

If (@ FETCH_STATUS = 0)

Begin

Print 'did' + cast (@ seq as varchar) + 'artist: '+ @ xingming

Print case @ xingbie when 'male' then' other 'when' female 'then' her 'end

+ 'Nickname:' + @ nicheng

End

Close CURSOR_2

Go

-- Delete the cursor in the Third Batch

Deallocate CURSOR_2

When the scroll option is enabled, fetch can be used to read next (backward shift), prior (forward), first (first line), last (last line), absolute (locate to absolute row by numerical value), relative (locate relative row by numerical value ).

A global cursor always exists once defined, so it can be seen in each batch. It does not disappear until deallocate is used to delete it. CURSOR_STATUS ('global', 'cursor _ 2') can check its status.

[Nesting of cursors]

The system performance is greatly affected. Just take a look.

If (CURSOR_STATUS ('global', 'cursor _ 3 ')! =-3) deallocate CURSOR_3

Declare CURSOR_3 cursor

Select yanchuid from yanchu

Open CURSOR_3

Declare @ ycid int

Fetch next from CURSOR_3

Into @ ycid

While (@ FETCH_STATUS = 0)

Begin

Print '+ cast (@ ycid as varchar) +' performers :'

Declare CURSOR_4 cursor

Select xingming from yiren where yirenid in

(Select yirenid from yanchuyiren where yanchuid = @ ycid)

-- This statement uses subqueries. In fact, a cursor can be nested.

Declare @ xingming varchar (50)

Open CURSOR_4

Fetch next from CURSOR_4 into @ xingming

While (@ FETCH_STATUS = 0)

Begin

Print @ xingming

Fetch next from CURSOR_4 into @ xingming

End

Close CURSOR_4

Deallocate CURSOR_4

Fetch next from CURSOR_3

Into @ ycid

Print''

End

Close CURSOR_3

Deallocate CURSOR_3

Cursor variable]

A cursor variable is a way to actually use a cursor as a data type. The difference between a cursor variable and a cursor object lies in the existence @. When creating a cursor variable, first declare @ cursor, and then set @ cursor variable name = cursorfor select statement.

Declare @ c1 CURSOR

Set @ c1 = cursor for select xingming from yiren

Open @ c1

Declare @ xingming varchar (50)

Fetch next from @ c1 into @ xingming

Print @ xingming

Close @ c1

Deallocate @ c1

Iv. Considerations for cursors

[Disadvantages of cursor]

Using a cursor will fetch a result set and process it one by one, increasing the burden on the server. Furthermore, the efficiency of using a cursor is far from the efficiency of using the default result set. Therefore, try not to use a cursor.

[Additional instructions for cursors]

When we open a cursor, it does not point to the first record, but to the front of the first record. We can use books as a metaphor. A cursor can not only point to records in a record set (each page of the book content ), it can also point to the places where there is no record outside the record set (the cover and back cover of the book ).

@ Fetch_status there are three values: 0 indicates that fetch is executed normally,-1 indicates that fetch exceeds the result set, and-2 indicates that the row to which fetch is directed does not exist.

5. Modify the paging query stored procedure and use the cursor

Modify the first branch to the following code:

If @ currentpage> 1

Begin

If @ currentpage> @ totalpages

Begin

Set @ currentpage = @ totalpages

End

Declare @ start int, @ count int

Set @ count = 0

Set @ start = @ currentpage * @ pagesize + 1

Set @ SQL = 'Clare cursor_1 cursor scroll for select * from'

+ @ Tablename + 'ORDER BY' + @ idname

Exec (@ SQL)

Open cursor_1

Fetch relative @ start, @ pagesize from cursor_1

While @ fetch_status = 0

Begin

Set @ count = @ count + 1

Fetch next from cursor_1

If @ count = @ pagesize-1

Break

End

Close cursor_1

Deallocate cursor_1

End

And remove

Exec (@ SQL)

You can. If this sentence is not removed, the statement will be executed again at the end of the stored procedure, and the @ cursor_1 cursor will be incorrectly generated again.