SQL-Cursors (instance)

Why cursors are used:

• General query: The set or multi-set as a whole to deal with, do not rely on any order;

• Cursor Object query: It is used to process rows in the result set returned by the query, processing only one row at a time in the specified order;

Steps to use cursors:

1. Declares a cursor on the basis of a query;

2. Open the cursor;

3. Extracts the column values from the first cursor record to the specified variable;

4. when the last row of the cursor has not been exceeded (@ @FETCH_STATUS The return value of the function is 0) , loops through the cursor record: In each traversal, the column values are extracted from the current cursor record to the specified variable, and the corresponding processing is performed for the current row.

5. Close the cursor;

6. Release cursors;

Example : use cursors to calculate sales.custorders The total number of consecutive orders per customer per month in the view:

--( code logic:

-- declaring Cursors -- from CustOrder in view by Customer ID returns the base data row in the order of the order month;

-- by looping through each record, the code keeps track of the customer's current sequential order quantity and saves the value in the variable @runqty , each time a customer is found, the variable is reset. Then the customer ID, Order month, the current monthly order quantity, the current month's continuous order quantity as a row inserted into the table variable @result .

[email protected]: order quantity of the month

[email protected]: number of consecutive orders for the current month

-- when all the records for the cursor are completed, the table variable is queried to display the resulting continuous aggregated values.

--)

Set nocount on -- Do not return T-SQL number of rows affected, Count ;

Use TSQLFundamentals2008

Declare @result Table

(

Custid int,

OrderMonth datetime,

Qty int,

Runqty int,

Primary key(custid,ordermonth)

);

Declare

@custid as int,

@prvcustid as int,

@ordermonth datetime,

@qty as int,

@runqty as int;

--step 1 : declaring a cursor on the basis of a query

Declare C Cursor fast_forward /*read only, forward only*/for

Select CustID, ordermonth, Qty

from sales. CustOrders

Order by CustID, ordermonth;

--step 2: Open Cursor

Open C

--step 3: extracts the column values from the first cursor record to the specified variable;

FETCH NEXT from C into @custid, @ordermonth, @qty;

Select @prvcustid=@custid, @runqty=0;

--step 4: when the last row of the cursor has not been exceeded (@ @FETCH_STATUS The return value of the function is 0) , loops through the cursor record: In each traversal, the column values are extracted from the current cursor record to the specified variable, and the corresponding processing is performed for the current row.

while @ @FETCH_STATUS = 0

BEGIN

IF @custid <> @prvcustid

Select@prvcustid = @custid,@runqty=0;

Set @runqty =@runqty +@qty;

Insert Into @result values(@custid,@ordermonth,@qty,@runqty );

Fetch next from C into @custid,@ordermonth, @qty

END

--step 5: close the cursor;

CLOSE C;

--step 4: release cursors;

deallocate C;

-- when all the records for the cursor are completed, the table variable is queried to display the resulting continuous aggregated values.

Select

Custid,

Convert (varchar(7),ordermonth,121) as OrderMonth,

Qty,

Runqty

from @result

Order by CustID, ordermonth;

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1633886

SQL-Cursors (instance)