光标提取位置 [英] Cursor fetch location

问题描述

我有一个光标如下：

  DECLARE  trancur 游标 FAST_FORWARD 对于 
  SELECT  
 t.ExpCom * t2.SplitPerc，
 t.TrNo 
  FROM ＃TmpAgtTot2 t2（ NOLOCK ） 
 内部 加入 #TmpTran t（ NOLOCK ） ON  t.RepCode = t2.TranRepCode 
 其中 t.ASL 中的class =code-keyword>（' ' A ' '，' ' S ' '，' ' L ' '）

接下来我做：

 获取 trancur  INTo   @ NextGrossComm ， @ TrNo  
   SELECT   @ CurrGrossComm  =  @ CurrGrossComm  + IsNull（ @ NextGrossComm  ， 0 ） 

在此之前：

    @@ fetch_status  =  0   AND   @ CurrGrossComm <  @ MaxGrossComm  
  BEGIN  
 设置  @ myflag  =  1  
  @CurrGrossComm  =  @ CurrGrossComm  + IsNull（ @ NextGrossComm ， 0 ）
 
 结束 

我的问题是，@ NextGrossComm是否填充了带下划线代码的光标值，或者在我进入while循环之前不会发生？如果它有来自游标的值，那么它具有哪条记录 - 它是第一条记录吗？

解决方案

Fetch检索数据。如果没有返回数据，您不希望在While循环内使用数据。此外，你需要在While循环的底部有一个FETCH NEXT。

  DECLARE  trancur  cursor  FAST_FORWARD 对于 
  SELECT  
 t.ExpCom * t2.SplitPerc，
 t.TrNo 
  FROM ＃TmpAgtTot2 t2（ NOLOCK ）
 内部 加入 #TmpTran t （ NOLOCK ） ON  t.RepCode = t2.TranRepCode 
 其中 t.ASL  （' ' ' '，'  ' S ' '，' ' L ' '）
 
 获取 FIRST trancur  INTO   @ NextGrossComm ，< span class =code-sdkkeyword> @ TrNo  
    @@ fetch_status  =  0   AND   @ CurrGrossComm <  @ MaxGrossComm  
  BEGIN  
 设置  @ myflag  =  1  
 设置  @CurrGrossComm  =  @ CurrGrossComm  + IsNull（ @NextGrossComm ， 0 ）
 获取 NEXT trancur  INTO   @ NextGrossComm ， @ TrNo  
  END  

I have a cursor as follows:

DECLARE trancur cursor FAST_FORWARD For 
         SELECT 
               t.ExpCom*t2.SplitPerc, 
               t.TrNo 
          FROM #TmpAgtTot2 t2 (NOLOCK) 
          Inner Join #TmpTran t (NOLOCK) ON t.RepCode=t2.TranRepCode 
          Where  t.ASL in (''A'', ''S'', ''L'')

Then next I do:

Fetch trancur INTo @NextGrossComm, @TrNo
	SELECT @CurrGrossComm = @CurrGrossComm + IsNull(@NextGrossComm,0)

before this:

While @@fetch_status = 0 AND @CurrGrossComm < @MaxGrossComm
	BEGIN
              Set @myflag  = 1
              @CurrGrossComm = @CurrGrossComm + IsNull(@NextGrossComm,0)

        End

My Question is, is @NextGrossComm populated with a value from the cursor for the underlined code or that will not happen until I enter the while loop? If it has a value from the cursor, then which record does it have - Is it the 1st record?

解决方案

The Fetch retrieves the data. You do not want to use the data until within the While loop in case there is no data returned. Also, you need a FETCH NEXT at the bottom of the While loop.

DECLARE trancur cursor FAST_FORWARD For
         SELECT
               t.ExpCom*t2.SplitPerc,
               t.TrNo
          FROM #TmpAgtTot2 t2 (NOLOCK)
          Inner Join #TmpTran t (NOLOCK) ON t.RepCode=t2.TranRepCode
          Where  t.ASL in (''A'', ''S'', ''L'')

Fetch FIRST trancur INTO @NextGrossComm, @TrNo
While @@fetch_status = 0 AND @CurrGrossComm < @MaxGrossComm
    BEGIN
         Set @myflag  = 1
         Set @CurrGrossComm = @CurrGrossComm + IsNull(@NextGrossComm,0)
         Fetch NEXT trancur INTO @NextGrossComm, @TrNo
    END

这篇关于光标提取位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！