1、实验八实验八 实 验 报 告 课程名称：SQL Server 数据库基础 任课教师：池宗琳 实验名称：存储过程 年级、专业：2018 级电子信息工程 学 号：20181060093 姓 名：马 信 日期：2019 年 11 月 28 日 云南大学 信息学院 一、实验目的 1、掌握使用 SELECT语句实现对数据库的简单查询 2、掌握使用 SELECT 语句实现对数据库的多表链接查询和子查询 二、实验内容、方法、步骤、结果与分析 完成以下各题功能，保存或记录实现各题功能的 Transact-SQL 语句。1.在数据库 HrSystem 中创建存储过程 avg._wage,用于求所有员工的平均工资
2、，并通过输出参数返回该平均工资。要求在创建存储过程之前要首先判断该存储过程是否已经存在，如果存在，则将其删除。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 DEC
3、LARE 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
4、 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
5、Hrsystem GO DROP PROCEDURE max_wage GO DROP PROCEDURE avg_wage(二)触发器 创建一个“学生信息”数据库，包含“学生基本信息”表、“专业”表和“系”表，各表包含的字段如下。“学生基本信息”表:学号;姓名;性别;班级;出生日期;专业编号。“专业”表:专业编号;专业名称;系编号。“系”表:系编号;系名称;系简介。各字段类型按其实际含义自行定义，输人-些数据，要求数据要有代表性。以下操作要求全部在 SQL Server Management Studio 中完成，保存或记录实现各题功能的 Transcat-SQL 语句(包括测试相应触发器是
6、否生效的相关语句及测试结果)。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
7、)+专业名：+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”，当发生更新“专业名称”字段的操作时，给出警告并撤销更新 US
8、E 学生信息 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=系编
9、号 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
10、 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=学号
11、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.