oracle PL/SQL定义结构化类型 CREATE OR REPLACE TYPE type_specialPax AS OBJECT -- 定义特服旅客对象。 -- HHB -- 2013-05-15 ( paxName VARCHAR2 (150 ), -- 姓名 psgGender VARCHAR2 (150 ), -- 性别 flightNo VARCHAR2 (150 ), -- 航班号 orgCityAirp VARCHAR2 (150 ), -- 机场 dstCityAirp VARCHAR2 (150 ), -- 到达机场 lclDptDate Date , -- 当地日期1 lclDptDate_e Date , -- 当地日期2 specialCode VARCHAR2 (150 ), -- 特服编码 idNo VARCHAR2 (150 ), -- 证件号 specialAuditResult VARCHAR2 (150 ), -- 审核结果 isCd VARCHAR2 (150 ), -- isOther VARCHAR2 (150 ), -- pnr VARCHAR2 (150 ), -- PNR speType VARCHAR2 (150 ), -- 特殊类型 isCancel VARCHAR2 (150 ), -- 是否取消 localStd Date , -- 当地时间1 localStd2 Date , -- 当地时间2 sortStr VARCHAR2 (2000 ), -- 排序列 orderStr VARCHAR2 (150 ), -- 排序 page number , -- 页码 rowSize number -- 行数 ) 定义包含自定义类型的参数的存储过程 CREATE OR REPLACE PROCEDURE prc_querySpecialPax ( paxInfo type_specialPax, sqlStrRe out varchar2, outcursor out sys_refcursor ) as startIndex number; endIndex number ; maxRow number ; sqlStr varchar2 (32767 ); sqlStr2 varchar2 (32767 ); sqlStr3 varchar2 (32767 ); begin ........ end prc_querySpecialPax; Java调用存储过程 [java] view plaincopyprint?String sql= "call prc_querySpecialPax(?,?,?)" ; OracleConnection con = ( OracleConnection) mydataSource .getConnection(); StructDescriptor structdesc = new StructDescriptor( "TYPE_SPECIALPAX" , con); Object[] paramObj = new Object[21]; paramObj[0]=pax.getPaxName()+ "" ; paramObj[1]=pax.getPsgGender()+ "" ; paramObj[2]=pax.getFlightNo()+ "" ; paramObj[3]=pax.getOrgCityAirp()+ "" ; paramObj[4]=pax.getDstCityAirp()+ "" ; paramObj[5]=pax.getLclDptDate()== null ?null : new Date(pax.getLclDptDate().getTime()); paramObj[6]=pax.getLclDptDate_e()== null ?null : newDate(pax.getLclDptDate_e().getTime()); paramObj[7]=pax.getSpecialCode(); paramObj[8]=pax.getIdNo()+ "" ; paramObj[9]=pax.getSpecialAuditResult()+ "" ; paramObj[10]=pax.getIsCd()+ "" ; paramObj[11]=pax.getIsOther()+ "" ; paramObj[12]=pax.getPnr()+ "" ; paramObj[13]=pax.getSpeType()+ "" ; paramObj[14]=pax.getIsCancel()+ "" ; paramObj[15]=pax.getLocalStd()== null ?null : newDate(pax.getLocalStd().getTime()); paramObj[16]=pax.getLocalStd2()== null ?null : newDate(pax.getLocalStd2().getTime()); paramObj[17]=pax.getSort()+ "" ; paramObj[18]=pax.getOrder()+ "" ; paramObj[19]=page; paramObj[20]=rows; STRUCT s= new STRUCT(structdesc, con, paramObj); OracleCallableStatement proc=(OracleCallableStatement)con.prepareCall(sql); ResultSet rs= null ; proc.setSTRUCT(1, s); proc.registerOutParameter(2, Types. VARCHAR ); proc.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR ); proc.execute(); String sqlSt=proc.getString(2); rs=(ResultSet)proc.getObject(3); List<AutoSpecialPaxInfo> paxinfos= new ArrayList<AutoSpecialPaxInfo>(); AutoSpecialPaxInfo paxInfo= null ; while (rs.next()) { ..... } rs.close(); con.close(); |
| |
|