1、实验五 存储过程和触发器的定义和使用 徐龙琴设计制作实验五 存储过程和触发器的定义和使用一、 实验目的1、 掌握局部变量、全局变量、流程控制语句的使用方法2、 了解存储过程的类型和作用，并掌握使用对象资源管理器和 Transact-SQL 语句创建存储过程的方法及使用方法。3、 理解触发器的特点和作用，并掌握使用 Transact-SQL 语言创建触发器的方法二、 实验内容1. 在 学生成绩库中中有如下各表：学生表（Student）create database 学生成绩数据库create table Student(学号 Char(6) not null,姓名 Char(8) not nul
2、l,性别 Bit not null,出生日期 smalldatetime,专业 Char(10),所在系 Char(10),联系电话 Char(11) null)课程表（Course）学号 姓名 性别 出生日期 专业 所在系 联系电话020101 杨颖 0 1980-7-20 计算机应用 计算机 88297147020102 方露露 0 1981-1-15 信息管理 计算机 88297147020103 俞奇军 1 1980-2-20 信息管理 计算机 88297151020104 胡国强 1 1980-11-7 信息管理 计算机 88297151020105 薛冰 1 1980-7-29 水
3、利工程 水利系 88297152020201 秦盈飞 0 1981-3-10 电子商务 经济系 88297161020202 董含静 0 1980-9-25 电子商务 经济系 88297062020203 陈伟 1 1980-8-7 电子商务 经济系 88297171020204 陈新江 1 1980-7-20 房建 水利系 88297171实验五 存储过程和触发器的定义和使用 徐龙琴设计制作create table Course(课程号 Char(3) not null,课程名 Char(20) not null,教师 Char(10) ,开课学期 Tinyint,学时 Tinyint,学分
4、 Tinyint not null,)学生选课成绩表（SC）create table SC(学号 Char(6) not null,课程号 Char(3) not null,成绩 Smallint,)对三个表格分别导入，截图如下：课程号 课程名 教师 开课学期 学时 学分101 计算机原理 陈红 2 45 3102 计算方法 王颐 3 45 3103 操作系统 徐格 2 60 4104 数据库原理及应用 应对刚 3 75 5105 网络基础 吴江江 4 45 3106 高等数学 孙中文 1 90 6107 英语 陈刚 1 90 6108 VB 程序设计 赵红韦 3 70 5学号 课程号 成绩0
5、20101 101 85020101 102 87020101 103 88020102 101 58020102 102 63020104 107 76020202 103 55020202 107 80020203 103 57020204 103 71实验五 存储过程和触发器的定义和使用 徐龙琴设计制作实验五 存储过程和触发器的定义和使用 徐龙琴设计制作2、T-SQL 语句中 流程控制语句 的使用（1）全局变量的使用。显示到当前日期和时间为止试图登录 SQL Server 的次数。select getdate() as 当前的日期和时间,connections as 试图登陆的次数实验五
6、 存储过程和触发器的定义和使用 徐龙琴设计制作（2）IF 语句的使用。在 Student 表中，若存在学号 “020205”的学生，则显示该学生的信息，否则插入该学生的记录（020205, 李萍,0, 1983-7-20, 电子商务, 经济系, 88297171）If exists(select * from Student where 学号= 020205)select * from Student where 学号= 020205Else insert into Student values(020205, 李萍,0, 1983-7-20, 电子商务, 经济系, 88297171)实验五
7、存储过程和触发器的定义和使用 徐龙琴设计制作查询杨颖有没有选课，若选了课，则统计其平均成绩，若没有选课，则输出“杨颖没有选课”If exists(select 姓名 from Student,SC where 姓名=杨颖 and Student.学号=SC. 学号)select avg(成绩) from SC,Student where Student.姓名= 杨颖and Student.学号 =SC.学号Else print 杨颖没有选课!（3）循环语句的使用。用 WHILE 语句编程计算 1100 之间所有能被 3 整除的数的个数及总和。实验五 存储过程和触发器的定义和使用 徐龙琴设计制作
8、DECLARE S SMALLINT,I SMALLINT,NUMS SMALLINT SET S=0SET I=1SET NUMS=0WHILE (I=60 AND 成绩=70 AND 成绩=90 THEN 优秀ENDFROM SC3、存储过程的的使用。在上面学生成绩库中完成如下操作：（1）创建如下不带参数的简单存储过程：实验五 存储过程和触发器的定义和使用 徐龙琴设计制作查询成绩在 60 至 80 分之间的学生的学号和课程号。create procedure SCListas select 学号,课程号from SCwhere 成绩60 and 成绩80（2）分别创建如下的带输入参数的存储
9、过程：根据用户输入的学号，删除该学生选课成绩记录；create procedure studentqk1(学号 char(10)asdelete 成绩from SCwhere 学号=学号向 SC 表中插入数据。create procedure sccharu(学号 char(10),课程号 char(10),成绩 int)asinsert into SC values(学号, 课程号, 成绩)（3）创建如下的带输出参数的存储过程，并写出一个执行该过程的例子：根据用户输入的学号，课程号，输出其成绩。create procedure grade(学号 char(10),课程号 char(10) o
10、utput,成绩 int output)asselect 成绩 =成绩from SCwhere 学号=学号 and 课程号 =课程号declare 成绩 intexecute grade 020101,101,成绩 outputselect 成绩 =成绩实验五 存储过程和触发器的定义和使用 徐龙琴设计制作（4）创建存储过程 proc_t1，要求实现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过程，显示“信息管理”专业学生的选课情况列表。create procedure proc_t1 (专业 char(10)asse
11、lect Student.学号,Student.专业, Student.姓名 ,Course.课程号,Course. 课程名,Course. 学分,成绩from Student,Course,SCwhere Student.学号=SC.学号 and Course.课程号= SC.课程号 and Student.专业= 专业exec proc_t1 信息管理实验五 存储过程和触发器的定义和使用 徐龙琴设计制作（5）对学生成绩库中已创建的存储过程 proc_t1 进行修改，要求实现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调
12、用修改后的存储过程，显示“信息管理”专业男生的选课情况列表。alter procedure proc_t1 (专业 char(10)asselect Student.学号,Student.专业, Student.姓名 ,Course.课程号,Course. 课程名,Course. 学分,成绩from Student,Course,SCwhere Student.学号=SC.学号 and Course.课程号= SC.课程号 and Student.专业=专业 and 性别=1exec proc_t1 信息管理实验五 存储过程和触发器的定义和使用 徐龙琴设计制作（6）删除学生成绩库中的存储过程
13、proc_t2。drop procedure proc_t1（7）编写存储过程，要求实现如下功能：输入课程名称，产生该课程各分数段及其相应人数的成绩分布情况统计。create procedure scqk (课程名 char(10)asbegin select 课程名,sum (case when 成绩 between 0 and 59 then 1 else 0 end ) as 不及格,sum (case when 成绩 between 60 and 69 then 1 else 0 end ) as 60-69,sum (case when 成绩 between 70 and 89 th
14、en 1 else 0 end ) as 70-89,sum (case when 成绩 between 90 and 100 then 1 else 0 end ) as 90-100from SC,Coursewhere SC.课程号= Course.课程号 and 课程名=课程名group by 课程名endexec scqk 计算机原理实验五 存储过程和触发器的定义和使用 徐龙琴设计制作4、触发器的使用。在上面学生成绩库中完成如下操作：（1）DML 触发器的使用创建一个 INSERT 触发器 tri_sc_insert，当向 sc 表中添加数据时，如果添加的数据与 Student 表中
15、的数据不匹配（没有对应的学号），则将此数据删除。create trigger tri_sc_insert on SCfor insert asbegindeclare bh char(6)select bh=inserted.学号from insertedif not exists (select 学号from Student where Student.学号=bh)delete SC where 学号=bhend创建一个 UPDATE 触发器 tri_sc_upd，用来防止用户修改 SC 表的成绩。create trigger tri_sc_upd on SC for update asif
16、 update(成绩 )begin print 修改失败。raiserror (不能修改SC表的成绩 ,16,10)rollback transactionend 创建一个级联修改触发器 trigger_1，即当修改学生课程表 Course 中的某门课的课实验五 存储过程和触发器的定义和使用 徐龙琴设计制作程号时，对应学生的选课表 SC 中的课程号也作修改 create trigger trigger_1 on Coursefor updateasif update(课程号 )begin update SCset 课程号=(select 课程号from inserted)from SC,del
17、etedwhere SC.课程号=deleted.课程号end建立一个级联删除触发器 trigger_2，实现，即当删除表 Student 中的记录时，自动删除表 sc 中对应选课记录。create trigger trigger_2 on Studentfor deleteasbegin delete SCfrom SC,deletedwhere SC.学号=deleted.学号end创建触发器 trigger_3，实现当修改学生表中的数据时，显示提示信息“学生表被修改了” 。create trigger trigger_3 on Student for update asprint 学生表
18、被修改了！修改已创建的触发器 trigger_3，实现当修改学生表中的数据时，显示提示信息“学生表中 XXX 号学生记录被修改了” 。alter trigger trigger_3 on Student for update asdeclare kch char(6)select kch=学号from deletedprint 学生表中学号为+kch+ 的记录被修改了删除学生表上的触发器 trigger_3。drop trigger trigger_3实验五 存储过程和触发器的定义和使用 徐龙琴设计制作（2）DDL 触发器的使用（选作）使用 DDL 触发器来防止在数据库中创建表。创建一个 sa
19、fety_1 触发器，禁止用户在学生成绩库数据库创建表。并运行 CREATE TABLE newtable(ID int)语句验证。用 DDL 触发器来防止在数据库中修改、删除表。创建一个 safety_2 触发器，禁止用户在学生成绩库数据库中修改、删除表。并运行 DROP TABLE SC 语句验证删除触发器 safety_1 和 safety_2三、 思考题1、存储过程的类型有哪些？分别有什么特征？。2、如何创建一个存储过程？试述存储过程在程序设计的作用。3、假设必须修改数据库中的一个存储过程，同时有几个用户被授予执行这个存储过程的权限，请问执行哪个语句可以实现修改，但又不影响现有的权限。4、列出本实验你遇到的问题及解决办法
