在大数据量处理方面,存储过程有着独特的优势,如果把数据加载到中间件处理,用户体验差不说,对系统也是灾难。测试中遇到几个问题:
1.只有int能传到存储过程中,String 和date不行,是jar包的问题,解决方案:
F:\app\Administrator\product\11.2.0\dbhome_1\jlib\orai18n.jar
D:\program\weblogic\oracle_common\modules\oracle.jdbc_11.2.0\ojdbc6.jar
2.date精度丢失(小时分钟秒)的问题,解决方案用java.sql.Timestamp.
drop table test purge;
create table test as select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,
OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS,
TEMPORARY,SECONDARY,EDITION_NAME from dba_objects;
drop type TEST_OBJECT;
CREATE OR REPLACE TYPE TEST_OBJECT AS OBJECT
(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
SECONDARY VARCHAR2(1),
EDITION_NAME VARCHAR2(30)
);
create or replace procedure t_list_to_p(
testObject TEST_OBJECT,
t_count out number,
dCREATED out date)
is
OWNER VARCHAR2(30);
OBJECT_ID NUMBER;
OBJECT_TYPE VARCHAR2(19);
OBJECT_NAME VARCHAR2(128);
STATUS VARCHAR2(7);
CREATED date;
sqlT varchar2 (32767);
SCREATED VARCHAR2(19);
begin
OWNER := testObject.OWNER;
OBJECT_ID := testObject.OBJECT_ID;
OBJECT_TYPE := testObject.OBJECT_TYPE;
STATUS := testObject.STATUS;
CREATED := testObject.CREATED;
sqlT := 'select count(1) from test where object_id=:1 and owner=:2 and created>=:3';
EXECUTE IMMEDIATE sqlT into t_count using OBJECT_ID,OWNER,CREATED;
dCREATED := CREATED;
end t_list_to_p;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class TestObjectToProdure {
static final String driver_class = "oracle.jdbc.driver.OracleDriver";
static final String connectionURL = "jdbc:oracle:thin:@10.100.15.18:1521:ora11";
static final String userID = "test";
static final String userPassword = "test";
public void runTest(TestVO testVO) {
Connection con = null;
CallableStatement stmt = null ;
try {
Class.forName (driver_class).newInstance();
con = DriverManager.getConnection(connectionURL, userID, userPassword);
StructDescriptor tDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", con);
Object[] tObject = new Object[11];
tObject[0] = testVO.getOWNER();
tObject[1] = testVO.getObject_name();
tObject[2] = testVO.getObject_id();
tObject[3] = testVO.getDATA_OBJECT_ID();
tObject[4] = testVO.getOBJECT_TYPE();
tObject[5] = testVO.getCREATED() ;
tObject[6] = testVO.getLAST_DDL_TIME() ;
tObject[7] = testVO.getSTATUS() ;
tObject[8] = testVO.getTEMPORARY() ;
tObject[9] = testVO.getSECONDARY() ;
tObject[10]= testVO.getEDITION_NAME() ;
STRUCT struct = new STRUCT(tDescriptor, con, tObject);
stmt = con.prepareCall("{call t_list_to_p(?,?,?)}");
stmt.setObject(1, struct);
stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
stmt.registerOutParameter(3, Types.DATE );
stmt.execute();
String sqlStatment=stmt.getString(2);
System.out.println(stmt.getTimestamp(3));
} catch (Exception e) {
e.printStackTrace();
}finally{
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) throws Exception{
TestObjectToProdure testObjectToProdure = new TestObjectToProdure();
TestVO testVO = new TestVO();
testVO.setObject_id(20);
testVO.setOWNER("SYS");
testVO.setOBJECT_TYPE("TABLE");
testVO.setSTATUS("1");
testVO.setCREATED(getDate());
testObjectToProdure.runTest(testVO);
}
public static java.sql.Timestamp getDate() throws Exception{
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = "2009-8-15 08:01:00";
java.util.Date currentTime = formatter.parse(dateString);
java.sql.Timestamp sTime=new java.sql.Timestamp(currentTime.getTime());
return sTime;
}
}
import java.sql.Date;
import java.sql.Timestamp;
public class TestVO {
private int object_id;
private String object_name;
private String OBJECT_TYPE;
private String OWNER;
private String STATUS;
private String TEMPORARY;
private String SECONDARY;
private String EDITION_NAME;
private int DATA_OBJECT_ID;
private Timestamp CREATED;
private Date LAST_DDL_TIME;
public Timestamp getCREATED() {
return CREATED;
}
public void setCREATED(Timestamp created) {
CREATED = created;
}
public int getDATA_OBJECT_ID() {
return DATA_OBJECT_ID;
}
public void setDATA_OBJECT_ID(int data_object_id) {
DATA_OBJECT_ID = data_object_id;
}
public String getEDITION_NAME() {
return EDITION_NAME;
}
public void setEDITION_NAME(String edition_name) {
EDITION_NAME = edition_name;
}
public Date getLAST_DDL_TIME() {
return LAST_DDL_TIME;
}
public void setLAST_DDL_TIME(Date last_ddl_time) {
LAST_DDL_TIME = last_ddl_time;
}
public int getObject_id() {
return object_id;
}
public void setObject_id(int object_id) {
this.object_id = object_id;
}
public String getObject_name() {
return object_name;
}
public void setObject_name(String object_name) {
this.object_name = object_name;
}
public String getOBJECT_TYPE() {
return OBJECT_TYPE;
}
public void setOBJECT_TYPE(String object_type) {
OBJECT_TYPE = object_type;
}
public String getOWNER() {
return OWNER;
}
public void setOWNER(String owner) {
OWNER = owner;
}
public String getSECONDARY() {
return SECONDARY;
}
public void setSECONDARY(String secondary) {
SECONDARY = secondary;
}
public String getSTATUS() {
return STATUS;
}
public void setSTATUS(String status) {
STATUS = status;
}
public String getTEMPORARY() {
return TEMPORARY;
}
public void setTEMPORARY(String temporary) {
TEMPORARY = temporary;
}
public String toString() {
StringBuffer sb = new StringBuffer(512);
sb.append("TestVO[");
sb.append("\n object_id=").append(this.object_id);
sb.append("\n object_name=").append(this.object_name);
sb.append("\n OBJECT_TYPE=").append(this.OBJECT_TYPE);
sb.append("\n OWNER=").append(this.OWNER);
sb.append("\n STATUS=").append(this.STATUS);
sb.append("\n TEMPORARY=").append(this.TEMPORARY);
sb.append("\n SECONDARY=").append(this.SECONDARY);
sb.append("\n EDITION_NAME=").append(this.EDITION_NAME);
sb.append("\n DATA_OBJECT_ID=").append(this.DATA_OBJECT_ID);
sb.append("\n CREATED=").append(this.CREATED);
sb.append("\n LAST_DDL_TIME=").append(this.LAST_DDL_TIME);
sb.append("\n]");
return sb.toString();
}
}