Sql server database cursor operating summary 01

1.Creating cursor.

Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT_statement

[FOR {READ ONLY|UPDATE [OF column_name [,...n]]}]

DECLARE Camera_Cursor SCROLL CURSOR
FOR SELECT CameraName,CameraType,Area,Street,Site,Longitude,Latitude,CameraAddress,UpdateTime FROM Camera ORDER BY UpdateTime

2.Openning Cursor.

Syntax:

OPEN cursor_name

OPEN Camera_Cursor

3.Fetching data from cursor.

Syntax:

FETCH [[fetch_orientation]FROM] cursor_name INTO host_variables

DECLARE @cameraname_val NVARCHAR(100),
@cameratype_val NVARCHAR(150),
@area_val NVARCHAR(50),
@street_val NVARCHAR(250),
@site_val NVARCHAR(250),
@longitude_val FLOAT,
@latitude_val FLOAT,
@cameraaddress_val NVARCHAR(250),
@updatetime_val DATETIME
/*CameraName,CameraType,Area,Street,Site,Longitude,Latitude,CameraAddress,UpdateTime*/
FETCH LAST FROM Camera_Cursor INTO @cameraname_val,@cameratype_val,@area_val,@street_val,@site_val,@longitude_val,
@latitude_val,@cameraaddress_val,@updatetime_val
Select 'Camera name'=@cameraname_val,'Camera type'=@cameratype_val,'Camera address'=@cameraaddress_val,'Update time'=@updatetime_val
FETCH PRIOR FROM Camera_Cursor INTO @cameraname_val,@cameratype_val,@area_val,@street_val,@site_val,@longitude_val,
@latitude_val,@cameraaddress_val,@updatetime_val
Select 'Camera name'=@cameraname_val,'Camera type'=@cameratype_val,'Camera address'=@cameraaddress_val,'Update time'=@updatetime_val
FETCH PRIOR FROM Camera_Cursor INTO @cameraname_val,@cameratype_val,@area_val,@street_val,@site_val,@longitude_val,
@latitude_val,@cameraaddress_val,@updatetime_val
Select 'Camera name'=@cameraname_val,'Camera type'=@cameratype_val,'Camera address'=@cameraaddress_val,'Update time'=@updatetime_val

result figure:

4.Closing cursor

Syntax:

CLOSE cursor_name

CLOSE Camera_Cursor