使用SQLJ编写存储过程

　2007-11-11 04:42:26　来源：WEB开发网　　　

核心提示： ·一个SQL存储过程·经验总结：ASP与存储过程解析·sql server(WINDOWS平台上强大的数据库平台)编写存储过程小工具·如何在SQL存储过程中处理错误·使用C#创建sql server(WINDOWS平台上强大的数据库平台)的存储过程

(WINDOWS平台上强大的数据库平台)

(和PHP搭配之最佳组合)

　　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 csrPeriodData(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 < vTransactionIdCount
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.Oracle(大型网站数据库平台)Connection;
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 {

StartEndDate 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.");

}

}

上一页 1 2 3 4