内容发布更新时间 : 2024/12/23 12:03:35星期一 下面是文章的全部内容请认真阅读。
数据库技术 实验 成绩 课程名称实验名称 学号 存储过程和触发器的使用 姓名 班级 日期 14.11.25 实验目的: 1. 掌握存储过程的使用方法; 2. 掌握触发器的实现方法; 实验平台: 利用RDBMS(SQL Server 2008)及其交互查询工具(查询分析器)来操作T-SQL语言; 实验内容: 1. 存储过程 (1) 创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作。 alter procedure yuangong_infol @EmployeeID char(6),@name nchar(20) as begin declare @year int set @year=( select WorkYear from Employees where EmployeeID=@EmployeeID) declare @DepartmentID char(3) set @DepartmentID=( select DepartmentID from Departments where DepartmentName=@name) if (@year>6) update Employees set DepartmentID=@DepartmentID where EmployeeID=@EmployeeID End exec dbo.yuangong_infol '000000','经理办公室' (2) 创建存储过程,根据每个员工的学历将收入提高500元。 alter proc SA_IN @enu char(6) as begin update Salary set InCome=InCome+500 from Salary,Employees where Employees.EmployeeID=Salary.EmployeeID and Education=@enu end select InCome from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID and Education='本科' go exec dbo.sa_in '本科' go select InCome from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID and Education='本科' select InCome from Salary,Employees where Salary.EmployeeID=Employees.EmployeeID and Education='本科' (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,