Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Loop (Oracle 10.2.0.3.0)
PL/SQL Loop [message #340631] Wed, 13 August 2008 08:46 Go to next message
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
Member
Hello,

I'd like to ask for some help to optimize / correct my following PL/SQL Code:

CREATE OR REPLACE PROCEDURE lux1006.calc_b2a_per_quarter_v3 (
   p_client            VARCHAR2,
   p_begin_date   IN   DATE,
   p_end_date     IN   DATE
)
IS
   v_avg_aum         NUMBER;
   v_ratio           NUMBER;
   v_fund            VARCHAR2 (100);
   v_quarter_end     DATE;
   v_quarter_start   DATE;
   v_tradedate       DATE;

   CURSOR c_retrieve_avg_aum
   IS
/* Select the Avg AuM converted in MFRS_TEST.CURRENCY */
      SELECT DISTINCT fund, SUM (assetcur)
                 FROM (SELECT   dbc.fund, mfrs_test.currency,
                                AVG (volume * e1.to_usd / e2.to_usd
                                    ) AS assetcur
                           FROM lux1006.daily_calc_7223 dbc,
                                lux1006.EXCHANGE e1,
                                lux1006.EXCHANGE e2,
                                lux1006.mfrs_test mfrs_test
                          WHERE dbc.clientcode = mfrs_test.clientcode
                            AND dbc.fund = mfrs_test.fund
                            AND e1.tradedate = v_quarter_end
                            AND e2.tradedate = v_quarter_end
                            AND dbc.tradedate BETWEEN v_quarter_start
                                                  AND v_quarter_end
                            AND e1.currency = mfrs_test.currency
                            AND e2.currency = dbc.tradeccy
                            AND mfrs_test.nr = 1
                       GROUP BY dbc.fund, mfrs_test.currency)
             GROUP BY fund
             ORDER BY fund;
BEGIN
   DBMS_OUTPUT.put_line ('p_begin_date: ' || p_begin_date);
   DBMS_OUTPUT.put_line ('p_end_date: ' || p_end_date);

   SELECT TRUNC (p_begin_date, 'Q')
     INTO v_quarter_start
     FROM DUAL;

   DBMS_OUTPUT.put_line ('v_quarter_start: ' || v_quarter_start);

   SELECT ADD_MONTHS (TRUNC (p_begin_date, 'Q'), 3) - 1
     INTO v_quarter_end
     FROM DUAL;

   DBMS_OUTPUT.put_line ('v_quarter_end: ' || v_quarter_end);

   /* Open Cursor  - needs to opened BEFORE the Loop Starts */
   OPEN c_retrieve_avg_aum;

   LOOP
      v_avg_aum := 0;
      v_ratio := 0;
      v_fund := NULL;
      v_tradedate := NULL;

      /* fetch first line of cursor into variables v_fund and v_avg_aum */
      FETCH c_retrieve_avg_aum
       INTO v_fund, v_avg_aum;

      IF v_quarter_end < p_end_date AND c_retrieve_avg_aum%NOTFOUND
      THEN
         v_quarter_start := v_quarter_end + 1;
         v_quarter_end := ADD_MONTHS (TRUNC (v_quarter_start, 'Q'), 3) - 1;
         DBMS_OUTPUT.put_line ('v_quarter_start: ' || v_quarter_start);
         DBMS_OUTPUT.put_line ('v_quarter_end: ' || v_quarter_end);

         CLOSE c_retrieve_avg_aum;

         OPEN c_retrieve_avg_aum;

         LOOP
            FETCH c_retrieve_avg_aum
             INTO v_fund, v_avg_aum;

            EXIT WHEN c_retrieve_avg_aum%NOTFOUND;

            /* select the right percentage out of table mfrs_test */
            SELECT rebate_percent
              INTO v_ratio
              FROM (SELECT clientcode, nr, fund, rebate_percent
                      FROM mfrs_test
                     WHERE fund = v_fund
                       AND holding_from < v_avg_aum
                       AND clientcode = p_client)
             WHERE nr IN (
                      SELECT MAX (nr)
                        FROM mfrs_test
                       WHERE fund = v_fund
                         AND holding_from < v_avg_aum
                         AND clientcode = p_client);

            /* Update daily_calc table with the average aum, new percentage for recalculation and sysdate */
            UPDATE lux1006.daily_calc_7223 dbc
               SET dbc.total_aum_distr_ccy = v_avg_aum,
                   dbc.rebate_percentage = v_ratio,
                   dbc.imported_on = SYSDATE
             --dbc.flag = 12
            WHERE  dbc.clientcode = p_client
               AND dbc.fund = v_fund
               AND dbc.tradedate >= v_quarter_start
               AND dbc.tradedate <= v_quarter_end;

            DBMS_OUTPUT.put_line (   'IN IF LOOP: Fund: '
                                  || v_fund
                                  || ' has following AVG_AUM '
                                  || v_avg_aum
                                  || ' and following rebate percentage '
                                  || v_ratio
                                  || ' for following quarter '
                                  || TO_CHAR (v_quarter_end, 'YYYY-Q')
                                 );
         END LOOP;
      ELSIF v_quarter_end >= p_end_date AND c_retrieve_avg_aum%NOTFOUND
      THEN
         DBMS_OUTPUT.put_line ('NOW EXIT');
         EXIT;
      ELSE
         DBMS_OUTPUT.put_line ('CONTINUE');
      END IF;
      
      
      /* select the right percentage out of table mfrs_test */
      SELECT rebate_percent
        INTO v_ratio
        FROM (SELECT clientcode, nr, fund, rebate_percent
                FROM mfrs_test
               WHERE fund = v_fund
                 AND holding_from < v_avg_aum
                 AND clientcode = p_client)
       WHERE nr IN (
                SELECT MAX (nr)
                  FROM mfrs_test
                 WHERE fund = v_fund
                   AND holding_from < v_avg_aum
                   AND clientcode = p_client);

      /* Update daily_calc table with the average aum, new percentage for recalculation and sysdate */
      UPDATE lux1006.daily_calc_7223 dbc
         SET dbc.total_aum_distr_ccy = v_avg_aum,
             dbc.rebate_percentage = v_ratio,
             dbc.imported_on = SYSDATE
       --dbc.flag = 12
      WHERE  dbc.clientcode = p_client
         AND dbc.fund = v_fund
         AND dbc.tradedate >= v_quarter_start
         AND dbc.tradedate <= v_quarter_end;

      /* DBMS_OUTPUT is just for information */
      DBMS_OUTPUT.put_line (   'Fund: '
                            || v_fund
                            || ' has following AVG_AUM '
                            || v_avg_aum
                            || ' and following rebate percentage '
                            || v_ratio
                            || ' for following quarter '
                            || TO_CHAR (v_quarter_end, 'YYYY-Q')
                           );

      
      
   /* As soon the cursor is empty, exit the loop */
   --EXIT WHEN c_retrieve_avg_aum%NOTFOUND;
   END LOOP;

   CLOSE c_retrieve_avg_aum;

   COMMIT;
END calc_b2a_per_quarter_v3;
/


DBMS_OUTPUT RESULT:

p_begin_date: 01-JAN-08
p_end_date: 30-JUN-08
v_quarter_start: 01-JAN-08
v_quarter_end: 31-MAR-08
CONTINUE
Fund: XXX has following AVG_AUM 252043.08 and following rebate percentage .085 for following quarter 2008-1
CONTINUE
Fund: XXX has following AVG_AUM 39061356.78 and following rebate percentage .05 for following quarter 2008-1
v_quarter_start: 01-APR-08
v_quarter_end: 30-JUN-08
IN IF LOOP: Fund: XXX has following AVG_AUM 206073.00 and following rebate percentage .085 for following quarter 2008-2
IN IF LOOP: Fund: XXX has following AVG_AUM 37885351.98 and following rebate percentage .05 for following quarter 2008-2
Fund: XXX has following AVG_AUM 37885351.98 and following rebate percentage .05 for following quarter 2008-2
NOW EXIT


Target:

The procedure has to calculate the values quarter by quarter.

But the Dates given to the procedure by parameters could contain more than on quarter.

For example:
The Begin date provided as parameter is: 01-jan-08
The End date provided as parameter is: 30-jun-08

Question

1.) What is the best way to use the cursor with the quarter start and quarter end dates.
--> In this example: Open the cursor once for Q1 2008 and then for Q2 2008 and update my table accordingly.

thanks for help / ideas.

Re: PL/SQL Loop [message #340638 is a reply to message #340631] Wed, 13 August 2008 09:48 Go to previous message
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
Member
I just tried another approach, which looks better for me.

Thanks for taking the time to point out mistakes that I might not see.

CREATE OR REPLACE PROCEDURE lux1006.calc_b2a_per_quarter_v3 (
   v_client            VARCHAR2,
   v_begin_date   IN   DATE,
   v_end_date     IN   DATE
)
IS
   v_avg_aum         NUMBER;
   v_ratio           NUMBER;
   v_fund            VARCHAR2 (100);
   v_quarter_end     DATE;
   v_quarter_start   DATE;

   CURSOR c_retrieve_avg_aum
   IS
/* Select the Avg AuM converted in MFRS_TEST.CURRENCY */
      SELECT DISTINCT fund, SUM (assetcur)
                 FROM (SELECT   dbc.fund, mfrs_test.currency,
                                AVG (volume * e1.to_usd / e2.to_usd
                                    ) AS assetcur
                           FROM lux1006.daily_calc_7223 dbc,
                                lux1006.EXCHANGE e1,
                                lux1006.EXCHANGE e2,
                                lux1006.mfrs_test mfrs_test
                          WHERE dbc.clientcode = mfrs_test.clientcode
                            AND dbc.fund = mfrs_test.fund
                            AND e1.tradedate = v_quarter_end
                            AND e2.tradedate = v_quarter_end
                            AND dbc.tradedate BETWEEN v_quarter_start
                                                  AND v_quarter_end
                            AND e1.currency = mfrs_test.currency
                            AND e2.currency = dbc.tradeccy
                            AND mfrs_test.nr = 1
                       GROUP BY dbc.fund, mfrs_test.currency)
             GROUP BY fund
             ORDER BY fund;
BEGIN
   DBMS_OUTPUT.put_line ('v_begin_date: ' || v_begin_date);
   DBMS_OUTPUT.put_line ('v_end_date: ' || v_end_date);

   SELECT TRUNC (v_begin_date, 'Q')
     INTO v_quarter_start
     FROM DUAL;

   DBMS_OUTPUT.put_line ('v_quarter_start: ' || v_quarter_start);

   SELECT ADD_MONTHS (TRUNC (v_begin_date, 'Q'), 3) - 1
     INTO v_quarter_end
     FROM DUAL;

   DBMS_OUTPUT.put_line ('v_quarter_end: ' || v_quarter_end);

   LOOP
      /* Open the cursor */
      OPEN c_retrieve_avg_aum;

      LOOP
         /* Fetch information for first quarter into the cursor and update afterwards the daily_calc_table */
         FETCH c_retrieve_avg_aum
          INTO v_fund, v_avg_aum;

         /* Exit the inner Loop, when Cursor is empty (when all information for the current Quarter has been executed) */
         EXIT WHEN c_retrieve_avg_aum%NOTFOUND;

         /* select the right percentage out of table mfrs_test */
         SELECT rebate_percent
           INTO v_ratio
           FROM (SELECT clientcode, nr, fund, rebate_percent
                   FROM mfrs_test
                  WHERE fund = v_fund
                    AND holding_from < v_avg_aum
                    AND clientcode = v_client)
          WHERE nr IN (
                   SELECT MAX (nr)
                     FROM mfrs_test
                    WHERE fund = v_fund
                      AND holding_from < v_avg_aum
                      AND clientcode = v_client);

         /* Update daily_calc table with the average aum, new percentage for recalculation and sysdate */
         UPDATE lux1006.daily_calc_7223 dbc
            SET dbc.total_aum_distr_ccy = v_avg_aum,
                dbc.rebate_percentage = v_ratio,
                dbc.imported_on = SYSDATE
          --dbc.flag = 12
         WHERE  dbc.clientcode = v_client
            AND dbc.fund = v_fund
            AND dbc.tradedate >= v_quarter_start
            AND dbc.tradedate <= v_quarter_end;

         DBMS_OUTPUT.put_line (   'Fund: '
                               || v_fund
                               || ' has following AVG_AUM '
                               || v_avg_aum
                               || ' and following rebate percentage '
                               || v_ratio
                               || ' for following quarter '
                               || TO_CHAR (v_quarter_end, 'YYYY-Q')
                              );
      END LOOP;

      /* Exit the outer Loop as soon the last quarter_end is equal or bigger then the End date AND the Cursor is empty (all information has been processed)*/
      EXIT WHEN v_quarter_end >= v_end_date AND c_retrieve_avg_aum%NOTFOUND;

      CLOSE c_retrieve_avg_aum;

      /* Calculate new Quarter information */
      /* Afterwards the inner loop is executed again (Cursor runs with new quarter information) */
      v_quarter_start := v_quarter_end + 1;
      v_quarter_end := ADD_MONTHS (TRUNC (v_quarter_start, 'Q'), 3) - 1;
      DBMS_OUTPUT.put_line ('v_quarter_start: ' || v_quarter_start);
      DBMS_OUTPUT.put_line ('v_quarter_end: ' || v_quarter_end);
   END LOOP;

   COMMIT;
END calc_b2a_per_quarter_v3;
/

Previous Topic: DESC MATERIALIZED VIEW
Next Topic: reg date function
Goto Forum:
  


Current Time: Fri Dec 09 03:59:33 CST 2016

Total time taken to generate the page: 0.13833 seconds