Sql server database cursor operating summary 03

1.Creating cursor.

Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT_statement

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

DECLARE hpCamera_SetValCursor CURSOR
FOR SELECT Latitude FROM hp_Camera WHERE CameraType=N'球机'
FOR UPDATE

2.Openning Cursor.

Syntax:

OPEN cursor_name

OPEN hpCamera_SetValCursor

3.Fetching data from cursor.

Syntax:

FETCH [[fetch_orientation]FROM] cursor_name INTO host_variables

Locating UPDATE sentence syntax:

UPDATE table_name {SET column_name1=value1}[,......,column_name n=value,n] WHERE CURRENT OF cursor_name

DECLARE @avg_lat FLOAT,@T_val FLOAT
SELECT @avg_lat=AVG(latitude) FROM hp_Camera WHERE CameraType=N'球机'
FETCH FROM hpCamera_SetValCursor INTO @T_val
WHILE @@FETCH_STATUS=0
BEGIN
    IF @T_val IS NULL
    UPDATE hpCamera SET Latitude=@avg_lat WHERE CURRENT OF hpCamera_SetValCursor
    FETCH FROM hpCamera_SetValCursor INTO @T_val
END

Figure before operating:

Figure got results:

4.Closing cursor

Syntax:

CLOSE cursor_name

CLOSE hpCamera_SetValCursor