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  |
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  |
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;
/
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 01:53:11 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
