SQL Server 2000中如何使用遊標

 

DECLARE FLAG CURSOR scroll dynamic                /*scroll表示可隨意移動遊標指標（否則只能向前），dynamic表示可以讀寫遊標（否則遊標唯讀）*/
  FOR SELECT ORDER_NUM FROM ORDERS
OPEN FLAG

DECLARE  @order_num int
DECLARE @n int                                                            /*定義使用者變數*/

SET @n = 1

FETCH NEXT FROM FLAG INTO @order_num
UPDATE ORDERS SET no = @n WHERE CURRENT OF FLAG

WHILE  @@fetch_status=0
BEGIN
 FETCH NEXT FROM FLAG INTO @order_num
 UPDATE ORDERS SET no = @n WHERE CURRENT OF FLAG
 SET @n = @n + 1
END

CLOSE FLAG
DEALLOCATE FLAG