oracle存储过程一例

CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS
------------------------------------------------------------------------
-- Oracle 包
---国航支付平台VISA退款
-- 游标定义：
--
-- 存储过程定义：
-- PY_WEBREFUND_VISA_PREPARE  ： VISA退款准备
-- 最后修改人：dougq
-- 最后修改日期：2007.4.17
------------------------------------------------------------------------

 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;
/