如何在 PL/SQL 中使用循环多次运行相同的查询?
使用此代码我无法多次运行插入查询；它只要求 ID 和名称一次(如果计数器中的值 > 1 ).
Using this code I'm not able to run an insert query multiple times; it asks for ID and name only one time ( if value in counter is > than 1 ).
declare
counter number := 0 ;
begin
counter := &counter ;
while counter > 0 loop
insert into customer values ( &id, '&name' ) ;
counter := counter - 1 ;
end loop ;
end ;
让我用一个例子来解释:-
Let me explain with the help of an example :-
假设我将值 2 放入 counter.有了这个，它应该两次询问我的 ID 和名称，但它只询问一次，并将我为 ID 和名称输入的值复制到表 test 中两次.
Say I put value 2 in counter. With this it should ask me for ID and name twice but it only asks once, and it copies the values I entered for the ID and name into table test twice.
我该如何解决这个问题?如果我不能，那么请建议可以解决我的问题的替代代码.
How can I fix this? If I can't then please suggest alternative code which will fix my problem.
推荐答案
替换变量 &counter、&id 和 &name 每个都被评估一次，当 PL/SQL 块被编译时 - 不是，因为它正在执行.
The substitution variables &counter, &id and &name are each evaluated once, when the PL/SQL block is compiled - not as it is being executed.
变量不会也不能在 PL/SQL 块内重新评估或重新提升.该块作为数据库中的单个单元执行 - 一旦它被提交执行，它就独立于客户端，客户端只等待它完成(除非你中断它，客户端也会处理).PL/SQL 不是交互式语言，您不应将客户端功能(例如替换变量)与 SQL 或 PL/SQL 功能混淆.
The variables are not, and cannot be, re-evaluated or re-promoted within the PL/SQL block. The block is executed as a single unit within the database - once it has been submitted for execution it is independent of the client, which just waits for it to complete (unless you interrupt it, which the client also handles). PL/SQL is not an interactive language, and you shouldn't confuse client functionality (e.g. substitution variables) with SQL or PL/SQL functionality.
只是为了好玩，您可以基于 counter 生成一个脚本，该脚本对 ID 和名称进行适当数量的提示，并将它们转换为可以通过简单插入使用的格式:
Just for fun, you could generate a script based on counter which does the appropriate number of prompts for IDs and names, and gets them into a format that could be used by a simple insert:
set serveroutput on
set feedback off
set echo off
set verify off
set termout off
accept counter "How many value pairs do you want to insert?"
var ids varchar2(4000);
var names varchar2(4000);
spool /tmp/prompter.sql
begin
-- prompt for all the value pairs
for i in 1..&counter loop
dbms_output.put_line('accept id' ||i|| ' number "Enter ID ' ||i|| '"');
dbms_output.put_line('accept name' ||i|| ' char "Enter name ' ||i|| '"');
end loop;
-- concatenate the IDs into one variable
dbms_output.put('define ids="');
for i in 1..&counter loop
if i > 1 then
dbms_output.put(',');
end if;
dbms_output.put('&'||'id'||i);
end loop;
dbms_output.put_line('"');
-- concatenate the names into one variable
dbms_output.put('define names="');
for i in 1..&counter loop
if i > 1 then
dbms_output.put(',');
end if;
-- each name wrapped in single quotes
dbms_output.put(q'['&]'||'name'||i||q'[']');
end loop;
dbms_output.put_line('"');
end;
/
spool off
@/tmp/prompter
insert into customer (id, name)
select i.id, n.name
from (
select rownum as rid, column_value as id
from table(sys.odcinumberlist(&ids))
) i
join (
select rownum as rid, column_value as name
from table(sys.odcivarchar2list(&names))
) n
on n.rid = i.rid;
select * from customer;
这会创建一个名为 prompter.sql 的文件(我已经把它放在/tmp 中；把它放在适合你环境的地方！)；'值对的数量'提示回答为 2，临时脚本看起来包含:
That creates a file called prompter.sql (I've put it in /tmp; put it somewhere suitable for your environment!); with the 'number of value pairs' prompt answered as 2 that temporary script would look contain:
accept id1 number "Enter ID 1"
accept name1 char "Enter name 1"
accept id2 number "Enter ID 2"
accept name2 char "Enter name 2"
define ids="&id1,&id2"
define names="'&name1','&name2'"
然后使用 @ 运行该临时脚本，提示用户输入所有这些单独的值.然后在插入使用的选择中使用由组合替换变量构建的表集合.
That temporary script is then run with @, prompting the user for all those individual values. And then table collections built from the combined substitution variables are used in a select, which is used by the insert.
相关文章