实验五 存储过程和触发器的定义和使用

实验五存储过程和触发器的定义和使用

一、实验目的

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'

Else

insert 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.学号

Else

print'杨颖没有选课!'

（3）循环语句的使用。

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

DECLARE @S SMALLINT,@I SMALLINT,@NUMS SMALLINT SET @S=0

SET @I=1

SET @NUMS=0

WHILE(@I<=100)

BEGIN

IF(@I%3=0)

BEGIN

SET @S=@S+@I

SET @NUMS=@NUMS+1

END

SET @I=@I+1

END

PRINT @S

PRINT @NUMS

②利用GOTO语句求出从1加到5的总和。

DECLARE @S SMALLINT,@I SMALLINT SET @I=1

SET @S=0

BEG:

IF(@I<=5)

BEGIN

SET @S=@S+@I

SET @I=@I+1

GOTO BEG

END

PRINT @S

（4）WAITFOR语句的使用。

①等待5秒后执行查询Student学生信息

waitfor delay '00:00:05'

select*

from Student

②等到晚上10:20执行存储过程 update_all_stats 。BEGIN

WAITFOR TIME'10:20'

EXECUTE update_all_stats

END

（5）CASE语句的使用。

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

SELECT学号,

性别=

CASE性别

WHEN'1'THEN'男'

WHEN'0'THEN'女'

END

FROM Student

②从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不

及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分

时输出“优秀”。

SELECT学号,课程号,

成绩=

CASE

WHEN成绩IS NULL THEN'未考'

WHEN成绩<60 THEN'不及格'

WHEN成绩>=60 AND成绩<70 THEN'及格'

WHEN成绩>=70 AND成绩<90 THEN'良好'

WHEN成绩>=90 THEN'优秀'

END

FROM SC

3、存储过程的的使用。在上面学生成绩库中完成如下操作：（1）创建如下不带参数的简单存储过程：