数据库中的游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。

查询结果为多条记录的SELECT语句、CURRENT 形式的 UPDATE 和 DELETE 语句。

步骤为：说明游标、打开游标、推进游标指针并取当前记录、关闭游标。

CURRENT 形式的 UPDATE 和 DELETE 语句：用到 WHERECURRENT OF <游标名>

 

示例：

--1）、利用游标依次查询 学生成绩表 中大学物理课程的选课记录，并且输出成绩排名

USE JXGL

GO

-- 查询,并用查询结果建立一个游标

DECLARE Grade_cursor CURSOR SCROLL FOR

 SELECT 学生ID,成绩

 FROM学生成绩表,课程设置表

 WHERE 学生成绩表.课程ID=课程设置表.课程ID

 AND 课程名='大学物理' AND 成绩 IS NOTNULL

 ORDER BY 成绩

OPEN Grade_cursor

DECLARE @SC_sno varchar(12),@SC_gradeINT    --声明主变量，接收游标区数据

PRINT '大学物理成绩排名'

PRINT '---------学号--------成绩----------'

FETCH LAST FROM Grade_cursor INTO@SC_sno,@SC_grade

PRINT '第一名:  '+@SC_sno+'     '+CONVERT(CHAR(5),@SC_grade)

FETCH RELATIVE -1 FROM Grade_cursor INTO@SC_sno,@SC_grade

PRINT '第二名:  '+@SC_sno+'     '+CONVERT(CHAR(5),@SC_grade)

FETCH PRIOR FROM Grade_cursor INTO@SC_sno,@SC_grade

PRINT '第三名:  '+@SC_sno+'     '+CONVERT(CHAR(5),@SC_grade)

FETCH FIRST FROM Grade_cursor INTO@SC_sno,@SC_grade

PRINT '最低分:  '+@SC_sno+'     '+CONVERT(CHAR(5),@SC_grade)

CLOSE Grade_cursor

DEALLOCATE Grade_cursor

GO

-------------------------------------------------

2）、利用游标逐一查询选修大学英语课程的学生选课记录，如果参加考试且成绩不及格，则删除此选课记录*/

USE JXGL

GO

-- 查询,并用查询结果建立一个游标

DECLARE Grade_cursor CURSOR FOR

 SELECT 学生ID,成绩

 FROM学生成绩表,课程设置表

 WHERE 学生成绩表.课程ID=课程设置表.课程ID

  AND课程名='大学英语'

  AND成绩 IS NOT NULL

  FORUPDATE  

OPEN Grade_cursor

DECLARE @SC_sno varchar(12),@SC_grade INT

FETCH NEXT FROM Grade_cursor INTO@SC_sno,@SC_grade

WHILE @@FETCH_STATUS = 0         -------------------------?

BEGIN

 IF@SC_grade<60

    BEGIN

      DELETE FROM 学生成绩表

         WHERE CURRENT OF Grade_cursor          ------------------?

      PRINT @SC_sno+'大学英语成绩不及格，删除考试纪录'

    END

 FETCH NEXT FROM Grade_cursor INTO@SC_sno,@SC_grade

END

CLOSE Grade_cursor

DEALLOCATE Grade_cursor

GO

----

SELECT * INTO 学生成绩表1 FROM 学生成绩表

delete 学生成绩表

insert into 学生成绩表 select * from 学生成绩表1

-----------------------------------------------------------------

3）、--利用游标依次查询 学生情况表 中不同专业学生的信息（班级,学生ID, 姓名），并输出

USE 教学管理

GO

---声明变量--------

DECLARE @st_id varchar(12),  --学号

 @st_Spec varchar(20),  --专业

 @st_class varchar(10), --班级

 @st_name varchar(10)  --姓名

--声明游标Spec_cursor------

DECLARE Spec_cursor CURSOR

READ_ONLY       ----------------------?

FOR

  SELECT distinct 专业

  FROM 学生情况表

  order by 专业

--打开游标Spec_cursor------

OPEN Spec_cursor

--从Spec_cursor游标中检索行--

FETCH NEXT FROM Spec_cursor INTO@st_Spec    

WHILE @@FETCH_STATUS = 0

BEGIN

 

 --打印学生名单的表头----

 PRINT @st_Spec+'学生名单'

 PRINT '--------------------------'

 PRINT '班级    学号        姓名'

 PRINT '--------------------------'

 

 --声明游标st_cursor------    --游标中嵌套游标

 DECLARE st_cursor CURSOR FOR

   SELECT 班级,学生ID, 姓名

   FROM 学生情况表

   WHERE 专业 = @st_Spec

   ORDER BY 学生ID

 

 OPENst_cursor

 

 --从st_cursor游标中检索指定系名的记录行--

 FETCH NEXT FROM st_cursor INTO@st_class,@st_id, @st_name

 

 WHILE @@FETCH_STATUS = 0

 BEGIN

 PRINT @st_class+'   '+ @st_id +'    '+@st_name

 FETCH NEXT FROM st_cursor INTO @st_class,@st_id, @st_name

 END

      

       PRINT '--------------------------'

       PRINT ' '

 CLOSE st_cursor

 DEALLOCATE st_cursor

 FETCH NEXT FROM Spec_cursor INTO @st_Spec

END

CLOSE Spec_cursor

DEALLOCATE Spec_cursor

GO

--------------------------------------------

 

二、存储过程

存储过程和函数是命名块，被编译后保存在数据库中，可以被反复调用，运行速度较快，使用时只需调用即可。

 

示例：

1）、带有输入参数

为教学管理数据库创建一存储过程proc1，显示指定学生（根据学号id）选择各门课程的成绩

USE JXGL

GO

CREATE PROC proc1      ---创建存储过程

@snum char(10) =NULL    --参数列表（需指定数据类型）；此处初始为空

AS                      --后为SQL块

IF @snum IS NULL

    PRINT '请重新执行，你必须提供学号！'

ELSE

IF NOT EXISTS (SELECT * FROM 学生情况表 WHERE 学生ID=@snum)   ---如果输入的学生ID不存在于 学生情况表

    PRINT '请提供正确的学号！'

ELSE

   SELECT A.学生ID,姓名,课程名,成绩

   FROM 学生情况表 A, 学生成绩表 B , 课程设置表 C

   WHERE A.学生ID= B.学生ID

         and C.课程ID= B.课程ID

         and B.学生ID= @snum

GO

--执行proc1

 DECLARE @snum char(10)  ---声明主变量

  SET@snum = '4100205'   --指定主变量的值，即指定的学生

 EXECUTE proc1 @snum      --执行存储过程

------------------------------------------------------------------------

2）、带有输出参数

为教学管理数据库创建一个带有输出参数(OUTPUT)的存储过程存proc2，显示指定课程的考试成绩在指定分数段的学生，并返回该分数段的学生人数

--1、声明创建存储过程

create PROC proc2      

--2、参数列表

@lscore float,   --指定分数段的最低分

@hscore float,   --指定分数段的最高分

@cnum nvarchar(10),   --课程ID

@STU_COUNT smallint  OUTPUT      --------------------------代表是输出参数

AS

--3、SQL块

--显示指定课程的考试成绩在指定分数段的学生（学生ID，姓名，指定课程的成绩）

   SELECT B.学生ID, 姓名, 成绩

   FROM 学生情况表 A, 学生成绩表 B

   WHERE A.学生ID= B.学生ID

       and B.课程ID=@cnum

       and 成绩 BETWEEN @lscore and @hscore

   ORDER BY 成绩, B.学生ID

--计算该分数段的学生人数；

SET @STU_COUNT=( SELECT count(distinct D.学生ID) 

                 FROM 学生成绩表 D

               WHERE D.课程ID= @cnum

               and 成绩 BETWEEN@lscore and @hscore) 

GO

--4、执行；proc2，统计并输出指定课程，指定成绩段内的人数

declare @l int,@h int,@kh char(10),@rs int

set @l=60

set @h=80

set @kh='032101'  --课程ID

exec proc2 @l, @h,@kh ,@rs output

print rtrim(@kh)+'课程'+convert(char(3),@l)+'-'+convert(char(3),@h)+'分人数:'+convert(char(2),@rs)+'人'

----------------convert语句该如何用

------------------------------------------------------

3）、使用游标

为教学管理数据库创建一个带有 OUTPUT 游标参数的存储过程, 统计某课程考试成绩在指定分数段的学生人数，并输出学生成绩单

ALTER PROC proc3

@lscore float,

@hscore float,

@cnum nvarchar(10),

@STU_COUNT int OUTPUT,

@STU_list CURSOR VARYING OUTPUT     --输出游标参数

AS

SET @STU_COUNT=( SELECT count(distinct 学生ID)

                 FROM 学生成绩表

                 WHERE 课程ID= @cnumand

                      成绩 BETWEEN@lscore and @hscore) 

SET @STU_list=CURSOR

 FOR

       SELECT B.学生ID,姓名,成绩

       FROM 学生情况表 A,学生成绩表 B

       WHERE A.学生ID= B.学生ID

                and B.课程ID=@cnum

                and 成绩 BETWEEN@lscore and @hscore

       ORDER BY 成绩, B.学生ID                         --定义游标@STU_list

 

OPEN @STU_list       --打开游标@STU_list

GO

---------

--执行proc3，统计并输出指定课程，指定成绩段内的人数

declare @l int,@h int,@kh char(10),@rs int

DECLARE @STlist CURSOR                     --声明游标类型变量

set @l=60

set @h=80

set @kh='032101'

exec proc3 @l, @h,@kh ,@rsoutput,@STU_list= @STlist OUTPUT

DECLARE @stnum char(10),@stnamechar(10),@stgrade float    --声明游标中使用的接收变量

print rtrim(@kh)+'课程'+convert(char(3),@l)+'-'+convert(char(3),@h)+'分人数:'+convert(char(2),@rs)+'人'

print '--------------------------------'

print '学号      姓名      成绩         '

print '--------------------------------'

FETCH NEXT FROM @STlist INTO @stnum,@stname ,@stgrade

WHILE (@@FETCH_STATUS = 0)

     BEGIN

   print @stnum+@stname+convert(char(5),@stgrade)

  FETCH NEXT FROM @STlist INTO @stnum ,@stname ,@stgrade

     END

--CLOSE @STlist        --关闭游标@STCOUNT

--DEALLOCATE @STlist   --释放游标@STCOUNT

go