9.1. 游标的定义
定义游标:
DECLARE cur_Categories INSENSITIVE CURSOR
FOR SELECT CategoryID,CategoryName FROM Categories;
从游标中检索行的操作称为提取。提取选项如下:
? FETCH FIRST 提取游标中的第一行。
? FETCH NEXT 提取上次提取行之后的行。
? FETCH PRIOR 提取上次提取行之前的行。
? FETCH LAST 提取游标中的最后一行。
? FETCH ABSOLUTE n
如果 n 为正整数,则提取游标中从第 1 行开始的第 n 行。如果 n 为负整数,则提取游标中的倒数第 n 行。如果 n 为 0,则没有行被提取。
? FETCH RELATIVE n
提取上次所提取行之后的第 n 行。如果 n 为正数,则提取所上次提取行之后的第 n 行。如果 n 为负数,则提取上次所提取行之前的第 n 行。如果 n 为 0,则同一行被再次提取。
9.2. 检索游标
DECLARE Teacher_Cursor SCROLL CURSOR
FOR
SELECT tname,dname,age FROM Teacher ORDER BY age
OPEN Teacher_Cursor --打开游标
DECLARE @T_Name CHAR(12),@T_Department CHAR(12),@T_Age INT
FETCH LAST FROM Tracher_Cursor INTO @T_Name,@T_Department,@T_Age --跳到最后一行
PRINT @T_Name + @T_Department + CAST(@T_Age AS CHAR(2))
FETCH PRIOR FROM Tracher_Cursor INTO @T_Name,@T_Department,@T_Age --跳到倒数第二行
PRINT @T_Name + @T_Department + CAST(@T_Age AS CHAR(2))
DECLARE @CategoryId Int
DECLARE @CategoryName Varchar(50)
OPEN cur_Categories
FETCH NEXT FROM cur_Categories INTO @CategoryId,@CategoryName
WHILE @@Fetch_Status = 0
BEGIN
PRINT '品种ID:' + @CategoryId + '产品名称:' + @CategoryName
FETCH NEXT FROM cur_Categories INTO @CategoryId,@CategoryName
END
9.3. 基于游标的DELETE
语法如下:
DELETE FROM table_name WHERE CURRENT OF cursor_name
注:
1、 WHERE子句必须与CURRENT OF一起使用,用来确定打开的游标。
2、 如果游标的SELECT子句中包含有ORDER BY子句,DBMS将把游标限定为READ ONLY,因此不能在相同的游标SELECT语句中同时有ORDER BY和FOR UPDATE语句。
DECLARE BoyTeacher_Cursor CURSOR
FOR
SELECT * FROM TEACHER WHERE TSEX='male'
FOR UPDATE --说明游标可更新
OPEN BoyTeacher_Cursor
FETCH FROM BoyTeacher_Cursor
DELETE FROM TEACHER WHERE CURRENT OF BoyTeacher_Cursor --删除第一行
FETCH FROM BoyTeacher_Cursor
FETCH FROM BoyTeacher_Cursor
DELETE FROM TEACHER WHERE CURRENT OF BoyTeacher_Cursor --删除第三行
CLOSE BoyTeacher_Cursor
9.4. 基于游标的UPDATE
语法:UPDATE table_name SET WHERE CURRENT OF cursor_name
DECLARE GrilTeacher_Cursor CURSOR
FOR
SELECT SAL FROM TEACHER WHERE TSEX='female'
FOR UPDATE --说明游标可更新
DECLARE @Avg_Sal INT,@T_Sal INT
SELECT @Avg_Sal = AVG(SAL) FROM TEACHER WHERE TSEX='female'
OPEN GrilTeacher_Cursor
FETCH FROM GrilTeacher_Cursor
INTO @T_Sal
WHERE @@FETCH_STATUS = 0 --FETCH执行成功
BEGIN
IF @T_Sal IS NULL
UPDATE TEACHER SET SAL = @Avg_Sal
WHERE CURRENT OF GrilTeacher_Cursor
FETCH FROM GrilTeacher_Cursor INTO @T_Sal--游标下移一条记录
END
CLOSE GrilTeacher_Cursor
9.5. 游标的调用
DECLARE cur_Categories INSENSITIVE CURSOR
FOR SELECT CategoryID,CategoryName FROM Categories;
9.6. 游标的关闭
CLOSE cur_Categories
9.7. 游标的删除
DEALLOCATE cur_Categories