Oracle中的替換變數，&變數名，&&變數名說明

&變數名
每次引用，都需要宣告一次

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

new   1: select '20' from dual

 

'20'

----

20

 

SQL> select '&&POS&&DEPARTMENT_ID' from dual;

old   1: select '&&POS&&DEPARTMENT_ID' from dual

new   1: select 'MANAGER20' from dual

 

'MANAGER20'

------------------

MANAGER20

 

SQL> select '&&POS.&&DEPARTMENT_ID' from dual;

old   1: select '&&POS.&&DEPARTMENT_ID' from dual

new   1: select 'MANAGER20' from dual

 

'MANAGER20'

------------------

MANAGER20

 

SQL> select '&&POS..&&DEPARTMENT_ID' from dual;

old   1: select '&&POS..&&DEPARTMENT_ID' from dual

new   1: select 'MANAGER.20' from dual

 

'MANAGER.20'

--------------------

MANAGER.20

SQL> define REQUEST_RRS_INDX_TBS="REQUEST_RRS_INDX"

 

SQL> select '&&REQUEST_RRS_INDX_TBS._00.dbf' from dual;

old   1: select '&&REQUEST_RRS_INDX_TBS._00.dbf' from dual

new   1: select 'REQUEST_RRS_INDX_00.dbf' from dual

 

'REQUEST_RRS_INDX_00.DBF'

----------------------------------------------

REQUEST_RRS_INDX_00.dbf

 

SQL> select '&&REQUEST_RRS_INDX_TBS_00.dbf' from dual;

Enter value for request_rrs_indx_tbs_00:

old   1: select '&&REQUEST_RRS_INDX_TBS_00.dbf' from dual

new   1: select 'dbf' from dual

 

'DBF'

------

dbf