- 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 , -- 当地日期1lclDptDate_e Date , -- 当地日期2specialCode VARCHAR2 ( 150 ), -- 特服编码idNo VARCHAR2 ( 150 ), -- 证件号specialAuditResult VARCHAR2 ( 150 ), -- 审核结果isCd VARCHAR2 ( 150 ), --isOther VARCHAR2 ( 150 ), --pnr VARCHAR2 ( 150 ), -- PNRspeType VARCHAR2 ( 150 ), -- 特殊类型isCancel VARCHAR2 ( 150 ), -- 是否取消localStd Date , -- 当地时间1localStd2 Date , -- 当地时间2sortStr 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) asstartIndex number ;endIndex number ;maxRow number ;sqlStr varchar2 ( 32767 );sqlStr2 varchar2 ( 32767 );sqlStr3 varchar2 ( 32767 );begin........end prc_querySpecialPax;
- Java调用存储过程
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();