oracle 如何使用pl/sql循环接受用户输入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1870670/
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 loop accepting user input with pl/sql?
提问by Kyle
I want to be able to insert a variable number of rows into a table based on user input? eg.
我希望能够根据用户输入在表中插入可变数量的行吗？例如。
Please enter value, enter "done" when no more values: value 1
Please enter value, enter "done" when no more values: value 2
Please enter value, enter "done" when no more values: done
2 Rows inserted successfully.
I'm not sure how to store the rows temporarily and I'm not sure how to ask the user multiple times to insert data. Does pl/sql have arrays?
我不确定如何临时存储行，也不确定如何多次要求用户插入数据。pl/sql 有数组吗？
Thanks
谢谢
回答by Tony Andrews
As others have said, PL/SQL alone is not suitable for this task, you need a UI on top to interact with the end user. However, if you have a real need to do this in SQL Plus, it is possible using the technique I described in this SO question.
正如其他人所说，单独的 PL/SQL 不适合此任务，您需要在顶部有一个 UI 来与最终用户进行交互。但是，如果您确实需要在 SQL Plus 中执行此操作，则可以使用我在此 SO 问题中描述的技术。
You need to create 2 SQL Plus scripts:
您需要创建 2 个 SQL Plus 脚本：
1) A script to perform a single insert, here called script_insert.sql:
1) 执行单个插入的脚本，这里称为 script_insert.sql：
insert into t1 values ('&1.');
@main
2) A script to control the process, here called main.sql:
2）一个控制进程的脚本，这里称为main.sql：
accept selection prompt "Please enter value, enter 'done' when no more values: "
set term off verify off
column script new_value v_script
select case '&selection.'
when 'done' then ''
else '@script_insert &selection.'
end as script
from dual;
set term on
@&v_script.
Now in SQL Plus you can run it like this:
现在在 SQL Plus 中你可以像这样运行它：
SQL> select * from t1;
no rows selected
SQL> @main
Please enter value, enter 'done' when no more values: 1
Please enter value, enter 'done' when no more values: 2
Please enter value, enter 'done' when no more values: 3
Please enter value, enter 'done' when no more values: done
SQL> select * from t1;
N1
----------
1
2
3
Let me reiterate that this demonstrates it can be done, I would not claim it to be a good way to implement the requirement - unless it is just an ad hoc tool to be used by a DBA or developer. I would never give an end userSQL Plus as a UI!
让我重申，这表明它可以做到，我不会声称它是实现需求的好方法——除非它只是一个供 DBA 或开发人员使用的临时工具。我永远不会给最终用户SQL Plus 作为 UI！
回答by wadesworld
I think you're pounding a nail with a screwdriver.
我想你是在用螺丝刀敲钉子。
You'd get far more flexibility using a Python script, a PHP page, a Perl script, a Java program, or any other environment that can access Oracle.
使用 Python 脚本、PHP 页面、Perl 脚本、Java 程序或任何其他可以访问 Oracle 的环境，您将获得更大的灵活性。
回答by Justin Cave
Most likely, you can't, at least not without writing some sort of client application (not a SQL*Plusscript). PL/SQLruns on the server and has nothing to accept user input. SQL*Plusruns on the client but it has no looping constructs. You can't mix the two, so you can't have a PL/SQL loop that happens repeatedly prompts the user for input and does an INSERT based on the values passed in.
很可能，您不能，至少不能不编写某种客户端应用程序（不是SQL*Plus脚本）。 PL/SQL在服务器上运行，没有什么可以接受用户输入。 SQL*Plus在客户端上运行，但它没有循环结构。您不能将两者混合使用，因此您不能有一个 PL/SQL 循环反复提示用户输入并根据传入的值执行 INSERT。
Most commonly, people get around this by writing a small front-end script in your favorite scripting language that gathers the input and then issues appropriate INSERT statements.
最常见的是，人们通过用您最喜欢的脚本语言编写一个小的前端脚本来解决这个问题，该脚本收集输入然后发出适当的 INSERT 语句。
If you really, really want to accomplish the task in SQL*Plus, it is probably possible, but quite painful. You would basically have to construct an infinite loop that you threw an error to escape from. For example
如果你真的，真的想完成 中的任务SQL*Plus，那可能是可能的，但很痛苦。您基本上必须构建一个无限循环，您可以抛出一个错误来逃避。例如
Define a script a.sql (I happen to store mine in c:\temp)
定义一个脚本 a.sql （我碰巧将我的存储在 c:\temp 中）
whenever sqlerror exit;
accept x_val number prompt "Enter a value for x or -1 to stop ";
INSERT INTO x( col1 ) values( :x_val );
BEGIN
IF( &x_val = -1 )
THEN
commit;
RAISE_APPLICATION_ERROR( -20001, 'Done' );
END IF;
END;
/
@c:\temp\a.sql
And then in SQL*Plus
然后在 SQL*Plus
SQL> variable x_val number;
SQL> crate table x( col1 number );
SQL> truncate table x;
Table truncated.
SQL> @c:\temp\a.sql
Enter a value for x or -1 to stop 3
1 row created.
old 2: IF( &x_val = -1 )
new 2: IF( 3 = -1 )
PL/SQL procedure successfully completed.
Enter a value for x or -1 to stop 4
1 row created.
old 2: IF( &x_val = -1 )
new 2: IF( 4 = -1 )
PL/SQL procedure successfully completed.
Enter a value for x or -1 to stop 5
1 row created.
old 2: IF( &x_val = -1 )
new 2: IF( 5 = -1 )
PL/SQL procedure successfully completed.
Enter a value for x or -1 to stop -1
1 row created.
old 2: IF( &x_val = -1 )
new 2: IF( -1 = -1 )
BEGIN
*
ERROR at line 1:
ORA-20001: Done
ORA-06512: at line 4
Horribly ugly, of course, but it "works" for some definition of "works"
当然，非常丑陋，但它对“作品”的某些定义“有效”
回答by Rene
Maybe you can have your user enter a comma seperated list of values?
也许您可以让您的用户输入逗号分隔的值列表？
Please enter values: 1,2,3,4,5
请输入值：1、2、3、4、5
Then process the string in your plsql block.
然后处理 plsql 块中的字符串。
回答by Jeffrey Kemp
I'd recommend Oracle Application Express. It's free, and dead easy to build simple apps very quickly.
我推荐 Oracle Application Express。它是免费的，而且非常容易快速构建简单的应用程序。
Sure, it may be overkill for your extremely simple requirement here, but chances are someone will come back and say "I loved your little SQL*Plus script - could you just add a few more features to it" and before you know it you're building a skyscraper with a shack foundation.
当然，对于您这里极其简单的要求来说，这可能有点矫枉过正，但很有可能有人会回来说“我喜欢您的小 SQL*Plus 脚本——您能不能再给它添加一些功能”，然后在您意识到之前用棚屋地基重建摩天大楼。
