SQL数据库学习之路（练习）---游标操作、动态SQL语句

实验的数据连接我之前的博客SQL练习创建数据库内容

游标操作：

一、实现一个游标，顺序读取并打印所有学生的  学号、课程号、成绩 信息，读取过程中删除S5的选课记录，并将为空的成绩修改为60分。

提示：编写过程中，可需参阅联机丛书获取下列内容的具体用法

1、声明变量可用declare，为变量赋值用set

2、需要判断可用if语句，如if内需执行多条语句，可用begin 和 end 来限定if作用的范围

3、可通过while循环来依次读取所有记录，读取状态可用@@FETCH_STATUS获取

4、如游标已创建，但执行过程中出错。导致重新执行时提示游标已存在，可用cursor_status来检查是否存在该游标，如存在，则先deallocate

declare @S# VARCHAR(16);     --定义临时变量
declare @C# VARchar(8);
declare @SCORE VARCHAR(4);
declare mycursor cursor for   --定义游标
select SC.S#,SC.C#,SC.SCORE FROM dbo.SC
open mycursor;    --打开游标
fetch from mycursor into @S#,@C#,@SCORE;
WHILE @@FETCH_STATUS =0  --如果上一次操作成功则继续循环 
BEGIN 
print 'S#: '+@S#+'C#:'+@C#+'SCORE:'+@SCORE
fetch from mycursor into @S#,@C#,@SCORE; 
END 
空行是因为成绩的数据位空（NULL）

在操作游标时，需要通过declare先定义几个需要的临时变量，

declare mycursor cursor for  select SC.S#,SC.C#,SC.SCORE FROM dbo.SC 这句话是定义游标语句。

mycursor是游标的名字，dbo.SC是游标使用的数据库表名，SC.S#,SC.C#,SC.SCORE是SC表的属性。

创建好游标之后需要打开游标 open

上面的实验代码为使用游标语句检索SC表中的S#,C#,SCORE三个属性的数据。结果如下图：

 读取过程中删除S5的选课记录，并将为空的成绩修改为60分。

代码如下：

open mycursor;    --打开游标
fetch from mycursor into @S#,@C#,@SCORE;
WHILE @@FETCH_STATUS =0  --如果上一次操作成功则继续循环 
BEGIN
if(@SCORE is null)
update SC set SCORE='60' where current of mycursor
if(@S#='s5')
delete from SC where current of mycursor  
print 'S#: '+@S#+'C#:'+@C#+'SCORE:'+@SCORE
fetch from mycursor into @S#,@C#,@SCORE; 
END 

结果如下：

 

二、实现一个卷游标，逆序打印所有学生的  学号、课程号、成绩信息

declare mycursor scroll cursor for   --定义游标
select SC.S#,SC.C#,SC.SCORE FROM dbo.SC order by S# DESC;

 动态SQL语句

一、实现一个存储过程getTopResultByTbname，要求输入表名和要检索的结果数量n，检索该表的前n条记录。

use Work
go
create procedure getTopResultByTbname   --创建函数getTopResultByTbname
@number int,@tablename varchar(4)  --定义函数中number，tablename属性
as begin
declare @sql varchar(50) 
set @sql='select top '+ cast(@number as char) + '*from' + @tablename;
exec(@sql);
end

DROP PROCEDURE getTopResultByTbname   --释放函数getTopResultByTbname
exec getTopResultByTbname 3,' SC'   --查询SC表前三行

二、实现一个函数getRankBySnoAndCno，要求输入学生的学号和课程号，返回该生该门课的排名，要求，如果输入的学号或课程号在SC表中不存在，则返回为-1。

go
create function getRankBySnoAndCno  --定义方法getRankBySnoAndCno
(@s# char(16),@c# char(16)) returns int --定义方法中属性S#,C#
as begin
declare @ret int ,@score float
if not exists (select *from sc where S#=@s# and C#=@c# )
set @ret=-1
else begin
set @score =(select SCORE from sc where S#=@s# and C#=@c# ) --SQL语句
set @ret=(select count(*) from SC where C#=@c# and SCORE>@score)+1 --SQL语句
end 
return @ret
end
select dbo.getRankBySnoAndCno('s1','c1') --对SC表中的s1,c1进行检索