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

3、理解触发器的特点和作用，并掌握使用Transact-SQL语言创建触发器的方法二、实验内容1.在学生成绩库中中有如下各表：学生表（Student）学号姓名性别出生日期专业所在系联系电话020101杨颖01980-7-20计算机应用计算机88297147 020102方露露01981-1-15信息管理计算机88297147 020103俞奇军11980-2-20信息管理计算机88297151 020104胡国强11980-11-7信息管理计算机88297151 020105薛冰11980-7-29水利工程水利系88297152 020201秦盈飞01981-3-10电子商务经济系88297161 020202董含静01980-9-25电子商务经济系88297062 020203陈伟11980-8-7电子商务经济系88297171 020204陈新江11980-7-20房建水利系88297171 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）课程号课程名教师开课学期学时学分101计算机原理陈红2453102计算方法王颐3453103操作系统徐格2604104数据库原理及应用应对刚3755105网络基础吴江江4453106高等数学孙中文1906107英语陈刚1906108VB程序设计赵红韦3705create table Course(课程号Char(3)not null,课程名Char(20)not null,教师Char(10),开课学期Tinyint,学时Tinyint,学分Tinyint not null,)学生选课成绩表（SC）学号课程号成绩020********020********020********020********020********020********020********020********020********020********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）WAITFOR语句的使用。

①等待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成绩<60THEN'不及格'WHEN成绩>=60AND成绩<70THEN'及格'WHEN成绩>=70AND成绩<90THEN'良好'WHEN成绩>=90THEN'优秀'ENDFROM SC3、存储过程的的使用。

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

create procedure SCListasselect学号,课程号from SCwhere成绩>60and成绩<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成绩between0and59then1else0end)as'不及格',sum(case when成绩between60and69then1else0end)as'60-69',sum(case when成绩between70and89then1else0end)as'70-89',sum(case when成绩between90and100then1else0end)as'90-100' from SC,Coursewhere SC.课程号=Course.课程号and课程名=@课程名group by课程名endexec scqk'计算机原理'4、触发器的使用。