存储过程学习四

isiqi

浏览: 16467833 次
性别:
来自: 济南

最近访客更多访客>>

nison

hellohank

wangyy

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-07 ( 335)
2012-06 ( 252)
2012-05 ( 362)
更多存档...

1.java代码

Map<String, Object> map = (Map<String, Object>) vm;
// 取数据
order_item_id = (BigDecimal) map.get("order_item_id");
cust_order_id = (BigDecimal) map.get("cust_order_id");
//10新装订购 15拆机退订
service_offer_id = (BigDecimal) map.get("service_offer_id");
//业务主键
order_item_obj_id = (BigDecimal) map.get("order_item_obj_id");
//T20增值产品 T03主产品
order_item_cd = (String) map.get("order_item_cd");

Map<String, Object> paraMap = new HashMap<String, Object>();
paraMap.put("cust_order_id", cust_order_id);
paraMap.put("order_item_id", order_item_id);
paraMap.put("service_offer_id", service_offer_id);
paraMap.put("order_item_obj_id", order_item_obj_id);
paraMap.put("order_item_cd", order_item_cd);
ibatisTemplate.queryForObject("OrderAndActive.completionProcedure", paraMap);

2.xml配置文件

<parameterMap class="java.util.Map" id="completionMap">
<parameter property="cust_order_id" mode="IN"/>
<parameter property="order_item_id" mode="IN"/>
<parameter property="service_offer_id" mode="IN"/>
<parameter property="order_item_obj_id" mode="IN"/>
<parameter property="order_item_cd" mode="IN"/>
</parameterMap>
<procedure id="completionProcedure" parameterMap="completionMap">
{ call PK_VSOP_COMLETION.P_VSOP_COMLETION(?,?,?,?,?) }
</procedure>

3.存储过程代码

CREATE OR REPLACE PACKAGE BODY PK_VSOP_COMLETION IS
/**
* 模块名称: VSOP增值业务交付平台 - 订单项竣工
* 功能: 订单项竣工
* 设计人: 赵兴国(VSOP增值业务交付平台V1.0)
* 时间: 2010-08-26
**/
PROCEDURE P_VSOP_COMLETION(
I_CUST_ORDER_ID IN NUMBER, --订单ID
I_ORDER_ITEM_ID IN NUMBER, --订单项ID
I_SERVICE_OFFER_ID IN NUMBER, --10新装订购 15拆机退订
I_ORDER_ITEM_OBJ_ID IN NUMBER, --业务主键
I_ORDER_ITEM_CD IN VARCHAR2 --T20增值产品 T03主产品 T05附属产品
) IS
V_CUST_ORDER_ID NUMBER(12); --订单ID
V_ORDER_ITEM_ID NUMBER(12); --订单项ID
V_SERVICE_OFFER_ID NUMBER(12); --10新装订购 15拆机退订
V_ORDER_ITEM_OBJ_ID NUMBER(12); --业务主键
V_ORDER_ITEM_CD VARCHAR2(10); --T20增值产品 T03主产品 T05附属产品

return_sub_cur COMMCUR;
returnSubSyn RETURN_SUB_SYN%ROWTYPE;
V_COUNT NUMBER;
V_PROD_INST_ID VARCHAR2(30);

BEGIN
V_CUST_ORDER_ID := I_CUST_ORDER_ID;
V_ORDER_ITEM_ID := I_ORDER_ITEM_ID;
V_SERVICE_OFFER_ID := I_SERVICE_OFFER_ID;
V_ORDER_ITEM_OBJ_ID := I_ORDER_ITEM_OBJ_ID;
V_ORDER_ITEM_CD := I_ORDER_ITEM_CD;

--首先竣工订单项
EXECUTE IMMEDIATE 'update order_item t set t.status = ''2002'' , t.status_date = sysdate where t.order_item_id = :1'
USING V_ORDER_ITEM_ID;

--条件符合，就进行拆机操作
IF V_ORDER_ITEM_CD = 'T03' AND V_SERVICE_OFFER_ID = '15' THEN
--拆机操作
EXECUTE IMMEDIATE 'SELECT B.PROD_INST_ID
FROM ORDER_ITEM A, ORDER_RELATION B
WHERE A.ORDER_ITEM_OBJ_ID = B.ORDER_RELATION_ID
AND A.ORDER_ITEM_ID = :V_ORDER_ITEM_ID'
INTO V_PROD_INST_ID
USING V_ORDER_ITEM_ID;
--拆主产品
EXECUTE IMMEDIATE 'UPDATE PROD_INST T SET T.STATE_CD = ''1101'' AND T.PROD_INST_ID = :1'
USING V_PROD_INST_ID;
--拆附属产品
EXECUTE IMMEDIATE 'UPDATE TB_PRD_VSOP_SUBPRD_INST T SET T.STATE_CD = ''1101'' WHERE T.PROD_INST_ID = :1'
USING V_PROD_INST_ID;
--拆订购关系
EXECUTE IMMEDIATE 'UPDATE ORDER_RELATION O
SET O.STATE = ''1101'',O.MODIFY_TIME = SYSDATE
WHERE O.PROD_INST_ID = (SELECT B.PROD_INST_ID
FROM ORDER_ITEM A, ORDER_RELATION B
WHERE A.ORDER_ITEM_OBJ_ID = B.ORDER_RELATION_ID
AND A.ORDER_ITEM_ID = :1)'
USING V_ORDER_ITEM_ID;
RETURN;
END IF;

--判断是否要竣工订单(查询此订单下是否存在没有竣工且不是套餐的订单项)
EXECUTE IMMEDIATE 'SELECT COUNT(1)
FROM CUSTOMER_ORDER A
WHERE EXISTS (SELECT B.CUST_ORDER_ID
FROM ORDER_ITEM B
WHERE B.STATUS != ''2002''
AND B.ORDER_ITEM_CD != ''T06''
AND B.CUST_ORDER_ID = A.CUST_ORDER_ID)
AND A.CUST_ORDER_ID = :1'
INTO V_COUNT
USING V_CUST_ORDER_ID;

--如果V_COUNT等于0，则竣工订单，当前订单项，以及套餐并为同步准备数据
IF V_COUNT=0 THEN
--竣工订单时同时竣工套餐(T06的订单项)
EXECUTE IMMEDIATE 'update ORDER_ITEM a set a.status = ''2002'', a.status_date = sysdate where a.order_item_cd = ''T06'' and a.cust_order_id = :1'
USING V_CUST_ORDER_ID;
--竣工订单
EXECUTE IMMEDIATE 'update customer_order t set t.status = ''900'' , t.status_date = sysdate where t.cust_order_id = :1'
USING V_CUST_ORDER_ID;

--为订购结果通知准备数据
OPEN return_sub_cur FOR 'SELECT DISTINCT (A.OTHER_SYS_ORDER_ID) AS CUST_ORDER_ID,
E.PRODUCT_NBR,
D.ACC_NBR,
A.ORDER_CHANNEL
FROM CUSTOMER_ORDER A,
ORDER_ITEM B,
ORDER_RELATION C,
PROD_INST D,
PRODUCT E
WHERE A.CUST_ORDER_ID = B.CUST_ORDER_ID
AND B.ORDER_ITEM_OBJ_ID = C.ORDER_RELATION_ID
AND C.PROD_INST_ID = D.PROD_INST_ID
AND D.PRODUCT_ID = E.PRODUCT_ID
AND B.ORDER_ITEM_CD = ''T20''
AND A.OTHER_SYS_ORDER_ID IS NOT NULL
AND A.ORDER_CHANNEL IN
(SELECT T.SYSTEM_CODE
FROM SYSTEM_INFO_ATTR T
WHERE T.ATTR_NAME = ''returnsub_syn'')
AND A.STATUS = ''900''
AND A.CUST_ORDER_ID = :1'
USING V_CUST_ORDER_ID;
LOOP
FETCH return_sub_cur INTO returnSubSyn.Cust_Order_Id,returnSubSyn.Product_Nbr,returnSubSyn.Acc_Nbr,returnSubSyn.Order_Channel;
EXIT WHEN return_sub_cur%NOTFOUND;
END LOOP;

IF returnSubSyn.Cust_Order_Id IS NOT NULL AND length(returnSubSyn.Cust_Order_Id)>0 THEN
EXECUTE IMMEDIATE 'INSERT INTO RETURN_SUB_SYN (RETURN_SUB_SYN_ID,CUST_ORDER_ID,PRODUCT_NBR,ACC_NBR,ORDER_CHANNEL,ADD_TIME)
VALUES(SEQ_VSOP_RETURN_SUB_SYN_ID.nextval,:2,:3,:4,:5,SYSDATE)'
USING returnSubSyn.Cust_Order_Id,returnSubSyn.Product_Nbr,returnSubSyn.Acc_Nbr,returnSubSyn.Order_Channel;
END IF;

--为订购关系向集团同步准备数据
EXECUTE IMMEDIATE 'INSERT INTO ORDER_ITEM_ACTIVE
(ORDER_ITEM_ID,
CUST_ORDER_ID,
SERVICE_OFFER_ID,
ORDER_ITEM_CD,
ORDER_ITEM_OBJ_ID,
CUST_WORKSHEET_ID,
STATUS,
STATUS_DATE,
STATE_CHANGE_REASON,
PRIORITY,
PRE_HANDLE_FLAG,
HANDLE_TIME,
ARCHIVE_DATE,
FINISH_TIME,
RECODE_ID)
SELECT T1.ORDER_ITEM_ID,
T1.CUST_ORDER_ID,
T1.SERVICE_OFFER_ID,
T1.ORDER_ITEM_CD,
T1.ORDER_ITEM_OBJ_ID,
T1.CUST_WORKSHEET_ID,
''2001'',
T1.STATUS_DATE,
T1.STATE_CHANGE_REASON,
T1.PRIORITY,
T1.PRE_HANDLE_FLAG,
T1.HANDLE_TIME,
T1.ARCHIVE_DATE,
T1.FINISH_TIME,
SEQ_VSOP_ORDITEMACT_RECODEID.NEXTVAL
FROM ORDER_ITEM T1,
PROD_INST T2,
ORDER_RELATION T3,
PRODUCT T4,
PRODUCT_SYSTEM_INFO T5
WHERE T1.ORDER_ITEM_CD = ''T20''
AND T1.STATUS = ''2002''
AND T2.PROD_INST_ID = T3.PROD_INST_ID
AND T2.STATE_CD != ''1101''
AND T1.ORDER_ITEM_OBJ_ID = T3.ORDER_RELATION_ID
AND T3.PRODUCT_ID = T4.PRODUCT_ID
AND T4.PRODUCT_ID = T5.PRODUCT_ID
AND T4.MANAGE_GRADE = ''03''
AND T5.SYSTEM_CODE IN (''103'', ''104'', ''105'')
AND T1.CUST_ORDER_ID = :1'
USING V_CUST_ORDER_ID;
END IF;

--处理订购关系
IF V_ORDER_ITEM_CD = 'T20' AND V_SERVICE_OFFER_ID = '15' THEN
--处理订购关系失效
EXECUTE IMMEDIATE 'update order_relation r set r.state = ''1101'' ,r.modify_time = sysdate where r.order_relation_id = :1'
USING V_ORDER_ITEM_OBJ_ID;
END IF;
IF V_ORDER_ITEM_CD = 'T20' AND V_SERVICE_OFFER_ID = '10' THEN
--处理订购关系正常
EXECUTE IMMEDIATE 'update order_relation r set r.state = ''1001'' ,r.modify_time = sysdate where r.order_relation_id = :1'
USING V_ORDER_ITEM_OBJ_ID;
END IF;
COMMIT;
END P_VSOP_COMLETION;
END PK_VSOP_COMLETION;