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

[Jim Kennedy]

"David" <ni*******@yahoo.com> wrote in message
news:37*************************@posting.google.co m...

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
Much simpler and more efficient to:(no need for execute immediate) declare
money number := 50000.00;
year number := 1;
begin for i in 1..17
loop insert into my_401k values (:year, :money + 0.10* :money); year := year + 1;
end loop;
end;
/

[Mark C. Stock]

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:oqvHb.682774$Fm2.590452@attbi_s04...
|
| "David" <ni*******@yahoo.com> wrote in message
| news:37*************************@posting.google.co m...
| > 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
|
| Much simpler and more efficient to:(no need for execute immediate)
| > declare
| > money number := 50000.00;
| > year number := 1;
| > begin for i in 1..17
| > loop
| insert into my_401k values (:year, :money + 0.10* :money);
| > year := year + 1;
| > end loop;
| > end;
| > /
| >
|
|

jim's solution is simpler and probably the best way to issue this INSERT,
since the statement is not dynamic (only the values)

however, if you did need to use execute immediate, you could either just
concatenate the local PL/SQL variable value (your concatenation operators
where missing as well):

| > execute immediate 'insert into my_401k values ('||year||', '||money +
0.10||')'

or better yet (again, if execute immediate was necessary because the INSERT
statement was not known at compile time), use proper bind variable syntax
(see the USING keyword in the PL/SQL manual's EXECUTE IMMEDIATE section)

also, a very important reminder, and an observation

[_] always (always, always, always) explicitly list the column in your
INSERT statement -- never rely on insert into TABLE values (....); as soon
as the structure of the table changes, your code breaks
[_] do you really need a persistent record of the values stored in the
database? if not, don't do database inserts when a simply using PL/SQL
variables would suffice
--
Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting

[David]

Hi Jim,

Thank you for your help. I still got the same error message based on your code

Any where wrong?

Thanks

David

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;
nli@HOPE1> /
SP2-0552: Bind variable "MONEY" not declared.


ni*******@yahoo.com (David) wrote in message news:<37*************************@posting.google.c om>...

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

[Hans Forbrich]

David wrote:

Hi Jim,

Thank you for your help. I still got the same error message based on your code

Any where wrong?

Thanks

David

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;
nli@HOPE1> /
SP2-0552: Bind variable "MONEY" not declared.

ni*******@yahoo.com (David) wrote in message news:<37*************************@posting.google.c om>...

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

Since this is a PL/SQL variable, does it need to be bound? What happens
when you drop the semi-colons?

What I understand from bind variables is they are used to bind between
the host environment (eg C, VB, SQL*Plus) and the SQL & PL/SQL
environment. Flames for correction appreciated.

/Hans

[nullpointer]

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

***********************************************

ni*******@yahoo.com (David) wrote in message news:<37**************************@posting.google. com>...

Hi Jim,

Thank you for your help. I still got the same error message based on your code

Any where wrong?

Thanks

David

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;
nli@HOPE1> /
SP2-0552: Bind variable "MONEY" not declared.


ni*******@yahoo.com (David) wrote in message news:<37*************************@posting.google.c om>...

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

[Ken Denny]

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

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.

--
Ken Denny
http://www.kendenny.com/

[Frank van Bortel]

Hans Forbrich wrote:

David wrote:

Hi Jim,

Thank you for your help. I still got the same error message based on your code

Any where wrong?

Thanks

David

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;
nli@HOPE1> /
SP2-0552: Bind variable "MONEY" not declared.

ni*******@yahoo.com (David) wrote in message news:<37*************************@posting.google.c om>...

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

Since this is a PL/SQL variable, does it need to be bound? What happens
when you drop the semi-colons?

What I understand from bind variables is they are used to bind between
the host environment (eg C, VB, SQL*Plus) and the SQL & PL/SQL
environment. Flames for correction appreciated.

/Hans

No - it does not need binding; just loose the ':' (and quotes):
insert into my_401k values(year, 1.1*money) ;

The exec imm *can* use bind variables:
execute immediately ('insert into my_401k values(:y, :m)'
using year, money * 1.1;

Have never used the using money * 1.1 sytax though, so it's
without warrenty.
Oh - heck:
SQL> create table my_401k (year number, money number(12,2));
1 declare
2 money number := 50000.00;
3 year number := 1;
4 begin
5 for i in 1..17
6 loop
7 execute immediate 'insert into my_401k values (:y,:m)'
8 using year, money * 1.1;
9 year := year + 1;
10 end loop;
11* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from my_401k;

YEAR MONEY
---------- ----------
1 55000
2 55000
3 55000
4 55000
5 55000
6 55000
7 55000
8 55000
9 55000
10 55000
11 55000

YEAR MONEY
---------- ----------
12 55000
13 55000
14 55000
15 55000
16 55000
17 55000

--
A prosperous 2004,
Regards,
Frank van Bortel

[Frank van Bortel]

Ken Denny wrote:

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

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.

--
A prosperous 2004,
Regards,
Frank van Bortel

[David]

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

Ken Denny wrote:

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

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,

Thank you very much. That is exactly I was looking for.

Have a Happy and Prosporous New Year!

David

[David]

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

Ken Denny wrote:

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

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********@netscape.net> wrote in message news:<bs**********@news2.tilbu1.nb.home.nl>...

Ken Denny wrote:

nu**********@rediffmail.com (nullpointer) wrote in
news:c0**************************@posting.googl e.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

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