如何循环使用pl/sql接受用户输入? [英] how to loop accepting user input with pl/sql?
问题描述
我希望能够根据用户输入将可变数量的行插入表中?例如.
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.
我不确定如何临时存储行,也不确定如何多次询问用户插入数据. pl/sql是否具有数组?
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并不适合此任务,您需要在顶部有一个UI与最终用户进行交互.但是,如果您确实需要在SQL Plus中执行此操作,则可以使用我在
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.
您需要创建2个SQL Plus脚本:
You need to create 2 SQL Plus scripts:
1)执行单个插入的脚本,这里称为script_insert.sql:
1) A script to perform a single insert, here called script_insert.sql:
insert into t1 values ('&1.');
@main
2)一个控制过程的脚本,这里称为main.sql:
2) A script to control the process, here called 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.
现在在SQL Plus中,您可以像这样运行它:
Now in SQL Plus you can run it like this:
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
让我重申,这表明它可以完成,但我不会声称它是实现该要求的好方法-除非它只是DBA或开发人员要使用的临时工具.我绝对不会提供最终用户 SQL Plus作为用户界面!
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 user SQL Plus as a UI!
这篇关于如何循环使用pl/sql接受用户输入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!