第一,創建orcale中的Java對象.
命令:
create or replace and compile Java source named "TransMetaData"
as
/*
Java code
*/
注意:1.不用寫包名.
2.方法必須是static的
3.如果方法需要參數,則需要在第二步,創建帶參數的存儲過程.
如果方法需要返回值,則需要在第二步,創建帶返回值的存儲過程.
例如:
create or replace and compile Java source named "TransMetaData"
as
import Java.io.*;
import Java.sql.*;
import Java.util.PropertIEs;
import Oracle.jdbc.driver.OracleDriver;
public class TransMetaData {
/**
* @param args
* @throws SQLException
* @throws UnsupportedEncodingException
*/
public static void donow(String str) throws FileNotFoundException, IOException,SQLException, UnsupportedEncodingException {
str = "_"+str;
Properties props = new PropertIEs();
props.load(new FileInputStream("c:/tables.propertIEs"));
String DATABAST_SOURCE_URL = props.getProperty("DATABAST_SOURCE_URL"+str);
String DATABAST_SOURCE_USERNAME = props.getProperty("DATABAST_SOURCE_USERNAME"+str);
String DATABAST_SOURCE_PASSWORD = props.getProperty("DATABAST_SOURCE_PASSWord"+str);
String DATABAST_TARGET_URL = props.getProperty("DATABAST_TARGET_URL"+str);
String DATABAST_TARGET_USERNAME = props.getProperty("DATABAST_TARGET_USERNAME"+str);
String DATABAST_TARGET_PASSWORD = props.getProperty("DATABAST_TARGET_PASSWord"+str);
String SQL_SOURCE_TABLENAME = props.getProperty("SQL_SOURCE_TABLENAME"+str);
String SQL_TARGET_TABLENAME = props.getProperty("SQL_TARGET_TABLENAME"+str);
String SQL_TABLE_FIELD = props.getProperty("SQL_TABLE_FIELD"+str);
Connection conn = null;
Connection conn_to = null;
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(DATABAST_SOURCE_URL,DATABAST_SOURCE_USERNAME,DATABAST_SOURCE_PASSWord);
conn_to = DriverManager.getConnection(DATABAST_TARGET_URL,DATABAST_TARGET_USERNAME,DATABAST_TARGET_PASSWord);
if (!conn.isClosed()&&!conn_to.isClosed()){
System.out.println("**源與目的數據庫連接成功**");
conn.setAutoCommit(false);
conn_to.setAutoCommit(false);
}else{
new Exception("源與目的數據庫連接不成功，請檢查！");
}
PreparedStatement pstmt = conn.prepareStatement("SELECT "+SQL_TABLE_FIELD+" FROM "+SQL_SOURCE_TABLENAME);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
//numberOfColumns代表源表列的數量
String[] data = new String[numberOfColumns];
String mark = "";
String temp;
PreparedStatement pstmt2 = conn_to.prepareStatement("delete from "+SQL_TARGET_TABLENAME);
pstmt2.executeUpdate();
pstmt2.close();
while(rs.next()){
mark = "";
for(int i=0;i<numberOfColumns;i++){
if(rs.getString(i+1) != null){
temp = (rs.getString(i+1).trim());
}else{
temp="";
}
data[i] = new String(temp.getBytes("ISO-8859-1"), "GBK");
if (i!=(numberOfColumns-1)){
mark = mark + "?,";
}else{
mark = mark + "?";
}
}
String sql1 = "insert into "+SQL_TARGET_TABLENAME+" ( "+SQL_TABLE_FIELD+" ) values ( "+mark+" )";
PreparedStatement pstmt1 = conn_to.prepareStatement(sql1);
for(int i=0;i<numberOfColumns;i++){
pstmt1.setString(i+1,data[i]);
}
pstmt1.executeUpdate();
pstmt1.close();
}
conn.commit();
conn_to.commit();
System.out.println("**源與目的數據導出成功**");
}
}
/
第二,創建存儲過程,帶參數
create or replace procedure TransMetaData(p_str in varchar2)
as language Java
name 'TransMetaData.donow(Java.lang.String)';
/
注意:
1.如果第一步的Java對象方法donow無參數,即為static donow()
則此處為create or replace procedure TransMetaData
as language Java
name 'TransMetaData.donow()';
/
2.注意兩個存儲過程的不同,我們的參數在Java代碼裡為String,
因此這裡寫Java.lang.String.
3.如果需要有返回值,(Java代碼修改省略,返回double類型),則存儲過程為
create or replace procedure TransMetaData(p_str in varchar2) return number
as language Java
name 'TransMetaData.donow(Java.lang.String) return double';
/
第三,執行存儲過程
無參數:exec TransMetaData;
帶參數:exec TransMetaData(1);
//-------------------------------附上一個網上找的簡單的例子---------------------------------------
第一步:
create or replace Java SOURCE Named JS_Rand
As
public class clsRand{
public static double Rand(){
return Java.lang.Math.random();
}
}
/
第二步.
CREATE OR REPLACE function rand return number
as
language Java Name
'clsRand.Rand() return double';
/
第三步:
run
begin
dbms_output.put_line(rand);
End;
/
