SQL*PLUS中的替换变量(& &&)

最新推荐文章于 2022-06-23 18:27:36 发布

huangfanno1

最新推荐文章于 2022-06-23 18:27:36 发布

阅读量463

点赞数

文章标签： sql resources bt session oracle 脚本

今天oracle support提供一个脚本，大致如下：

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