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

Question

我有这个相对简单的 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;

Answer 1

在我说别的之前，关于嵌入式 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 语句。