数据库实验报告

实验五

实验题目：过程

指导老师：李萍

专业班级：计算机科学与技术系1001班        　　         

姓    名：刘萌（2010100155）                         

20##年 12月1日

实验类型__验证__           实验室_软件实验室一__

一、实验题目

过    程

二、实验目的和要求

掌握用户存储过程的创建，了解一些常用的系统存储过程，以及调用和删除过程，并熟悉使用存储过程来进行数据库应用程序的设计。

1．基于学生—课程数据库创建一存储过程，用于检索数据库中某个专业学生的人数，带有一个输入参数，用于指定专业。

create procedure pro_s  @stu_sdept  varchar(5) 

//这是带参数的过程，参数不用（）括

as

select count(*) as 人数 from student

where sdept = @ stu_sdept

1．存储过程的执行

execute pro_s 实参  //实参可以是变量，也可以是常量

2．基于学生-课程数据库创建一存储过程，该过程带有一个输入参数，一个输出参数。其中输入参数用于指定学生的学号，输出参数用于返回学生的平均成绩。

   create procedure pro_stu  @stu_sno char(6),

                        @stu_avg float output 

//这个带output的是输出参数

   as

      select @stu_avg = avg(grade)  //将平均值给了变量

      from student,sc

      where student. sno = sc. sno and student.sno=@stu_sno

3．存储过程的执行

declare @stuavg float  //用于存放输出变量内容的

execute pro_stu ‘1000’，@stuavg output// 输出参数必须是变量

select @stuavg //看结果

4．在pubs数据库中建立一个存储过程，用于检索数据库中某一价位的图书信息。参数有两个，用于指定图书价格的上下限。如果找到满足条件的图书，则返回0，否则返回1。

create procedure pro_title  @pro_minnprice money,

                      @pro_maxprice moneye

as

  if  exists

       (select price from titles

        where price>=@ pro_minnprice and

              price<=@ pro_maxprice)

      return 0

   else

      return 1

5．存储过程的执行

6．存储过程的删除

三、  实验内容

基本表的建立：

Title：                      sc：

Stu：

1.基于学生—课程数据库创建一存储过程，用于检索数据库中某个专业学生的人数，带有一个输入参数，用于指定专业。

create procedure pro_s  @stu_sdept  varchar(5) 

//这是带参数的过程，参数不用（）括

as

select count(*) as 人数 from student

where sdept = @ stu_sdept

2.存储过程的执行

execute pro_s 实参  //实参可以是变量，也可以是常量

3.基于学生-课程数据库创建一存储过程，该过程带有一个输入参数，一个输出参数。其中输入参数用于指定学生的学号，输出参数用于返回学生的平均成绩。

   create procedure pro_stu  @stu_sno char(6),

                        @stu_avg float output 

//这个带output的是输出参数

   as

      select @stu_avg = avg(grade)  //将平均值给了变量

      from student,sc

      where student. sno = sc. sno and student.sno=@stu_sno

执行结果：

4．存储过程的执行

declare @stuavg float  //用于存放输出变量内容的

execute pro_stu ‘1000’，@stuavg output// 输出参数必须是变量

select @stuavg //看结果

5．在pubs数据库中建立一个存储过程，用于检索数据库中某一价位的图书信息。参数有两个，用于指定图书价格的上下限。如果找到满足条件的图书，则返回0，否则返回1。

create procedure pro_title  @pro_minnprice money,

                      @pro_maxprice moneye

as

  if  exists

       (select price from titles

        where price>=@ pro_minnprice and

              price<=@ pro_maxprice)

      return 0

   else

      return 1

6．存储过程的执行

execute pro_qq price

7．存储过程的删除

四、  实验总结

通过本次试验，我掌握了用户存储过程的创建，了解一些常用的系统存储过程，以及调用和删除过程，并熟悉使用存储过程来进行数据库应用程序的设计。过程遇到了很多困难，通过与同学的讨论，研究，把困难一一击破。

 

第二篇：数据库应用实验报告视图存储过程触发器等的建立与维护

内蒙古工业大学信息工程学院

实 验 报 告

课程名称： 数据库应用 实验名称： 视图存储过程触发器等的建立与维护 实验类型： 验证性□ 综合性□ 设计性□

实验室名称： 班级： 学号：

姓名： 组别：

同组人： 成绩：

实验日期：

预习报告成绩： 指导教师审核（签名）： 年 月 日

预习报告

一、实验目的

1．学会使用企业管理器建立视图，应用视图插入、删除、修改数据；

2．掌握存储过程的使用方法；

3．掌握触发器的使用方法。

二、实验内容

此实验是综合视图、存储过程、触发器等知识应用的一个综合性实验。

实验要求：

1．利用所创建的数据库和数据表，综合应用视图、存储过程、触发器等知识完善数据库；

2．掌握应用更新视图数据可以修改基本表数据的方法；

3．熟练掌握添加、修改、删除记录的存储过程的定义及调用；

4．掌握通过触发器来实现数据的参照完整性。

实验内容要求：

利用员工管理数据库YGGL中 3个表：

Employees：员工自然信息表、Departments：部门信息表、Salary：员工薪水情况表。

（1） 利用YGGL各表建立视图实现各种连接查询。建立视图view1,查询所有职工的员工编

号、姓名、部门名和收入，并按部门名顺序排列。建立视图view2，查询所有职工的员工编号、姓名和平均工资。建立视图view3，查询各部门名和该部门的所有职工平均工资。

（2） 编写对YGGL各表进行插入、修改、删除操作的存储过程，然后编写程序，调用这些

存储过程。创建一个为Employees表添加员工记录的存储过程addEmployees。创建一个存储过程delEmployees删除Employees表中指定员工编号的记录。

（3） 对于YGGL数据库，请用触发器实现两个表间的参照完整性。在表Departments上创

建一个触发器Departments _update，当更改部门编号时同步更改Employees表中对应的部门编号。在表Employees上创建一个触发器Employees _delete，当删除员工记录时同步删除salary表中对应的工资收入记录。

参考实例步骤：

1.创建视图

（1）班级表（U_CLASSES ）：ID含义为"班号",CLASS含义为"班名",DEPARTMENT含义为所在 系,各字段类型按需要设置是否允许为空，ID字段被设置为主键。

（2）成绩表（U_SCORES ）：STUDENT_ID含义为学号,COURSE_ID含义为课程号,SCORE为成绩,各字段类型按需要设置是否允许为空，STUDENT_ID 、COURSE_ID字段被设置为主键。

（3）课程表（U_COURSES）：COURSE含义为课程名称， ID含义为课程编号，CREDIT含义为课程学分。

（4）学生表

1. 用企业管理器建立一个基于学生表、课程表、成绩表的视图，要求该视图显示学号、

姓名、课程、成绩。

1) 启动企业管理器、注册、连接

2) 展开服务器、数据库、在视图上右击，在快捷菜单中执行"新建视图(V)..."

3) 在新视图窗口内的关系图窗格内右击鼠标，弹出的菜单即为视图设计菜单，执行"

添加表(B)..."

4) 再在添加表对话框中选择U_SCORES表，再单击添加按钮。

5) 依此操作，分别添加U_STUDENTS、U_COURSES表，单击关闭按钮。

6) 再在关系窗格内，拖动U_STUDENTS表的"ID"至U_SCORES的STUDENT_ID,拖动

U_COURSES表的"ID"至U_SCORES的COURSE_ID,再分别选中U_STUDENTS表的"ID","NAME"列(列前的复选框),U_COURSES表的"COURSE"列以及U_SCORES表的"SCORE"列,然后单击"!"按钮,显示视图结果。

7) 单击保存按钮,将视图保存为V_SCORES,单击确定。

2. 用查询分析器建立一个基于学生表、班级表的学生视图(V_STUDENTS),包括学号、姓

名、班级、系,SQL语句如下：

1) CREATE VIEW dbo.V_STUDENTS

AS

SELECT dbo.U_STUDENTS.ID,dbo.U_STUDENTS.NAME, dbo.U_CLASSES.CLASS, dbo.U_CLASSES.DEPARTMENT

FROM dbo.U_STUDENTS INNER JOIN

dbo.U_CLASSES ON dbo.U_STUDENTS.CLASS_ID = dbo.U_CLASSES.ID

3. 自己写一个SQL语句建立一个基于课程表的视图(V_COURSES)，要求显示课程编号、

课程名、学分。

2．创建存储过程

在查询分析器编辑窗口输入各存储过程的代码并执行以下程序。

(1) 添加职员记录的存储过程EmployeeAdd：

USE YGGL

GO

CREATE PROCEDURE Emplo)reeAdd

(@employeeid char6)，@name char(10)，@birthday datetime，

@sex bit，@address char(20)，@zip char(6)，@phonenumber char(12)， @emailaddress char(20)，@departmenflD char(3))

AS

BEGIN

INSERT INTO Employees

VALUES(@employeeid，@name，@birthday，@sex，@address，

@zip，@phonenumber,@emailaddress，@departmentlD)

END

RETURN

GO

(2) 修改职员记录的存储过程EmployeeUpdate：

USE YGGL

GO

CREATE PROCEDURE EmployeeUpdate

(@empid char(6)，@employeeid char(6)，@name char(10)，@birthday datetime， @sex bit，@address char(20)，@zip chat(6),@phonenumber char(12)，

@emailaddress char(20)，@departmentlD char(3))

AS

BEGIN

UPDATE Employees ‘

SET Employeeid=@employeeid，

Name=@name．

Birthday=@birthday，

Sex=@sex，

Address=@address．

Zip=@zip，

Phonenumber=-@phonenumber,

Emailaddree=@emailaddress．

DepartmentD=@departmenflD

WHERE Employeeid=@empid

END

RETURN

GO

(3) 删除职员记录的存储过程EmployeeDelete：

USE YGGL

GO

CREATE PROCEDURE EmployeeDelete

(@employeeid char(6))

AS

BEGIN

DELETE FROM Employees

WHERE Employeeid=@employeeid

END

RETURN

G0

3．调用存储过程

USE YGGL

EXEC EmployeeAdd’990230’，’刘朝’,’890909’,1,’武汉小洪山5号’，”，”，”，’3’ GO

USE YGGL

EXEC Employeeupdate’990230’，’990232’,’刘平’，’890909’ ,1,’武汉小洪山5号’，”，”，”，’2’ GO

USE YGGI，

EXEC EmployeeDelete’990232’

GO

分析一下此段程序执行时可能出现哪几种情况。

【思考与练习】

编写如下T-SQL程序：

(1) 自定义1个数据类型，用于描述YGGL数据库中的DepartmentlD字段，然后编写代码

重新定义数据库各表。

(2) 编写对YGGL各表进行插入、修改、删除操作的存储过程，然后，编写l段程序调用

这些存储过程。

(3)对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列应满足参照完整性规则，请用触发器实现两个表问的参照完整性。

4.创建触发器

对于YGGL数据库，表Employees的DepartmentID列与表Departments的DepartmentID列对应满足参照完整性规则，即：

（1） 向Employees表添加1条记录时，该记录的DepartmentID值在Departments

表中应存在。

（2） 修改Departments表DepartmentID 字段值时，该字段在Employees表中的对

应值也应修改。

（3） 删除Departments表中1条记录时，该记录DepartmentID字段值在Employees

表中对应的记录也应删除。

对于上述参照完整性规则，在此通过触发器实现。

在查询分析器编辑窗口输入各触发器的代码并执行：

①向Employees表插入或修改1条记录时，通过触发器检查记录的DepartmentID值在Departments表是否存在，若不存在，则取消插入或修改操作。

USE YGGL

GO

CREATE TRIGGER EmployeesIns on dbo.Employees

FOR INSRET,UPDATE

AS

BEGIN

IF((SELECT ins.departmentid from inserted ins)NOT IN

(SELECT departmentid FROM departments))

ROLLBACK

／*对当前事务回滚，即恢复到插入前的状态

END

②修改Departments表departmentID字段值时，该字段在Employees表中的对应值也进行相应修改。

USE YGGL

GO

CREATE TRIGGER DepartmentsUpdate on dbo．Departments

FoR UPDATE

AS

BEGIN

IF(COLUMNS_UPDATED()&01)>0

UPDATE Employees

SET DepartmentlD=(SELECT ins.DepartmentlD from INSERTED ins)

WHERE DepaxtmentlD=(SELECT DepartmentlD FROM deleted)

END

GO

③删除Departments表中1条记录的同时删除该记录departmentlD字段值在Employees表中对应的记录。

USE YGGL

GO

CREATE TRIGGER DepartmentsDelete On db.Departments

FOR DELETE

AS

BEGIN

DELETE FROM Employees

WHERE DepartmentlD=(SELECT DepartmentlD FROM deleted)

END

GO

【思考与练习】

上述触发器的功能用完整性的方法完成。

【思考与练习】

编写如下T-SQL程序：

（1） 自定义1个数据类型，用于描述YGGL数据库中的DepartmentlD字段，然后编写

代码重新定义数据库各表。

（2） 对于YGGL数据库，表Employees的EmployeelD列与表Salary的EmployeelD列

应满足参照完整性规则，请用触发器实现两个表间的参照完整性。

实验报告成绩： 指导教师审核（签名）： 年 月 日

实验报告

1. 添加职员记录的存储过程EmployeeAdd：

2. 修改职员记录的存储过程EmployeeUpdate：

3. 删除职员记录的存储过程EmployeeDelete

4. 调用存储过程

5. 向Employees表插入或修改1条记录时，通过触发器检查记录的DepartmentID值在Departments表是否存在，若不存在，则取消插入或修改操作。

6.修改Departments表departmentID字段值时，该字段在Employees表中的对应值也进行相应修改。

7. 删除Departments表中1条记录的同时删除该记录departmentlD字段值在Employees表中对应的记录