越來越糟糕=w=/

在導入JXL之後, 大部份基礎的資料都已經齊備..

首先要先建立一個類似中繼器角色的procedure或是function來做讀取的動作
CREATE OR REPLACE function ~~~~~( ~~~~ ) return varchar2
as language java
name '~~~( java.lang.Integer, java.lang.String, ~~~~~ ) return java.lang.String';
/

Function的宣告跟字串的宣告必需一致 , 也就是Number要對應Integer ... 依此類推

接著建立相對應的Java程式做讀檔的動作
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ~~~ as import
java.io.*;
import jxl.*;
import java.sql.SQLException;

public class ~~~ {
public static String ~~( ~~~ ) {

....

File f = new File(directory, file);
result = "" + f.getAbsolutePath() + " ";
if(f.exists()){
try {
WorkbookSettings st=new WorkbookSettings();
st.setEncoding(Encode); // 這部份是做Encoding, ex. UTF-8, BIG5
Workbook book = Workbook.getWorkbook(f,st);
//get a Sheet object.
Sheet sheet = book.getSheet(x_sheet); // 實際開啟Sheet就靠它
//get 1st-Column,1st-Row content.
Cell cell = sheet.getCell(x, y);
result = cell.getContents();
book.close();
} catch (Exception e) {
// e.printStackTrace();
result = result + " Error Occur ";
}
}else{
result = result + " No file ";
}
return result;
}
public static String ~~~( ~~~ ) throws Exception{
int x_colCount;
int x_rowCount;

...

File f = new File(directory, file);
result = "" + f.getAbsolutePath() + " ";
if(f.exists()){
try {
WorkbookSettings st=new WorkbookSettings();
st.setEncoding(Encode);
Workbook book = Workbook.getWorkbook(f,st);
//get a Sheet object.
Sheet sheet = book.getSheet(x_sheet - 1);
//get 1st-Column,1st-Row content.

if ( colCount == null )
x_colCount = sheet.getColumns() - 1;
else
x_colCount = colCount - 1;
if ( line_end == null )
x_rowCount = sheet.getRows() - 1;
else
x_rowCount = line_end - 1;

result = "";
for ( int k = line_start - 1 ; k <= x_rowCount ; k++ ) {
for ( int i = 0; i <= x_colCount ; i++ ) {
Cell cell = sheet.getCell(i, k);
result = result + cell.getContents();
if ( i < x_colCount )
result = result + "\t";
}
result = result + "\n";
}
book.close();
} catch (ArrayIndexOutOfBoundsException e) {
// e.printStackTrace();
//SQLException
throw new SQLException("NO_DATA_FOUND");

}
}else{
result = result + " No file ";
throw new Exception("NO_DATA_FOUND");
}
return result;
}
}



這樣寫下來還是有一些問題, ex. 不知道該如何拋正常的exception讓SQL能夠正常接著繼續做exception的判定
如果單讀一格效能很差, 因為需不停的做檔案IO... 但如果合併成NULL又怕有資料過大的問題