create or replace procedure buyunits is mnav number; mpno varchar2(100); mtransactioncharge number; mservicecharge number; maccno number; cursor ecursor is select accno from sipholder where EXTRACT(day from TO_DATE(ECS,'DD-MON-RR'))=5 order by ECS; begin OPEN ecursor; LOOP FETCH ecursor into maccno; EXIT WHEN ecursor%NOTFOUND; select portfoliono into mpno from sipholder where accno=rec.accno; select nav into mnav from sip_price where portfoliono=mpno; select servicecharge into mservicecharge from sipparam; select transactioncharge into mtransactioncharge from sipparam; update savingaccperm set balance=balance- balance-permonthinvestment; mtotalamountleft=permonthinvestment- (permonthinvestment*mtransactioncharge)/100 - (permonthinvestment*mservicecharge)/100 update sipholder set totalunitsbought=totalunitsbought +(mtotalamountleft)/mnav; end loop; close ecursor; end;
|show 3 more comments|
closed as too localized by a_horse_with_no_name, Mat, Jon Seigel, dezso, StanleyJohns Apr 23 '13 at 13:52
This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.
This will work, but you have all sorts of problems in your db application. Mainly it seems to me that you haven't structured the tables in a good manner, nor have you normalized the design. You're also quoting column names incorrectly, improperly using selects into variables, etc. If you can, please ask a senior DBA to review your code and db design.
This is according to the following table structure (which you posted in the comments):