-
Hi everybody,
I want to create the Procedure menu that will read the parameter from the keybord interactively and perform some actions not leaving the menu.
Something like this:
CREATE OR REPLACE PROCEDURE menu AS
...
BEGIN
LOOP
=> reading the value from keybord
IF value = 'A' THEN
=> call some function
ELSEIF value = 'B' THEN
=> call another function
ELSEIF value = 'C' THEN
EXIT;
END IF;
END LOOP;
END;
In SQL*Plus we can use ACCEPT and then call the stored procedure passing the "value" as parameter, but when the procedure is done the script is finished. I can't find any way to create loop-like script that will repeat reading of the value.
It is possible to have the loop in PL/SQL but what about the interactive reading of the value?. I looked through several PL/SQL books and didn't find any example.
Any clue?
Kas
-
The problem is that SQL*plus has no loop function. You can rig up something using three files as follows:
------------------- Menu.sql --------------------------
prompt 1 Option 1
prompt 2 Option 2
prompt 3 Option 3
prompt 4 Exit
accept option prompt 'Choice: '
col result new_value result
select decode(&option, 4, 'file_exit', 'run_file '||&option) result
from dual
/
@&result
--------------------- run_file.sql --------------------------------
begin
myproc(&1);
end;
/
@menu
----------------------- file_exit.sql-------------------------------
exit
--------------------------------------------------------------------
A bit naff, but SQL*Plus isn't really designed for this sort of thing.
-
Thank you nealh!!
I tested yours proposal and it is working fine.
Generally it is what I need, so I will implement this approach.
But I am still curies, is any way to do this in the PL/SQL?
Kas
-
I don't believe so. Obviously, PL/SQL has a loop function but it doesn't have the ability to accept keyboard input. You can do something like:
begin
loop:
if a = '&xx' then
...
end loop;
end;
SQL*plus will prompt you for a variable subsitution but it then passes control over to PL/SQL. You can't get PL/SQL to come back to SQL*plus to ask for more input.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|