发布时间 : 星期日 文章第11章 数据库应用实践指导 - 图文更新完毕开始阅读a401d1785f0e7cd18425367a
第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
数据库原理及应用学习与实践指导 SQL Server 2012
go
create trigger student_delete on Student for delete as
declare @Sno1 int
select @Sno1=deleted.stu_id from deleted delete from student_teacher_course
where student_teacher_course.stu_id=@Sno1
（3）为表student_teacher_course学生选课创建一个更新触发器，当更改表student_teacher_course的成绩数据时，如果成绩由原来的小于60分更改为大于60分，该学生能得到相应学分，如果由原来的大于等于60更改为小于等于60分，则相应学分改为0。
create trigger credit_update on student_teacher_course for update as
declare @credit0 int declare @grade0 int
select @grade0=inserted.score from inserted
select @credit0=course.credit from course,inserted where course.course_id=inserted.course_id if (@grade0>=60) begin
update student_teacher_course set student_teacher_course.credit =@credit0
from student_teacher_course,inserted
where student_teacher_course.course_id=inserted.course_id and student_teacher_course.stu_id
=inserted.stu_id
end else begin
update student_teacher_course set student_teacher_course.credit=0 from student_teacher_course,inserted
where student_teacher_course.course_id=inserted.course_id and student_teacher_course.stu_id
=inserted.stu_id
end 执行：
update student_teacher_course set score=80 where stu_id='1201' and course_id='100001'
11.7实验七 数据库应用系统设计 11.7.1实验目的 （1）综合应用数据建模工具、SQLServer2012、.NET、Java软件开发语言等多门课程的知识，规范、科学地完成一个小型数据库应用系统的设计。
（2）掌握数据库系统的基本概念、原理和技术，掌握SQL Server等常用数据库管理系统软件的使用，掌握软件开发工具的使用及软件开发的一般步骤与方法，从而提高其系统分析、软件设计、数据库应用及团队开发的能力
（3）熟悉SQL Server 2012数据库管理系统的应用开发，在Microsoft Visual Studio下，通过VB.net或C#语言开发一个windows数据库应用程序（C/S模式）或开发一个Web数据库应用程序（B/S模式）。 11.7.2 实验内容及步骤 在本实验中，学生可选择自己相对比较熟悉的应用系统业务模型。要求学生通过本实验能较好地巩固数据库的基本概念、基本原理、关系数据库的设计理论、设计方法等主要相关知识点，针对实际问题设计概念模型，并应用现有的软件开发工具完成小型数据库应用系统的设计与实现。
读者可使用VB.NET或C#语言，通过ADO.NET技术，读者自行创建一个数据库应用系统。使用户可以通过Windows应用程序向系统数据库中添加、修改和删除数据。系统数