The following is a stored procedure with 2 cursors for an order cancellation
SetAnsi_nulls onSetQuoted_identifier onGoALTER PROCEDURE [dbo].[Cancelorderbysystem] asBEGINDeclare /*declaring Variables*/@Status varchar( -),--Status@TimeNow datetime,--Current Time@TradeID varchar( -),--Order Tracking number@GoodsID int,--Product ID@Num int,--Quantity@SkuID int --Specification IDSet @Status='Trade_closed_by_system' /*Assigning a value to a variable*/Set @TimeNow=getdate() begin Transaction;--start the execution of a transaction--query all orders that have expired--determine if the order has a specification for an expired order by querying its sub-order--If there is no specification need to determine whether there are specifications, there is no change in the total inventory--If there is a specification first to judge the release of the specification, there is only release spec stock and total inventory--Modify the status of an orderDeclareCancelorder_cursorcursor for --declaring CursorsSelectTradeID fromWxtradewhereStatus='Wait_buyer_pay' andOuttime<=@TimeNow OPENCancelorder_cursor--Open CursorFETCH NEXT fromCancelorder_cursor--gets the next row of data for the cursor into @TradeID --make the variable get the order number of the specified line of the current cursor ----------------------External cursor (master order) begin-------------------------- while(@ @FETCH_STATUS = 0)--FETCH statement execution succeededBEGIN --Modify the status of an order UpdateWxtradeSetStatus=@Status, Closetime=@TimeNow whereTradeID=@TradeID --Modify the total inventory and spec inventory, and query its child orders for expired orders DeclareOrderdetail_cursorcursor for --declaring Cursors SelectGoodsid,skuid,num fromWxorderwhereTradeID=@TradeID OPENOrderdetail_cursor--Open cursors (child orders) FETCH NEXT fromOrderdetail_cursor--gets the next row of data for the cursor into @GoodsID,@SkuID,@Num --causes the variable to get the item ID, specification ID, quantity of the line specified by the current cursor ----------------------inner nested cursor (child order) begin---------------------- ------------------------------------------------------------------------ while(@ @FETCH_STATUS = 0)--FETCH statement execution succeeded BEGIN if(@SkuID is NULL) BEGIN --no specifications need to first determine whether there are specifications, there is no change in the total inventory, no change if not exists(SelectSkuID from [Sku] whereGoodsid=@GoodsID) BEGIN UpdateGoodsSetQuantity=Quantity+@Num whereGoodsid=@GoodsID END END Else BEGIN --If there is a specification first to determine whether the specification exists, the presence of the release of the specification inventory and total inventory if exists(SelectSkuID from [Sku] whereSkuID=@SkuID) BEGIN Update [Sku] SetItemquantity=Itemquantity+@Num whereSkuID=@SkuID UpdateGoodsSetQuantity=Quantity+@Num whereGoodsid=@GoodsID END END FETCH NEXT fromOrderdetail_cursor--get the next line of the cursor (sub-order) into @GoodsID,@SkuID,@Num --causes the variable to get the item ID, specification ID, quantity of the line specified by the current cursor END CLOSEOrderdetail_cursor--Close cursors (child orders) deallocateOrderdetail_cursor--Release cursors (child orders) ----------------------inner nested cursor (child order) End---------------------- ---------------------------------------------------------------------- FETCH NEXT fromCancelorder_cursor--get the next line of the cursor (main order) into @TradeID --make the variable get the order number of the specified line of the current cursorEndCLOSECancelorder_cursor--Close cursor (main order)deallocateCancelorder_cursor--Release cursor (main order) ----------------------External cursor (main order) End--------------------------if(@ @error>0) begin rollback Transaction return 0 EndElse begin Commit Transaction return 1 EndEND
Reference: http://blog.csdn.net/gxiangzi/article/details/6774786#0-qzone-1-7441-d020d2d2a4e8d1a374a433f596ad1440
http://blog.csdn.net/superhoy/article/details/7663542#0-qzone-1-15425-d020d2d2a4e8d1a374a433f596ad1440
Nested cursors for SQL Server stored procedures