ORACLE TYPE運用

 

1.建立oracle 類型
  CREATE OR REPLACE TYPE BCESCDE.IBPS_ACCT_AMT_RECORD_TYPE IS OBJECT (
  OLD_CHANNEL_SEQ_NO   VARCHAR2(30),       --原查詢申請的渠道流水號
  PARTYNO              VARCHAR2(12),       --客戶號
  AGREEMENT_NO         VARCHAR2(60),       --協議號
  ACCT_NO              VARCHAR2(32),       --帳號
  ACCT_TYPE            VARCHAR2(1),        --帳號類型 0-對公客戶，1-借記卡，2-個人存摺，3-貸記卡
  SUB_ACCT_NO          VARCHAR2(32),       --子帳號 信用卡明細時有，其他情況無
  CURR_TYPE            VARCHAR2(3),         --幣種
  BALANCE              NUMBER,              --餘額  
  DEPOSIT_TYPE         VARCHAR2(40),        --存款類型名稱
  FROM_DATE            VARCHAR2(10),        --起息日期
  END_DATE             VARCHAR2(10),        --到期日
  CREDIT_LIMIT         NUMBER,              --信用額度
  );

  CREATE OR REPLACE TYPE BCESCDE.IBPS_ACCT_AMT_TABLE_TYPE IS TABLE OF BCESCDE.IBPS_ACCT_AMT_RECORD_TYPE;

2.過程輸入參數中的運用
  PROCEDURE ibp_add_bill_bat_pro(in_partyno   IN VARCHAR2, --客戶號
                                                                     in_bill_list IN ibps_acct_amt_table_type, --
                                                                    out_retcode  OUT VARCHAR2, --輸出：
                                                                    --0成功
                                                                      --9系統異常
                                                                   out_message OUT VARCHAR2 --輸出執行結果資訊
                                                                    ) IS
      i NUMBER;
      v_serial_no VARCHAR2(20);
   BEGIN
      i := in_bill_list.FIRST;
      LOOP
         v_serial_no := ibp_gen_serial_no_pro('1');
         INSERT INTO ibp_bill_tab
            (partyno,
             handFlag,
             opType,
             BillId,
             billFlag,
             accNo,
             accName,
             insert_time,
             update_time)
         VALUES
            (in_partyno,
             in_bill_list(i).handFlag,
             '1',--0修改1新增
             v_serial_no,
             in_bill_list(i).billFlag,
             in_bill_list(i).accNo,
             in_bill_list(i).accName,
             SYSDATE,
             SYSDATE);

         EXIT WHEN i = in_bill_list.LAST;
         i := in_bill_list.NEXT(i);
      END LOOP;

      out_retcode := '0';
      out_message := '成功';
   EXCEPTION
      WHEN OTHERS THEN
         out_retcode := '9';
         out_message := '[ibp_add_bill_bat_pro]錯誤資訊' || to_char(SQLCODE) || '-' ||
                        substrb(SQLERRM, 1, 120);
   END ibp_add_bill_bat_pro;
  
  
3.在java中調用過程
       Connection oConn = null;    
    oConn = ((WLConnection)getSqlMapClientTemplate().getDataSource().getConnection()).getVendorConnection();
    oConn.setAutoCommit(false);
    
    //建立模型
    StructDescriptor structDesc = StructDescriptor.createDescriptor(getSchema() + ".IBP_BILL_RECORD_TYPE" , oConn);
    ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(getSchema() + ".IBP_BILL_TABLE_TYPE" , oConn);
    
    Object[] params = new Object[billInfoList.size()];
       
        //填充資料
    for(int i = 0, size = billInfoList.size(); i < size; i++) {
     BillInfoInDTO billInfo = (BillInfoInDTO)billInfoList.get(i);
     BigDecimal tranAmt = null;
     BigDecimal balance = null;
     if(null != billInfo.getTranAmt() && !"".equals(billInfo.getTranAmt())){
      tranAmt = new BigDecimal(billInfo.getTranAmt());
     }
     if(null != billInfo.getBalance() && !"".equals(billInfo.getBalance())) {
      balance = new BigDecimal(billInfo.getBalance());
     }
     Object[] javaObjects = {billInfo.getBillId(), billInfo.getHandFlag(), billInfo.getOpType(),
                       billInfo.getBillFlag(), billInfo.getAccNo(), billInfo.getAccName()};
     STRUCT struct = new STRUCT(structDesc, oConn, javaObjects);
     params[i] = struct;
    }
    ARRAY pArray = new ARRAY(arrayDesc, oConn, params);
    //調用過程
    OracleCallableStatement ocs = (OracleCallableStatement)oConn.prepareCall("{call ibp_newebank_pkg.ibp_add_bill_bat_pro(?,?,?,?)}");
    ocs.setString(1, partyNo);
    ocs.setARRAY(2, pArray);
    ocs.registerOutParameter(3, Types.VARCHAR);
    ocs.registerOutParameter(4, Types.VARCHAR);
    ocs.execute();

    String retCode = ocs.getString(3);
    String outMessage = ocs.getString(4);