Definition and use of SQL Midstream label

Source: Internet
Author: User
Tags rowcount

Type of cursor:
1, static cursors (do not detect changes in data rows)
2. Dynamic cursor (reflects all data row changes)
3. Forward-only cursors (does not support scrolling)
4, keyset cursor (can reflect the changes, but can not accurately reflect the insertion, deletion)

Cursor Use order:
1. Defining cursors
2. Open cursor
3. Using Cursors
4. Close the cursor
5. Releasing the cursor

Grammar

DECLARE cursor name CURSOR [LOCAL | global][forward_only | scroll][static | KEYSET | DYNAMIC] [Read_Only | Scroll_locks]
For Selet statement [for update[of column name [, column name]]
Note: Local local cursor global global cursor
Forward_only only forward SCROLL scrolling
Static KEYSET Key Set dynamic
READ_ONLY read-only scroll_locks lock cursor when moving forward

Get data for a cursor
FETCH [[NEXT | PRIOR | First | Last |
absolute{N | @nvar | RELATIVE {n | @nvar}]
From] cursor name [into variable]
Note:
Next line PRIOR Previous line first row
Last line ABSOLUTE n n Nth Row
RELATIVE n the nth row at the beginning of the current position
into variable assigns the value of each field in the current row to the variable

Cursor State variables:
@ @fetch_status Cursor State
0 successes-1 failures-2 lost
@ @cursor_rows The number of rows in the result set in the cursor
n Rows-1 cursor is a dynamic 0 empty set cursor
To manipulate the current line of the cursor:
Current of cursor name

Example 1: Iterating through a query's dataset with a cursor

Use pubs

Go

Declare @auid char, @aulname varchar, @aufname varchar, @st char (2), @auinfo varchar (50)

DECLARE auth_cur cursor FOR

Select au_id, au_lname, au_fname, state

From authors

Open Auth_cur

FETCH NEXT from Auth_cur to @auid, @aulname, @aufname, @st

while (@ @fetch_status =0)

Begin

print ' Author number: ' [email protected]

print ' Author name: ' [email protected]+ ', ' [email protected]

print ' state: ' [email protected]

print '--------------------------'

FETCH NEXT from Auth_cur to @auid, @aulname, @aufname, @st

End

Close Auth_cur

Deallocate auth_cur

Example 2: Modifying and deleting data using cursors

Declare auth_cur cursor Scroll for

Select au_id, au_lname, au_fname, state

From authors for update of au_lname

Open Auth_cur

DECLARE @rowcount int

Set @rowcount = 6

Fetch absolute @rowcount from Auth_cur--sets the row identified by the variable @rowcount as the forward

--The following 1 lines are modified using cursors

Update authors set Au_lname= ' WHERE current of auth_cur--modifies the forward in the cursor

--The following 1 lines are used to delete operations using cursors

Delete from authors where current of auth_cur

Definition and use of SQL Midstream label

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.