Re: how to use plsql to calculate compond growth

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Mon, 29 Dec 2003 21:06:32 +0100
Message-ID: <bsq0um$msq$1_at_news4.tilbu1.nb.home.nl>

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_at_HOPE1> /
>>SP2-0552: Bind variable "MONEY" not declared.
>>
>>ning_1898_at_yahoo.com (David) 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

> 
> 
> 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

Received on Mon Dec 29 2003 - 21:06:32 CET