可以更换光标吗? [英] Can replace the cursor?

问题描述

我写了个人所得税的函式:

I written a funtion for personal income tax:

CREATE Function [dbo].[PersonalIncomeTax](@BasicSalary MONEY, @NumberOfDependents int)
RETURNS MONEY
BEGIN
DECLARE @Taxes MONEY, @ExemptionCircumstances MONEY,@ExemptionItself MONEY,@ExemptionMax MONEY
DECLARE @FromLevel MONEY, @ToLevel MONEY, @PercentTax FLOAT
SET @Taxes=0

SELECT @ExemptionCircumstances = ExemptionCircumstances*@NumberOfDependents,
@ExemptionItself = ExemptionItself,@ExemptionMax = ExemptionMax FROM CF_Thue 

IF(@ExemptionCircumstances>@ExemptionMax)
	SET @BasicSalary=@BasicSalary-@ExemptionMax
ELSE
   SET  @BasicSalary=@BasicSalary-@ExemptionCircumstances
  
DECLARE Taxes_Cursor CURSOR FOR 
	SELECT FromLevel,ToLevel,PercentTax FROM CF_Tax ORDER BY PercentTax
	OPEN Taxes_Cursor

FETCH NEXT FROM Taxes_Cursor INTO @FromLevel, @ToLevel,@PercentTax
WHILE(@@FETCH_STATUS=0)
BEGIN
		IF((@BasicSalary>=@FromLevel) AND (@BasicSalary<@ToLevel))
			SET @Taxes=@Taxes+(@BasicSalary-@FromLevel)*(@PercentTax/100)
		 IF((@BasicSalary>@FromLevel) AND (@BasicSalary>=@ToLevel))
		 	SET @Taxes=@Taxes+(@ToLevel-@FromLevel)*(@PercentTax/100)
	FETCH NEXT FROM Taxes_Cursor INTO @FromLevel, @ToLevel,@PercentTax
END 
CLOSE Taxes_Cursor
DEALLOCATE Taxes_Cursor


Return @Taxes

END

使用光标很慢.怎样在结果不变的代码中替换光标.谢谢all

Use the cursor was slow. How can replace the cursor in the code which results not change.Thanks all

推荐答案

您不需要光标.你能不能做这样的事情:

You don''t need a cursor for this. Can you not do something like:

SELECT @Taxes = SUM(ISNULL(Tax, 0))
FROM
(
	SELECT CASE WHEN ((@BasicSalary >= FromLevel) AND (@BasicSalary < ToLevel)) THEN ((@BasicSalary - FromLevel)*(PercentTax/100))
				WHEN ((@BasicSalary > FromLevel) AND (@BasicSalary >= ToLevel)) THEN ((ToLevel - FromLevel)*(PercentTax/100))
			ELSE 0
			END AS Tax
	FROM CF_Tax WITH (NOLOCK)
)

除此之外，您可以/应该查看查询性能和游标性能.您的光标非常基础，因此可以将以下某些内容应用于光标以加快速度.

Added to that, you can / should look at query performance and cursor performance. Your cursor is very basic and so you could apply the some of the following to the cursor to speed it up.

DECLARE Taxes_Cursor CURSOR FAST_FORWARD FORWARD_ONLY READ_ONLY FOR  
SELECT FromLevel,ToLevel,PercentTax FROM CF_Tax ORDER BY PercentTax
OPEN Taxes_Cursor

可以使用单个选择或使用CTE(公用表表达式)来完成许多查询.如果正确使用SQL，那就太棒了.

Many many queries can be done using a single select or using a CTE (Common table expression). SQL is fantastic if used correctly.

代替游标，使用临时表或带有标识字段的表变量，并在该标识字段中迭代代替游标.
这样的

in place of cursor use temprory table or table variable with identity field and iterate through this identity field in place of cursor.
like that

SELECT FromLevel,ToLevel,PercentTax into #tmpTable FROM CF_Tax ORDER BY PercentTax

alter table #tmpTable add Id int identity
select @cnt=count from #tmpTable 
declare @i int set @i=1
while @i<=@cnt
begin
select @FromLevel=FromLevel, @ToLevel=ToLevel,@PercentTax=PercentTax from #tmpTable where id=@i
--- use your logic
set @i=@i+1
end

这篇关于可以更换光标吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！