java调用oracle存储过程/函数。参数为复杂类型(数组,自定义类型)
对于存储过程/函数入参，或出参为复杂类型的情况，这里列出官方的参考实践。有时间再补充。
1.出参为数组的情况：
16.4.3.2 Passing an Array to a Callable Statement
To retrieve a collection as an OUT parameter in PL/SQL blocks, execute the following to register the bind type for your OUT parameter.
-
Cast your callable statement to an
OracleCallableStatement:OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}"); -
Register the
OUTparameter with this form of theregisterOutParameter()method:ocs.registerOutParameter (int param_index, int sql_type, string sql_type_name); //这里sql_type_name为我们自定义的type类型的名称。如MY_TYPE.
//(CREATE OR REPLACE TYPE MY_TYPE AS VARRAY(2) of integer).Where
param_indexis the parameter index,sql_typeis the SQL typecode, andsql_type_nameis the name of the array type. In this case, thesql_typeisOracleTypes.ARRAY. -
Execute the call:
ocs.execute();
-
Get the value:
oracle.sql.ARRAY array = ocs.getARRAY(1);//1为数组参数在参数列表中的序号(从1开始)
int[] intArray = array.getIntArray();//转为java数组
System.out.println("array(1):"+intArray[0]);
2.出参为自定义类型(对象)的情况：
13.3.5.2 Retrieving SQLData Objects from a Callable Statement OUT Parameter
Suppose you have an OracleCallableStatement ocs that calls a PL/SQL function GETEMPLOYEE(). The program passes an employee number (empnumber) to the function; the function returns the corresponding Employee object.
-
Prepare an
OracleCallableStatementto call theGETEMPLOYEE()function.OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{ ? = call GETEMPLOYEE(?) }");
-
Declare the
empnumberas the input parameter toGETEMPLOYEE(). Register theSQLDataobject as theOUTparameter, with typecodeOracleTypes.STRUCT. Then, execute the statement.ocs.setInt(2, empnumber); ocs.registerOutParameter(1, OracleTypes.STRUCT, "EMP_OBJECT"); ocs.execute();
-
Use the
getObject()method to retrieve the employee object. The following code assumes that there is a type map entry to map the Oracle object to Java typeEmployee:Employee emp = (Employee)ocs.getObject(1);
If there is no type map entry, then
getObject()would return anoracle.sql.STRUCTobject. Cast the output to typeSTRUCT, because thegetObject()method signature returns the genericjava.lang.Objecttype:STRUCT emp = (STRUCT)ocs.getObject(1);
文档参考：
Oracle® Database JDBC Developer's Guide and Reference
10g Release 1
(10.1)
Part Number B10979-02
章节： 13 Working with Oracle Object Types
章节： 16 Working with Oracle Collections
今天有时间翻开以前的日志，下面附上我的实现摘要(2013.03.01)：
数据库部分：
1.包声明：
create or replace package KP_CFCS4 is --改签原票汇总合计 procedure get_cs4_resign_sum(where_sql IN string, sum_cs4 OUT KS_CS4SUM); end KP_CFCS4;
2.包体：
create or replace package body KP_CFCS4 is /* * 改签原票汇总合计 */ procedure get_cs4_resign_sum(where_sql IN string, sum_cs4 OUT KS_CS4SUM) is sqlStr varchar2(500); begin sum_cs4 := KS_CS4SUM(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0); /*770 合计 "669,191.50" --改签原票的金额合计 980 电子支付银行卡张数 "1,245.00" --POS机张数、乘车凭证张数两项之和 981 现金总票价 "459,655.00" --合计中的现金部分 983 银行卡总票价 "11,782.50" --合计中的POS机部分 984 乘车证总票价 "197,754.00" --合计中的乘车凭证部分(打票部分) 993 现金净退款 "459,655.00" --已取消改签手续费，金额同“现金总票价”。 995 银行卡净退款 "11,782.50" --已取消改签手续费，金额同“银行卡总票价”。 996 乘车证净退款 "197,754.00"*/ --已取消改签手续费，金额同“乘车证总票价”。 sqlStr := 'select sum( kppjzs0 * kpfyhj0 ),'|| ' sum( kpposzs + kpdpzs0 ),'|| ' sum( kprmbzs * kpfyhj0 ),'|| ' sum( kpposzs * kpfyhj0 ),'|| ' sum( kpdpzs0 * kpfyhj0 ) '|| ' from PTCHECK.AKP_SHCCZB0 t '|| where_sql; sqlStr := sqlStr||' and t.kpfpbj0=''9'''; --增加改签原票条件 execute immediate sqlStr INTO sum_cs4.sum_all_amount, sum_cs4.sum_card_ticket_num, sum_cs4.sum_cash_amount, sum_cs4.sum_bank_card_amount, sum_cs4.sum_voucher_amount; --计算合计：改签原票的金额合计 = 现金总票价 + 银行卡总票价 + 乘车证总票价。 --采用加法，避免前台页面数据不对 sum_cs4.sum_all_amount := sum_cs4.sum_cash_amount + sum_cs4.sum_bank_card_amount + sum_cs4.sum_voucher_amount; --净退款(金额同各“xx总票价) sum_cs4.sum_cash_return_amount := sum_cs4.sum_cash_amount; sum_cs4.sum_bank_card_return_amount := sum_cs4.sum_bank_card_amount; sum_cs4.sum_voucher_return_amount := sum_cs4.sum_voucher_amount; dbms_output.put_line('sum_cs4.sum_all_amount：'||sum_cs4.sum_all_amount); dbms_output.put_line('sum_cs4.sum_card_ticket_num：'||sum_cs4.sum_card_ticket_num); dbms_output.put_line('sum_cs4.sum_cash_amount：'||sum_cs4.sum_cash_amount); dbms_output.put_line('sum_cs4.sum_bank_card_amount：'||sum_cs4.sum_bank_card_amount); dbms_output.put_line('sum_cs4.sum_voucher_amount：'||sum_cs4.sum_voucher_amount); end; begin -- 初始化 null; end KP_CFCS4;
其中用到的对象(自定义类型)：
KS_CS4SUM：
create or replace type KS_CS4SUM as object ( -- Author : qinxike -- Created : 2012-10-24 16:42:48 -- Purpose : 拆分财收四汇总对象 -- Attributes --公用属性 --约定票据类型【1：电子客票,2：改签原票,3：退票】，有些字段为公用字段，我用类型ID标出。 sum_ticket_num int, --总张数(包括改签原票),未用 --正常售票统计的属性 sum_sell_ticket_num int, --发售客票张数（778）(总张数-改签原票张数),【1】 sum_card_ticket_num int, --电子支付银行卡张数（980）,【1，2，3】 sum_all_amount int, --费用合计（770）=981+982+983,【1，2，3】 sum_distant_amount int, --异地票总票价（773）,【1，3】 sum_cash_amount int, --现金票总票价（981）,【1，2，3】 sum_pet_card_amount int, --储值卡总票价（982）,【1】 sum_bank_card_amount int, --银行卡总票价（983）,【1，2，3】 sum_voucher_amount int, --乘车证总票价（984）,【1，2，3】 sum_cash_resign_amount int, --现金改签新票总票价（986）,【1】 sum_bank_card_resign_amount int, --银行卡改签新票总票价（988）,【1】 --改签票增加的属性 sum_cash_return_amount int, --现金净退款（993）,【2，3】 sum_bank_card_return_amount int, --银行卡净退款（995）,【2，3】 sum_voucher_return_amount int, --乘车证净退款（996）,【2，3】 --退票增加的属性 --退票费用总和 = 退票手续费(767) + 挂失补票手续费(769) sum_loss_compen_amount int, --挂失补退票款额(766)【3】 sum_loss_compen_charge int, --挂失补票手续费(769)【3】 sum_return_charge int, --退票(手续)费(767)【3】 sum_ticket_return_amount int, --客票净退款(768)【3】 sum_cash_return_charge int, --现金退票(手续)费(987)【3】 sum_bank_card_return_charge int, --银行卡退票(手续)费(989)【3】 sum_voucher_return_charge int --乘车证退票(手续)费(990)【3】 )
java后台代码部分：
1.主要代码：
说明：使用了spring的jdbcTemplate执行存储过程(hibernate的session.doWork()无返回值,无语)，返回自定义对象(ksCs4SumData)，这个对象就是对应存储过程定义的自定义类型(KS_CS4SUM)。其中还用到了java.sql.CallableStatement.getObject(int parameterIndex, Map<String, Class<?>> map)方法进行转换和获取对象，具体是用一个Map映射数据库类型名到类对象：typeMap.put("PTCHECK.KS_CS4SUM", KsCs4SumData.class);然后在KsCs4SumData类中实现具体的读取/写入过程（参考KsCs4SumData的代码）
/** * 财收四汇总合计[统计级别有 单张，窗口，车站].调用存储过程实现 * @param cs4Level 统计标识 * @param cs4Condition 统计条件VO * @return KsCs4SumData 合计对象 */
@Override @SuppressWarnings("unchecked") @Transactional(timeout=2*60*1000) public KsCs4SumData callCs4Sum(String cs4Level, KsCs4ConditionVo cs4Condition){ String callSQL = ""; String preWhereSQL = "";//预定义的whereSQL,解决final的whereSQL不能重复赋值的问题 String ticketTypeId = cs4Condition.getTicketTypeId(); if(ticketTypeId.equals("1")){ //售票统计,使用主表的whereSQL callSQL = "{call kp_cfcs4.get_Cs4_Sum(?,?)}"; preWhereSQL = KsCs4Action.makeSQLStrWithSup(cs4Level,cs4Condition); }else if(ticketTypeId.equals("2")){ //改签统计,使用主表的whereSQL callSQL = "{call kp_cfcs4.get_cs4_resign_sum(?,?)}"; preWhereSQL = KsCs4Action.makeSQLStrWithSup(cs4Level,cs4Condition); }final String whereSQL = preWhereSQL; final String typeName = "KS_CS4SUM";//Oracle自定义类型的名称 log.info("拆分财收四合计.统计项基础whereSQL:"+whereSQL); //执行存储过程 KsCs4SumData ksCs4SumData = super.ptcheckJdbcTemplate.execute(callSQL, new CallableStatementCallback<KsCs4SumData>() { @Override public KsCs4SumData doInCallableStatement(CallableStatement cstmt) throws SQLException, DataAccessException { // 使用oracle的CallableStatement // OracleCallableStatement ocs = (OracleCallableStatement) cstmt; // ocs.setString(1, whereSQL); // ocs.registerOutParameter(2, OracleTypes.STRUCT, TypeName); // ocs.execute(); // // 获取对象,使用struct // // STRUCT struct = (STRUCT)ocs.getObject(2); // // Object[] objs = struct.getAttributes();//返回对象的所有属性 // // for(Object obj :objs) // // System.out.println(obj);//输出对象第一个值 // 使用java的CallableStatement cstmt.setString(1, whereSQL); cstmt.registerOutParameter(2, Types.STRUCT,typeName); cstmt.execute(); // 获取对象,使用typeMap转换成po对象 Map typeMap = new HashMap(); typeMap.put("PTCHECK.KS_CS4SUM", KsCs4SumData.class); KsCs4SumData cs4SumData = (KsCs4SumData) cstmt.getObject(2, typeMap); log.info("KsCs4SumData:" + cs4SumData); return cs4SumData; } }); return ksCs4SumData; }
2.用到的对象：
KsCs4SumData：该对象也有限制条件，就是发实现java.sql.SQLData接口，覆盖getSQLTypeName(),readSQL(SQLInput stream, String typeName),writeSQL(SQLOutput stream)方法。其实就是在存储过程返回的类型上一个个属性(attribute)读取/写入KsCs4SumData(通过java.sql.CallableStatement.getObject(int parameterIndex, Map<String, Class<?>> map)执行)。
另我曾在sql代码中定义该类型而不是单独声明成Type，但后面发现java中无法识别该类型，必须定义到Types下:
，
package com.revenue.auditkp.vo; import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; /** * 对应数据库自定义类型KS_CS4SUM * 字段的详细解释见数据库中KS_CS4SUM的说明 * @author qinxike * */ public class KsCs4SumData implements SQLData { private String SQLTypeName = "";// 数据库完全限定类型名称PTCHECK.KS_CS4SUM private int sumTicketNum; // --总张数(包含改签原票) private int sumSellTicketNum;// 发售客票张数(不包含改签原票) private int sumCardTicketNum;// 电子支付银行卡张数 private int sumAllAmount; // 费用合计 private int sumDistantAmount;// 异地票总票价 private int sumCashAmount;// 现金票总票价 private int sumPetCardAmount;// 储值卡总票价 private int sumBankCardAmount;// 银行卡总票价 private int sumVoucherAmount;// 乘车证总票价 private int sumCashResignAmount;// 现金改签新票总票价 private int sumBankCardResignAmount;// 银行卡改签新票总票价 //改签原票增加的字段 private int sumCashReturnAmount; //--现金净退款（993）,【2，3】 private int sumBankCardReturnAmount; //--银行卡净退款（995）,【2，3】 private int sumVoucherReturnAmount; //--乘车证净退款（996）,【2，3】 //退票增加的字段 private int sumLossCompenAmount; //--挂失补退票款额(766)【3】 private int sumLossCompenCharge; //--挂失补票手续费(769)【3】 private int sumReturnCharge; //--退票(手续)费(767)【3】 private int sumTicketReturnAmount; //--客票净退款(768)【3】 private int sumCashReturnCharge; //--现金退票(手续)费(987)【3】 private int sumBankCardReturnCharge; //--银行卡退票(手续)费(989)【3】 private int sumVoucherReturnCharge; //--乘车证退票(手续)费(990)【3】 @Override public String getSQLTypeName() throws SQLException { return SQLTypeName; } @Override public void readSQL(SQLInput stream, String typeName) throws SQLException { SQLTypeName = typeName; sumTicketNum = stream.readInt(); sumSellTicketNum = stream.readInt(); sumCardTicketNum = stream.readInt(); sumAllAmount = stream.readInt(); sumDistantAmount = stream.readInt(); sumCashAmount = stream.readInt(); sumPetCardAmount = stream.readInt(); sumBankCardAmount = stream.readInt(); sumVoucherAmount = stream.readInt(); sumCashResignAmount = stream.readInt(); sumBankCardResignAmount = stream.readInt(); //改签原票增加的字段 sumCashReturnAmount=stream.readInt(); sumBankCardReturnAmount=stream.readInt(); sumVoucherReturnAmount=stream.readInt(); //退票增加的字段 sumLossCompenAmount=stream.readInt(); sumLossCompenCharge=stream.readInt(); sumReturnCharge=stream.readInt(); sumTicketReturnAmount=stream.readInt(); sumCashReturnCharge=stream.readInt(); sumBankCardReturnCharge=stream.readInt(); sumVoucherReturnCharge=stream.readInt(); } @Override public void writeSQL(SQLOutput stream) throws SQLException { stream.writeInt(sumTicketNum); stream.writeInt(sumSellTicketNum); stream.writeInt(sumCardTicketNum); stream.writeInt(sumAllAmount); stream.writeInt(sumDistantAmount); stream.writeInt(sumCashAmount); stream.writeInt(sumPetCardAmount); stream.writeInt(sumBankCardAmount); stream.writeInt(sumVoucherAmount); stream.writeInt(sumCashResignAmount); stream.writeInt(sumBankCardResignAmount); //改签原票增加的字段 stream.writeInt(sumCashReturnAmount); stream.writeInt(sumBankCardReturnAmount); stream.writeInt(sumVoucherReturnAmount); //退票增加的字段 stream.writeInt(sumLossCompenAmount); stream.writeInt(sumLossCompenCharge); stream.writeInt(sumReturnCharge); stream.writeInt(sumTicketReturnAmount); stream.writeInt(sumCashReturnCharge); stream.writeInt(sumBankCardReturnCharge); stream.writeInt(sumVoucherReturnCharge); } //getter setter略 }
至此，我们的程序可以工作了，调用存储过程，返回一个定义好的po对象。感觉是不是很复杂？我感觉是结果挺简单，过程挺复杂。涉及到的东西较多，依赖多，就是不太实用，少一个东西就起不来，还不如用中间表缓存数据进行查询来得方便。我认为除非是有必要的理由，否则就不要用这东西了。但存储过程返回多个值的情况还是有的，有没有更简便的实现方式呢？
