SQL2008课后习题答案实验7

更新时间:2023-06-22 12:32:37 阅读: 评论:0

SQL2008课后习题答案实验7
1.存储过程
1>创建存储过程,使用Employees表中的员工人数来初始化一个局部变量,并调用这个存储过程
create procedure test @number1 int output
as
begin
declare @number2 int
t @number2=(lect COUNT (*)from Employees)
t @number1=@number2
end
fearful是什么意思
执行该存储过程,并查看结果;
declare @num int
exec test @num output
lect @num
2>创建存储过程,比较两个员工的实际收入,若前者比后者高就输出0,否则输出1;
create procedure COMPA @ID1 char(6),@ID2 char(6),@BJ int output
as
rfa
begin
declare @SR1 float,@SR2 float
lect @SR1=InCome-OutCome from Salary where EmployeeID=@ID1
lect @SR2=InCome-OutCome from Salary where EmployeeID=@ID2
if @ID1>@ID2
t @BJ=0
el
t @BJ=1
end
执行该存储过程,并查看结果;
declare @BJ int
i kisd a girlexec COMPA '000001','108991',@BJ output
lect @BJ
3>创建添加职员记录的存储过程EmploeeAdd;
create procedure EmployeeAdd
(
@EmployeeID char(6),@Name char(10),@Education char(4),@Birthday datetime,
@WorkYear tinyint,@Sex bit,@Address char(40),@PhoneNumber char(12),
@DepartmentID char(3)
)as
begin
inrt into Employees
values(@EmployeeID,@Name,@Education,@Birthday,@WorkYear,@Sex,@Address,@PhoneNumber,@DepartmentID)
end
return
go
执行该存储过程;
exec EmployeeAdd '990230','刘朝','本科','840909',2,1,'洪武小洪山5号','85465213','3'msconfig
4>创建一个带有output游标参数的存储过程,在Employees表中声明并打开一个游标;
create procedure em_cursor @em_cursor cursor varying output
as
begin
t @em_cursor=CURSOR forward_only static
FOR
lect * from Employees
open @em_cursor
end
声明一个局部游标变量,执行上述存储过程,并将游标赋值给局部游标变量,然后通过该游标变量读取记录;
declare @MyCursor cursor
exec em_cursor @em_cursor=@MyCursor output
fetch next from @MyCursor肇事者 英文
while (@@FETCH_STATUS=0)
begin
fetch next from @MyCursor
end
clo @MyCursor
deallocate @MyCursor
有限责任公司英文
go
5>创建存储过程,使用游标确定一个员工的实际收入是否排在前三名。结果为1表示是,结果为0表示否;
幼儿英语儿歌create procedure top_three @em_id char,@ok bit output
as
begin
declare @x_em_id char(6)
declare @act_in int,@q int
declare salary_dis cursor for
lect EmployeeID,InCome-OutCome from Salary
order by InCome-OutCome desc
t @q=0
t @ok=0
open salary_dis
fetch salary_dis into @x_em_id,@act_in
while @q<3 and @ok=0
begin
t @q=@q+1
if @x_em_id=@em_id
t @ok=1
damn itfetch salary_dis into @x_em_id,@act_in
end
clo salary_dis
deallocate salary_dis
end
执行该存储过程,并查看结果;
declare @ok bit
exec top_three '108991',@ok output
lect @ok
6>创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工
作;
7>创建存储过程,根据每个员工的学历将收入提高500元;
8>创建存储过程,使用游标计算本科及以上学历的员工在总员工数中所占的比例;文具盒的英文
9>使用命令方式修改存储过程的定义;
2.触发器
1>向Employees表插入或修改一个记录时,通过触发器检查记录的DepartmentID值在Departments表中是否存在,若不存在,则取消插入或修改操作;
create trigger EmployeesIns on dbo.Employees
for inrt,update
as
begin
if ((lect DepartmentID from inrted)not in
(lect DepartmentID from Departments))
rollbackgreen
end
向Employees表插入或修改一行记录,查看效果;

本文发布于:2023-06-22 12:32:37,感谢您对本站的认可!

本文链接:https://www.wtabcd.cn/fanwen/fan/90/153700.html

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。

标签:过程   游标   员工   记录   使用   创建   修改
相关文章
留言与评论(共有 0 条评论)
   
验证码:
Copyright ©2019-2022 Comsenz Inc.Powered by © 专利检索| 网站地图