游标名称代替额外的变量 [英] Cursor name in place of extra variable

问题描述

这是我的程序.在其中我使用了名为"CUR_TO_COLLECT_DATA"的游标. 我还使用了一个名为"VAR_CUR_TO_COLLECT_DATA"的变量，该变量的表类型为"TBL_TRIAL_BALANCE_REPORT".

This is my procedure. in which I have used cursor named "CUR_TO_COLLECT_DATA". Also I have used a variable named "VAR_CUR_TO_COLLECT_DATA" which is of "TBL_TRIAL_BALANCE_REPORT" table type.

现在，我不想使用"VAR_CUR_TO_COLLECT_DATA"变量，而是要使用游标的名称，即"CUR_TO_COLLECT_DATA"代替"VAR_CUR_TO_COLLECT_DATA"变量.

Now I don't want to use "VAR_CUR_TO_COLLECT_DATA" variable, and want to use name of the cursor I.e "CUR_TO_COLLECT_DATA" in place of "VAR_CUR_TO_COLLECT_DATA" variable.

可以执行此任务吗?如果是，那么请帮助如何?

Is doing this task possible? If yes, then please help how?

PROCEDURE PR_TRIAL_BALANCEWITHOUTFOR IS

    CURSOR CUR_TO_COLLECT_DATA IS(
      SELECT NAME, SUM(CREDIT) AS CREDIT, SUM(DEBIT) AS DEBIT
        FROM (

              SELECT (SELECT GL_NAME
                         FROM QM_GL
                        WHERE QM_GL.GL_ID = QT_ACCOUNTING.GL_ID) AS NAME,
                      DECODE(QT_ACCOUNTING.TRANS_TYPE,
                             'CR',
                             (QT_ACCOUNTING.TRANS_AMOUNT),
                             0.00) AS CREDIT,
                      DECODE(QT_ACCOUNTING.TRANS_TYPE,
                             'DR',
                             (QT_ACCOUNTING.TRANS_AMOUNT),
                             0.00) AS DEBIT

                FROM QT_ACCOUNTING, QM_ACCOUNTING_PERIOD

               WHERE QT_ACCOUNTING.VALUE_DATE BETWEEN
                     QM_ACCOUNTING_PERIOD.PERIODFROM AND
                     QM_ACCOUNTING_PERIOD.PERIODTO
                 AND QM_ACCOUNTING_PERIOD.STATUS = 'O'

              )
       GROUP BY NAME);

    VAR_CUR_TO_COLLECT_DATA TBL_TRIAL_BALANCE_REPORT%ROWTYPE;

  BEGIN

    OPEN CUR_TO_COLLECT_DATA;

    LOOP

      FETCH CUR_TO_COLLECT_DATA
        INTO VAR_CUR_TO_COLLECT_DATA;

      IF (VAR_CUR_TO_COLLECT_DATA.CREDIT - VAR_CUR_TO_COLLECT_DATA.DEBIT) > 0 THEN
        INSERT INTO TBL_TRIAL_BALANCE_REPORT
        VALUES
          (VAR_CUR_TO_COLLECT_DATA.NAME,
           (VAR_CUR_TO_COLLECT_DATA.CREDIT - VAR_CUR_TO_COLLECT_DATA.DEBIT),
           0);
      END IF;
      IF (VAR_CUR_TO_COLLECT_DATA.DEBIT - VAR_CUR_TO_COLLECT_DATA.CREDIT) > 0 THEN
        INSERT INTO TBL_TRIAL_BALANCE_REPORT
        VALUES
          (VAR_CUR_TO_COLLECT_DATA.NAME,
           0,
           (VAR_CUR_TO_COLLECT_DATA.DEBIT - VAR_CUR_TO_COLLECT_DATA.CREDIT));
      END IF;

      EXIT WHEN CUR_TO_COLLECT_DATA%NOTFOUND;

    END LOOP;

    CLOSE CUR_TO_COLLECT_DATA;

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      BEGIN
        CLOSE CUR_TO_COLLECT_DATA;
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
  END;

推荐答案

您可以尝试执行以下操作:

You can try doing something like this:

PROCEDURE PR_TRIAL_BALANCEWITHOUTFOR IS

    CURSOR CUR_TO_COLLECT_DATA IS(
      SELECT NAME, SUM(CREDIT) AS CREDIT, SUM(DEBIT) AS DEBIT
        FROM (

              SELECT (SELECT GL_NAME
                         FROM QM_GL
                        WHERE QM_GL.GL_ID = QT_ACCOUNTING.GL_ID) AS NAME,
                      DECODE(QT_ACCOUNTING.TRANS_TYPE,
                             'CR',
                             (QT_ACCOUNTING.TRANS_AMOUNT),
                             0.00) AS CREDIT,
                      DECODE(QT_ACCOUNTING.TRANS_TYPE,
                             'DR',
                             (QT_ACCOUNTING.TRANS_AMOUNT),
                             0.00) AS DEBIT

                FROM QT_ACCOUNTING, QM_ACCOUNTING_PERIOD

               WHERE QT_ACCOUNTING.VALUE_DATE BETWEEN
                     QM_ACCOUNTING_PERIOD.PERIODFROM AND
                     QM_ACCOUNTING_PERIOD.PERIODTO
                 AND QM_ACCOUNTING_PERIOD.STATUS = 'O'

              )
       GROUP BY NAME);

  --  VAR_CUR_TO_COLLECT_DATA TBL_TRIAL_BALANCE_REPORT%ROWTYPE;

  BEGIN

    FOR REC_TO_COLLECT_DATA IN CUR_TO_COLLECT_DATA LOOP

      IF (REC_TO_COLLECT_DATA.CREDIT - REC_TO_COLLECT_DATA.DEBIT) > 0 THEN
        INSERT INTO TBL_TRIAL_BALANCE_REPORT
        VALUES
          (REC_TO_COLLECT_DATA.NAME,
           (REC_TO_COLLECT_DATA.CREDIT - REC_TO_COLLECT_DATA.DEBIT),
           0);
      END IF;
      IF (REC_TO_COLLECT_DATA.DEBIT - REC_TO_COLLECT_DATA.CREDIT) > 0 THEN
        INSERT INTO TBL_TRIAL_BALANCE_REPORT
        VALUES
          (REC_TO_COLLECT_DATA.NAME,
           0,
           (REC_TO_COLLECT_DATA.DEBIT - REC_TO_COLLECT_DATA.CREDIT));
      END IF;

    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;

顺便说一句，我不喜欢

      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;

part-如果有例外，您不应该让它沉默...

part- if there is an exception you shouldn't silence it...

这篇关于游标名称代替额外的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！