Oracle - 如何使用&没有被问及价值？

Question

基本上，我不想被问到这样的值：：

SQL> select &test from dual;
Enter value for test:

我只想沿着脚本声明 &test ，如下所示：

&test varchar2(100):= 'some value'; --of course, this don't work.

Answer 1

执行

SQL> set define off

在运行代码之前

---

SQL> select '&test' from dual;

'&TES
-----
&test

SQL>

---

。如果您想“声明”它，请使用 var：

SQL> var test varchar2(200);
SQL> exec :test := 'some value';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
some value

SQL>

---

在动态 SQL 中：我不会锁定任何人，但是 - 我会更改我的密码。

SQL> connect scott/tiger
Connected.
SQL> var test varchar2(200);
SQL> exec :test := 'lion';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
lion

SQL> begin
  2    execute immediate 'alter user scott identified by ' || :test;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect scott/lion
Connected.
SQL>