实验八 T-sql，存储过程

1.变量的使用

实验代码、实验过程（界面方式下的抓图）、实验结果（抓图） （1）创建局部变量@xh（char(9)）并使用set将student表中“李勇”的学号字段赋给该变量，然后使用select显示变量的值；

Decalre @xh char(9)
Set @xh = (select sno from student where sname=’李勇’)
Select @xh as 学号
1
2
3

（2）计算学生信息表student中学生最高年龄和最低年龄之差，并使用select将结果赋值给@cz，并使用print显示变量的值。

Declare @cz int
Select @cz=max(sage)-min(sage) from  student
Print @cz
1
2
3

2.应用内置函数

实验代码、实验过程（界面方式下的抓图）、实验结果（抓图） （1）求course表中课程号为“03”的课程名称的长度，并在消息提示框中输出结果。结果格式为：“03号课程名称的长度为：**”。
提示：使用CONVERT函数。

Declare @length int
Set @length = len(select cname from course where cno=’3’)
Print  ‘03号课程名称的长度为：’+str(@length,1)
1
2
3

（2）使用字符串串联运算符“+”，将student表中的所有字段串联在一起用一个“学生信息”字段显示出来。

Select sname+’ ’+convert(varchar)+’ ’+sdept  as  ‘学生信息’
From student
1
2

3. 流程控制语句

实验代码、实验过程（界面方式下的抓图）、实验结果（抓图） (1)判断student表中是否存在编号为2005150026的学生，如果存在，则显示该学生基本信息；若不存在，则显示“查无此人”。

Declare @str 
Set @str = ‘2005150026’
If (@str in (select sno from student))
Begin
Select * from student where sno = @str 
End
Else
Print ‘查无此人’
1
2
3
4
5
6
7
8

(2)查询学号为“200515002”的学生的平均分是否超过了85分，若超过则输出“XX(学生姓名)考出了高分”，否则输出“XX(学生姓名)考的一般”。

Declare @name varchar(10),@pjf int
Select @name = (select sname from student where sno = ‘200515002’)
Set @pjf =  avg(grade) from sc where sno=’200515002’
If (@pjf>85) print @name+‘考出了高分’
Else print @name+‘考的一般’
1
2
3
4
5

4. 定义函数

实验代码、实验过程（界面方式下的抓图）、实验结果（抓图） (1)定义一个函数CHECK_SNO实现如下功能：对于一个给定的sno值，查询该值在student表中是否存在，若存在则返回0，否则返回-1。

Create function CHECK_SNO(@sno varchar(10))
Return int
As
Begin
If (@sno in (select sno from student))
Return 0
Else
Return -1
End
1
2
3
4
5
6
7
8
9

(2)写一段T-sql程序调用上述函数。当向sc表插入一行记录时，首先调用函数CHECK_SNO检索该记录的sno值在表student中是否存在对应值，若存在，则将该记录插入记录（‘200515023’,’02’,75）到sc表中。

Declare @sno
Set @sno = ‘200515023’
If (CHECK_SNO(@sno)==0)
Begin
Insert into sc values(‘200515023’,’02’,75)
End
1
2
3
4
5
6

(3)定义一函数，按系别统计当前所有学生的平均年龄，并调用该函数求出“CS”系学生的平均年龄。

Create fnuction f(@sdept varchar(10))
Return int
As
Begin
Declare @pjnl int
Select @pjnl = avg(sage) from student where sdept=@sdept
Return @pjnl
End
1
2
3
4
5
6
7
8

Declare @sdept varchar(10),@cnt int
Set @sdept =’CS’
Set @cnt = f(@sdept)
Select @cnt
1
2
3
4

5．存储过程

实验代码、实验过程（界面方式下的抓图）、实验结果（抓图） （1）创建一个无参存储过程pr_StuScore，查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。

Create proc pr_StuScore
As
Select  a.sdept,a.sno,a.sname,a.sex,b.cname,c.grade
From student a,course b,sc c
Where c.sno=a.sno and c.cno=b.cno
1
2
3
4
5

（2）创建一个带参数的存储过程pr_StuScoreInfo2，该存储过程根据传入的学生编号和课程名称查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。

Create proc pr_StuScoreInfo2(@sno varchar(10),@cname varchar(10))
As
Select a.sdept,a.sno,a.sname,a.sex,b.cname,c.grade
From student a,course b,sc c
Where a.sno = c.sno and b.cno = c.cno and a.sno = @sno and b.cname = @cname
1
2
3
4
5

（3）创建一个带参数的存储过程pr_xxscore，该存储过程根据传入的学生编号和课程号，并通过变量输出该学生的课程成绩。

Create proc pr_xxscore(@sno varchar(10),@cno varchar(10),@res int output)
As
Select @res = grade from sc where sno=@sno and cno=@cno
1
2
3

（4）创建存储过程，通过给定两个学生的学号，比较两个学生的年龄，若前者比后者高就输出0，否则输出1。（调用时比较200515001号和200515002号的年龄）。

Create proc pr_compare(@sno1 varchar(10),@sno2 varchar(10))
As
Begin
Declare @age1,@age2
Select @age1 = age from student where sno=@sno1
Select @age2 = age from student where sno=@sno2
If(@age>@age2)print ‘0’
Else print ‘1’
End
1
2
3
4
5
6
7
8
9

Exec pr_compare ‘200515001’,‘200515002’
1

（5）编写带参数的存储过程pr_cavg，根据传入的课程名称统计该课程的平均成绩。

Create proc pr_cavg(@cname varchar(10),@res int output)
As
Begin
Select @res = avg(grade) 
From coure a,sc c 
Where a.cno = c.cno and a.cname = @cname  
End
1
2
3
4
5
6
7

（6）创建一存储过程pr_age，用于统计某年龄段所有学生的学号、姓名及所选课程信息。

Create proc pr_age(@age1 int ,@age2 int)
As
Begin
Select a.sno,a.sname,b.cno,b.cname,b.ccredit
From student a,course b,sc c
Where (a.sage between @age1 and @age2 ) and a.sno=c.sno and b.cno = c.cno
End
1
2
3
4
5
6
7

（7）创建一个添加学生记录的存储过程stduentadd，用来给student表添加一条记录。（‘200515028’,‘徐小明’,‘男’,24,‘CS’）

Create proc studentadd(@sno varchar(10),@sname varchar(10),@sex varchar(2),@age int,@sdept varchar(10))
As
Begin
Insert into student values(@sno,@sname,@sex,@age,@sdept)
End
1
2
3
4
5