下载文档实
验
报
告
课程名称：
SQL
Server
数据库基础
任课教师：
池宗琳
实验名称：
存储过程
年级、专业：
2018级电子信息工程
学
号：
20181060093
姓
名：
马
信
日期：
2019
年
月
日
云南大学
信息学院
一、实验目的1、掌握使用SELECT语句实现对数据库的简单查询
2、掌握使用SELECT语句实现对数据库的多表链接查询和子查询
二、实验内容、方法、步骤、结果与分析
完成以下各题功能，保存或记录实现各题功能的Transact-SQL语句。
1.在数据库HrSystem中创建存储过程avg._wage,用于求所有员工的平均工资，并通过输出参数返回该平均工资。要求在创建存储过程之前要首先判断该存储过程是否已经存在，如果存在，则将其删除。
USE
Hrsystem
GO
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
＇avg_wage＇)
DROP
PROC
avg_wage
GO
CREATE
PROC
avg_wage
@AVWAGE
AS
FLOAT
AS
SELECT
@AVWAGE
=
AVG(Wage)
FROM
Employees
@AVWAGE
GO
2.执行第1题创建的存储过程avg_
wage,打印员工平均工资。
USE
Hrsystem
GO
DECLARE
@avg
AS
FLOAT
EXEC
avg_wage
@avg
3.在数据库HrSystem中创建存储过程max_
wage,根据指定的部门名称(输人参数)返回该部门的最高工资(输出参数)。要求在创建存储过程之前要首先判断该存储过程是否已经存在，如果存在，则将其删除。
USE
Hrsystem
GO
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
＇max_wage＇)
DROP
PROC
avg_wage
GO
CREATE
PROC
max_wage
@Dename
varchar(20),@MAX_wage
FLOAT
OUTPUT
AS
SELECT
@MAX_wage
=
MAX(Wage)
FROM
Employees
WHERE
Dep_id
IN(SELECT
Dep_id
FROM
Departments
WHERE
Dep_name
=
@Dename)
GROUP
BY
Dep_id
4.执行第3题创建的存储过程max
wage,指定部门为“财务部”，打印该类部门的最高工资。
USE
Hrsystem
GO
DECLARE
@MAX_wage
FLOAT
EXEC
max_wage
＇财务部＇,@MAX_wage
OUTPUT
@MAX_wage
5.删除存储过程avg_
wage和I
max_
wage。
USE
Hrsystem
GO
DROP
PROCEDURE
max_wage
GO
DROP
PROCEDURE
avg_wage
(二)触发器
创建一个“学生信息”数据库，包含“学生基本信息”表、“专业”表和“系”表，各表包含的字段如下。
“学生基本信息”表:学号;姓名;性别;班级;出生日期;专业编号。
“专业”表:专业编号;专业名称;系编号。
“系”
表:系编号;系名称;系简介。
各字段类型按其实际含义自行定义，输人-
-些数据，要求数据要有代表性。
以下操作要求全部在SQL
Server
Management
Studio
中完成，保存或记录实现各题功能的Transcat-SQL语句（包括测试相应触发器是否生效的相关语句及测试结果)。
1.在“专业”表上创建一个INSERT触发器“TRG1”。当发生插入专业表操作时，将显示插入的记录。
USE
学生信息
GO
CREATE
TRIGGER
TRG1
ON
专业
FOR
INSERT
AS
DECLARE
@depid
INT
DECLARE
@depname
varchar(50)
DECLARE
@number
INT
SELECT
@depid
=
专业编号
FROM
inserted
SELECT
@number
=
系编号
FROM
inserted
SELECT
@depname
=
专业名称
FROM
inserted
PRINT(＇系名：＇+STR(@depid)+＇专业名：＇+STR(@depname)+＇系的编号：＇+str(@number))
INSERT
INTO
专业
(专业编号,专业名称,系编号)
VALUES(@depid,@depname,@number)
2.在“专业”表上创建一个DELETE触发器“TRG2”，当发生删除操作时，将给出警告、列出删除的记录并撤销删除。
USE
学生信息
GO
CREATE
TRIGGER
TRG2
ON
专业
FOR
DELETE
AS
PRINT(＇警告！禁止删除＇)
ROLLBACK
TRANSACTION
3.在“专业”表上创建一个UPDTAE触发器“TRG3”，当发生更新“专业名称”字段的操作时，给出警告并撤销更新
USE
学生信息
GO
CREATE
TRIGGER
TRG3
ON
专业
FOR
UPDATE
AS
DECLARE
@temp_proid
INT
DECLARE
@temp_xiid
INT
DECLARE
@temp_porna
varchar(50)
SELECT
@temp_porna
=
专业名称
FROM
inserted
IF
@temp_porna
IS
not
NULL
BEGIN
PRINT(＇禁止修改专业名称＇)
ROLLBACK
TRANSACTION
END
ELSE
BEGIN
SELECT
@temp_porna
=
专业名称
FROM
deleted
SELECT
@temp_xiid
=
系编号
FROM
deleted
SELECT
@temp_proid
=
专业编号
FROM
deleted
UPDATE
专业
SET
专业编号
=
@temp_proid,系编号
=
@temp_xiid
WHERE
专业名称
=
@temp_porna
END
4.在“学生基本信息”表上创建一
一个更新触发器“TRG4“，当发生更新“学号”或“姓名”字段的操作时给出警告，并撤销更新。
USE
学生信息
GO
CREATE
TRIGGER
TRG4
ON
学生基本信息
FOR
UPDATE
AS
DECLARE
@temp_stunum
char(11)
DECLARE
@temp_name
char(10)
DECLARE
@temp_gender
BIT
DECLARE
@temp_class
varchar(10)
DECLARE
@temp_date
DATETIME
DECLARE
@temp_proID
INT
SELECT
@temp_name
=
姓名
FROM
inserted
SELECT
@temp_stunum
=
学号
FROM
inserted
IF
@temp_name
IS
NOT
NULL
OR
@temp_stunum
IS
NOT
NULL
BEGIN
PRINT(＇禁止修改学号或者姓名＇)
ROLLBACK
TRANSACTION
END
ELSE
BEGIN
SELECT
@temp_stunum
=
学号
FROM
deleted
SELECT
@temp_name
=
姓名
FROM
deleted
SELECT
@temp_gender
=
性别
FROM
inserted
SELECT
@temp_class
=
班级
FROM
inserted
SELECT
@temp_date
=
出生日期
FROM
inserted
SELECT
@temp_proID
=
专业编号
FROM
inserted
UPDATE
学生基本信息
SET
性别
=
@temp_gender,班级
=
@temp_class,出生日期
=
@temp_date,专业编号
=
@temp_proID
WHERE
学号
=
@temp_stunum
END
5.删除以
上各题创建的所有触发器。做好“学生信息”数据库的备份，以备第10章、第章上机操作时使用。
USE
学生信息
GO
DROP
TRIGGER
TRG1
DROP
TRIGGER
TRG2
DROP
TRIGGER
TRG3
DROP
TRIGGER
TRG4
三、实验小结【对自己而言，通过实验学到的关键技术方法】
掌握了触发器的一些基本方法：
1.创建触发器
2.分清了触发器的种类，但是还是需要深入了解dml触发器中三个种类触发器的不同。
3.了解了触发器在我们实际操作中的作用
4.
