oracle变量替换出现标点符号,SQL*PLUS中的替换变量(& &&)

PROMPT ROUTING_SEQUENCE_ID = &ROUT_SEQ_ID

PROMPT OPERATION_SEQUENCE_ID = &OP_SEQ_ID

PROMPT RESOURCE_SEQ_NUM = &RES_SEQ_NUM

PROMPT RESOURCE_ID = &RES_ID

SELECT

SCHEDULE_SEQ_NUM SSN,

BASIS_TYPE BT,

UOM_CODE UC,

PRINCIPAL_FLAG PF

FROM

MRP_AP_OPERATION_RESOURCES_V

WHERE

ROUTING_SEQUENCE_ID = &ROUT_SEQ_ID  AND

OPERATION_SEQUENCE_ID = &OP_SEQ_ID  AND

--RESOURCE_ID = &RES_ID  --AND

RESOURCE_SEQ_NUM = &RES_SEQ_NUM;

SELECT

SCHEDULE_SEQ_NUM SSN,

BASIS_TYPE BT,

UOM_CODE UC,

PRINCIPAL_FLAG PF

FROM

MRP_AP_OPERATION_RESOURCES_V

WHERE

--ROUTING_SEQUENCE_ID = &ROUT_SEQ_ID  AND

--OPERATION_SEQUENCE_ID = &OP_SEQ_ID  AND

RESOURCE_ID = &RES_ID  AND

RESOURCE_SEQ_NUM = &RES_SEQ_NUM;

SELECT

SCHEDULE_SEQ_NUM SSN,

BASIS_TYPE BT,

UOM_CODE UC,

PRINCIPAL_FLAG PF

FROM

MRP_AP_OPERATION_RESOURCES_V

WHERE

ROUTING_SEQUENCE_ID = &ROUT_SEQ_ID  AND

OPERATION_SEQUENCE_ID = &OP_SEQ_ID  AND

RESOURCE_ID = &RES_ID;

--AND  RESOURCE_SEQ_NUM = &RES_SEQ_NUM

SELECT

SCHEDULE_SEQ_NUM,

NVL(ALTERNATE_NUMBER  ,

0) ALT_NUM ,

RESOURCE_SEQ_NUM

FROM

MRP_AP_OPERATION_RESOURCES_V

WHERE

ROUTING_SEQUENCE_ID = &ROUT_SEQ_ID  AND

OPERATION_SEQUENCE_ID = &OP_SEQ_ID  AND

RESOURCE_ID = &RES_ID;

SELECT

SCHEDULE_SEQ_NUM,

RESOURCE_SEQ_NUM,

SCHEDULE_FLAG

FROM

BOM_OPERATION_RESOURCES

WHERE

RESOURCE_ID = (&RES_ID/2)  AND

OPERATION_SEQUENCE_ID = (&OP_SEQ_ID/2);

SELECT

REPLACEMENT_GROUP_NUM,

SCHEDULE_SEQ_NUM,

SCHEDULE_FLAG,

SUBSTITUTE_GROUP_NUM

FROM

BOM_SUB_OPERATION_RESOURCES

WHERE

RESOURCE_ID = (&RES_ID/2)  AND

OPERATION_SEQUENCE_ID = (&OP_SEQ_ID/2);

我运行的时候发现输入四个变量后，在紧跟着的每个select中还要再次输入，很麻烦。后来查了一下资料知道了&与&&的区别。在这里做个小记录：

&：引用的变量只存在当前的语句中。举例如下：

SQL> select 1+&var from dual;

Enter value for var: 1

old   1: select 1+&var from dual

new   1: select 1+1 from dual

1+1

----------

2

SQL> /                   --重复执行需要重新输入变量var的值。

Enter value for var: 2

old   1: select 1+&var from dual

new   1: select 1+2 from dual

1+2

----------

3

&&：引用的变量存在于当前的sesion。举例如下：

SQL> select 1+&&var from dual;

Enter value for var: 1

old   1: select 1+&&var from dual

new   1: select 1+1 from dual

1+1

----------

2

SQL> /

old   1: select 1+&&var from dual

new   1: select 1+1 from dual

1+1

----------

2

SQL> select 100+&var from dual;

old   1: select 100+&var from dual

new   1: select 100+1 from dual

100+1

----------

101

所以我只要在前面的变量前加一个&就使它的作用范围变成session级的了。如下：

PROMPT ROUTING_SEQUENCE_ID = &&ROUT_SEQ_ID

PROMPT OPERATION_SEQUENCE_ID = &&OP_SEQ_ID

PROMPT RESOURCE_SEQ_NUM = &&RES_SEQ_NUM

PROMPT RESOURCE_ID = &&RES_ID