实验五存储过程和触发器的定义和使用一、实验目的1、掌握局部变量、全局变量、流程控制语句的使用方法2、了解存储过程的类型和作用，并掌握使用对象资源管理器和Transact-SQL语句创建存储过程的方法及使用方法。

3、理解触发器的特点和作用，并掌握使用Transact-SQL语言创建触发器的方法二、实验容1. 在学生成绩库中中有如下各表：学生表（Student）create database学生成绩数据库create table Student(学号Char(6)not null,Char(8)not null,性别Bit not null,出生日期smalldatetime,专业Char(10),所在系Char(10),联系Char(11)null)课程表（Course）create table Course(课程号Char(3)not null,课程名Char(20)not null,教师Char(10),开课学期Tinyint,学时Tinyint,学分Tinyint not null,)学生选课成绩表（SC）create table SC(学号Char(6)not null,课程号Char(3)not null,成绩Smallint,)对三个表格分别导入，截图如下：2、T-SQL语句中流程控制语句的使用（1）全局变量的使用。

显示到当前日期和时间为止试图登录SQL Server的次数。

select getdate()as'当前的日期和时间',connections as'试图登陆的次数'（2）IF语句的使用。

①在Student表中，若存在学号“020205”的学生，则显示该学生的信息，否则插入该学生的记录（020205,萍,0, 1983-7-20, 电子商务, 经济系, 88297171）If exists(select*from Student where学号='020205')select*from Student where学号='020205'Elseinsert into Student values('020205','萍','0','1983-7-20','电子商务','经济系','88297171')②查询颖有没有选课，若选了课，则统计其平均成绩，若没有选课，则输出“颖没有选课”If exists(select from Student,SC where='颖'and Student.学号=SC.学号)select avg(成绩)from SC,Student where Student.='颖'and Student.学号=SC.学号Elseprint'颖没有选课!'（3）循环语句的使用。

①用WHILE语句编程计算1－100之间所有能被3整除的数的个数及总和。

DECLARE S SMALLINT,I SMALLINT,NUMS SMALLINTSET S=0SET I=1SET NUMS=0WHILE(I<=100)BEGINIF(I%3=0)BEGINSET S=S+ISET NUMS=NUMS+1ENDSET I=I+1ENDPRINT SPRINT NUMS②利用GOTO语句求出从1加到5的总和。

DECLARE S SMALLINT,I SMALLINTSET I=1SET S=0BEG:IF(I<=5)BEGINSET S=S+ISET I=I+1GOTO BEGENDPRINT S（4）W AITFOR语句的使用。

①等待5秒后执行查询Student学生信息waitfor delay '00:00:05'select*from Student②等到晚上10:20执行存储过程update_all_stats 。

BEGINWAITFOR TIME'10:20'EXECUTE update_all_statsEND（5）CASE语句的使用。

①查询Student的SNO，SEX，如果SEX为“1”则输出“男”，如果为“0”输出“女”。

SELECT学号,性别=CASE性别WHEN'1'THEN'男'WHEN'0'THEN'女'ENDFROM Student②从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。

SELECT学号,课程号,成绩=CASEWHEN成绩IS NULL THEN'未考'WHEN成绩<60 THEN'不及格'WHEN成绩>=60 AND成绩<70 THEN'及格'WHEN成绩>=70 AND成绩<90 THEN'良好'WHEN成绩>=90 THEN'优秀'ENDFROM SC3、存储过程的的使用。

在上面学生成绩库中完成如下操作：（1）创建如下不带参数的简单存储过程：查询成绩在60至80分之间的学生的学号和课程号。

create procedure SCListasselect学号,课程号from SCwhere成绩>60 and成绩<80（2）分别创建如下的带输入参数的存储过程：①根据用户输入的学号，删除该学生选课成绩记录；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)output,成绩int output)asselect成绩=成绩from SCwhere学号=学号and课程号=课程号declare成绩intexecute grade '020101','101',成绩outputselect'成绩'=成绩（4）创建存储过程proc_t1，要现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、、课程号、课程名、成绩、学分等。

并调用此存储过程，显示“信息管理”专业学生的选课情况列表。

create procedure proc_t1 (专业char(10))asselect Student.学号,Student.专业,Student.,Course.课程号,Course.课程名,Course.学分,成绩from Student,Course,SCwhere Student.学号=SC.学号and Course.课程号=SC.课程号and Student.专业=专业exec proc_t1 '信息管理'（5）对学生成绩库中已创建的存储过程proc_t1进行修改，要现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、、课程号、课程名、成绩、学分等。

并调用修改后的存储过程，显示“信息管理”专业男生的选课情况列表。

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）删除学生成绩库中的存储过程proc_t2。

drop procedure proc_t1（7）编写存储过程，要现如下功能：输入课程名称，产生该课程各分数段及其相应人数的成绩分布情况统计。

create procedure scqk (课程名char(10))asbeginselect课程名,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 then 1 else 0 end ) as'70-89',sum (case when成绩between 90 and 100 then 1 else 0 end ) as'90-100' from SC,Coursewhere SC.课程号=Course.课程号and课程名=课程名group by课程名endexec scqk '计算机原理'4、触发器的使用。

在上面学生成绩库中完成如下操作：（1）DML触发器的使用①创建一个INSERT触发器tri_sc_insert，当向sc表中添加数据时，如果添加的数据与Student表中的数据不匹配（没有对应的学号），则将此数据删除。

create trigger tri_sc_insert on SCfor insertasbegindeclare 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 SCfor updateasif update(成绩)beginprint'修改失败。

'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,deletedwhere SC.课程号=deleted.课程号end④建立一个级联删除触发器trigger_2，实现，即当删除表Student中的记录时，自动删除表sc中对应选课记录。