扩展实践 以随书赠送的素材库中的数据库实例 : 教务管理系统 为例, 要求 : 附加 教务管理系统 数据库至 SQL Server 2008 数据库服务器中 扩展实践 7-1 在教务管理系统数据库的 学生信息 表中定义一个基于 set 赋值语句, 将学生信息统计查询出的学生总人数赋值给局部变量 @num, 并用 print 语句输出 declare @num int set @num=(select count( 学号 ) from 学生信息 ) print @num 扩展实践 7-2 在教务管理系统数据库的 学生信息 表中定义一个基于 set 赋值语句, 将学生信息查询出的所有女生赋值给局部变量 @ssex, 并用 select 语句输出 declare @ssex varchar(2) set @ssex=' 女 ' select * from 学生信息 where 性别 =@ssex 扩展实践 7-3 在教务管理系统数据库的 学生信息 表中定义一个基于 set 赋值语句, 检索出班级编号为 20021340000104 的所有男生 ( 提示 : 将 20021340000104 赋值给班级 编号, 将男生赋值给 @ssex), 并用 select 语句输出 declare @clsnum varchar(14) declare @ssex varchar(2) set @clsnum='20021340000104' set @ssex=' 男 ' select * from 学生信息 where 班级编号 =@clsnum and 性别 =@ssex 扩展实践 7-4 存储过程综合实训 ( 此部分以 xsxk 数据库为例 ) (1) 创建一个存放学生表中所有学生信息的存储过程, 包含学号 姓名 性别 年龄 籍 贯 所在班级 电话 use xsxk go create proc proc_1 select sno,sname,ssex,2018-year(birthday)'sage',origin,cls,tel from s exec sinfo (2) 创建一个指定学生学号的选修课程信息, 包含课程编号 课程名 学分
create proc proc_2 @sno nchar(10) select sc.cno,cname,credit from sc,c where c.cno=sc.cno and sno=@sno exec proc_2 '2011010101' (3) 创建一个指定学生学号的未选修课程信息, 包含课程编号 课程名 学分 create proc proc_3 @sno nvarchar(10) select cno,cname,credit from c where exists(select cno from sc where sno=@sno and c.cno=sc.cno) exec proc_3 '2011010101' (4) 创建一个指定班级的学生成绩的存储过程 proc_cj1, 包含学号 姓名 课程名 成绩 create proc proc_cj1 @cls nvarchar(20) select sc.sno,sname,cname,score from s,sc,c where s.sno=sc.sno and c.cno=sc.cno and cls=@cls exec proc_cj1 '11 网络技术 ' (5) 创建一个指定学生学号的学生成绩存储过程 proc_cj2, 包含课程编号 课程名称 成 绩 create proc proc_cj2 @sno nvarchar(10) select sc.cno,cname,score from sc,s,c where c.cno=sc.cno and s.sno=sc.sno and sc.sno=@sno exec proc_cj2 '2011010101' (6) 创建一个指定学生班级和性别的存储过程 proc_ssex, 包含学号 姓名 性别 年龄 籍贯, 所在班级 电话 create proc proc_ssex @cls nvarchar(20), @ssex nvarchar(2) select sno,sname,ssex,2018-year(birthday)'sage',origin,cls,tel from s
where cls=@cls and ssex=@ssex exec proc_ssex '11 网络技术 ',' 男 ' (7) 创建一个指定学生姓名 ( 值为模糊匹配 ) 的存储过程 proc_sname, 用来显示学生的学 号 姓名 性别 年龄 电话 邮箱等 create proc proc_sname @sname char(6) select sno,sname,ssex,2018-year(birthday)'sage',tel,email from s where sname like'%'+@sname+'%' exec proc_sname ' 小 ' (8) 创建一个统计学生信息表中指定班级名称的学生人数的存储过程 proc_num create proc proc_num @cls nvarchar(20), @num int output select @num=(select COUNT(sno) from s where cls=@cls) declare @xsnum int exec proc_num '11 网络技术 ',@xsnum output select @xsnum' 指定班级人数 ' (9) 创建一个用来显示课程信息的存储过程 proc_c create proc proc_c select * from c exec proc_c (10) 创建一个用来显示指定学号的学生成绩不及格的存储过程 proc_cj3 create proc proc_cj3 @sno nvarchar(10) select * from sc where sno=@sno and score<60 exec proc_cj3 '2011010104' (11) 创建一个指定班级, 指定籍贯的学生信息的存储过程 proc_cls, 包含学号 姓名 性别 年龄 电话等 create proc proc_cls @cls nvarchar(20), @origin nchar(10)
select sno,sname,ssex,2018-year(birthday)'sage',tel from s where cls=@cls and origin=@origin exec proc_cls '11 网络技术 ',' 北京 ' (12) 创建一个家庭住址 ( 值为模糊匹配 ) 的存储过程 proc_address, 用来显示学生的学 号 姓名 性别 年龄 电话等 create proc proc_address @address nvarchar(50) select sno,sname,ssex,2018-year(birthday)'sage',tel from s where address like '%'+@address+'%' exec proc_address ' 杭州 ' 扩展实践 7-5 创建一个存储过程 proc_getstuinfo, 用于返回 教务管理系统 数据库上某个班级 (20021340000104) 中包含的学生信息, 通过为同一存储过程指定不同的班级编号, 返回不同的学生信息 /* 参数的定义 */ create procedure proc_getstuinfo @clsnum varchar(14) select 学号, 姓名, 性别, 班级编号, 年级, 籍贯 from 学生信息 where 班级编号 =@clsnum /* 按位置传递参数 */ execute proc_getstuinfo '20021340000104' /* 通过参数名传递参数 */ execute proc_getstuinfo @clsnum='20021340000104' 扩展实践 7-6 建立一个 insert 触发器 trig_ 新生注册, 当新学生注册的时候, 在 教务管理系统 的 学生信息 表中插入一条学生信息记录的同时, 更新 班级信息 的班级人数 /* 编写 insert 触发器程序 */ use 教务管理系统 go create trigger trig_ 新生注册 on 学生信息 after insert
begin update 班级信息 set 人数 = 人数 +1 where 班级编号 in(select 班级编号 from inserted) end /* 触发 insert 触发器, 执行效果如图 7-15 所示 */ select 人数 ' 新生注册前的班级人数 ' from 班级信息 where 班级编号 insert into 学生信息 ( 学号, 姓名, 班级编号, 性别 ) values('101011',' 章红 '','20001340010102',' 女 ') select 人数 ' 新生注册后的班级人数 ' from 班级信息 where 班级编号 select 学号, 姓名, 班级编号, 性别 from 学生信息 where 班级编号 图 7-15 运行 insert 触发器执行结果 扩展实践 7-7 建立一个 delete 触发器 trig_ 学生离校, 当学生离校的时候, 在 教务管理系统 的 学生信息 表中删除一条指定学生信息记录的同时, 更新 班级信息 的班级人数 /* 编写 delete 触发器程序 */ use 教务管理系统 go create trigger trig_ 学生离校 on 学生信息 after delete begin update 班级信息 set 人数 = 人数 -1 where 班级编号 in(select 班级编号 from deleted)
end /* 触发 delete 触发器, 执行效果如图 7-16 所示 */ select 人数 ' 学生离校前的班级人数 ' from 班级信息 where 班级编号 delete from 学生信息 where 学号 ='101010' select 人数 ' 学生离校后的班级人数 ' from 班级信息 where 班级编号 select 学号, 姓名, 班级编号, 性别 from 学生信息 where 班级编号 图 7-16 运行 delete 触发器执行结果 扩展实践 7-8 在 xsxk 数据库, 创建一个与 s 表结构一致并包含 s 表所有数据的新表 temp_s, 声明一个名为 信息删除 _cur 的游标, 定位删除 temp_s 表中当前行数据 /* 创建新表 temp_s*/ select * into temp_s from s /* 查看新建的 temp_s 表中的数据 */ select * from temp_s /* 声明游标 */ declare 信息删除 _cur cursor for select * from temp_s /* 打开游标 */ open 信息删除 _cur /* 提取数据 */ fetch from 信息删除 _cur delete from temp_s where current of 信息删除 _cur /* 查看结果 */ select * from temp_s /* 关闭游标 */ close 信息删除 _cur deallocate 信息删除 _cur
扩展实践 7-9 以上题的 temp_s 为参考操作数据, 在 xsxk 数据库中声明一个名为 学生地址 _cur 的游标, 该游标返回的结果为 temp_s 表中 籍贯 = 北京 的学生的相关信息 /* 声明游标 */ declare 学生地址 _cur cursor for select sno,sname,ssex,cls,origin from temp_s where origin=' 北京 ' /* 打开游标 */ open 学生地址 _cur /* 提取游标 */ fetch next from 学生地址 _cur /* 关闭游标 */ close 学生地址 _cur deallocate 学生地址 _cur