Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2623071
  • 博文数量: 323
  • 博客积分: 10211
  • 博客等级: 上将
  • 技术积分: 4934
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-27 14:56
文章分类

全部博文(323)

文章存档

2012年(5)

2011年(3)

2010年(6)

2009年(140)

2008年(169)

分类: Oracle

2009-07-28 11:51:20

今天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

     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

阅读(2367) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~