oracle 存储过程 一例

CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS    
------------------------------------------------------------------------    
-- Oracle 包       
-- 游标定义：    
--    
-- 存储过程定义：    
-- PY_WEBREFUND_VISA_PREPARE  ： VISA退款准备    
-- 最后修改人：dougq    
-- 最后修改日期：2010.4.15   
------------------------------------------------------------------------    
   
 PROCEDURE PY_WEBREFUND_VISA_PREPARE (    
  in_serialNoStr   IN  VARCHAR2, --用"|"隔开的一组网上退款申请流水号    
  in_session_operatorid IN VARCHAR2, --业务操作员    
  out_return_code     OUT VARCHAR2, --存储过程返回码    
  out_visaInfoStr     OUT VARCHAR2    
 );    
     
END PY_PCKG_REFUND2;    
/    
   
   
CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS    
     
 PROCEDURE PY_WEBREFUND_VISA_PREPARE (    
  in_serialNoStr      IN  VARCHAR2, --用"|"隔开的一组网上退款申请流水号    
  in_session_operatorid IN VARCHAR2,--业务操作员    
  out_return_code     OUT VARCHAR2, --存储过程返回码    
  out_visaInfoStr     OUT VARCHAR2    
 ) IS    
  --变量声明    
  v_serialno  VARCHAR2(20);--网上退款申请流水号    
  v_refserialno VARCHAR2(20);--支付交易流水号    
  v_tobankOrderNo VARCHAR2(30);--上送银行的订单号    
  v_orderDate  VARCHAR2(8);--订单日期    
  v_businessType VARCHAR2(10);--业务类型    
  v_currType  VARCHAR2(3);--订单类型（ET－电子机票）    
  v_merno   VARCHAR2(15);--商户号    
  v_orderNo  VARCHAR2(20);--商户订单号    
  v_orderState VARCHAR2(2);    
  v_refAmount     NUMBER(15,2);--退款金额     
  v_tranType  VARCHAR(2);--交易类型    
  v_bank   VARCHAR2(10);--收单银行    
  v_date   VARCHAR2 (8);--交易日期    
  v_time   VARCHAR2 (6);--交易时间    
  v_datetime  VARCHAR2 (14);--获取的系统时间    
  v_index_start NUMBER;    
  v_index_end  NUMBER;    
  v_i    NUMBER;    
 BEGIN    
  -- 初始化参数    
  out_visaInfoStr := '';    
  v_i := 1;    
  v_index_start := 1;    
  v_index_end := INSTR(in_serialNoStr,'|',1,1);     
  v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end-1);    
  v_datetime := TO_CHAR (SYSDATE, 'yyyymmddhh24miss');    
  v_date := SUBSTR (v_datetime, 1, 8);    
  v_time := SUBSTR (v_datetime, 9, 14);    
   
  --从退款请求表中查询定单信息（商户号、商户订单号、退款金额）    
  WHILE v_index_end > 0 LOOP    
   SELECT    
    WEBR_MERNO,    
    WEBR_ORDERNO,    
    WEBR_AMOUNT,    
    WEBR_SERIALNO,    
    WEBR_REFUNDTYPE    
   INTO    
    v_merno,    
    v_orderNo,    
    v_refAmount,    
    v_serialno,    
    v_tranType    
      FROM     
    PY_WEB_REFUND    
      WHERE     
    WEBR_REFREQNO = v_refserialno;    
       
   --将查询到的数据组成串    
   out_visaInfoStr := out_visaInfoStr || v_merno || '~' || v_orderNo || '~' || v_refAmount + '|';    
      
   --为下次循环做数据准备    
      v_i := v_i + 1;    
      v_index_start := v_index_end + 1;    
      v_index_end := INSTR(in_serialNoStr,'|',1,v_i);    
      IF v_index_end > 0 THEN    
        v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end - 1);          
      END IF;    
          
   --根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：WTRN_TOBANKORDERNO    
   SELECT    
    WTRN_TOBANKORDERNO,    
    WTRN_ORDERNO,    
      WTRN_ORDERDATE,    
      WTRN_BUSINESSTYPE,    
    WTRN_ACCPBANK,    
    WTRN_TRANCURRTYPE    
   INTO    
    v_tobankOrderNo,    
    v_orderNo,    
    v_orderDate,    
    v_businessType,    
    v_bank,    
    v_currType    
   FROM PY_WEBPAY_VIEW    
    WHERE WTRN_SERIALNO = v_serialno;    
        
   --记录流水表(退款)    
      INSERT INTO PY_WEBPAY_TRAN(    
    WTRN_SERIALNO,    
    WTRN_TRANTYPE,     
    WTRN_ORIGSERIALNO,    
    WTRN_ORDERNO,     
    WTRN_ORDERDATE,     
    WTRN_BUSINESSTYPE,    
    WTRN_TRANCURRTYPE,    
    WTRN_TRANAMOUNT,    
    WTRN_ACCPBANK,     
    WTRN_TRANSTATE,     
    WTRN_TRANTIME,    
    WTRN_TRANDATE,     
    WTRN_MERNO,     
    WTRN_TOBANKORDERNO    
   )VALUES(    
    v_refserialno, --和申请表的流水号相同，作为参数传人    
    v_tranType,    
    v_serialno, --原交易流水号，查询退款申请表得到    
    v_orderNo,    
    v_orderDate,    
    v_businessType,    
    v_currType,    
    v_refAmount,    
    v_bank,    
    '1',    
    v_time,    
    v_date,    
    v_merno,    
    v_tobankOrderNo --上送银行的订单号，查询流水表得到    
   );    
   
   --更新网上退款申请表    
   UPDATE PY_WEB_REFUND    
   SET     
    WEBR_IFDISPOSED = '1',    
    WEBR_DISPOSEDOPR = in_session_operatorid,    
    WEBR_DISPOSEDDATE = v_datetime    
   WHERE     
    WEBR_REFREQNO = v_refserialno;    
       
   --更新定单表    
   IF v_tranType = '2' THEN    
    v_orderState := '7';    
   ELSE    
    v_orderState := '10';    
   END IF;    
     
   UPDATE PY_ORDER    
   SET    
    ORD_ORDERSTATE = v_orderState    
   WHERE    
     ORD_ORDERNO = v_orderNo    
    AND ORD_ORDERDATE = v_orderDate    
    AND ORD_BUSINESSTYPE = v_businessType;     
  END LOOP;    
     
  -- 异常处理    
  EXCEPTION    
   WHEN OTHERS THEN    
   ROLLBACK;    
   out_return_code := '14001';    
   RETURN;     
 END;    
   
END PY_PCKG_REFUND2; 
/