在《用SQLJ開發數據庫》一文中，我描述了SQLJ是什麼；比較它和PL/SQL以及JDBC之間的差別；最後又討論了SQLJ元素。在本文中，我將一步一步的介紹如何使用DeleteClassification存儲過程。
1. 編寫一個SQLJ存儲過程
2. 把它裝載進一個Oracle數據庫
3. 在數據庫中發布Java方法
4. 運行過程
5. 和PL/SQL相對應的過程比較結果
PL/SQL存儲過程 DeleteClassification過程主要使用兩個表工作，ValidTransaction (父表）和Classification (子表)，是一種使用TransactionId外鍵的一到多的關系。 存儲過程的目的就是刪除所有指定的時期/月份的ValidTransaction的Classification紀錄。 ValidTransaction表中的TransactionDate字段選擇這個日期范圍之內的記錄。
在例子中，ValidTransaction包含12個時期的數據，共有一百二十萬行。 在ValidTransaction表中的每一行，Classification表分別有10行與之對應，所以就共有一千二百萬行數據。清除一個指定月份的Classification數據意味著從Classification表中刪除1百萬行數據。
DeleteClassification過程的代碼使用了一些Oracle 8i PL/SQL中的新的特性，象BULK BINDS和BULK COLLECT，用來改善事務的執行性能並增加你在事務中可交付的數據數量，從而減少事務的數目並且降低發生ORA - 01555錯誤的可能性。 BULK COLLECT子句能使整個TransactToReset游標一步就被取出到v_TransactionId PL/SQL表中。 然後游標立即被關閉以避免交叉獲取錯誤，這樣也同樣減少ORA - 01555錯誤的發生。
最後，這個過程計算刪除行數的百分比。 如果結果超過了限度，主關鍵字索引可能就會被損壞而且你還可能要重建它。
代碼段1 演示了DeleteClassification存儲過程的PL/SQL版本。
創建SQLJ存儲過程將要編寫服務器端SQLJ代碼，另外將在一台客戶機上執行編譯過程。這個途徑提供翻譯SQLJ代碼的最大靈活性--選項設置和錯誤處理在客戶機上比在服務器上更方便。
開發一個SQLJ存儲過程由五個步驟組成：
1. 使用Java類的靜態方法創建一個SQLJ源文件，後面將要把這個源文件通過SQL封裝器與PL/SQL環境合並起來。
2. 把這個SQLJ源文件編譯成一個Java類或者一組Java類。把所有的類合並成一個.jar文件。
3. 使用loadjava實用程序把這個.jar文件裝載入Oracle數據庫服務器。 確定裝載過程是成功的，為每個Java類都創建一個Java Class類型的模式對象。
4. 為存儲的Java過程/函數創建一個PL/SQL封裝器。
5. 通過從SQL * Plus或者PL/SQL上調用存儲的Java過程來測試這些過程。
第一步： 創建SQLJ源文件 使用import語句啟動PipelineReset.sqlj文件。 只導入你計劃在你的類中使用的Java類和程序包。
import Java.sql.SQLException;
import Java.sql.Timestamp;
import Java.util.ArrayList;
import Java.util.Iterator;
SQLJ運行時刻程序包可在JServer Java虛擬機( JVM)中找到。 還有，你不必編寫創建到數據庫的連接的代碼了，因為JServer本身帶有到數據庫服務器的隱含的連接。由於你將在客戶機上編譯源代碼，所以必須包括下列三個import語句：
import oracle.jdbc.driver.OracleConnection;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ExecutionContext;
接下來，需要聲明迭代程序類。在迭代程序類聲明中定義了SQLJ為你構造的Java類。你必須把SQLJ迭代程序聲明和連接上下文說明放在你的SQLJ源文件裡的Java類的頂端，緊接著import語句之後。
下列三個迭代程序聲明提供PL/SQL版的三個游標函數：
#sql iterator StartEndDate
(Timestamp startDate, Timestamp endDate);
#sql iterator ValidTransactions
(Long transactionId);
#sql iterator ClassificationCount
(long);
使用下面三個參數定義PipelineReset.Java類的靜態DeleteClassification方法：
public class PipelineReset {
public static void DeleteClassification(
String periodName,
int commitBatchSize,
int percentageDeleted) throws SQLException {
...
}
}
現在我們把代碼放入方法中。 首先你需要實例化一個迭代程序對象決定具體時期的起始和結束時間。 使用periodname參數：
StartEndDate dateIter;
Timestamp endDate = null;
Timestamp startDate = null;
#sql dateIter = { select startDate,
endDate
from Period
where description = :periodName };
if (dateIter.next()) {
startDate = dateIter.startDate();
endDate = dateIter.endDate();
};
dateIter.close();
StartEndDate dateIter;下一步是實例化ValidTransactions類型的迭代程序對象並把你的具體時期內全部的TransactionId都放入ArrayList類型的vtIdList對象中。 在PL/SQL版本中，這個工作是由游標csrTransactToReset完成的。
ValidTransactions vtIter;
#sql vtIter = { select T.TransactionId
from ValidTransaction T
where T.TransactionDate >= :startDate
and T.TransactionDate <= :endDate
and exists (select '1'
from Classification C
where C.TransactionId = T.TransactionId) };
ArrayList vtIdList = new ArrayList();
while (vtIter.next()) {
vtIdList.add((Long) vtIter.TransactionId());
}
vtIter.close();
現在在vtIDList對象中循環，刪除Classification中TransactionId每個值所對應的行。
您正在看的SQLserver教程是:使用SQLJ編寫存儲過程。
ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext();
ClassificationCount countIter;
long vDeletedRows = 0;
long vTotalRows = 0;
int vCount = 0;
if (vtIdList.size() > 0) {
#sql countIter = { select count(*) from Classification };
#sql { fetch : countIter into :vTotalRows };
countIter.close();
Iterator itr = vtIdList.iterator();
while (itr.hasNext()) {
vCount++;
#sql [ec] { delete from Classification
where TransactionId = :((Long) itr.next()) };
vDeletedRows = vDeletedRows + ec.getUpdateCount();
if ((vCount % commitBatchSize) == 0) {
#sql [ec] { commit };
}
}
#sql [ec] { commit };
}
最後，你必須決定你是否要重建索引。 從你的SQLJ代碼中調用相同的RebuildIndex PL/SQL存儲過程：
if ((((float) vDeletedRows / (float) vTotalRows) * 100) >=
percentageDeleted ) {
#sql [ec] {call RebuildIndex('CLASSIFICATION_PK_IND',
'parallel nologging compute statistics')};
}
代碼清單2 顯示了PipelineReset.sqlj文件的完整的清單。
第二步： 翻譯並編譯SQLJ源文件 SQLJ翻譯器通過檢查不正確的SQLJ語法執行對SQLJ源文件的分析。然後調用一個可執行SQLJ語句的語義檢驗程序。如果在sqlj.exe命令中指定user = userName/passWord選項，語義檢驗程序將使用這個信息來連接數據庫並檢驗存在的數據庫表和用於SQLJ源文件的存儲過程。隨後SQLJ翻譯器為SQLJ源文件中的每個連接類生成Java源文件和一個單獨的配置文件。Java虛擬機調用編譯程序( javac)把所有的.Java文件編譯為.class類文件。 最後，JVM調用Oracle SQLJ定制程序把生成的配置文件轉換為Oracle特定的組件。
本文中不可能包含所有的SQLJ實用命令行選項，但是如果你需要更多信息，請參閱相應的Oracle文檔。
我發現把下面的信息放入名為Translator.propertIEs的屬性文件中會很方便：
sqlj.user=scott
sqlj.passWord=tiger
sqlj.driver=oracle.jdbc.driver.OracleDriver
sqlj.url=jdbc:Oracle:oci8:@ORCL
sqlj.warn=verbose
sqlj.linemap
你可以在OracleHome/sqlj/demo目錄中找到一個樣本sqlj.propertIEs文件。下面給出了用來翻譯和編譯SQLJ源文件的命令：
sqlj -props=translator.propertIEs -explain -ser2class -status PipelineReset.sqlj
推薦在SQLJ命令行中包括- explain和- status選項，這樣就能顯示並很容易了解翻譯和編譯步驟。 使用- ser2class標志把生成的配置文件(.ser文件)轉換成.class文件。
在本例中，用六個類文件結束：主要的PipelineReset.class，三個迭代程序類( StartEndDate.class，ValidTransactions.class和ClassificationCount.class)以及配置類( PipelineReset_SJProfile.class和PipelineReset_SJProfileKeys.class)。使用下列命令把它們封裝到一個PipelineReset.jar Java壓縮文件中:
jar cvf0 PipelineReset.jar *.class
現在需要把生成的類載入數據庫中。
第三步：把Java文件裝載入數據庫服務器
Oracle提供了一個默認的使用Oracle JDBC OCI8驅動程序的loadJava實用程序來完成連接。下面就是用一個ORCL數據庫和一個用戶scott/tiger做為參數來使用這個實用程序的語法：
loadJava -user scott/tiger@ORCL -resolve PipelineReset.jar
還可以指定Oracle JDBC瘦驅動程序：
loadJava -thin -user scott/tiger@server:1521:ORCL -resolve PipelineReset.jar
使用- resolve標志直接地分析類的引用而不是等待每個類被第一次調用。
在User_Objects Oracle詞典中運行查詢，使loadJava把所有的類都成功加載。
select object_name,
status
from User_Objects
where object_type = 'Java CLASS'
order by object_name;
下面是上面查詢的輸出結果：
OBJECT_NAME STATUS
------------------------------ -------
ClassificationCount VALID
PipelineReset VALID
PipelineReset_SJProfile0 VALID
PipelineReset_SJProfileKeys VALID
StartEndDate VALID
ValidTransactions VALID
6 rows selected.
正如所見，你所有的Java類都被成功地載入數據庫中了。
第四步：為Java存儲過程創建PL/SQL封裝程序
即使Java代碼已經在數據庫中了，但是它還是不能真正的可用。 Oracle數據庫不能直接調用Java類方法。 它通過PL/SQL間接調用它們並把它們傳到JServer Java虛擬機中來運行。這就是為什麼需要為PipelineReset類的DeleteClassification靜態方法創建一個PL/SQL封裝程序（也就是在數據庫服務器中發布Java方法）。
下面的腳本將創建PipelineResetFull程序包的一個JDeleteClassification過程。 這個過程是PipelineReset Java類的DeleteClassification靜態方法的PL/SQL封裝程序。
create or replace package PipelineResetFull as
procedure JDeleteClassification
(periodName in varchar2,
commitBatchSize
您正在看的SQLserver教程是:使用SQLJ編寫存儲過程。 in number,
percentDeleted in number);
end PipelineResetFull;
/
create or replace package body PipelineResetFull as
procedure JDeleteClassification
(periodName in varchar2,
commitBatchSize in number,
percentDeleted in number)
as
language Java
name 'PipelineReset.DeleteClassification(Java.lang.String,
int,
int)';
end PipelineResetFull;
/
例如：下列的語法是錯誤的：
...
name 'PipelineReset.DeleteClassification(String,
int,
int)';
...
它能夠編譯，但是將拋出運行時間異常：
ERROR at line 1:
ORA-29531: no method DeleteClassification in class PipelineReset
這是怎麼發生的呢？DeleteClassification不就在PipelineReset類中嗎？ 事實上，它是這個類中的唯一的方法!
Oracle不能匹配PL/SQL封裝程序中的DeleteClassification方法與一個實際類的方法。 在這種情況下，你必須明確地指定一個完全符合規格的類的路徑，除非它是一個基類，就像在我們例子中的int Java類型。
第五步：測試Java存儲過程 在數據庫中發布Java代碼之後，我們就會發現在PL/SQL和Java過程之間就沒有用法差異了。可以使用下列代碼從SQL * Plus中調用並測試PL/SQL和Java代碼：
begin
DeleteClassification('January 1999', 10000, 10);
End;
/
begin
PipelineResetFull.JDeleteClassification('January 1999', 10000, 10);
end;
/
現在我們甚至不能分辯出哪個是Java過程，因為它已經完全和PL/SQL環境整合起來了。
SQLJ：一個有價值的數據庫開發解決方案 出於本文的教學目的，我有意從一個簡單的SQLJ程序開始談起，而沒有直接進入更加復雜的內容。 SQLJ的改善執行性能的特點，比如行預取指令和批處理能力，允許SQLJ開發者從多個塊中的數據庫檢索數據行，並把SQL語句成批地發送回數據庫中。
我認為SQLJ是一個非常有價值的數據庫開發解決方案，將不斷的發展和成熟。 Oracle 9i最近在SQLJ中引入動態SQL查詢語言，這樣使“SQLJ是嵌入Java中的靜態SQL查詢語言”這句話稱為了歷史。那意味著SQLJ不再需要使用龐大的JDBC代碼來處理動態的SQL，因為它可以處理並使用靜態的和動態的SQL，而且可以調用象PL/SQL這樣的數據庫專用程序。
代碼段1
create or replace procedure DeleteClassification
(pPeriodName in varchar2: = null,
pCommitBatchSize in integer := 10000,
pPercentDeleted in integer:= 10)
as
vPeriodSeq pls_integer;
vPeriodStart date;
vPeriodEnd date;
vBeginIndex pls_integer := 0;
vEndIndex pls_integer := 0;
vTransactionIdCount integer := 0;
vDeletedRows integer := 0;
vTotalRows integer := 0;
vPercentageDeleted pls_integer := 0;
cursor csrPeriodData (pPeriodDescription Period.Description%type) is
select PeriodSeq,
StartDate,
EndDate
from Period
where Description = pPeriodDescription;
type tTransactionId is
table of ValidTransaction.TransactionId%type
index by binary_integer;
vTransactionId tTransactionId;
cursor csrTransactToReset (pStartDate IN date, pEndDate IN date) IS
select T.TransactionId
from ValidTransaction T
where T.TransactionDate >= pStartDate
and T.TransactionDate <= pEndDate
and exists
(select '1'
from Classification C
where C.TransactionId = T.TransactionId);
cursor csrClassificationCount is
>select count(*)
from Classification;
begin
if pPeriodName is null
then
RAISE_APPLICATION_ERROR(-20301,
'You must supply a valid period.');
end if;
open csrPe
riodData(pPeriodName);
fetch csrPeriodData into vPeriodSeq, vPeriodStart, vPeriodEnd;
if csrPeriodData%notfound
then
close csrPeriodData;
RAISE_APPLICATION_ERROR(-20302,
'You must supply a valid period. ' ||
'Please use the description fIEld of the period.');
end if;
close csrPeriodData;
open csrTransactToReset (vPeriodStart, vPeriodEnd);
fetch csrTransactToReset bulk collect into vTransactionId;
close csrTransactToReset;
vTransactionIdCount := vTransactionId.count;
if vTransactionIdCount > 0
then
open csrClassificationCount;
fetch csrClassificationCount into vTotalRows;
close csrClassificationCount;
while vEndIndex < vTrans
您正在看的SQLserver教程是:使用SQLJ編寫存儲過程。actionIdCount
loop
vBeginIndex := vEndIndex + 1;
if vEndIndex + pCommitBatchSize < vTransactionIdCount
then
vEndIndex := vEndIndex + pCommitBatchSize;
else
vEndIndex := vTransactionIdCount;
end if;
forall nIndex in vBeginIndex..vEndIndex
delete from Classification
where TransactionId = vTransactionId(nIndex);
vDeletedRows := vDeletedRows + sql%rowcount;
commit;
end loop;
vPercentageDeleted := round((vDeletedRows / vTotalRows) * 100);
if vPercentageDeleted >= p_percentDeleted
then
RebuildIndex('CLASSIFICATION_PK_IND',
'parallel nologging compute statistics');
end if;
end if;
end DeleteClassification;
代碼段2：
import Java.sql.SQLException;
import Java.sql.Timestamp;
import Java.util.ArrayList;
import Java.util.Iterator;
import oracle.jdbc.driver.OracleConnection;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ExecutionContext;
#sql iterator StartEndDate (Timestamp startDate, Timestamp endDate) ;
#sql iterator ValidTransactions (Long transactionId) ;
#sql iterator ClassificationCount (long) ;
public class PipelineReset
{
public static void DeleteClassification(
String periodName,
int commitBatchSize,
int percentageDeleted) throws SQLException {
Start
EndDate dateIter;
ValidTransactions vtIter;
ClassificationCount countIter;
Timestamp endDate = null;
Timestamp startDate = null;
int vCount = 0;
long vDeletedRows = 0;
long vTotalRows = 0;
#sql dateIter = { select startDate,
endDate
from Period
where description = :periodName };
long startTime = System.currentTimeMillis();
if (dateIter.next()) {
startDate = dateIter.startDate();
endDate = dateIter.endDate();
};
dateIter.close();
#sql vtIter = { select T.TransactionId
from ValidTransaction T
where T.TransactionDate >= :startDate
and T.TransactionDate <= :endDate
and exists (select '1'
from Classification C
where C.TransactionId = T.TransactionId) };
fetched from vtIter iterator */
ArrayList vtIdList = new ArrayList();
while (vtIter.next()) {
vtIdList.add((Long) vtIter.TransactionId());
}
vtIter.close();
ExecutionContext ec =
DefaultContext.getDefaultContext().getExecutionContext();
if (vtIdList.size() > 0) {
#sql countIter = { select count(*) from Classification };
#sql { fetch : countIter into :vTotalRows };
countIter.close();
Iterator itr = vtIdList.iterator();
while (itr.hasNext()) {
vCount++;
#sql [ec] { delete from Classification
where TransactionId = :((Long) itr.next()) };
vDeletedRows = vDeletedRows + ec.getUpdateCount();
if ((vCount % commitBatchSize) == 0) {
#sql [ec] { commit };
}
}
#sql [ec] { commit };
}
if ((((float) vDeletedRows / (float) vTotalRows) * 100) >=
percentageDeleted ) {
#sql [ec] { call RebuildIndex('CLASSIFICATION_PK_IND',
'parallel nologging compute statistics') };
}
}
long endTime = System.currentTimeMillis();
System.out.println("Total time : " +
((endTime - startTime) / 1000) + " sec.");
}
}
