数据库—游标(基于SQL Server)

博客中用到的数据库脚本文件:https://download.csdn.net/download/sunshine543123/12087175

(1) 针对EMPLOYEE表定义一个只读游标,查询男性员工的姓名、年龄、工资和所在部门名称(按工资由高到低排序)。查询结果范例如下图所示:在这里插入图片描述

go 
declare employ_cursor CURSOR 
for
select e.FNAME,LNAME,YEAR(GETDATE())-YEAR(e.BDATE) as AGE,SALARY,d.DNAME
from Employee e,Department d
where e.SEX='M' and e.DNO=d.DNUMBER
order by SALARY DESC

declare 
@fname varchar(20),        
@lname varchar(20),        
@age int,        
@salary int,       
@dname varchar(20),        
@count int,        
@i int
select @i=COUNT(*) from Employee where SEX='M'
set @count=0 
open employ_cursor 
fetch next from employ_cursor into @fname,@lname,@age,@salary,@dname 
while @@FETCH_STATUS=0
begin if(@count=0) 
print '工资最高的男性员工'
else if(@count=@i-1)
print '工资最低的男性员工'
else 
print '工资第'+convert(varchar(20),@count+1) +'高的男性员工'
print '姓名:'+@fname+' '+@lname
print '年龄:'+convert(varchar(20),@age)
print '工资:'+convert(varchar(20),@salary) print '所在部门:'+@dname
print '-------------------'
fetch next from employ_cursor      
into @fname,@lname,@age,@salary,@dname
set @count=@count+1
end 

close employ_cursor
deallocate employ_cursor
go

(2) 针对EMPLOYEE表定义一个游标,将游标中绝对位置为3的员工姓名改为你的拼音姓名,并将性别改为你的性别

declare cur2 scroll cursor
for
select ssn from Employee
open cur2
declare @s varchar(20)
fetch absolute 3 from cur2 into @s
update Employee set FNAME='111' , lname='111', SEX='F'
where ssn=@s
close cur2
deallocate cur2

(3) 针对EMPLOYEE表定义一个游标,将员工表中你的姓名那行员工删除

declare cur3  cursor
for
select FNAME,lname from Employee
declare @fname1 varchar(20),@lname1 varchar(20)
open cur3
fetch next from cur3 into @fname1,@lname1
while (@@FETCH_STATUS=0)
begin
 if (@fname1='111' and @lname1='111')
 delete employee where current of cur3
fetch next from cur3 into @fname1,@lname1
end 
close cur3
deallocate cur3

(4) 在员工表中增加一列“参加的项目总数”。创建一个游标统计员工参加的项目数,然后填入员工表中“参加的项目总数“列中。

go
alter table Employee add pronum int

declare employ_cursor CURSOR
for
select SSN from Employee

declare @ssn varchar(20),@i int
open employ_cursor
fetch next from employ_cursor into @ssn
while @@FETCH_STATUS=0
begin
select @i=COUNT(*) from WORKS_ON where ESSN=@ssn
update Employee set pronum=@i where SSN=@ssn
fetch next from employ_cursor into @ssn
end
close employ_cursor
deallocate employ_cursor

(5)创建一个多语句表值函数,函数自变量为部门名称,返回值为指定部门员工的项目参与情况和累计参与项目总小时数,如下图所示在这里插入图片描述

CREATE FUNCTION F_员工参与项目(@dpname VARCHAR(20))
RETURNS @员工参与项目表 TABLE (SSN CHAR(9), 
        FNAME VARCHAR(20), 
        LNAME VARCHAR(20),
        PNAME VARCHAR(50), 
        HOURS REAL)
AS
BEGIN
DECLARE @ssn1 CHAR(9),@fn varCHAR(20),@ln varchar(20), @totalhours real
DECLARE CUR_empdpname CURSOR
FOR SELECT ssn,fname,lname FROM Employee e , Department d WHERE dname=@dpname and e.DNO=d.DNUMBER
OPEN CUR_empdpname
FETCH NEXT FROM CUR_empdpname INTO @ssn1,@fn,@ln
WHILE @@fetch_status=0
BEGIN
select @totalhours=SUM(hours) from WORKS_ON where ESSN=@ssn1 
INSERT @员工参与项目表 SELECT SSN,FNAME,LNAME,PNAME,HOURS FROM WORKS_ON W
JOIN Employee E ON W.ESSN=E.SSN
JOIN Project P ON W.PNO=P.PNUMBER
where SSN=@ssn1
INSERT INTO @员工参与项目表 VALUES(@ssn1,@fn,@ln,'所有项目工作总时间为:',@totalhours)
FETCH NEXT FROM CUR_empdpname INTO @ssn1,@fn,@ln
END
CLOSE CUR_empdpname
DEALLOCATE CUR_empdpname
RETURN
END
GO
select * from dbo. F_员工参与项目('research')
展开阅读全文
©️2020 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值