如何在SQLPlus或PL/SQL中制作菜单? [英] How to make a menu in SQLPlus or PL/SQL?

问题描述

我正在制作此程序，该程序将具有一个菜单，该菜单可获取用户的输入并根据他/她的选择执行特定的脚本.类似于以下内容:

I am making this program that will have a menu that gets the user's input and performs a certain script based on his/her choice. Something along the lines of:

Please make a selection:
1: Do script a
2: Do script b
3: Do script c

我查看了以下链接:如何制作SQLPlus或PL/SQL菜单

我能够从中得到一些帮助，但是它不能完全正常工作.因为有些行我听不懂.见下文.

And I was able to get something out of it, BUT it's not fully functional. Because there are certain lines I don't understand. See below.

PROMPT  1: Make a sales invoice 
PROMPT  2: Inquire a sales invoice

accept selection PROMPT "Enter option 1-2: "

set term off
column script new_value v_script  -- What's column script?
select case '&selection.'
       when '1' then '@test.sql'
       when '2' then '@test.sql'
       else '@FinalAssignment.sql'
       end as script  -- What script is this referring to? 
from dual; -- Don't know this

set term on

@&v_script. -- What script is being ran here?

我可以让该程序运行脚本，但是其他方法实际上不起作用.如果我选择了超出该范围的数字，脚本将关闭，而不是重新加载自身.同样，当脚本运行时，它会自行关闭，而不是返回主菜单.我知道我必须运行一个循环来解决此问题，但是我不知道如何用这种语言来实现它.

I can get the program to run the scripts, BUT the else doesn't really work. If I pick a number outside that range the script will close, instead of reloading itself. Also when a script is ran it closes itself, instead of going back to the main menu. I know I have to run a loop to fix this problem, BUT I don't know how to implement it in this language it.

任何帮助，我们感激不尽. 谢谢！

Any help is appreciated it. Thanks!

推荐答案

答案1.列script是as script定义的select查询中的列，表示是列别名.

Ans 1. Column script is the column in the select query defined by as script, which means script is a column alias.

答案2. as script被称为select stmt的列别名.此select语句中只有一列.示例SELECT column AS col1 FROM table;.列 NEW_VALUE 获取选定的值(从case stmt，在这种情况下为脚本名称test1.sql，test2.sql或FinalAssignment.sql)，并将其存储在v_script中.

Ans 2. as script is referring to as a column alias of the select stmt. There is only one column in this select statement. Example SELECT column AS col1 FROM table;. The column NEW_VALUE gets the selected value (which is driven from the case stmt, which in this case is the script name test1.sql, test2.sql, or FinalAssignment.sql) and stores it into v_script.

Ans 3. dual表是一种特殊的单行表，默认情况下出现在所有Oracle数据库安装中. dual 此处的更多信息.您可以select来自dual的任何内容，例如select sysdate from dual;或select 'ABCDEF' AS col1 from dual;.

Ans 3. dual table is a special one-row table present by default in all Oracle database installations. More on dual here. You can select anything from dual, like select sysdate from dual; or select 'ABCDEF' AS col1 from dual;.

Ans 4. v_script列将包含您在select查询中的case语句(即菜单选择)中选择的脚本名称(如Ans中所述) 2.).选择该脚本后，您可能要运行该选定的脚本(不是吗?).那就是@&v_script的作用.您使用@script_name

Ans 4. The v_script column will contain the script name as per your selection from the case statement in the select query, i.e. from the menu selection (as discussed in Ans 2.). Once that is selected, you may want to run that selected script (Wouldn't you?). Thats what @&v_script does. You run a script in SQLPlus using @script_name

PROMPT  1: Make a sales invoice 
PROMPT  2: Inquire a sales invoice    
accept selection PROMPT "Enter option 1-2: "

set term off
column script new_value v_script  --Q1. What's column script?
select case '&selection.'          --from accept above
       when '1' then '@test1.sql'  --script to run when chosen option 1.
       when '2' then '@test2.sql'  --script to run when chosen option 2.
       else '@FinalAssignment.sql' --this script
       end as script  --Q2. What script is this referring to? 
from dual; --Q3. Don't know this

set term on

@&v_script. --Q4. What script is being ran here?

注意事项-

1. FinalAssignment.sql应该是脚本本身的名称，即上面代码所在的脚本.

1. FinalAssignment.sql should be the name of the script itself i.e. the script where the above code is.

第1,2和3行是脚本的一部分. PROMPT 是有效的SQL * Plus命令，并且 ACCEPT .

Line 1,2, and 3 are part of the script. PROMPT is a valid SQL*Plus command and so is ACCEPT.

这篇关于如何在SQLPlus或PL/SQL中制作菜单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！