Use Cursor in PL/SQL

[Asvni]

Hi,

I am new to Oracle and started to write procedures to fetch records of certain years and do a comparison between 3 years. Once that is computed, I need to do a division between third year and the first year(C/A). ALso need to do a division between third year and second Year (C/B).
Below is the example:

Month 1 Month 2
1998 A A
1999 B B
2000 C C
C/A C/A
C/B C/B


1999 A A
2000 B B
2001 C C
C/A C/A
C/B C/B

I am stuck with the division part because I am getting too may rows error.
I would like to know how can I write a cursor to put in the division values for the each 3 years comparison?
I would really appreaciate if someone could help me give me guidance in writing cursors because I don't know the best way to solve this problem?
Please help.
Thank you.

 

[rcurva]

You can give this a try;

CREATE OR REPLACE
PROCEDURE myproc (pstartyr NUMBER, pendyr NUMBER) IS

CURSOR year_cur
SELECT amount, year, month
FROM table1
WHERE year BETWEEN pstartyr AND pendyr
AND month = v_month;

v_amount_1 NUMBER;
v_amount_2 NUMBER;
v_ctr NUMBER;
v_ca NUMBER;
v_cb NUMBER;

BEGIN
v_ctr := 0;

FOR i IN 1..12 LOOP

v_month := i;

FOR rec IN year_cur LOOP
v_ctr := v_ctr + 1;

IF v_ctr >= 3 THEN

v_ca := rec.amount/v_amount_1; -- C/A
v_cb := rec.amount/v_amount_2; -- C/B

INSERT INTO table ..

v_amount_1 := v_amount_2;
v_ctr := 1;

ELSIF v_ctr = 1 THEN
v_amount_1 := rec.amount;

ELSIF v_ctr = 2 THEN
v_amount_2 := rec.amount;

END IF;

END LOOP;


END LOOP;

END;
/

 

[Asvni]

Dear Rcurva,

Thank you for answering my question. However, this doesn't help to solve my problem. I will explain what I have done:
in my procedure which only calculates ORIGINAL,INFORCE1 and INFORCE 2 values:

Procedure Persistency(Start_Year,Month)--start year is 2001
--month is January
n_start_year number;
test number;

begin

test :=start_year
n_start_year:=start_year-3;

while test>=n_start_year loop
if start_year:=2001 then
persist_original_2001;
persist_inforce1_2001;
persist_inforce2_2001;
elseif start_year:=2000 then
persist_original_2000;
persist_inforce1_2000;
persist_inforce2_2000;
else
persist_original;
persist_inforce1;
persist_inforce2;
end if;
test := test-1;
end loop;
end;

The result generated is excellent. When I try to insert another procedure after persist_inforce2 procedure in each if case to do the division calculation, and execute the procedure,I get the error message 'Too many error'.
That is why I thought of opting for cursor. But how do I incorporate it so that I can calculate the values immediately after the 3 sub procedures(i.e. persist_original_2001,
persist_inforce1_2001,
persist_inforce2_2001) in the if else case?

My table which contains the values look like below except for Ratio 1 and Ratio 2 which involves the division factor and that is the one I am stuck with. Can anyone help me on this?



Jan Feb ... Dec
2000-2001 ORIGINAL 4183331.55 4247746.02 ... 5559570.42
2000-2001 INFORCE1 4183331.55 4247746.02 ... 5559570.42
2000-2001 INFORCE2 4183331.55 4247746.02 ... 5559570.42
Ratio 1 Inforce2/Original for each month
Ratio 2 Inforce2/Inforce1 for each month

1999-2000 ORIGINAL 4860722.01 4115821.11 ... 4899207.07
2000-2001 INFORCE1 3148999.29 2760830.13 ... 3336004.16
2000-2001 INFORCE2 3148999.29 2760830.13 ... 3336004.16
Ratio 1 Inforce2/Original for each month
Ratio 2 Inforce2/Inforce1 for each month


1998-1999 ORIGINAL 3429196.83 3572398.00 ... 4837588.20
1999-2000 INFORCE1 2262203.33 2258530.52 ... 3040696.00
2000-2001 INFORCE2 1761107.56 1823631.11 ... 2389499.92
Ratio 1 Inforce2/Original for each month
Ratio 2 Inforce2/Inforce1 for each month

1997-1998 ORIGINAL 4130881.92 4656573.24 ... 2902663.51
1998-1999 INFORCE1 2821150.32 3416201.79 ... 1922090.10
1999-2000 INFORCE2 1957787.56 2303347.37 ... 1451823.67
Ratio 1 Inforce2/Original for each month
Ratio 2 Inforce2/Inforce1 for each month

 

[dudeami]

Why not do a self join and do it all in the select?

Select a.year, a.month1, a.month1/b.month1, a.month1/c.month1 ...
From table1 a, table1 b, table1 c
where b.year = a.year-1
and c.year = a.year -2
and a.year = 2001

Or am I missing something?

FYI... you would probably want your select statement to be ...
Select a.year, a.month1, Decode(b.month1,0,0,a.month1/b.month1), Decode(c.month1,0,0,a.month1/c.month1)
to avoid a divide by zero error, if there is a chance that your data will contain zeros.

Also, an outer join might be a good idea, if there is a chance that past year doesn't exist...

 

[Asvni]

Dear Dudeami,

I cannot do the self-join as there is no join with any of the columns since there is no similarities. & this in return, gives more than 1 value(not cartesan product). If I hardcode a.year=2001,it gives me too many values.
Anyway, thank you.

Is there any other way to do the division calculation?

I used Report Builder to project a tabular report from the table which stores all the values for Original, Inforce 1 and Inforce 2 for all the mentioned years like below:

Jan Feb ... Dec
2000-2001 ORIGINAL 4183331.55 4247746.02 ... 5559570.42
2000-2001 INFORCE1 4183331.55 4247746.02 ... 5559570.42
2000-2001 INFORCE2 4183331.55 4247746.02 ... 5559570.42


1999-2000 ORIGINAL 4860722.01 4115821.11 ... 4899207.07
2000-2001 INFORCE1 3148999.29 2760830.13 ... 3336004.16
2000-2001 INFORCE2 3148999.29 2760830.13 ... 3336004.16


1998-1999 ORIGINAL 3429196.83 3572398.00 ... 4837588.20
1999-2000 INFORCE1 2262203.33 2258530.52 ... 3040696.00
2000-2001 INFORCE2 1761107.56 1823631.11 ... 2389499.92


1997-1998 ORIGINAL 4130881.92 4656573.24 ... 2902663.51
1998-1999 INFORCE1 2821150.32 3416201.79 ... 1922090.10
1999-2000 INFORCE2 1957787.56 2303347.37 ... 1451823.67


I put a function for Ratio1 to do the division using Inforce2 divide Original after each 3 years, but this again raised error: ORA01422:exact fetch returns more than requested number of rows.

Can someone please help me - I need to give this report asap. Please help.
Thank you in advance.

 

[carp]

Here's one approach, and I don't think cursors are needed:

DECLARE
A asvni%ROWTYPE;
B asvni%ROWTYPE;
C asvni%ROWTYPE;
v_max_year NUMBER;
BEGIN
SELECT max(year) INTO v_max_year
FROM asvni;
--
SELECT * INTO C
FROM asvni
WHERE year = v_max_year;
--
SELECT * INTO B
FROM asvni
WHERE year = v_max_year - 1;
--
SELECT * INTO A
FROM asvni
WHERE year = v_max_year - 2;
--
dbms_output.put_line(a.year||' => '||a.month1||' => '||a.month2);
dbms_output.put_line(b.year||' => '||b.month1||' => '||b.month2);
dbms_output.put_line(c.year||' => '||c.month1||' => '||c.month2);
dbms_output.put_line('C/A => '||round(c.month1/a.month1,3)||' => '||round(c.month2/a.month2,3));
dbms_output.put_line('C/B => '||round(c.month1/b.month1,3)||' => '||round(c.month2/b.month2,3));
END;
/

My data looks like
SQL> select * from asvni
2 ;

YEAR MONTH1 MONTH2
---------- ---------- ----------
1999 3 5
2000 7 11
2001 13 17

and the results look like:

1999 => 3 => 5
2000 => 7 => 11
2001 => 13 => 17
C/A => 4.333 => 3.4
C/B => 1.857 => 1.545

You'll need to tweak the output to suit your needs. Also note that, depending on your data, you'll need to adjust the code to handle the possibilites of NULL and zero values, as well as the possibility of having only one or two years worth of data.