java结构化参数调用存储过程-优快云博客

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调用存储过程

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