Oracle安装程序 ：
CREATE OR REPLACE TYPE BD_TB_STRUCT AS OBJECT( start_ts TIMESTAMP(3), end_ts TIMESTAMP(3), time_type NUMBER(19), duration NUMBER(12)) FINAL;/CREATE OR REPLACE PROCEDURE merge_time_bounds( s1_bd_t IN bd_tb_struct, s2_bd_t IN bd_tb_struct, r_bd_t OUT bd_tb_struct)IS p_start TIMESTAMP(3) := LEAST( s1_bd_t.start_ts, s2_bd_t.start_ts ); p_end TIMESTAMP(3) := GREATEST( s1_bd_t.end_ts, s2_bd_t.end_ts );BEGIN r_bd_t := new BD_TB_STRUCT( p_start, p_end, COALESCE( s1_bd_t.time_type, s2_bd_t.time_type ), ( CAST( p_end AS DATE ) - CAST( p_start AS DATE ) ) * 24 * 60 * 60 );END;/
Java SQLData类 ：
import java.math.BigDecimal;import java.math.BigInteger;import java.sql.SQLData;import java.sql.SQLException;import java.sql.SQLInput;import java.sql.SQLOutput;import java.sql.Timestamp;import java.time.LocalDateTime;import java.time.ZoneOffset;public class BoundsSQL implements SQLData{ public static final String SQL_TYPE = "BD_TB_STRUCT"; public java.sql.Timestamp start; public java.sql.Timestamp end; public BigInteger type; public BigInteger duration; public BoundsSQL() { } public BoundsSQL( final int year, final int month, final int dayOfMonth, final int hour, final int minute, final int seconds, final long duration, final long type ) { final long epochSeconds = LocalDateTime.of( year, month, dayOfMonth, hour, minute, seconds ).toEpochSecond( ZoneOffset.UTC ); this.start = new Timestamp( epochSeconds * 1000 ); this.end = new Timestamp( (epochSeconds + duration) * 1000 ); this.duration = BigInteger.valueOf( duration ); this.type = BigInteger.valueOf( type ); } @Override public String getSQLTypeName() throws SQLException { return SQL_TYPE; } @Override public void readSQL( SQLInput stream, String typeName ) throws SQLException { start = stream.readTimestamp(); end = stream.readTimestamp(); type = stream.readBigDecimal().toBigInteger(); duration = stream.readBigDecimal().toBigInteger(); } @Override public void writeSQL( SQLOutput stream ) throws SQLException { stream.writeTimestamp( start ); stream.writeTimestamp( end ); stream.writeBigDecimal( new BigDecimal( type ) ); stream.writeBigDecimal( new BigDecimal( duration ) ); } @Override public String toString() { return String.format( "Start: %snEnd: %snDuration: %snType: %s", start, end, duration, type ); }}从Java调用存储过程 ：
使用调用存储过程
OracleCallableStatement#setObject( int, Object)以传递参数，并将类放入类型映射中，并使用
OracleCallableStatement#registerOutParameter( int, int,string )和
OracleCallableStatement#getObject( int )检索参数。
import java.sql.DriverManager;import java.sql.SQLException;import java.util.Map;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleConnection;import oracle.jdbc.OracleTypes;public class PassStructToProcedure{ public static void main( final String[] args ){ OracleConnection con = null; try{ Class.forName( "oracle.jdbc.OracleDriver" ); con = (OracleConnection) DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "USERNAME", "PASSWORD" ); BoundsSQL bound1 = new BoundsSQL( 2019, 1, 1, 0, 0, 0, 10, 1 ); BoundsSQL bound2 = new BoundsSQL( 2019, 1, 1, 0, 0, 5, 10, 2 ); OracleCallableStatement st = (OracleCallableStatement) con.prepareCall( "{ call MERGE_TIME_BOUNDS( ?, ?, ? ) }" ); st.setObject( 1, bound1 ); st.setObject( 2, bound2 ); st.registerOutParameter( 3, OracleTypes.STRUCT, BoundsSQL.SQL_TYPE ); st.execute(); Map<String,Class<?>> typeMap = con.getTypeMap(); typeMap.put( BoundsSQL.SQL_TYPE, BoundsSQL.class ); BoundsSQL out = (BoundsSQL) st.getObject( 3 ); System.out.println( out.toString() ); st.close(); } catch (ClassNotFoundException | SQLException ex) { System.out.println( ex.getMessage() ); ex.printStackTrace(); } finally { try{ if ( con != null ) con.close(); } catch( SQLException e ) { } } }}输出 ：
Start: 2019-01-01 00:00:00.0End: 2019-01-01 00:00:15.0Duration: 15Type: 1
