SQL Server Advanced Features

1. Cursors

Purpose: In the case of a lot of data, if the Java code in the loop after the update data, will cause frequent database connection, cost performance, so you can use the cursor

Role: Queries come out of the collection directly in SQL to traverse the update

DECLAREAllunitsCURSOR//define a cursor, and for the following is a set of cursors that you define for that collection.          for(SELECTId,code fromTbl_unitwhereTbl_unit. Parent_unit_code is NULL)        OPENallunits //Open cursor Declare @ID int,@CODE nvarchar( -) //define the variables you want to use in the cursor, it is important to note that the number of variables here must match the number of queries in the collection above you  .FETCH NEXT  fromAllunits into @ID,@CODE //Opening first row of data, importing parameters          while @ @FETCH_STATUS = 0//Determine whether the next data exists for the cursor: =0 indicates success, = 1 indicates failure or not in the result set, = 2 indicates that the fetched row does not exist.         BEGIN//Start business logic            PRINT @CODE            PRINT @ID                UPDATETbl_unitSETTbl_unit.parent_unit_code= (                    ISNULL(                        (SELECTCODE fromTbl_unitWHERETbl_unit. CODE=  Left(@CODE,6)+' the'   andId!= @ID),                        (SELECTCODE fromTbl_unitWHERETbl_unit. CODE=  Left(@CODE,3)+'000000'   andId!= @ID)                    )                )    WHERETbl_unit.id= @ID        FETCH NEXT  fromAllunits into @ID,@CODE //Import the next row of data         End//cursor ends        CLOSEallunits //close cursors deallocateAllunits//Release cursors

SQL Server Advanced Features