Re: how to use plsql to calculate compond growth

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:oqvHb.682774$Fm2.590452_at_attbi_s04...
|
| "David" <ning_1898_at_yahoo.com> wrote in message
| news:37032184.0312271915.d9d43ef_at_posting.google.com...
| > 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