在oracle 10g中，如何在循环中接受用户输入? [英] In oracle 10g, how do I accept user input in a loop?

问题描述

我有以下pl/sql块:

I have the following pl/sql block:

BEGIN
  FOR I IN 1 .. 5
  LOOP
    INSERT INTO TEST_TABLE VALUES('&slno',SYSDATE);
  END LOOP;
END;

当我执行上面的代码块时，它仅接受一个输入. 但这应该总共需要5个输入. 我的代码中缺少什么?有人可以帮我吗?

When I executed the above block it is taking only one input. But it is supposed to take 5 input in total. What is missing in my code? Can any one help me please?

推荐答案

它是一个替换变量-sql * plus功能-不能这样工作.您必须调用undefine &slno或accept slno ...使其接受其他输入，但是它们也是sqlplus，而不是pl/sql命令，因此您将无法在循环中调用它们，

It is a substitution variable - an sql*plus feature - and it doesn't work this way. You have to call undefine &slno or accept slno ... to make it take another input, but these are also sqlplus, not pl/sql commands, so you won't be able to invoke them in a loop,

在这里您唯一可以做的就是

Probably the only thing you can do here is

INSERT INTO TEST_TABLE VALUES('&slno1',SYSDATE);
INSERT INTO TEST_TABLE VALUES('&slno2',SYSDATE);
INSERT INTO TEST_TABLE VALUES('&slno3',SYSDATE);
INSERT INTO TEST_TABLE VALUES('&slno4',SYSDATE);
INSERT INTO TEST_TABLE VALUES('&slno5',SYSDATE);

更新:幸运的是，您可以通过生成接受独立输入的单独顺序语句列表来解决此问题:

update: Fortunately, you can work this around by generaing a list of separate sequential statements accepting independent inputs:

22:38:59 @> conn system/sys@oars_sandbox
Connected.
22:39:01 SYSTEM@oars_sandbox> @s:\test
Enter value for var1: a
Enter value for var2: b
Enter value for var3: c
22:39:06 SYSTEM@oars_sandbox> commit;
22:39:11 SYSTEM@oars_sandbox> select * from test_table;

COL1       COL2
---------- -------------------
a          07.12.2012 22:39:10
b          07.12.2012 22:39:11
c          07.12.2012 22:39:11
22:39:17 SYSTEM@oars_sandbox> get s:\test
  1  set echo off
  2  set define off
  3  set termout off
  4  set feedback off
  5  set timing off
  6  spool s:\123.sql
  7  begin
  8    for i in 1 .. 3 loop
  9      dbms_output.put_line('insert into test_table values(''&var'||i||''', sysdate);');
 10    end loop;
 11  end;
 12  /
 13  spool off
 14  set define "&"
 15  set termout on
 16* @s:\123.sql
22:39:24  17  .
22:39:58 SYSTEM@oars_sandbox> get s:\123.sql
  1  insert into test_table values('&var1', sysdate);
  2  insert into test_table values('&var2', sysdate);
  3* insert into test_table values('&var3', sysdate);
22:40:04 SYSTEM@oars_sandbox>

这篇关于在oracle 10g中，如何在循环中接受用户输入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！