db2 - 在 rpgle 中将游标提取到数组中的性能缓慢-6ren

db2 - 在 rpgle 中将游标提取到数组中的性能缓慢

转载作者：行者123 更新时间：2023-12-05 01:49:39

我有这个相对简单的 rpg 程序，其中行 exec sql workKeySocWorkCodesCur for 500 rows into :childWorkKeyArray;

执行非常缓慢。每次执行大约需要 6 秒。我不明白的是，当我以交互方式运行此查询时，或者当我在仅包含此语句的单独 RPG 中测试此语句时(通过替换主机变量值)，它似乎在不到一秒钟内完成。那么这个程序有什么不同可能导致它变慢？

**FREE
// ******************************************************************
// Globals
// ******************************************************************

dcl-ds  parentWorkKeyArray         qualified dim(500);
          workKey              like(P_IacWrkKey.WorkKey) inz(0);
          workIsRef            char(1) inz (*blanks);
          workKeyParentKey     like(P_IacWrkKey.WorkKey) inz(0);
end-ds;

dcl-ds  childWorkKeyArray          qualified dim(500);
          workKey              like(P_IacWrkKey.WorkKey) inz(0);
          workIsRef            char(1) inz (*blanks);
          mergedToWorkKey      like(P_IacWrkKey.WorkKey) inz(0);
end-ds;


dcl-ds ISWCDUPDS                          Extname('POTISWCDUP') qualified;
end-ds;

dcl-s  merged                    ind inz(*off);
dcl-s  multipleActiveWorksPresent   ind inz(*off);
dcl-s  countOfNonReferencedWorks int(5);
dcl-s  index                              int(5) inz(1);
dcl-s  isChildWorkKeySameAsParent         int(5);
dcl-s  isChildWorkKeyMergedToParent       int(5);
dcl-s  socCodeFound                       ind inz(*off);
dcl-s  socCodeTBMFound                    ind inz(*off);
dcl-s  pos                                int(5);
dcl-s  gCheckSqlKeyField                  varchar(128);
dcl-s  check                              char(3);
dcl-s  socWorkCodeString                  like(ISWCDUPDS.ISWCSOCWCD);
dcl-s  iswcReference                      like(ISWCDUPDS.ISWC);

dcl-c firstElement const(1) ;


// ------------------------------------------------------------------------
// Program procedure flow
// ------------------------------------------------------------------------
dcl-pi *n end-pi;

exec sql SET OPTION COMMIT = *NONE ;

if not BeginProgram();
  ErrorHandling();
  return;
elseif not ProcessRequest();
  ErrorHandling();
  return;
endif;
*inlr = *On;
return ;

//  ------------------------------------------------------------------------
//  * Procedure name: ProcessRequest
//  * Purpose:        Process Request
//  * Returns:        True or False
//  *------------------------------------------------------------------------

dcl-proc ProcessRequest;

    dcl-pi *N ind end-pi;

    dcl-s referenceType  char(10);

    exec sql close potiswccur;
    exec sql open potiswccur;

    exec sql fetch first from potiswccur into :ISWCDUPDS;

   dow (sqlcod = 0);

      if not GetIswcWorkKeys();
        return cFalse;
      endif;

      referenceType = 'ISWC';
      if not CheckIfISWCMergdToISWCTBM(referenceType);
        return cFalse;
      endif;

      GetWorkKeyString(referenceType);

      if not GetSocietyWorkCodeWorkKeys();
        return cFalse;
      endif;

      referenceType = 'SOC';
      if not CheckIfISWCMergdToISWCTBM(referenceType);
        return cFalse;
      endif;

      GetWorkKeyString(referenceType);

      UpdateRecord();

      exec sql fetch next from potiswccur into :ISWCDUPDS;

   enddo;

   return cTrue;

end-proc ProcessRequest;


//  ------------------------------------------------------------------------
//  * Procedure name: getWorkKeyStringSocCode
//  * Purpose:        Combines all work keys into a string delimited by '|'
//  * Returns:        True or False
//  *------------------------------------------------------------------------

dcl-proc getWorkKeyString;

   dcl-pi *n ind ;
    referenceType char(10);
   end-pi;

   index = 1;

   dow (index < 500);

        if ( parentWorkKeyArray(index).workKey <> 0);
            if (referenceType = 'ISWC');
                if (index > 1);
                    ISWCDUPDS.IWKEY_ISWC = %Trim(ISWCDUPDS.IWKEY_ISWC)  + '|';
                endif;
                ISWCDUPDS.IWKEY_ISWC = %Trim(ISWCDUPDS.IWKEY_ISWC) + %char(parentWorkKeyArray(index).workKey);
            else;
                if (index > 1);
                    ISWCDUPDS.IWKEYSOCD = %Trim(ISWCDUPDS.IWKEYSOCD)  + '|';
                endif;
                ISWCDUPDS.IWKEYSOCD = %Trim(ISWCDUPDS.IWKEYSOCD) + %char(parentWorkKeyArray(index).workKey);
            endif;
        endif;

        if ( childWorkKeyArray(index).workKey <> 0);
             if (referenceType = 'ISWC');
                if (index > 1);
                    ISWCDUPDS.IWKEYMISWC = %Trim(ISWCDUPDS.IWKEYMISWC)  + '|';
                endif;
                ISWCDUPDS.IWKEYMISWC = %Trim(ISWCDUPDS.IWKEYMISWC)  + %char(childWorkKeyArray(index).workKey) ;
             else;
                if (index > 1);
                    ISWCDUPDS.IWKEYSOCDM = %Trim(ISWCDUPDS.IWKEYSOCDM) + '|' ;
                endif;
                ISWCDUPDS.IWKEYSOCDM = %Trim(ISWCDUPDS.IWKEYSOCDM) + %char(childWorkKeyArray(index).workKey) ;
             endif;
        endif;

      index = index + 1;

   enddo;
   return cTrue;
end-proc;


dcl-proc GetSocietyWorkCodeWorkKeys;

dcl-pi *n ind ;

      end-pi;

      clear parentWorkKeyArray;
      clear childWorkKeyArray;

      // declare cursor for fetching work keys of ISWC
      socWorkCodeString = ISWCDUPDS.ISWCSOCWCD ;
      exec sql close workKeySocWorkCodesCur;
      exec sql open workKeySocWorkCodesCur;
      exsr checkSQLCodeSR;

      exec sql fetch workKeySocWorkCodesCur for 500 rows into :parentWorkKeyArray;
      exsr checkSQLCodeSR;

      // declare cursor for fetching work keys of ISWC TBM
      socWorkCodeString = ISWCDUPDS.TMSOCWRKCD ;
      exec sql close workKeySocWorkCodesCur;
      exec sql open workKeySocWorkCodesCur;
      exsr checkSQLCodeSR;

      exec sql fetch workKeySocWorkCodesCur for 500 rows into :childWorkKeyArray;
      exsr checkSQLCodeSR;

      return cTrue;

      begsr checkSQLCodeSR;
          if not CheckSqlCode(sqlcode:sqlwarn(1):gCheckSqlKeyField);
              return cFalse;
          endif;
      endsr;

end-proc;

//----------------------------------------------------------------------------------------------
// Procedure name: GetIswcWorkKeys
// Purpose:        Fetches work keys to which ISWC and ISWC_TBM are allocated and
//                 places them in parentWorkKeyArray and  childWorkKeyArray respectively
// Returns:        Successful/unsuccessful action
//----------------------------------------------------------------------------------------------

dcl-proc GetIswcWorkKeys;

      dcl-pi *n ind ;

      end-pi;

      clear parentWorkKeyArray;
      clear childWorkKeyArray;

      // declare cursor for fetching work keys of ISWC
      iswcReference = ISWCDUPDS.ISWC ;
      exec sql close workKeyISWCCur;
      exec sql open workKeyISWCCur;
      exsr checkSQLCodeSR;

      exec sql fetch workKeyISWCCur for 500 rows into :parentWorkKeyArray;
      exsr checkSQLCodeSR;

      // declare cursor for fetching work keys of ISWC TBM
      iswcReference = ISWCDUPDS.ISWC_TBM ;
      exec sql close workKeyISWCCur;
      exec sql open workKeyISWCCur;
      exsr checkSQLCodeSR;

      exec sql fetch workKeyISWCCur for 500 rows into :childWorkKeyArray;
      exsr checkSQLCodeSR;

      return cTrue;

      begsr checkSQLCodeSR;
          if not CheckSqlCode(sqlcode:sqlwarn(1):gCheckSqlKeyField);
              ErrorHandling();
              return cFalse;
          endif;
      endsr;

end-proc;

//---------------------------------------------------------------------
// Procedure name: checkIfISWCMergdToISWCTBM
// Purpose:        checks if both sides of the pair are merged
// Returns:        Successful/unsuccessful action
//---------------------------------------------------------------------

dcl-proc checkIfISWCMergdToISWCTBM;

    dcl-pi *n ind;
        referenceType char(10);
    end-pi;

    dcl-s workKeysFound ind;
    dcl-s workKeysTBMFound ind;
    dcl-s multipleActiveWorksPresent ind;
    dcl-s merged ind;

    if not checkNumberOfActiveWorksOnISWC(multipleActiveWorksPresent);
         return cFalse;
    endif;

    if not multipleActiveWorksPresent;

        if not checkIfWorkCodesFound(workKeysFound: workKeysTBMFound: referenceType);
            return cFalse;
        endif;

        if (workKeysFound and workKeysTBMFound);
            checkIfChildCompletelyMergedToParent(merged);
         endif;

        if merged;
            if referenceType = 'ISWC';
                ISWCDUPDS.ISWCSORM = 'MERGED';
            else;
                ISWCDUPDS.SOCCSORM = 'MERGED';
            endif;
        else;
            if referenceType = 'ISWC';
                ISWCDUPDS.ISWCSORM = 'NOT MERGED';
            else;
                ISWCDUPDS.SOCCSORM = 'NOT MERGED';
            endif;
        endif;


    endif;

    return cTrue;

end-proc;

//------------------------------------------------------------------------------------------
// Procedure name: checkIfChildCompletelyMergedToParent
// Purpose:        checks if child merged to parent
// Returns:        Successful/unsuccessful action
//-------------------------------------------------------------------------------------------

dcl-proc checkIfChildCompletelyMergedToParent;

      dcl-pi *n ind ;
        merged ind;
      end-pi;

      clear isChildWorkKeyMergedToParent;
      clear isChildWorkKeySameAsParent;
      index = 1;

      dow (index < 500 and childWorkKeyArray(index).workKey <> 0);

        merged = *on;
        // check if tbm work key is same as one of the parent work key
        isChildWorkKeySameAsParent = %lookup(childWorkKeyArray(index).workKey :  parentWorkKeyArray(*).workKey) ;

        // if work key not same as one of parent work key, check if they are merged to parent ?
        if (isChildWorkKeySameAsParent = 0);
            if (childWorkKeyArray(index).mergedToWorkKey <> 0); //possible only if child work key is referenced
                // check if the child is merged to a parent key - firstly check if child is merged to one of the parent keys.
                isChildWorkKeyMergedToParent = %lookup(childWorkKeyArray(index).mergedToWorkKey :  parentWorkKeyArray(*).workKey) ;
                if (isChildWorkKeyMergedToParent > 0);
                    ISWCDUPDS.message = %trim(ISWCDUPDS.message)  + 'ISWC TBM Work key ' + %char(childWorkKeyArray(index).workKey) + ' merged to ISWC work key ' + %char(childWorkKeyArray(index).mergedToWorkKey) + '||';
                else;
                     // if not direclty merged to one of the parent keys, check if the child is merged to the parent of parent - parentWorkKeyArray(*).workKeyParentKey!
                   isChildWorkKeyMergedToParent = %lookup(childWorkKeyArray(index).mergedToWorkKey :  parentWorkKeyArray(*).workKeyParentKey) ;
                   if ( isChildWorkKeyMergedToParent > 0 );
                     ISWCDUPDS.message = %trim(ISWCDUPDS.message)  + 'ISWC TBM Work key ' + %char(childWorkKeyArray(index).workKey) + ' and ISWC Work key '+
                     %char(parentWorkKeyArray(isChildWorkKeyMergedToParent).workKey) + ' merged to '+ %char(parentWorkKeyArray(isChildWorkKeyMergedToParent).workKeyParentKey);
                   endif;
                 endif;
            endif;

        else; // if tbm work key and iswc work key are same, it means both iswc's present on same work key.
            if ( check = 'SOC');
                ISWCDUPDS.message = %trim(ISWCDUPDS.message) + 'Society work code and Society work code TBM both present on work key ' + %char(childWorkKeyArray(index).workKey) + '||';
            else;
                ISWCDUPDS.message = %trim(ISWCDUPDS.message) + 'ISWC and ISWC_TBM both present on work key ' + %char(childWorkKeyArray(index).workKey) + '||';
            endif;
        endif;

        if ( isChildWorkKeySameAsParent = 0 and isChildWorkKeyMergedToParent = 0);
            merged = *off ;
            leave;
        endif;

        index = index + 1;

      enddo;

      return cTrue;

end-proc;



//------------------------------------------------------------------------------------------
// Procedure name: checkIfWorkCodesFound
// Purpose:        checks if workkeys present for incoming and target references
// Returns:        Successful/unsuccessful action
//-------------------------------------------------------------------------------------------

dcl-proc checkIfWorkCodesFound;

      dcl-pi *n ind;
        socCodeFound ind;
        socCodeTBMFound ind;
        referenceType char(10);
      end-pi;

      socCodeFound = *on;
      socCodeTBMFound = *on;

      if ( parentWorkKeyArray(firstElement) = *blanks );
        socCodeFound = *off;
        if referenceType = 'ISWC';
            ISWCDUPDS.message = %trim(ISWCDUPDS.message) + ' ISWC not found in ICE ||' ;
        else;
            ISWCDUPDS.message = %trim(ISWCDUPDS.message) + ' Soc. work codes not found in ICE ||' ;
        endif;
      endif;

      if ( childWorkKeyArray(firstElement) = *blanks );
        socCodeTBMFound = *off;
        if referenceType = 'ISWC';
            ISWCDUPDS.message = %trim(ISWCDUPDS.message) + ' ISWC TBM not found in ICE ||' ;
        else;
            ISWCDUPDS.message = %trim(ISWCDUPDS.message) + ' Soc. work codes TBM not found in ICE ||' ;
        endif;
      endif;

      return cTrue;

end-proc;


//---------------------------------------------------------------------
// Procedure name: checkNumberOfActiveWorksonISWC
// Purpose:        checks if target ISWC has more than 1 active work.
//                 If it does, not considered merged.
// Returns:        Successful/unsuccessful action
//---------------------------------------------------------------------

dcl-proc checkNumberOfActiveWorksonISWC;

    dcl-pi *n ind;
        multipleActiveWorksPresent ind;
    end-pi;

    index = 1;
    countOfNonReferencedWorks = 0;
    dow (index < 500 and parentWorkKeyArray(index).workKey <> 0);

        if ( parentWorkKeyArray(index).workIsRef = 'N');
            countOfNonReferencedWorks = countOfNonReferencedWorks + 1;

            if (countOfNonReferencedWorks > 1);
                multipleActiveWorksPresent = *On;
                ISWCDUPDS.message = %trim(ISWCDUPDS.message) + 'Multiple Non-referenced work keys assigned same ISWC - Hence not considered merged||';
                leave;
            endif;

        endif;
        index = index + 1;
    enddo;
    return cTrue;

end-proc;

//---------------------------------------------------------------------------------
// Procedure name: BeginProgram
// Purpose:        Init program
// Returns:        Successful/unsuccessful action
//---------------------------------------------------------------------------------

dcl-proc BeginProgram;
  dcl-pi *n ind end-pi;

  if not DeclareCursors();
    ErrorHandling();
    return cFalse;
  endif;

  return cTrue;

end-proc BeginProgram;


//--------------------------------------------------------------------------------------
// Procedure name: DeclareCursors
// Purpose:        Declares cursors for reading the input file and fetching work keys
// Returns:        Nothing
//---------------------------------------------------------------------------------------
dcl-proc DeclareCursors;
    dcl-pi *n ind end-pi;
//declare cursor for reading the input file
    exec sql declare potiswccur dynamic scroll cursor  for select * from potiswcdup;

// declare cursor for fetching work keys of ISWC
    exec sql declare workKeyISWCCur cursor for
        SELECT
        WRK.WORKKEY,
        WRKISREF,
        COALESCE(WORKKEYM, 0) AS WORKKEYM FROM POTISWCDUP POT JOIN IACWXR WXR
                        ON WXR.WORKREF = :iswcReference AND WXR.WRKREFTYPE = 'ISWC'
                                   JOIN IACWRK WRK ON WXR.WORKKEY = WRK.WORKKEY
                                   LEFT JOIN IACWRM WRM ON WRM.WORKKEYR = WRK.WORKKEY AND WRKRELTYPE = 'MERG'
                                   WHERE SLNUM = :ISWCDUPDS.SLNUM;

 // declare cursor for fetching work keys of SOC Work codes
        exec sql declare workKeySocWorkCodesCur cursor for
        WITH WORKREF_SOCIETY AS (
        SELECT SLNUM,
        SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 5) AS WORKREF ,
        SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) AS SOCIETY ,

        CASE
         WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) = '052' THEN 'ALLTC'
          WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) IN ('023', '055')  THEN 'SWREF'
           WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) IN ('077', '079','089', '090', '110', '116', '112' )  THEN 'IWKEY'
        END
        AS WRKREFTYPE1,

        CASE
            WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) = '052' THEN 'DELTC'
        END
        AS WRKREFTYPE2

        FROM POTISWCDUP ,
        TABLE(SYSTOOLS.SPLIT(:socWorkCodeString,',') ) WHERE SLNUM = :ISWCDUPDS.SLNUM
        AND SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) IN (SELECT SOCCODE FROM IACIAS) )

        SELECT
        WRK.WORKKEY,
        WRKISREF,
        COALESCE(WORKKEYM, 0) AS WORKKEYM FROM WORKREF_SOCIETY WSR JOIN IACWXR WXR

        ON WXR.WORKREF = WSR.WORKREF AND WXR.WRKREFTYPE IN (WSR.WRKREFTYPE1, WSR.WRKREFTYPE2)
                                   JOIN IACWRK WRK
        ON WXR.WORKKEY = WRK.WORKKEY
                                   LEFT JOIN IACWRM WRM
        ON WRM.WORKKEYR = WRK.WORKKEY AND WRKRELTYPE = 'MERG'

        UNION

        SELECT WORKREF , WRKISREF, COALESCE (WORKKEYM, 0)
        FROM WORKREF_SOCIETY JOIN IACWRK ON INT(WORKREF) = WORKKEY
                            LEFT JOIN IACWRM ON WORKKEYR = WORKKEY AND WRKRELTYPE = 'MERG'
        WHERE WRKREFTYPE1 = 'IWKEY';


        exec sql UPDATE thepav.POTISWCDUP SET
        MESSAGE = ' ',
        ISWCSORM = ' ',
        SOCCSORM = ' ',
        IWKEY_ISWC = ' ',
        IWKEYMISWC = ' ' ,
        IWKEYSOCDM = ' ',
        IWKEYSOCD =  ' ' ;

        return cTrue;

end-proc DeclareCursors;



dcl-proc UpdateRecord;

    dcl-pi *n ind end-pi;

        if ( ISWCDUPDS.IWKEY_ISWC = ISWCDUPDS.IWKEYMISWC );
            ISWCDUPDS.ISWCSORM  = 'SAME';
        endif;

        if ( ISWCDUPDS.IWKEYSOCD = ISWCDUPDS.IWKEYSOCDM );
            ISWCDUPDS.SOCCSORM = 'SAME';
        endif;


        exec sql
           UPDATE POTISWCDUP
           SET
               IWKEY_ISWC = :ISWCDUPDS.IWKEY_ISWC ,
               IWKEYMISWC = :ISWCDUPDS.IWKEYMISWC ,
               ISWCSORM   = :ISWCDUPDS.ISWCSORM   ,

               IWKEYSOCD  = :ISWCDUPDS.IWKEYSOCD  ,
               IWKEYSOCDM = :ISWCDUPDS.IWKEYSOCDM ,
               SOCCSORM   = :ISWCDUPDS.SOCCSORM

           WHERE SLNUM = :ISWCDUPDS.SLNUM;

    return cTrue;

end-proc;

//---------------------------------------------------------------------
// Procedure name: Pgm_ErrorHandling
// Purpose:        Process Error handling
// Returns:        Nothing
//---------------------------------------------------------------------
dcl-proc ErrorHandling;

   dump;

end-proc ErrorHandling;

我真的不明白为什么同样的提取操作在下面的程序中运行得如此之快。当我一按 F10 就检查调试时，它只是移动到下一行，这与上面的行不同，它只是停留在提取上大约 6 秒。

**FREE

dcl-ds  parentWorkKeyArray         qualified dim(500);
          //slnum                like(ISWCDUPDS.slnum) inz(0);
          workKey              int(10);
          workIsRef            char(1) inz (*blanks);
          workKeyParentKey     int(10);
end-ds;


dcl-ds ISWCDUPDS                          Extname('POTISWCDUP') qualified;
end-ds;

dcl-s string char(5000);
dcl-s slnum int(5) inz(1);

string = '052|0010018E';

 exec sql declare workKeySocWorkCodesCur cursor for
        WITH WORKREF_SOCIETY AS (
        SELECT SLNUM,
        SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 5) AS WORKREF ,
        SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) AS SOCIETY ,

        CASE
         WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) = '052' THEN 'ALLTC'
          WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) IN ('023', '055')  THEN 'SWREF'
           WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) IN ('077', '079','089', '090', '110', '116', '112' )  THEN 'IWKEY'
        END
        AS WRKREFTYPE1,

        CASE
            WHEN
            SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) = '052' THEN 'DELTC'
        END
        AS WRKREFTYPE2

        FROM POTISWCDUP ,
        TABLE(SYSTOOLS.SPLIT(:string,',') ) WHERE SLNUM = :slnum
        AND SUBSTR (VARCHAR(TRIM(ELEMENT), 20) , 1, 3) IN (SELECT SOCCODE FROM IACIAS) )

        SELECT
        WRK.WORKKEY,
        WRKISREF,
        COALESCE(WORKKEYM, 0) AS WORKKEYM FROM WORKREF_SOCIETY WSR JOIN IACWXR WXR

        ON WXR.WORKREF = WSR.WORKREF AND WXR.WRKREFTYPE IN (WSR.WRKREFTYPE1, WSR.WRKREFTYPE2)
                                   JOIN IACWRK WRK
        ON WXR.WORKKEY = WRK.WORKKEY
                                   LEFT JOIN IACWRM WRM
        ON WRM.WORKKEYR = WRK.WORKKEY AND WRKRELTYPE = 'MERG'

        UNION

        SELECT WORKREF , WRKISREF, COALESCE (WORKKEYM, 0)
        FROM WORKREF_SOCIETY JOIN IACWRK ON INT(WORKREF) = WORKKEY
                            LEFT JOIN IACWRM ON WORKKEYR = WORKKEY AND WRKRELTYPE = 'MERG'
        WHERE WRKREFTYPE1 = 'IWKEY';

        clear parentworkkeyarray;
        exec sql close workKeySocWorkCodesCur;
        exec sql open workKeySocWorkCodesCur;
        exec sql fetch workKeySocWorkCodesCur for 500 rows into :parentWorkKeyArray;
        *inlr = *on;

最佳答案

在我说别的之前，关于嵌入式 SQL，您必须了解两件事。首先是 SQL 预编译器按顺序读取源代码，不考虑子过程的执行顺序。二是declare cursor不是可执行语句，也就是编译时只供预编译器使用，然后注释掉。它不生成代码，但会影响为其他游标语句生成的代码。

我有点惊讶这个编译，但不是真的。原因是所有的 declare cursor 语句都在子过程中的程序末尾。根据documentation , declare cursor 必须在引用它的任何语句之前在物理上位于源中。在您的程序中，所有引用游标的语句都在其声明之前，但是 SQL 预处理器并不真正了解过程或程序流程，并且 declare cursor 语句不可执行，因此在运行时不会执行时间，因此不受过程的运行时调用顺序的影响。由于您的声明是静态的并且不依赖于过程参数，因此将它们与变量声明放在程序的开头。

同时查看程序的编译列表。引用宿主变量的游标的打开实际上是否有任何行表明它们正在使用这些宿主变量？查看打开，而不是声明。声明只是被注释掉，后面没有生成代码。

并且当您使用它时，set option 必须是预编译器遇到的第一个 SQL 语句，否则它什么都不做。所以一定要移动它。结构应该是这样的:

ctl-opt ....

dcl-* ...

exec sql set option ...

exec sql declare cursor ...

rest of the program

有一个异常(exception)，那就是当您的声明游标依赖过程参数作为主变量时。在这种情况下，我为 OpenCursor、FetchCursor 和 CloseCursor 创建了单独的过程，这些过程在程序中按顺序放置，OpenCursor 过程做的第一件事就是声明其游标。这并不是为了让 declare cursor 在打开之前运行，而是为了让在 declare cursor 语句中用作主变量的过程参数在声明的范围内。游标过程的顺序对预编译器很重要。 OpenCursor 必须放在第一位(不管它们被调用的顺序如何)，因为预编译器需要游标的定义，以便它可以正确生成执行 open cursor、fetch cursor 的代码 和 close cursor 语句。

关于db2 - 在 rpgle 中将游标提取到数组中的性能缓慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/73860521/

文章推荐： c# - 如何为 `dotnet new` 设置变量？

文章推荐： javascript - react 触发无限请求

文章推荐： c++ - 使用两个不同的键使对象可搜索

SQL Server - 游标
我正在尝试使用游标遍历表: DEClARE @ProjectOID as nvarchar (100) DECLARE @TaskOID as nvarchar (100) DECLARE TaskO
java - JOptionPane 游标
使用 JOprionPane 时，光标出现了一些问题。我将光标设置到 pharent 框架，然后使用这个显示一个对话框: Object[] possibilities = {"ham", "spam"
SQL Server 游标
我想将数据从一个表(原始数据，所有列都是 VARCHAR)复制到另一个表(使用相应的列格式进行格式化)。为了将数据从 rawdata 表复制到 formatted 表中，我使用游标来识别受影响的行。
SQL 游标...您会捍卫任何用例？
我先走了我 100% 属于集合运算阵营。但是当设置逻辑时会发生什么在整个所需的输入域上进行检索会导致如此大的检索，以至于查询显着减慢，变得缓慢，或者基本上需要无限的时间？在这种情况下，我将使用可能
MySql 工作台 - 游标
为什么我不能这样做？我想从 TABLEA 中搜索大于光标值的最接近的值，对两者执行平均函数并将结果放入 test3 中。我收到错误代码 1054 未知列“Xnearest in 'field list
MySQL 游标/获取
我希望以下存储例程返回一系列行，但它只返回 1: CREATE PROCEDURE example() BEGIN DECLARE current_id INT;
更新表的 PostgreSQL 游标
我有一张代表患者体检的表，它有检查 ID 和患者 ID。我想逐行浏览表格并获取每个患者 ID 并比较其不同的咨询，看看它是否被视为“new_attack”。我正在处理疟疾疾病，我们认为每个在过去 6
带参数的 PostgreSQL 游标
如文档所述here ，我需要声明一个在打开时接受参数的游标。我的查询类似于: DECLARE cur CURSOR (argName character varying) FOR SELECT *
sql - PostgreSQL 游标
我正在尝试使用 PostgreSQL 学习基本游标。这是我的脚本: DECLARE cur_employees CURSOR FOR SELECT * FROM employee CLOS
当前的 MYSQL 游标
*DELIMITER // create procedure test(OUT l_out INT) begin DECLARE done INT DEFAULT FALSE; declare l_s
Python psycopg2 游标
来自 psycopg2 文档: When a database query is executed, the Psycopg cursor usually fetches all the record
java - 游标 while 循环返回除最后一个值之外的每个值
我正在使用 while 循环遍历游标，然后输出数据库中每个点的经度和纬度值。出于某种原因，它没有返回光标中的最后一组(或第一个取决于我是否使用 Cursor.MoveToLast)经度和纬度值。这
PHPStorm 4 游标
不知道有没有人试过全新的PHPStorm 4 , 但我遇到了这个新版本的问题，而我以前的主要版本 (PHPStorm 3) 没有。基本上，当我单击代码 View 空白处的任意位置时，光标会设置在该位
mysql的存储过程、游标、事务实例详解
mysql的存储过程、游标、事务实例详解下面是自己曾经编写过的mysql数据库存储过程，留作存档，以后用到的时候拿来参考。其中，涉及到了存储过程、游标（双层循环）、事务。【说明】：代码
mysql存储过程游标循环使用介绍
Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里
sql - 游标/递归是这里唯一可能的选择吗？基于某些滞后条件的集合逻辑
我正在使用 SQL Server，我有一个包含 3 列(时间序列)的表 data ，带日期，hour开始，AwardStatus . 大部分奖励状态是随机生成的。有两种选择，授予或未授予。但是，业务
sql - 为什么人们如此讨厌 SQL 游标？
就目前情况而言，这个问题不太适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持，但这个问题可能会引发辩论、争论、民意调查或扩展讨论。如果您觉得这个问题可以改进并可能重新开放，visit
oracle - 具有最终记录副本的 Oracle 游标
Why am getting duplicate records ? pls correct me.Thanks in Advance. declare clazzes_rec clazzes%r
oracle - 具有最终记录副本的 Oracle 游标
Why am getting duplicate records ? pls correct me.Thanks in Advance. declare clazzes_rec clazzes%r
sql - sql - 设置顺序值而不循环/游标
我需要在数据表中设置一个非唯一标识符。这在组内是连续的，即。对于每个组，ID 应从 1 开始，并以 1 为增量递增，直到该组的最后一行。下表对此进行了说明。 “新 ID”是我需要填充的列。 Uniq

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

db2 - 在 rpgle 中将游标提取到数组中的性能缓慢