MSSQL OPEN CURSOR CLOSE cursor DELETE cursor

Source: Internet
Author: User
Tags mssql

DECLARE @mergedate datetime
DECLARE @masterid int
DECLARE @duplicateid int

Select @mergedate = getdate ()


Declare merge_cursor cursor Fast_forward for select Mastercustomerid, Duplicatecustomerid from Duplicatecustomers where I smerged = 0
--Define a Cursor object [Merge_cursor]
--the cursor contains the result of the query [select Mastercustomerid, Duplicatecustomerid from duplicatecustomers where ismerged = 0].

Open Merge_cursor
--Open cursor
FETCH NEXT from Merge_cursor into @masterid, @duplicateid
--take data to a temporary variable
While @ @fetch_status = 0-system @ @fetch_status = 0 o'clock Loop End
--Doing the recycling process
Begin
exec mergeduplicatecustomers @masterid, @duplicateid

Update Duplicatecustomers
Set
ismerged = 1,
Mergedate = @mergedate
where
Mastercustomerid = @masterid and
Duplicatecustomerid = @duplicateid

FETCH NEXT from Merge_cursor into @masterid, @duplicateid
--Take the value again
End

Close Merge_cursor
--Close cursor
Deallocate merge_cursor
--Delete cursor


Technical implementation of cursor interpretation
To learn the cursors in Msqsql we need to create a data table to simulate the data:
Create a datasheet student, which contains 4 columns

CREATE TABLE Student
(
ID int identity (1,1) primary key,--Auto grow number
Name varchar NOT NULL--name
Age int is not null--ages
Address varchar (+) NOT null--addresses
)

There are 4 procedures for using cursors: declaring cursors, opening cursors, fetching data, closing cursors.

Declare PCURR cursor for--declares a cursor named Pcurr
declare @customer nvarchar (50)
DECLARE @age int


Select Name,age from student-This query result is very important, this is the dataset we need to read with the cursor

Open Pcurr--opening cursors
FETCH NEXT from Pcurr into @customer, @age
while (@ @fetch_status = 0)--Determines whether the cursor is read, reads over, and returns 100 instead of 0
Begin
Print (@customer)
Print (@age)
FETCH NEXT from Pcurr into @customer, @age--values, and since our comprehensive result set returns a 2-line record, we need 2 custom parameters to fetch the result set
Close Pcurr--closing cursors
Deallocate Pcurr--Releasing cursors

The difference between close and deallocate:
Close is the closing cursor

, this time the cursor still exists, we can open again, Deallocate is the release of the cursor, this time the cursor does not exist, if we need to use the cursor, we do not have to declare a

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.