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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2122839/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2122839/