Oracle中的替换变量，&变量名，&&变量名说明

川长思鸟来 2024-04-18 00:14 62阅读 0赞

**&变量名**  
 每次引用，都需要声明一次  
  
 SQL> select \* from &test;  
 Enter value for test: dual  
 old   1: select \* from &test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X  
  
 SQL> select \* from &test;  
 Enter value for test: dual  
 old   1: select \* from &test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X  
  
 SQL> select \* from &test;  
 Enter value for test: dual  
 old   1: select \* from &test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X  
  
 SQL> select \* from &test;  
 Enter value for test: dual  
 old   1: select \* from &test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X  
  
 SQL> select \* from &test;  
 Enter value for test: dual  
 old   1: select \* from &test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X  
  
**&&变量名**  
 存在于整个会话中，只需声明一次  
  
 SQL> select \* from &&test;  
 Enter value for test: dual  
 old   1: select \* from &&test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X  
  
 SQL> select \* from &&test;  
 old   1: select \* from &&test  
 new   1: select \* from dual  
  
 DU  
 \--  
 X

#### 关于变量中点号"."的使用 ####

在变量和字符串在一起的时候，需要通过"."来区别声明变量和字符串，否则SQL Plus会将连在一起的变量和字符串当成另外一个变量

SQL> DEFINE POS = MANAGER

SQL> DEFINE DEPARTMENT\_ID = 20

SQL> select '&&POS' from dual;

old   1: select '&&POS' from dual

new   1: select 'MANAGER' from dual

'MANAGER'

\--------------

MANAGER

SQL> select '&&DEPARTMENT\_ID' from dual;

old   1: select '&&DEPARTMENT\_ID' from dual