数据库技术实验六.doc

1、课 程 名 称 数 据 库 技 术实 验 名 称 存 储 过 程 和 触 发 器 的 使 用实 验成 绩学 号 姓 名 班 级 日 期 14.11.25实 验 目 的 ：1. 掌 握 存 储 过 程 的 使 用 方 法 ；2. 掌 握 触 发 器 的 实 现 方 法 ；实 验 平 台 ：利 用 RDBMS（ SQL Server 2008） 及 其 交 互 查 询 工 具 （ 查 询 分 析 器 ） 来 操 作T-SQL 语 言 ；实 验 内 容 ：1. 存 储 过 程（ 1） 创 建 存 储 过 程 ， 要 求 当 一 个 员 工 的 工 作 年 份 大 于 6 年 时 将 其 转 到 经

2、理办 公 室 工 作 。alter procedure yuangong_infol EmployeeID char(6),name nchar(20)as begindeclare year intset year=(select WorkYearfrom Employeeswhere EmployeeID=EmployeeID)declare DepartmentID char(3)set DepartmentID=(select DepartmentIDfrom Departmentswhere DepartmentName=name)if (year6)update Employees

3、set DepartmentID=DepartmentIDwhere EmployeeID=EmployeeIDEndexec dbo.yuangong_infol 000000,经理办公室（ 2） 创 建 存 储 过 程 ， 根 据 每 个 员 工 的 学 历 将 收 入 提 高 500 元 。alter proc SA_IN enu char(6)asbeginupdate Salaryset InCome=InCome+500from Salary,Employeeswhere Employees.EmployeeID=Salary.EmployeeID and Education=en

4、uendselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科goexec dbo.sa_in 本科goselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education=本科select InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Ed

5、ucation=本科（ 3） 创 建 存 储 过 程 ， 使 用 游 标 计 算 本 科 及 以 上 学 历 的 员 工 在 总 员 工 数 中 的 比例 。declare edu varchar(10), part_count int, all_count int ;declare mycursor cursor for select distinct education, COUNT(education) over(partition by education) as part_count, COUNT(education) over() as all_count from Employe

6、es open mycursorfetch next from mycursor into edu,part_count,all_count while FETCH_STATUS=0begin print edu+占总人数比例：+convert (varchar(100),convert(numeric(38,2),part_count/1.0/all_count*100)+% fetch next from mycursor into edu,part_count,all_countendclose mycusordeallocate mycursor（ 4） 使 用 命 令 方 式 修 改

7、 及 删 除 一 个 存 储 过 程 。if exists(select workyear from Employees where workyear=3)drop procedure workyear2. 触 发 器（ 1） 对 于 YGGL 数 据 库 ， 表 Employees 的 Employeeid 列 与 表 Salary 的Employeeid 列 应 满 足 参 照 完 整 性 规 则 ， 请 用 触 发 器 实 现 两 个 表 间 的 参 照 完 整性 。create trigger Salaryins0 on Salaryfor insert,update asbegini

8、f(select employeeid from inserted) not in(select EmployeeID from Employees)rollbackendcreate trigger Employeesupdate0 on dbo.Employeesfor updateasbeginupdate Salaryset employeeid=(select employeeid from inserted)where employeeid=(select employeeid from deleted)endcreate trigger Employeesdelete0 on E

9、mployeesfor deleteasbegindelete from Salarywhere employeeid=(select employeeid from deleted)end（ 2） 当 修 改 表 Employees 时 ， 若 将 Employees 表 中 员 工 的 工 作 时 间 增 加 1年 ， 则 将 收 入 增 加 500， 若 增 加 2 年 则 增 加 1000， 依 次 增 加 。 若 工 作 时 间 减少 则 无 变 化 。create trigger em_workyear on Employeesafter updateasbegindeclare

10、a int,b intset a=(select workyear from inserted)set b=(select workyear from deleted)if(ab)update Salaryset income=income + (a-b)*500where enployeeid in(select EmployeesID from inserted)endupdate Employeesset workyear=12where EmployeesID=000001（ 3） 创 建 UPDATE 触 发 器 ， 当 Salary 表 中 InCome 值 增 加 500 时 ，

11、 outCome值 则 增 加 50。create trigger sa_income on Salaryfor update as beginif(select income from inserted)-(select income from deleted)=500)update Salary set outcome=outcome+50where enployeeid=(select enployeeid from inserted)end select income,outcomefrom Salary where enployeeid=000001（ 5） 创 建 INSTEAD

12、OF 触 发 器 ， 实 现 向 不 可 更 新 视 图 插 入 数 据 。create view a_viewasselect Employees.EmployeesID,name,workyear,income,outcomefrom Employees,Salarywhere Employees.EmployeesID=Salary.enployeeidgocreate trigger gxston a_viewinstead of insertasbegindeclare Ei char(6),name char(10),wy tinyint,ic float,oc floatsele

13、ct Ei=EmployeesID,name=name,wy=workyear,ic=income,oc=outcomefrom insertedinsert into Employees(EmployeesID,name,workyear) values(Ei,name,wy)insert into Salary values(Ei,ic,oc) endinsert into a_viewvalues(000011,小芳, 3,2000,1500)select * from a_viewwhere EmployeesID=000011（ 5） 创 建 DDL 触 发 器 ， 当 删 除 数

14、据 库 时 ， 提 示 “无 法 删 除 ”并 回 滚 删 除 操作 。create trigger table_delete on database after drop_tableas print不能删除表 rollback transaction go drop table YGGL实 验 总 结 （ 结 论 或 问 题 分 析 ） ：在 本 次 实 验 中 ， 感 觉 很 难 ， 对 触 发 器 和 存 储 过 程 不 是 很 了 解 ， 最 后 老 师 讲了 ， 自 己 通 过 讲 的 虽 然 说 做 出 来 了 ， 但 是 还 是 有 不 明 白 的 地 方 ， 需 要 自 己 在 下面 复 习 巩 固 。