如何使用pl/sql循环接受用户输入?

2021-12-24 00:00:00 oracle oracle10g plsql sqlplus

我希望能够根据用户输入向表中插入可变数量的行吗?例如.

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 作为 UI！

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!

相关文章