how to use plsql to calculate compond growth

[David]

I am learning plsql. I would like to run a stored procedure to
calculate my bank account value by predicted 10% annual growth rate.
Below is my plsql that is having problems. Your help is highly
appreciated.

Thanks

declare
money number := 50000.00;
year number := 1;
begin for i in 1..17
loop
execute immediate 'insert into my_401k values (':year', 'money + 0.10
* money')';
year := year + 1;
end loop;
end;
/
SP2-0552: Bind variable "YEAR" not declared.

Any where wrong in this script?

Thanks

[David]

Frank van Bortel <fv********@net scape.net> wrote in message news:<bs******* ***@news2.tilbu 1.nb.home.nl>.. .

Ken Denny wrote:

nu**********@re diffmail.com (nullpointer) wrote in
news:c0******** *************** ***@posting.goo gle.com:

PL SQL autobinds so no need to specify those as bind variables
This should work

1 declare
2 money number :=50000.00;
3 year number :=1;
4 begin for i in 1..17
5 loop insert into my_401k values(year, money + 0.1*money);
6 year := year +1;
7 end loop;
8* end;

Hope it Helps
nullpointer

That will get around the error message, but it won't give you the results
you're looking for. Note: you never changed the value of the variable
money, so every year money is going to have 55000.00 in the moey column.

1 declare
2 money number := 50000.00;
3 year number := 1;
4 begin
5 for i in 1..17
6 loop
7 money := money *1.1;
8 execute immediate 'insert into my_401k values (:y,:m)'
9 using year, money;
10 year := year + 1;
11 end loop;
12* end;

SQL> col money for 999,999,990.00
SQL> select * from my_401k;

YEAR MONEY
---------- ---------------
1 55,000.00
2 60,500.00
3 66,550.00
4 73,205.00
5 80,525.50
6 88,578.05
7 97,435.86
8 107,179.44
9 117,897.38
10 129,687.12
11 142,655.84

YEAR MONEY
---------- ---------------
12 156,921.42
13 172,613.56
14 189,874.92
15 208,862.41
16 229,748.65
17 252,723.51

17 rows selected.

Frank,

How about if I add 12000 anually to my account? How to calculate that?

Best Regards,

David

[Frank van Bortel]

David wrote:

Frank van Bortel <fv********@net scape.net> wrote in message news:<bs******* ***@news2.tilbu 1.nb.home.nl>.. .

Ken Denny wrote:

nu********** @rediffmail.com (nullpointer) wrote in
news:c0***** *************** ******@posting. google.com:

PL SQL autobinds so no need to specify those as bind variables
This should work

1 declare
2 money number :=50000.00;
3 year number :=1;
4 begin for i in 1..17
5 loop insert into my_401k values(year, money + 0.1*money);
6 year := year +1;
7 end loop;
8* end;

Hope it Helps
nullpoint er
That will get around the error message, but it won't give you the results
you're looking for. Note: you never changed the value of the variable
money, so every year money is going to have 55000.00 in the moey column.

1 declare
2 money number := 50000.00;
3 year number := 1;
4 begin
5 for i in 1..17
6 loop
7 money := money *1.1;
8 execute immediate 'insert into my_401k values (:y,:m)'
9 using year, money;
10 year := year + 1;
11 end loop;
12* end;

SQL> col money for 999,999,990.00
SQL> select * from my_401k;

YEAR MONEY
---------- ---------------
1 55,000.00
2 60,500.00
3 66,550.00
4 73,205.00
5 80,525.50
6 88,578.05
7 97,435.86
8 107,179.44
9 117,897.38
10 129,687.12
11 142,655.84

YEAR MONEY
---------- ---------------
12 156,921.42
13 172,613.56
14 189,874.92
15 208,862.41
16 229,748.65
17 252,723.51

17 rows selected.

Frank,

How about if I add 12000 anually to my account? How to calculate that?

Best Regards,

David

It's in there - here it goes with comments:
-- this is a so-called anonymous PL/SQL block; it has no
-- Procedure name.
declare -- Start declaration section - yes I have done Cobol...
money number := 50000.00; -- define a variable named "money" as
-- a number, and initialize it
year number := 1; -- ditto, for "year"
begin -- The PL/SQL starts here
for i in 1..17 -- initialize a loop variable
loop -- top end of the loop
money := money *1.1; -- recalculate "money", up it with 10%
execute immediate 'insert into my_401k values (:y,:m)'
using year, money; -- actual insert into table
year := year + 1; -- recalculate the year, up it with 1
end loop; -- OK that's all from the loop.
-- If i reached the value 17 we would
-- be done
end; -- nothing left to do after the
-- loop completes, tell PL/SQL to stop

I'll leave it up to you how to add 12k annually, instead of 10%
--
A prosperous 2004,
Regards,
Frank van Bortel