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