使用SQLJ编写存储过程

　　在《用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过程来测试这些过程。

·一个SQL存储过程·经验总结：ASP与存储过程解析·SQL Server编写存储过程小工具·如何在SQL存储过程中处理错误·收藏几段SQL Server语句和存储过程·asp 存储过程的使用·sql server2000中怎么对存储过程进行加·MySQL 5.0新特性教程 存储过程·使用C#创建SQL Server的存储过程·ASP中使用存储过程 　　第一步： 创建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, endDatefrom Periodwhere 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.TransactionIdfrom ValidTransaction Twhere T.TransactionDate >= :startDateand T.TransactionDate <= :endDateand exists (select '1'from Classification Cwhere C.TransactionId = T.TransactionId) };ArrayList vtIdList = new ArrayList();while (vtIter.next()) {vtIdList.add((Long) vtIter.TransactionId());}vtIter.close();

　　现在在vtIDList对象中循环，删除Classification中TransactionId每个值所对应的行。

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 Classificationwhere 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文件的完整的清单。

·一个SQL存储过程·经验总结：ASP与存储过程解析·SQL Server编写存储过程小工具·如何在SQL存储过程中处理错误·收藏几段SQL Server语句和存储过程·asp 存储过程的使用·sql server2000中怎么对存储过程进行加·MySQL 5.0新特性教程 存储过程·使用C#创建SQL Server的存储过程·ASP中使用存储过程 　　第二步： 翻译并编译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=scottsqlj.password=tigersqlj.driver=oracle.jdbc.driver.OracleDriversqlj.url=jdbc:oracle:oci8:@ORCLsqlj.warn=verbosesqlj.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,statusfrom User_Objectswhere object_type = 'JAVA CLASS'order by object_name;

　　下面是上面查询的输出结果：

OBJECT_NAME STATUS------------------------------ -------ClassificationCount VALIDPipelineReset VALIDPipelineReset_SJProfile0 VALIDPipelineReset_SJProfileKeys VALIDStartEndDate VALIDValidTransactions VALID6 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 asprocedure JDeleteClassification(periodName in varchar2,commitBatchSize in number,percentDeleted in number);end PipelineResetFull;/create or replace package body PipelineResetFull asprocedure JDeleteClassification(periodName in varchar2,commitBatchSize in number,percentDeleted in number)aslanguage javaname '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代码：

beginDeleteClassification('January 1999', 10000, 10);End;/beginPipelineResetFull.JDeleteClassification('January 1999', 10000, 10);end;/

　　现在我们甚至不能分辩出哪个是Java过程，因为它已经完全和PL/SQL环境整合起来了。

·一个SQL存储过程·经验总结：ASP与存储过程解析·SQL Server编写存储过程小工具·如何在SQL存储过程中处理错误·收藏几段SQL Server语句和存储过程·asp 存储过程的使用·sql server2000中怎么对存储过程进行加·MySQL 5.0新特性教程 存储过程·使用C#创建SQL Server的存储过程·ASP中使用存储过程 　　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)asvPeriodSeq 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) isselect PeriodSeq,StartDate,EndDatefrom Periodwhere Description = pPeriodDescription;type tTransactionId istable of ValidTransaction.TransactionId%typeindex by binary_integer; vTransactionId tTransactionId;cursor csrTransactToReset (pStartDate IN date, pEndDate IN date) ISselect T.TransactionIdfrom ValidTransaction Twhere T.TransactionDate >= pStartDateand T.TransactionDate <= pEndDateand exists(select '1'from Classification Cwhere C.TransactionId = T.TransactionId);cursor csrClassificationCount isselect count(*)from Classification;beginif pPeriodName is nullthenRAISE_APPLICATION_ERROR(-20301,'You must supply a valid period.');end if;open csrPeriodData(pPeriodName);fetch csrPeriodData into vPeriodSeq, vPeriodStart, vPeriodEnd;if csrPeriodData%notfoundthenclose 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 > 0thenopen csrClassificationCount;fetch csrClassificationCount into vTotalRows;close csrClassificationCount;while vEndIndex < vTransactionIdCountloopvBeginIndex := vEndIndex 1;if vEndIndex pCommitBatchSize < vTransactionIdCountthenvEndIndex := vEndIndex pCommitBatchSize;elsevEndIndex := vTransactionIdCount;end if;forall nIndex in vBeginIndex..vEndIndexdelete from Classificationwhere TransactionId = vTransactionId(nIndex);vDeletedRows := vDeletedRows sql%rowcount;commit;end loop;vPercentageDeleted := round((vDeletedRows / vTotalRows) * 100);if vPercentageDeleted >= p_percentDeletedthenRebuildIndex('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 {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,endDatefrom Periodwhere description = :periodName };long startTime = System.currentTimeMillis(); if (dateIter.next()) {startDate = dateIter.startDate();endDate = dateIter.endDate();};dateIter.close();#sql vtIter = { select T.TransactionIdfrom ValidTransaction Twhere T.TransactionDate >= :startDateand T.TransactionDate <= :endDateand exists (select '1'from Classification Cwhere 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 Classificationwhere 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.");}}