oracle 如何在 SQLPlus 或 PL/SQL 中创建菜单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1850105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a menu in SQLPlus or PL/SQL
提问by Kyle Goddard
I have several scripts that I would like to start from a menu presented to the SQLPlus user. Something like:
Please make a selection:
1: Do script a
2: Do script b
3: Do script c
我有几个脚本，我想从提供给 SQLPlus 用户的菜单开始。类似于：
请进行选择：
1：执行脚本 a
2：执行脚本 b
3：执行脚本 c
I just need a point in the right direction, not a quick answer.
我只需要一个正确方向的观点，而不是一个快速的答案。
回答by Tony Andrews
Here is a SQL Plus script to do that:
这是一个 SQL Plus 脚本来做到这一点：
prompt Please make a selection:
prompt 1: Do script a
prompt 2: Do script b
prompt 3: Do script c
accept selection prompt "Enter option 1-3: "
set term off
column script new_value v_script
select case '&selection.'
when '1' then 'script_a'
when '2' then 'script_b'
when '3' then 'script_c'
else 'menu'
end as script
from dual;
set term on
@&v_script.
NB The 'menu' in the ELSE part of the case expression is the name of this script, so that it runs itself again when the user enters an invalid option.
注意 case 表达式的 ELSE 部分中的“菜单”是此脚本的名称，因此当用户输入无效选项时，它会再次自行运行。
回答by Pablo Santa Cruz
It's hard to accomplish what you are trying to do with SQLPlus and/or PL/SQL.
很难用 SQLPlus 和/或 PL/SQL 完成您想要做的事情。
SQLPlus is a frontend for Oracle databases. Its main purpose is to perform queries against an Oracle RDBMS.
SQLPlus 是 Oracle 数据库的前端。它的主要目的是对 Oracle RDBMS 执行查询。
PL/SQL is a language to manipulate data in Oracle.
PL/SQL 是一种在 Oracle 中操作数据的语言。
Instead, if you want something with user interaction, I would suggest you to write a little script/program in insert your favorite language here(could python, C/C++, C#, Java) with an Oracle connection to perform the SQL queries or PL/SQL programs you need.
相反，如果您想要与用户交互的内容，我建议您编写一个小脚本/程序，在此处插入您喜欢的语言（可以是 python、C/C++、C#、Java），并使用 Oracle 连接来执行 SQL 查询或 PL您需要的 /SQL 程序。
回答by OMG Ponies
You can execute scripts from a master script:
您可以从主脚本执行脚本：
CASE LOWER(&v_script_selection)
WHEN 'a' THEN
@script_a.sql
WHEN 'b' THEN
@script_b.sql
WHEN 'c' THEN
@script_c.sql
ELSE
DBMS_OUTPUT('No such option available')
END
&variablenameis used to refer to the variable variablenamein SQLPlus, much the same way $variablenameis used in shell scripts. If variablenameis undefined, then SQLPlus prompts the user for a value.
&variablename用于引用variablenameSQLPlus 中的变量，与$variablename在 shell 脚本中使用的方式大致相同。如果variablename未定义，则 SQLPlus 提示用户输入一个值。
You can provide a path relative to the master script - the example relies on the supporting scripts to be in the same directory.
您可以提供相对于主脚本的路径 - 该示例依赖于位于同一目录中的支持脚本。
回答by Thorsten
If the scripts are totally unrelated, I'd use a simple batch file or shell script.
如果脚本完全不相关，我会使用一个简单的批处理文件或 shell 脚本。
回答by Thorsten
DBMS_OUTPUTcan be used to print lines to the screen. It looks like it has has functions that do a GET_LINE as well, but I've never used it and don't know how well they work.
DBMS_OUTPUT可用于在屏幕上打印行。看起来它也有执行 GET_LINE 的函数，但我从未使用过它，也不知道它们的工作情况如何。
You just need to be careful about your SQLPlus settings. It can truncate lines if you don't set it up properly.
您只需要注意您的 SQLPlus 设置。如果您没有正确设置它，它可能会截断行。
回答by Gary Myers
I'd got with a real language for this (as per Pablo's comment).
我为此使用了一种真正的语言（根据 Pablo 的评论）。
You could have some stuff in a login.sql that displayed a list suggestions when you connect (PRINT or PROMPT SQL*Plus statements).
您可以在 login.sql 中添加一些内容，在您连接时显示列表建议（PRINT 或 PROMPT SQL*Plus 语句）。
And you could have a bunch of scripts called 1.sql, 2.sql etc which would get run if the user entered @1, @2 etc. (as long as they are in the correct directory).
并且您可以拥有一堆名为 1.sql、2.sql 等的脚本，如果用户输入 @1、@2 等（只要它们在正确的目录中），它们就会运行。
But really SQL*Plus isn't suited for this.
但实际上 SQL*Plus 并不适合于此。
