第十一部分 游标

1.	游标：本质上而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。我们可以把它看作是种指针，它既可以指向当前位置，也可以只想结果集中的任何位置
2.	游标的分类：Transact-SQL、API游标、客户游标
3.	根据游标特点可分为四类：静态游标、动态游标、只进游标、键集驱动游标
4.	声明游标：
DECLARE cursor_name CURSOR
[LOCAL|GLOBAL]
[FORWARD_ONLY|SCROLL]
[STATIC | KEYSET|DYNAMIC|FAST_FORWARD]
[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name[,…n]]]
5.	打开游标
OPEN {{[GLOBAL] cursor_name } |cursor_variable_name}
6.	从由表中读取数据
FETCH [[NEXT |PRIOR　|FIRST |LAST|ABSOLUTE{n | @nvar} |RELATIVE{n |@nvar}]
FROM ]
{{[GLOBAL] cursor_name}|@cursor_variable_name} [INTO @variable_name [,…n]]
7.	关闭游标
8.	CLOSE {{[GLOBAL] cursor_name } | cursor_variable_name}
9.	释放游标
DEALLOCATE {{[GLOBAL ] cursor_name } | @cursor_variable_name}
游标创建与如何使用游标修改数据
A：创建游标变量
USE pubs
DECLARE MyCursor_001 Cursor FOR  
SELECT * FROM Jobs
OPEN MyCursor_001
DECLARE @CursorVar Cursor
SET @CursorVar =MyCursor_001
FETCH NEXT FROM @cCursorVar
CLOSE MyCursor_001
DEALLOCATE MyCursor_001
B:使用游标修改数据
Declare @id char(20)
Declare @ids(20)
Declare @names char(20)
Set @id=1
DECLARE author_cursor CURSOR
   FOR SELECT id,[name]FROM xuesheng
OPEN author_cursor
FETCH NEXT FROM authors_cursor
Into @ids,@names
WHILE @@FETCH_STATUS =0
BEGIN
   If @id=@ids
   Begin
     Update xuesheng set dianhua=88888 where id=ids
   End
FETCH NEXT FROM author_cursor
Into @ids,@names
End
CLOSE authors_cursor
DEALLOCATE authors_cursor

SELECT * FROM xuesheng