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