实验八

实 验 报 告 课程名称：
SQL Server 数据库基础 任课教师：
池宗琳 实验名称：
存储过程 年级、专业：
2018级电子信息工程 学 号：
20181060093 姓 名：
马 信 日期：
2019 年 11 月 28 日 云南大学 信息学院 一、实验目的 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 PRINT @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 PRINT @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.