SQL Cursor A simple example

1. The role of the cursor:

When Selecting, a result set is returned. If you need to read a row of data during the process of returning the result set. This row of data needs to be processed, such as returning a query result set based on the read data as query conditions, etc. The application needs to use a cursor.

The cursor allows the user to query the next row, previous row, first row or last row and process these read rows.

2. Cursor example

CREATE FUNCTION GetMRPlnFullBom - Create a function to query each row in the document based on the document key and process the data in each row.​

(

                                                                                            using   using   use       through use using use using using         through using ’ through ’ s ’ through using using using ‐ ‐ ‐ ‐ ‐ ‐ to define a temporary table for the result set of the final query and return it.

(

DocEntry int,

LineNum int,

LineNumLevel nvarchar(100),

ItmID nvarchar(20),

ItmName nvarchar(100),

LineType char(1),

Qty numeric( 19,9),

BomLevel int,

ParentEntry int,

ParentItmID nvarchar(20),

TopEntry int,

TopItmID nvarchar( )

)

AS

BEGIN

Declare @ItmID varchar(50) , @Qty int ,@LineNum int ,@ObjType int --Declaring local variables

--Declaring a cursor, XXX cannot be added before declaring the cursor @, for example, the following MRPlnA_ItmID, the format is DECLARE XXX CURSOR FOR = @DocEntry --The statement must be between DECLARE XXX CURSOR FOR and OPEN XXX .

Open MRPlnA_ItmID --Open the cursor

FETCH NEXT FROM MRPlnA_ItmID INTO @ItmID , @Qty ,@LineNum , @ObjType --FETCH NEXT FROM The data from elect, Store it in a temporary variable. In addition to NEXT, the FETCH format also includes PRIOR, FRIST, and LAST. They are the previous line, the first line and the last line.

WHILE @@FETCH_STATUS = 0 --@@FETCH_STATUS global variable, used to query the last status of FETCH and control the loop. It is 0 when the reading is completed, -1 when the reading fails, and -2 when the record is deleted.

BEGIN

Insert Into @TAB

Select DocEntry,LineNum,LineNumLevel,ItmID,ItmName,LineType,Qty*@Qty,BomLevel,ParentEntry,

ParentItmID,TopEntry,TopItmID, @D ocEntry SourceEntry ,@LineNum BaseLineNum ,@ ObjType BaseType

From GetBomFullItems(@ItmID, 'V 1.0', GetDate()) TD --GetBomFULLItmes is another query function.​

FETCH NEXT FROM MRPlnA_ItmID INTO @ItmID , @Qty ,@LineNum , @ObjType -- Select moves to the next line.

END

CLOSE MRPlnA_ItmID --Close the cursor

DEALLOCATE MRPlnA_ItmID --Release the cursor

RETURN

END