存储过程学习四

于 2010-08-26 13:40:00 发布
阅读量914 收藏
点赞数
文章标签： 存储 service date 产品 object insert
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tczxg/article/details/5840375
版权

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;

tczxg
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值