oracle中 怎么替换,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