Not able to control flow of program using if..end if. Please help

[gautamzone]

I want to develop a student record manipulation menu
containing options for:
1. Addition
2. Modification
3. Deletion

For each of the above operations, I have a set of input to
ask from the user.

The problem I am facing is as follows:

When the user chooses option 1 viz Addition, the required
result is achieved. However, when the user chooses option 2
viz Modification or option 3 viz Deletion, the code for Addition also gets executed.



My code is :

define menuline='-------------------------'
ttitle center STUDENT MANIPULATION SCREEN
PROMPT STUDENT MANIPULATION MENU
PROMPT &menuline
PROMPT
PROMPT 1. Add Records
PROMPT
PROMPT 2. Modify Records
PROMPT
PROMPT 3. Delete Records
PROMPT
PROMPT 4. Quit
PROMPT
ACCEPT choice PROMPT 'Enter your choice (1 - 4) :'

declare
rollno integer;
dob date;
marks_science number(5,2);
marks_maths number(5,2);
ch integer;
begin
ch:=&choice;
if ch=1 then
rollno:=&rollno;
dob:='&dob';
marks_science:=&marks_science;
marks_maths:=&marks_maths;
insert_proc(rollno,dob,marks_science,marks_maths);
elsif ch=2 then --modification
/* modification code
--------
-------- */
elsif ch=3 then --deletion
/* deletion code
--------
-------- */

end if;
end;


Is it because I have put user input statements in the if control
structure that the same code gets executed for all options.

How do I control proper flow of my program?

Please Help

Thanks a lot in advance

Gautam Bhagwandas

[ales]

You are mixing SQL*Plus Substitution Variables with PL/SQL code which probably won't work properly.
Good approach could be to split your script into five scripts.
The first would just ask the choice and then call another script according the user's choice:

define menuline='-------------------------' 
ttitle center STUDENT MANIPULATION SCREEN 
PROMPT STUDENT MANIPULATION MENU 
PROMPT &menuline 
PROMPT 
PROMPT 1. Add Records 
PROMPT 
PROMPT 2. Modify Records 
PROMPT 
PROMPT 3. Delete Records 
PROMPT 
PROMPT 4. Quit 
PROMPT 
ACCEPT choice PROMPT 'Enter your choice (1 - 4) :' 
@script&choice

Then script1.sql would be:

begin
insert_proc(&rollno,to_date('&dob'),&marks_science,&marks_maths);
end;

...
and finally script4.sql would be simply :

--quit

HTH,
Ales