plsql

 游标可以作为for..loop 循环，如下

 

CURSOR CUR_MOVES IS
      SELECT CMBM.TRANSACTION_DATE,
             CMBML.BATCH_MOVE_ID,
             CMBML.BATCH_MOVE_LINE_ID,
             CMBML.MOVE_ORDER_ID,
             CMBML.MOVE_ORDER_LINE_ID,
             CMBML.TRANSACTION_SOURCE_TYPE_ID,
             CMBML.TRANSACTION_ACTION_ID,
             CMBML.TRANSACTION_TYPE_ID,
             CMBML.ORGANIZATION_ID,
             CMBML.INVENTORY_ITEM_ID,
             CMBML.ITEM_NUMBER,
             CMBML.PRIMARY_UOM_CODE,
             CMBML.FROM_SUBINVENTORY,
             CMBML.FROM_LOCATOR_ID,
             CMBML.FROM_LOCATOR,
             CMBML.TO_SUBINVENTORY,
             CMBML.TO_LOCATOR_ID,
             CMBML.LOT_NUMBER,
             CMBML.TRANSACTION_QUANTITY
      FROM   CUX.CUX_MTL_BATCH_MOVE       CMBM,
             CUX.CUX_MTL_BATCH_MOVE_LINES CMBML
      WHERE  CMBM.BATCH_MOVE_ID = CMBML.BATCH_MOVE_ID
     -- and    cmbm.organization_id=cmbml.organization_id --add by wz 2013/05/22
     -- and    cmbm.organization_id=p_organization_id --add by wz  2013/05/22
      AND    CMBML.ERROR_FLAG = 'N'
      AND    CMBML.BATCH_MOVE_ID = PI_BATCH_MOVE_ID
      AND    NOT EXISTS
       (SELECT 1
              FROM   MTL_MATERIAL_TRANSACTIONS MMT
              WHERE  MMT.SOURCE_CODE = 'MOVE ORDER'
              AND    MMT.TRANSACTION_TYPE_ID = 64 --Move Order Transfer
              AND    MMT.ATTRIBUTE_CATEGORY = '整批发料'
              AND    MMT.ATTRIBUTE1 = CMBML.BATCH_MOVE_ID
              AND    MMT.ATTRIBUTE2 = CMBML.BATCH_MOVE_LINE_ID)
      AND    NOT EXISTS
       (SELECT 1
              FROM   MTL_TRANSACTIONS_INTERFACE MTI
              WHERE  MTI.SOURCE_CODE = 'MOVE ORDER'
              AND    MTI.TRANSACTION_TYPE_ID = 64 --Move Order Transfer
              AND    MTI.ATTRIBUTE_CATEGORY = '整批发料'
              AND    MTI.ATTRIBUTE1 = CMBML.BATCH_MOVE_ID
              AND    MTI.ATTRIBUTE2 = CMBML.BATCH_MOVE_LINE_ID);
  BEGIN
  -------------------------------------------------------------------------------------------------------------------------------------------------------------
    FOR REC_MOVES IN CUR_MOVES LOOP  --cur_moves为上面的游标。
      BEGIN
        SELECT ACCT_PERIOD_ID
        INTO   V_ACCT_PERIOD_ID
        FROM   ORG_ACCT_PERIODS
        WHERE  ORGANIZATION_ID = REC_MOVES.ORGANIZATION_ID
       -- WHERE  ORGANIZATION_ID = nvl(REC_MOVES.ORGANIZATION_ID,83)---edit by wz 2013/05/22
        AND    TRUNC(SYSDATE) BETWEEN TRUNC(PERIOD_START_DATE) AND
               TRUNC(SCHEDULE_CLOSE_DATE);
      EXCEPTION
        WHEN OTHERS THEN
          FND_FILE.PUT_LINE(FND_FILE.LOG, '不能取得当前日期的库存会计期.');
          RAISE SUBMIT_FAILED;
      END;