第11章 数据库应用实验指导
WHERE sex = @female
② 定义一个变量，用于获取号码为1212 的学生的家庭住址,并将该家庭住址的学生编号和姓名显示出来。
DECLARE @maddress char(40)
SELECT @maddress = (SELECT address FROM student WHERE stu_id = '1212') SELECT stu_id, name FROM student
WHERE address = @maddress
③使用CASE 语句对student 表按所在部门（dept_id）进行分类 SELECT stu_ID, Name, Address, dept_id = CASE dept_id
WHEN 1001 THEN '电子信息学院' WHEN 1002 THEN '机械学院部' WHEN 1003 THEN '电气学院' END
FROM student
(2)IF?ELSE语句
IF?ELSE条件控制语句是在执行T-SQL语句时强加条件。如果条件满足(布尔表达式返回TRUE时)，则在IF关键字及其条件之后执行T-SQL语句。可选的ELSE关键字引入备用的T-SQL语句，当不满足IF条件时(布尔表达式返回FALSE)，就执行这个语句。
若存在学号为“1212”的学生，则显示已存在的信息，否则插入该学生的记录。 要查询学号为“1212”的学生，可以使用SELECT语句和EXISTS函数完成， 具体实现步骤如下：
“查洵编辑框”窗口中输入以下实现上述功能的SQL程序：
USE teachingSystem GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_NULLS ON GO
IF EXISTS(SELECT stu_id FROM student WHERE stu_id='1212')
PRINT '已存在学号为1212的学生' ELSE
Insert into student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major) VALUES ('1212', N'1001', '陈静', '女', '1993-1-1' ,'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机')
②执行上述命令就可实现以上功能，图11-76是学生情况表中存在学号为'1212'的学生时的情况：可以从结果窗格中看到输出的信息。 图11-76 IF ELSE语句执行结果
（3）WHILE、BREAK和CONTINUE语句
WHILE语句一般用来设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行WHILE中的循环体。可以使用BREAK和CONTINUE关键字在循环内部设置条件来达到控制WHILE循环中语句的执行。
2.用户自定义函数的应用
用户可以使用CREATE FUNCTION语句编写自己的函数，以满足特殊需要。用户自定
数据库原理及应用学习与实践指导 SQL Server 2012
义函数，可以传递0个或多个参数，并返回一个简单的数值，一般来说返回的都是数值或字符型的数据。
（1）定义一个自定义函数，实现从出生年月到年龄的计算。自定义函数如下： create function re_year
(@vardate datetime,@curdate datetime) returns tinyint
as begin return datediff(yyyy,@vardate,@curdate) end
具体实现步骤为：
进入SSMS，选择要操作的服务器和数据库，单击“可编程性”下的“函数”，选择后单击鼠标右键，在弹出的快捷菜单中选择“新建”菜单并展开后选择“标量值函数”。
出现的“查询”窗口的“文本”输入框中输入需创建的用户定义函数，如图11-77所示。
图11-77 用户定义函数
（3）单击“执行”按钮，则系统在该数据库中创建了—个名为“Re_Year”的用户自定义函数。
（4）函数定义后，就可以在SQL语句中调用用户定义的函数完成指定的功能。
（5）进入SQL Server查询分析器界面，在“查询编辑框”窗口中输入如下的SQL语句：
---将用户定义函数Re_Year用在查询?student?中， ---直接给出学生情况的年龄。 USE teachingSystem GO
SELECT stu_id,name,sex,
dbo.Re_Year(birthday,GETDATE()) As 年龄 FROM student GO
第11章 数据库应用实验指导
图11-78 使用用户定义函数
⑺单击工具栏上的运行按钮执行上述SQL语句。语句执行后，从结果窗格中可以检索到学生情况表中的数据。
从运行结果中可以看出，每个学生通过用户定义函数Re_Year求得一个年龄。 上述过程也可以直接用SQL命令来完成“Re_Year”自定义函数的建立。
进入SQL Server查询分析器界面，在“查询编辑框”窗口中输入如下的SQL语句：
Use teachingSystem
if exists(SELECT * FROM dbo.sysobjects WHERE id=Object_id(N'[dbo].[Re_Year]') and xtype in
(N'FN',N'IF',N'TF'))
drop function [dbo].[Re_Year] GO
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS OFF GO
---创建?Re_Year?用户定义函数，该函数是将由所给出的日期（参数） ---计算出该日期与当前时间之间的年数(返回值) create function re_year
(@vardate datetime,@curdate datetime) returns tinyint as begin return datediff(yyyy,@vardate,@curdate) end GO
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS OFF GO
---将该用户定义函数用在查询?学生情况表?中，直接给出学生情况的年龄 USE teachingSystem GO
SELECT stu_id,name,sex,
dbo.Re_Year(birthday,GETDATE()) As 年龄 FROM student GO
用户的自定义函数不再使用时，可以使用DROP FUNCTION语句或在企业管理器中将其删除。
（2）删除上述案例中创建的名为“Re_Year”的用户自定义函数。
(1)进入SSMS，选择要操作的服务器和数据库，单击“可编程性”下的“函数”，选择
数据库原理及应用学习与实践指导 SQL Server 2012
“标量值函数”下面要删除的用户自定义函数，这里选择“Re_Year”。
(2)单击鼠标右键，在弹出的快捷菜单中选择“删除”，出现“删除对象”对话框。 (3)单击“确定”按钮，完成指定用户自定义函数的删除。 3.自行练习内容
（1）如果student表中有入校时间在2006年以后的学生，把该学生的学号，姓名和入学时间查询出来，否则输出“没有在2006年以后入学的学生”。（if..else）
（2）如果student表中有名叫“张思文”的学生，就把他的名字修改为“张思武”，并输出修改前后的学号，姓名，性别信息，否则输出“没有张思文这个人，所以无法修改啦！”
（3）查询student表，只要有年龄小于20岁的学生，就将每个学生的出生日期都加1个月，如此循环下去，直到所有的学生的年龄都不小于20岁。（while 循环）
（4）使用WHILE语句求1到100之间的累加和并输出 （5）定义一个用户自定义的函数Score_ReChange，将成绩从百分制转化为五级记分制。将该用户定义的函数用在查询每个学生的成绩中，给出五级记分制的成绩。
（6）定义一个用户自定义的函数，完成如下功能：如果学生有不及格的成绩，则在学生情况表的备注列中输入“有不及格的成绩”，否则输入“没有不及格的成绩”。
*11.6实验六 存储过程及触发器 11.6.1实验目的 （1）掌握SQL Server编程结构； （2）掌握数据存储过程及触发器使用； 11.6.2 实验内容及步骤 对teachingSystem数据库，编写存储过程，完成下面功能： 1．使用T-SQL语句创建存储过程
1）创建不带参数存储过程
（1）创建一个从student表查询学号为1202学生信息的存储过程proc_1，其中包括学号、姓名、性别、出生日期、系别等；调用过程proc_1查看执行结果。
use teachingSystem go
create proc proc_1 as
select stu_id,name, sex,birthday,dept_id from student
where sno=?1202? 执行： exec proc_1
（2）在teachingSystem数据库中创建存储过程proc_2，要求实现如下功能：查询学分为4的课程学生选课情况列表，其中包括学号、姓名、性别、课程号、学分、系别等。调用过程proc_2查看执行结果。
use teachingSystem go
create proc proc_2 as
select A.stu_id,name,sex,B.course_id,B.credit, B.dept_id from student A,course B, student_teacher_course C
where A.stu_id=C. stu_id and C.course_id=B.course_id and B.credit=4; 执行： exec proc_2
2）创建带参数存储过程
创建一个从student表中按学生学号查询学生信息的存储过程proc_3.其中包括：学号、姓名、性别、出生日期、系别等。查询学号通过执行语句中输入。
第11章 数据库应用实验指导
use teachingSystem go
create proc proc_3 @sno char(6) as
select stu_id,name,sex,birthday,dept_id from student
where stu_id=@sno 执行：
use teachingSystem go
exec proc_3 ?1212?
3）创建带输出参数存储过程
创建存储过程，比较两个学生的实际总分，若前者高就输出0，否则输出1
CREATE PROCEDURE PROC4
(@ID1 char(6), @ID2 char(6),@result int out ) AS BEGIN
DECLARE @SR1 int, @SR2 int
SET @SR1=(select totalscore FROM student WHERE stu_id= @ID1) SET @SR2=(select totalscore FROM student WHERE stu_id = @ID2) IF @SR1 > @SR2 SET @result = 0 ELSE
SET @result = 1 END
执行该存储过程，并查看结果 DECLARE @result int
EXEC PROC4 '1201', '1202', @result OUTPUT SELECT @result
2．使用T-SQL语句查看、修改和删除存储过程 （1）查看存储过程proc_2、proc_4定义
Exec sp_helptext proc_2
Exec sp_helptext proc_4
（2）删除存储过程proc_1 Drop proc proc_1
3．使用T-SQL语句实现触发器定义。
（1）为表student_teacher_course创建一个插入触发器，当向表student_teacher_course中插入一条数据时，通过触发器检查记录的stu_id值在表student中是否存在，若不存在，则取消插入操作,并检查course_id在表course中是否存在，若不存在也取消插入操作。
create trigger credit_insert on student_teacher_course for insert, UPDATE as
IF (SELECT stu_id FROM inserted) NOT IN (SELECT stu_id FROM student) BEGIN ROLLBACK END
IF (SELECT course_id FROM inserted) NOT IN (SELECT course_id FROM course) BEGIN ROLLBACK END
执行：
insert into student_teacher_course(course_id,teacher_id,stu_id,score) values('100001','30102','1205',90)
（2）为表student创建一个删除触发器，当删除表student中一个学生的资料时，将表sc中相应的成绩数据删除。
use teachingSystem go
If exists(select name from sysobjects where name= 'student_delete'and type='tr') Drop trigger student_delete