讲义16：游标

-- 游标： -- 定义：游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。     --  每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。 -- 功能：   -- 在数据库中，游标是一个十分重要的概念。   -- 游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。   -- 游标总是与一条SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。   -- 当决定对结果集进行处理时，必须声明一个指向该结果集的游标。   -- 游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。 -- 游标的使用：  -- 1.声明游标   -- 语法:DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]   -- 定义方法:    -- 方法1：    DECLARE My_cursor CURSOR FOR         SELECT * FROM exam order by t_grade desc    -- 方法2：    DECLARE @MyCursor CURSOR    SET @MyCursor = CURSOR LOCAL SCROLL FOR        SELECT * FROM exam  -- 2.打开游标   -- 语法:    OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }   -- 举例:    OPEN exam_cursor  -- 3.读取游标   -- 语法:    FETCH         [ [ NEXT | PRIOR | FIRST | LAST                 | ABSOLUTE { n | @nvar }                 | RELATIVE { n | @nvar }             ]             FROM         ]     { { [ GLOBAL ] cursor_name } | @cursor_variable_name }     [ INTO @variable_name [ ,...n ] ]    -- 举例:     FETCH NEXT FROM exam_cursor  -- 4.关闭游标   -- 语法:     CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }   -- 举例:     CLOSE exam_cursor  -- 5.释放游标   -- 语法:     DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }    -- 举例:     DEALLOCATE exam_cursorcreate table exam(t_number char(8),c_number char(6),t_grade decimal(5,2))insert into exam values('20040301','100101',79)insert into exam values('20040301','100102',88)insert into exam values('20040302','100101',90)insert into exam values('20040302','100103',75)insert into exam values('20040303','100101',79)insert into exam values('20040303','100102',75)insert into exam values('20040303','100103',95)insert into exam values('20040304','100102',43)insert into exam values('20040304','100103',68)insert into exam values('20040305','100101',64)insert into exam values('20040305','100102',87)insert into exam values('20040305','100103',92)-- 最简单的游标使用：DECLARE exam_cursor CURSOR   FOR SELECT * FROM exam order by t_grade descOPEN exam_cursorFETCH NEXT FROM exam_cursorCLOSE exam_cursorDEALLOCATE exam_cursor-- 函数:  -- @@CURSOR_ROWS 返回    -- -m 游标被异步填充。返回值 (-m) 是键集中当前的行数。The value returned (-m) is the number of rows currently in the keyset.    -- -1 游标为动态游标。因为动态游标可反映所有更改，所以游标符合条件的行数不断变化。因此，永远不能确定已检索到所有符合条件的行。The cursor is dynamic.    --  0 没有已打开的游标，对于上一个打开的游标没有符合条件的行，或上一个打开的游标已被关闭或被释放。No cursors have been opened.    --  n 游标已完全填充。返回值 (n) 是游标中的总行数。The value returned (n) is the total number of rows in the cursorSELECT @@CURSOR_ROWSDECLARE exam_cursor CURSOR FORSELECT t_number,t_grade FROM exam order by t_gradeOPEN exam_cursorFETCH NEXT FROM exam_cursorSELECT @@CURSOR_ROWSCLOSE exam_cursorDEALLOCATE exam_cursor  -- @@FETCH_STATUS 返回当前打开的游标的最后读取的状态    --  0 FETCH 语句成功 FETCH statement was successful.    -- -1 FETCH 语句失败或行不在结果集中 FETCH statement failed or the row was beyond the result set.    -- -2 提取的行不存在 Row fetched is missing.DECLARE exam_Cursor CURSOR FORSELECT * FROM examOPEN exam_CursorFETCH NEXT FROM exam_CursorWHILE @@FETCH_STATUS = 0BEGIN   FETCH NEXT FROM exam_CursorENDCLOSE exam_CursorDEALLOCATE exam_Cursor-- 定义存储过程使用游标(显示为结构集)CREATE PROCEDURE EXAM_LIST AS BEGIN  DECLARE exam_Cursor CURSOR FOR  SELECT * FROM exam  OPEN exam_Cursor  FETCH NEXT FROM exam_Cursor  WHILE @@FETCH_STATUS = 0  BEGIN    FETCH NEXT FROM exam_Cursor  END  CLOSE exam_Cursor  DEALLOCATE exam_CursorEND EXAM_LIST-- 定义存储过程使用游标(显示为文本方式)CREATE PROCEDURE EXAM_LIST1 AS  BEGIN  DECLARE @t_num char(8),@C_NUM CHAR(6),@GRADE INT  DECLARE exam_Cursor CURSOR FOR  SELECT T_NUMBER,C_NUMBER,T_GRADE FROM exam   OPEN exam_Cursor  FETCH NEXT FROM exam_Cursor INTO @T_NUM,@C_NUM,@GRADE  PRINT '学号    课程号  成绩'  PRINT '-------------------'  WHILE @@FETCH_STATUS = 0  BEGIN    PRINT @T_NUM + ' ' + @C_NUM +' ' + CONVERT(CHAR(10),@GRADE)    FETCH NEXT FROM exam_Cursor INTO @T_NUM,@C_NUM,@GRADE  END  CLOSE exam_Cursor  DEALLOCATE exam_CursorEND EXAM_LIST1 -- 定义存储过程使用游标(显示为文本方式)CREATE PROCEDURE EXAM_LIST2  @T_NUM char(8) AS  BEGIN  DECLARE @C_NUM CHAR(6),@GRADE INT  DECLARE exam_Cursor CURSOR FOR  SELECT T_NUMBER,C_NUMBER,T_GRADE FROM exam WHERE T_NUMBER=@T_NUM  OPEN exam_Cursor  FETCH NEXT FROM exam_Cursor INTO @T_NUM,@C_NUM,@GRADE  PRINT '学号      课程号  成绩'  PRINT '-------------------'  WHILE @@FETCH_STATUS = 0  BEGIN    PRINT @T_NUM + ' ' + @C_NUM +' ' + CONVERT(CHAR(10),@GRADE)    FETCH NEXT FROM exam_Cursor INTO @T_NUM,@C_NUM,@GRADE  END  CLOSE exam_Cursor  DEALLOCATE exam_CursorEND EXAM_LIST2 '20040301'--例：create table aa1(a1 varchar(10),a2 varchar(10))insert into aa1 values('1','str1')insert into aa1 values('1','str2')insert into aa1 values('2','str3')insert into aa1 values('2','str4')insert into aa1 values('2','str5')insert into aa1 values('3','str6')insert into aa1 values('3','str7')insert into aa1 values('4','str8')select * from aa1--显示结果集为:'1'     'str1 str2''2'     'str3 str4 str5''3'     'str6str7''4'     'str8'declare @a1 int,@a2 varchar(10)declare @temp varchar(50),@result varchar(50)DECLARE My_Cursor CURSOR FORSELECT a1,a2 FROM aa1OPEN My_CursorFETCH NEXT FROM My_Cursor into @a1,@a2set @temp =@a1set @result =@a2WHILE @@FETCH_STATUS = 0BEGIN   FETCH NEXT FROM My_Cursor into @a1,@a2   if @temp=@a1        set @result = @result + @a2   else     begin        select @temp,@result        set @temp = @a1        set @result = @a2     endENDselect @temp,@a2CLOSE My_CursorDEALLOCATE My_Cursor