&变量名
每次引用，都需要声明一次

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博客

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

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

最新文章