游标和循环的效率比拼！

icehell 2003-03-21 06:59:11

讨论！

...全文

450 32 打赏收藏转发到动态举报

写回复

用AI写文章

32 条回复

切换为时间正序

请发表友善的回复…

发表回复

xpilot 2003-03-30

打赏
举报

90%的使用游标的情况，都可以用临时表代替，我推荐多使用临时表，微软自己写的存储过程我就没见过用了游标的，倒是临时表用了不少！

效率的话没有比较过，不过感觉上临时表是很快的，而且不费什么内存！

伟大de虫子 2003-03-28

打赏
举报

疑问：游标是指conn,0,1后面的0，1吧？如果是，按你们说法，岂非一点用处都没有？如果不是，那你们所指的游标是什么？
表型变量又是指什么呢？使用形式？

icehell 2003-03-28

打赏
举报

别离题!

pengdali 2003-03-28

打赏
举报

使用update代替游标的功能:
题目：
如：我要发 8.85元,

需要 5 元 + 2 元 + 1 元 + 5 角 + 2 角 + 1 角 + 5 分各一个!

当然也可以给出其它答案

如：
需要（4 个 2 元）+ 5 角 + 2 角 + 1 角 + 5 分各一个!

只要结果等于 8.85 。当然，需用的"项目"越少越好！
======================================================

declare @table table(a money)
insert @table values(10)
insert @table values(5)
insert @table values(2)
insert @table values(1)
insert @table values(0.5)
insert @table values(0.2)
insert @table values(0.1)
insert @table values(0.05)
insert @table values(0.02)
insert @table values(0.01)

select *,0 Mymoney into # from @table order by a desc

declare @ money,@t int

set @=8.85

update # set @t=case when round(@/a,0,1)=0 then 0 else round(@/a,0,1) end,@=case when round(@/a,0,1)<>0 then cast((cast(@*100 as int)%cast(a*100 as int)+0.0) as money)/100 else @ end,mymoney=@t

select * from # where mymoney<>0
go
drop table #

cherno 2003-03-28

打赏
举报

AAsx(arbiter) 强贴！前面的全部晕倒~~

w_rose 2003-03-27

打赏
举报

使用临时表的目的是让同一客户联接的不同批处理之间共享数据。表型变量的作用域与此完全不同。不需要扩大作用域的话，尽量不要使用临时表。我至今在商品化软件中好像还从没有使用过任何临时表。也许是用为我是从2000开始使用SQL Server的缘故吧。

w_rose 2003-03-27

打赏
举报

在2000之后，不使用表型变量而使用临时表有点“老土”了！

xhfjy 2003-03-26

打赏
举报

AGREE tj_dns(愉快的登山者) ( )

愉快的登山者 2003-03-26

打赏
举报

1。用临时表代替游标功能
select identity(int,1,1) as id, * into #T from yourtable where ...
declare @i int
declare @tot int
select @tot = count(*) from #T
set @i = 1
while @i <= @tot
begin
select * from #t where id = @i
......
set @i = @i + 1
end
2。游标为什么比循环更耗费系统资源？
确切的说应该是比临时表循环更耗费系统资源。
因为游标是存放在内存中，临时表存放在硬盘的临时数据区。所以前者更耗费系统内存资源。
3。增加多用户互锁的机会？？？游标使用是对数据库独占方式的吗？用循环就可以解决这个问题吗？
游标以建立，就将相关的记录锁住，直到取消游标；
而循环只对正在处理的记录进行锁定，没有处理的记录不加锁。

yzhlinux 2003-03-26

打赏
举报

--4.先在工资主表中为员工插入工资条目,并得到最新标示 id
INSERT INTO RY_GL_GZ (RY_JCSJ__BianMa,RY_GL_GZ__GongZiShiJian,RY_GL_GZ__FDJinE,RY_GL_GZ__GDJinE,RY_GL_GZ__QiTaKouKuan) VALUES (@RY_JCSJ__BianMa,@RY_GL_GZ__GongZiShiJian,@RY_GL_GZ__FDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ__QiTaKouKuan)
SELECT @RY_GL_GZ__ID=RY_GL_GZ__ID FROM RY_GL_GZ WHERE RY_JCSJ__BianMa=@RY_JCSJ__BianMa AND RY_GL_GZ__GongZiShiJian=@RY_GL_GZ__GongZiShiJian
--4.结束
--把数据条目游标设置位头行
FETCH FIRST FROM SHUJUTIAOMUCUR INTO @RY_GL_GZ_ShuJuTiaoMu__ID,@RY_GL_GZ_ShuJuTiaoMu__ShuZhi,@RY_GL_GZ_TiaoMu__LeiXing_d,@RY_GL_GZ_TiaoMu__DuoYu,@RY_GL_GZ_ShuJuTiaoMu__JiSuanGongShi
--5.开始数据条目的遍历循环,位工资数据表添加数据
WHILE (@@FETCH_STATUS=0)
BEGIN --下面要给工资数据表初始化
--计算此条工资数据的总金额

--此处调用计算公式的函数来解析计算附加公式，来改变相应的金额，如：养老金
IF (LEFT(@RY_GL_GZ_TiaoMu__LeiXing_d,1)='1' and isnull(@RY_GL_GZ__GDJinE,0)<>0)
BEGIN
EXEC db_jisuangongshi @RY_GL_GZ__FDJinE,@RY_GL_GZ_ShuJuTiaoMu__ShuZhi,@RY_GL_GZ__FDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ_ShuJu__ZongE output,@RY_JCSJ__BianMa
--print '97@RY_GL_GZ__FDJinE1='+CAST(@RY_GL_GZ__FDJinE AS VARCHAR(100))+@RY_JCSJ__BianMa
END
ELSE IF (LEFT(@RY_GL_GZ_TiaoMu__LeiXing_d,1)='2' and isnull(@RY_GL_GZ__GDJinE,0)<>0)
BEGIN
EXEC db_jisuangongshi @RY_GL_GZ__GDJinE,@RY_GL_GZ_ShuJuTiaoMu__ShuZhi,@RY_GL_GZ__FDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ_ShuJu__ZongE output,@RY_JCSJ__BianMa
--print '102@RY_GL_GZ__GDJinE2='+CAST(@RY_GL_GZ__GDJinE AS VARCHAR(100))+@RY_JCSJ__BianMa
END
ELSE IF (LEFT(@RY_GL_GZ_TiaoMu__LeiXing_d,1)='3' and isnull(@RY_GL_GZ__GDJinE,0)<>0)
BEGIN
EXEC db_jisuangongshi @RY_GL_GZ__GDJinE,@RY_GL_GZ_ShuJuTiaoMu__ShuZhi,@RY_GL_GZ__FDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ__GDJinE,@RY_GL_GZ_ShuJu__ZongE output,@RY_JCSJ__BianMa
--print '107@RY_GL_GZ__GDJinE3='+CAST(@RY_GL_GZ__GDJinE AS VARCHAR(100))+@RY_JCSJ__BianMa
END
ELSE
BEGIN
SET @RY_GL_GZ_ShuJu__ZongE=NULL
END
--print '98@RY_GL_GZ_ShuJuTiaoMu__ShuZhi='
--print @RY_GL_GZ_ShuJuTiaoMu__ShuZhi
--print '@RY_GL_GZ_ShuJu__ZongE='
--print @RY_GL_GZ_ShuJu__ZongE
--总额计算完毕
--6.此时要计算此条工资的数据的成绩,先把成绩遍历设置为空
SET @RY_GL_GZ_ShuJu__ChengJi=NULL
--如果是浮动部分的
IF (CAST(@RY_GL_GZ_TiaoMu__LeiXing_d AS INT)=1)-- OR CAST(@RY_GL_GZ_TiaoMu__LeiXing_d AS INT)=2)
BEGIN
SELECT @RY_GL_GZ_ShuJu__ChengJi=CAST(RY_XX_GangWei_KaoHeBiao__FenShu AS FLOAT) FROM RY_XX_GangWei_KaoHeBiao WHERE RY_JCSJ__BianMa=@RY_JCSJ__BianMa AND YEAR(RY_XX_GangWei_KaoHeBiao__ShiJian2)=@THEYEAR AND YEAR(RY_XX_GangWei_KaoHeBiao__ShiJian1)=@THEYEAR AND MONTH(RY_XX_GangWei_KaoHeBiao__ShiJian2)=@THEMONTH AND MONTH(RY_XX_GangWei_KaoHeBiao__ShiJian1)=@THEMONTH
--print 'SELECT CAST(RY_XX_GangWei_KaoHeBiao__FenShu AS FLOAT) FROM RY_XX_GangWei_KaoHeBiao WHERE RY_JCSJ__BianMa='''+@RY_JCSJ__BianMa+''' AND YEAR(RY_XX_GangWei_KaoHeBiao__ShiJian2)='''+@THEYEAR+''' AND YEAR(RY_XX_GangWei_KaoHeBiao__ShiJian1)='''+@THEYEAR+''' AND MONTH(RY_XX_GangWei_KaoHeBiao__ShiJian2)='''+@THEMONTH+''' AND MONTH(RY_XX_GangWei_KaoHeBiao__ShiJian1)='''+@THEMONTH+''''
--print '@RY_GL_GZ_ShuJu__ChengJi='
--print @RY_GL_GZ_ShuJu__ChengJi
--调用计算成绩的函数来计算成绩(注:由于函数可以直接得到返回值,而不需要再次进行查询,所以位了效率考虑这里编写了特定的函数来处理)
SET @RY_GL_GZ_ShuJu__ChengJi=dbo.JISUANCHENGJI(null,CAST(@RY_GL_GZ_ShuJu__ChengJi AS FLOAT),@RY_GL_GZ_TiaoMu__DuoYu)

END
--6.结束
--插入此条目的工资数据
INSERT INTO RY_GL_GZ_ShuJu (RY_GL_GZ__ID,RY_GL_GZ_ShuJuTiaoMu__ID,RY_GL_GZ_ShuJu__ZongE,RY_GL_GZ_ShuJu__ChengJi) VALUES (@RY_GL_GZ__ID,@RY_GL_GZ_ShuJuTiaoMu__ID,@RY_GL_GZ_ShuJu__ZongE,@RY_GL_GZ_ShuJu__ChengJi)
FETCH NEXT FROM SHUJUTIAOMUCUR INTO @RY_GL_GZ_ShuJuTiaoMu__ID,@RY_GL_GZ_ShuJuTiaoMu__ShuZhi,@RY_GL_GZ_TiaoMu__LeiXing_d,@RY_GL_GZ_TiaoMu__DuoYu,@RY_GL_GZ_ShuJuTiaoMu__JiSuanGongShi
END
--去下一个人员处
FETCH NEXT FROM RYCUR INTO @RY_JCSJ__BianMa
END

SELECT 1 as returnvalue,'成功执行！创建了' + @RY_GL_GZ__GongZiShiJian_VARCHAR + ' 的工资表' as returndescription
CLOSE RYCUR
DEALLOCATE RYCUR
--插入统一解决代码
CLOSE SHUJUTIAOMUCUR
DEALLOCATE SHUJUTIAOMUCUR
--7.调用数据条目公式初始化过程
EXEC db_shujutiaomugongshi @RY_GL_GZ__GongZiShiJian
--7.结束
print 'db_gongzhichushihua___END'
END

yzhlinux 2003-03-26

打赏
举报

IF @C>0 --此时有这个时间的工资表
BEGIN
RAISERROR('不能创建 %s 的工资表，因为 %s 的工资表已经存在而且有 %d 条数据',10,1,@RY_GL_GZ__GongZiShiJian_VARCHAR,@RY_GL_GZ__GongZiShiJian_VARCHAR,@C)
select 0 as returnvalue,'不能创建' +@RY_GL_GZ__GongZiShiJian_VARCHAR + ' 的工资表，因为' + @RY_GL_GZ__GongZiShiJian_VARCHAR + ' 的工资表已经存在而且有 ' + cast(@C as varchar(10))+ ' 条数据' as returndescription
/*ROLLBACK TRANSACTION*/
END
ELSE
--此时已经确定这个月份的工资存在
BEGiN
DECLARE @RY_GL_GZ_TiaoMu__ID INT --条目id
DECLARE @RY_JCSJ__BianMa CHAR(25) --人员编码
DECLARE @RY_GL_GZ__FDJinE FLOAT --浮动金额
DECLARE @RY_GL_GZ__GDJinE FLOAT --固定金额
DECLARE @RY_GL_GZ__QiTaKouKuan FLOAT --其他扣款
DECLARE @RY_GL_GZ_ShuJu__ChengJi FLOAT --成绩变量（特意为绩效工资而设）
DECLARE @BianLiang VARCHAR(50) --变量
DECLARE @RY_GL_GZ_TiaoMu__DuoYu VARCHAR(1000)
DECLARE RYCUR CURSOR FOR SELECT RY_JCSJ__BianMa FROM RY_GL_GZ_BiaoZhuen WHERE RY_GL_GZ_BiaoZhuen__FaFangJiBie=@FaFangJiBie and LeiBie<>'类别' --人员游标
DECLARE TIAOMUCUR CURSOR FOR SELECT RY_GL_GZ_TiaoMu__LeiXing_d,NeiRong,RY_GL_GZ_TiaoMu__ShuZhi,RY_GL_GZ_TiaoMu__DuoYu,RY_GL_GZ_TiaoMu__JiSuanGongShi,RY_GL_GZ_TiaoMu__ID,BianLiang FROM RY_GL_GZ_TiaoMu WHERE LeiBie<>'类别'
--1.先将临时条目设置表导入工资条目表
DECLARE @RY_GL_GZ_TiaoMu__LeiXing_d CHAR(25)
DECLARE @NeiRong VARCHAR(50)
DECLARE @RY_GL_GZ_TiaoMu__ShuZhi VARCHAR(50)
DECLARE @RY_GL_GZ_TiaoMu__JiSuanGongShi VARCHAR(500)
DECLARE @RY_GL_GZ_ShuJuTiaoMu__GongShi VARCHAR(1000)
DELETE FROM RY_GL_GZ_ShuJuTiaoMu WHERE RY_GL_GZ__GongZiShiJian=@RY_GL_GZ__GongZiShiJian --删除已经存在的数据
OPEN TIAOMUCUR
FETCH NEXT FROM TIAOMUCUR INTO @RY_GL_GZ_TiaoMu__LeiXing_d,@NeiRong,@RY_GL_GZ_TiaoMu__ShuZhi,@RY_GL_GZ_TiaoMu__DuoYu,@RY_GL_GZ_TiaoMu__JiSuanGongShi,@RY_GL_GZ_TiaoMu__ID,@BianLiang
WHILE (@@FETCH_STATUS=0) --开始初始化数据条目
BEGIN
INSERT INTO RY_GL_GZ_ShuJuTiaoMu (RY_GL_GZ__GongZiShiJian,RY_GL_GZ_TiaoMu__LeiXing_d,NeiRong,RY_GL_GZ_ShuJuTiaoMu__ShuZhi,RY_GL_GZ_ShuJuTiaoMu__GongShi,RY_GL_GZ_ShuJuTiaoMu__DuoYu,RY_GL_GZ_ShuJuTiaoMu__JiSuanGongShi,RY_GL_GZ_TiaoMu__ID,BianLiang) VALUES (@RY_GL_GZ__GongZiShiJian,@RY_GL_GZ_TiaoMu__LeiXing_d,@NeiRong,@RY_GL_GZ_TiaoMu__ShuZhi,@RY_GL_GZ_ShuJuTiaoMu__GongShi,@RY_GL_GZ_TiaoMu__DuoYu,@RY_GL_GZ_TiaoMu__JiSuanGongShi,@RY_GL_GZ_TiaoMu__ID,@BianLiang)
SET @RY_GL_GZ_TiaoMu__ShuZhi =NULL
SET @RY_GL_GZ_TiaoMu__JiSuanGongShi=NULL
SET @RY_GL_GZ_ShuJuTiaoMu__GongShi=NULL
FETCH NEXT FROM TIAOMUCUR INTO @RY_GL_GZ_TiaoMu__LeiXing_d,@NeiRong,@RY_GL_GZ_TiaoMu__ShuZhi,@RY_GL_GZ_TiaoMu__DuoYu,@RY_GL_GZ_TiaoMu__JiSuanGongShi,@RY_GL_GZ_TiaoMu__ID,@BianLiang
SET @RY_GL_GZ_ShuJuTiaoMu__GongShi=@RY_GL_GZ_TiaoMu__JiSuanGongShi
END
CLOSE TIAOMUCUR
DEALLOCATE TIAOMUCUR
--1.结束。关闭条目游标
--2.初始化考勤表为没个员工计算并保存扣款金额
EXEC db_chushihua_kaoqinkoukuan @RY_GL_GZ__GongZiShiJian
--2.结束.
--以下读出每个人的数据，为每个员工进行初始化
--取出刚刚存入的条目数据
DECLARE @RY_GL_GZ__ID INT
DECLARE @RY_GL_GZ_ShuJuTiaoMu__ID INT
DECLARE @RY_GL_GZ_ShuJuTiaoMu__ShuZhi VARCHAR(50)
DECLARE @RY_GL_GZ_ShuJuTiaoMu__JiSuanGongShi VARCHAR(500)
DECLARE @RY_GL_GZ_ShuJu__ZongE FLOAT
set @RY_GL_GZ_ShuJu__ZongE=0
DECLARE SHUJUTIAOMUCUR CURSOR STATIC FOR SELECT RY_GL_GZ_ShuJuTiaoMu__ID,RY_GL_GZ_ShuJuTiaoMu__ShuZhi,RY_GL_GZ_TiaoMu__LeiXing_d,RY_GL_GZ_ShuJuTiaoMu__DuoYu,RY_GL_GZ_ShuJuTiaoMu__JiSuanGongShi FROM RY_GL_GZ_ShuJuTiaoMu WHERE RY_GL_GZ__GongZiShiJian=@RY_GL_GZ__GongZiShiJian
OPEN RYCUR
OPEN SHUJUTIAOMUCUR --打开数据条目表游标
FETCH NEXT FROM RYCUR INTO @RY_JCSJ__BianMa --这个循环遍历所有的人员
WHILE (@@FETCH_STATUS=0)
BEGIN
--3.获得此人在工资标准中的金额
SET @RY_GL_GZ__FDJinE=NULL
SET @RY_GL_GZ__GDJinE=NUlL
--得到考勤扣款的的金额
SELECT @RY_GL_GZ__QiTaKouKuan=SUM(RY_GL_KQ__SuoKouJinE) FROM RY_GL_KQ WHERE RY_JCSJ__BianMa=@RY_JCSJ__BianMa and month(RY_GL_KQ__RiQi)=@THEMONTH --要得到此人的考勤情况并计算出相应扣款
SELECT @RY_GL_GZ__FDJinE=RY_GL_GZ_BiaoZhuen__FD,@RY_GL_GZ__GDJinE=RY_GL_GZ_BiaoZhuen__GD FROM RY_GL_GZ_BiaoZhuen WHERE RY_JCSJ__BianMa=@RY_JCSJ__BianMa
--3.结束

yzhlinux 2003-03-26

打赏
举报

看看这个代码：
我在这里使用了大量的游标，套用的过程中还有更多的游标，运行非常好。
/*在创建工资表的时候初始化一张新表单，并执行*/
CREATE PROCEDURE db_gongzhichushihua
@RY_GL_GZ__GongZiShiJian SMALLDATETIME,
@FaFangJiBie varchar(10) = '1'
AS
DECLARE @C int
DECLARE @THEMONTH VARCHAR(10)
DECLARE @THEYEAR VARCHAR(10)
DECLARE @RY_GL_GZ__GongZiShiJian_VARCHAR VARCHAR(15)
print 'db_gongzhichushihua___BEGIN'
SET @THEMONTH=MONTH(dateadd(m,-1,@RY_GL_GZ__GongZiShiJian))
SET @THEYEAR=YEAR(@RY_GL_GZ__GongZiShiJian)
SET @RY_GL_GZ__GongZiShiJian_VARCHAR=CAST(@RY_GL_GZ__GongZiShiJian AS VARCHAR(15))
--检查是否这个月的工资已经存在,存在则给出错误信息并结束过程
SELECT @C=COUNT(*) FROM RY_GL_GZ WHERE year(RY_GL_GZ__GongZiShiJian)=year(@RY_GL_GZ__GongZiShiJian) and month(RY_GL_GZ__GongZiShiJian)=month(@RY_GL_GZ__GongZiShiJian) and day(RY_GL_GZ__GongZiShiJian)=day(@RY_GL_GZ__GongZiShiJian)

mjhnet 2003-03-25