JAVA与Oracle存储过程.doc

JAVA与Oracle存储过程22009年08月11日 星期二 上午 10:30 在前一篇文章中简单介绍了JAVA程序如何调用Oracle存储过程的基本语法程序结构， 本文将介绍JAVA跟Oracle的另一种关系，即通过JAVA编写Oracle存储过程。 通常情况下，我们都是使用Oracle数据库系统中的ps/sql语句来为oracle编写各种存储过程，不过，在Oracle的第八个版本8之后，为我们提供了编写存储过程的另一种新的选择，那就是使用JAVA来编写Oracle存储过程。从Oracle8.0版本开始，在Oracle数据库系统中自带了java虚拟机jvm，因此使得Oracle内置了对JAVA的全面支持，我们在使用JAVA可以实现的任何功能现在都可以放到Oracle中来实现，当然我们也可以在oracle中来操作其他数据库。JAVA的加入让Oracle展能力得到了升华，我们可以定义一个触发器来在进行任何数据库操作的时候对外界进行其他操作的功能。下面准备举个例子来说明下如何通过JAVA来创建Oracle存储过程，例子不在于有多高级，功能不在于多完善，我想，如果能从一个简单例子，引申出去，去繁衍更多的方法，去实现更多你自己需要的功能，这才是简单例子的作用。二、使用JAVA创建Oracle存储过程先看下如何使用JAVA来编写Oracle存储过程，我们在Oracle的sql plus中编写语句。、启动SQL PLUS、编写JAVA类，定义JAVA类别名，本存储过程为简单地输出传入参数的平方值。查看复制到剪切板打印create or replace and compile java source named PF as/* 通过JAVA类来 创建Oracle存储过程*/package ;public class javaCreatePro public static String test(int num) return num + 的平方为： + num * num;、将JAVA类中的方法test创建为一个Oracle函数方法查看复制到剪切板打印create or replace function PF_FUN(name integer) return varchar2 as language java name .javaCreatePro.test(java.lang.Integer) return java.lang.String;、在控制台直接调用创建的Oracle函数select PF_FUN(10) from dual;、输出结果10的平方为：100以上即是在Oracle中的SQL PLUS中使用JAVA程序来产生Oracle存储过程的演示例子，演示了其基本语法的实现跟简单的功能实现，可以看出在JAVA跟Oracle之间存在很多有趣的用法，因为oracle中有java虚拟机，使得Oracle变得更加有趣更加丰富多彩。用Java开发Oracle存储过程从Oracle 8i开始，Oracle数据库就全面引入了实用的Java虚拟机 Oracle JVM。Oracle和Java之间的这种紧密关系，使得一个非常重要的技术，出现在Oracle开发人员面前：Java存储过程。通过Java存储过程，开发人员在创建数据库应用的时候，就可以充分利用Java提供的各种优势。本文将针对这个越来越受欢迎的技术进行讨论。其目标读者是，初懂Oracle的Java开发人员和略知Java的Oracle PL/SQL开发人员。在强调Java存储过程的好处之后，我将示例说明在应用开发中，如何使用它们。 Java存储过程的好处在很多情况下使用Java存储过程是很有意义的。鉴于Java当前受欢迎的程度，这种情况是完全可能的，即越来越多的开发人员对于Java的熟练程度要好于PL/SQL。Java存储过程的出现，使得Java的开发人员可以用自己喜欢的语言来开发存储过程。对于有经验的PL/SQL开发人员，则可以利用Java语言的各种优点，来扩展数据库应用的功能。同时，Java使得编写独立于数据库的代码成为可能。更有意思的是，它允许你重用你已经存在的代码，从而大幅度地提高开发效率。 正如你将看到的， PL/SQL和Java可以在同一个应用中和谐共处，因此，大可不必非此即彼地选择其中之一。 PL/SQL是一种针对Oracle数据库，经过高度优化的、优秀的过程语言，Java应用在Oracle数据库中运行也具有很好的扩展性。除此之外，通过OracleJVM来执行Java程序，可以充分利用高效的内存回收技术和线程管理方面的能力。 Java存储过程，Step by Step简单地说，Java存储过程就是Java类，以schema对象的形式存储，通过调用规范(call specifications)，OracleSQL和PL/SQL可以对其访问。我们将看到，调用规范就是一些简单的PL/SQL声明，这些声明包装(wrap)了存储在数据库中的Java方法。开发Java存储过程，有四个必须的步骤。下面我们逐一来看看这些步骤。 #1.编写Java类第一步的妙处就是，它基本上和Oracle数据库没有什么关系。你只是简单地用你最喜欢的IDE，比如Oracle的Jdeveloper，去开发一些Java类。如果想被用作存储过程，Java方法必须是public且static的。 在移入Oracle数据库之前，你可以自由地编写、编译甚至对Java代码进行单元测试。事实上，对于通常的应用，这是一个很好的方式，因为这可以充分利用IDE的特性，诸如调试和代码生成。如果你想用Oracle的JVM来编译Java代码，后面将要说到的loadjava这个工具，将为你做这些工作。 下面的代码列出了一个简单的Java类 - EmpManager。它包只含了一个简单的,用于向数据库插入一个emp(员工)记录的方法。 import java.sql.*;import oracle.jdbc.*; public class EmpManager /Add an employee to the database. public static void addEmp(int emp_id, String emp_f_name, String emp_l_name,float emp_salary, int dept_id) System.out.println(Creating new employee.); try Connection conn = DriverManager.getConnection(jdbc:default:connection:); String sql = INSERT INTO emp + (emp_id,emp_f_name,emp_l_name,emp_salary,dept_id) + VALUES(?,?,?,?,?); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1,emp_id); pstmt.setString(2,emp_f_name); pstmt.setString(3,emp_l_name); pstmt.setFloat(4,emp_salary); pstmt.setInt(5,dept_id); pstmt.executeUpdate(); pstmt.close(); catch(SQLException e) System.err.println(ERROR! Adding Employee: + e.getMessage(); 到目前为止一切如常。在这个方法中，数据库链接URL是jdbc:default:connection:。在写要在Oracle数据库中运行的Java代码时，可以利用一个特殊的服务器端的JDBC驱动程序。这个驱动程序使用用户缺省的链接，并提供对数据库的最快访问速度。 #2.加载Java类我们的Java类将要编程一个真正的schema对象，所以必须将它移入数据库。为此，Oracle提供了一个命令行工具 loadjava。这个loadjava工具提供了一个SQL CREATE JAVA等语句必要的接口，并且也可以用于将Java相关的文件加载到数据库中。 由于现在还没有编译EmpManager.java，我们也可以让loadjava在加载的过程中顺便做完编译工作，这个可以通过打开 resolve开关来做到。 $ loadjava u scott/tiger v resolve EmpManager.java出 resolve开关外， -v将指示loadjava输出详细的(verbose)反馈， -u用于指定数据库用户和密码。因为我们让loadjava编译源文件，因此源文件和类文件都将是SCOTT schema的成员。 我们可以通过一个对USER_OBJECTS的查询来验证编译和加载的状态，如果正确，那么状态将是VALID的。 SELECT object_name, object_type, statusFROM user_objects WHERE object_type LIKE JAVA%; object_name object_type status EmpManager JAVA CLASS VALIDEmpManager JAVA SOURCE VALID 反之，如果编译失败，可以在视图USER_ERRORS中看到具体的错误。 如果选择用IDE编译，只需要简单将编译后的class文件加载即可，源文件可以保存在版本控制工具的文件系统中。Loadjava工具接受后缀为.sqlj (sqlj源文件)，.properties，.ser，.jar和.zip等文件。当后缀为.jar和zip的文件时，Oracle将自动解压，并将各成员存储成单个的schema对象。 在继续讨论之前，加载过程中有一个至关重要的组件值得一提：Oracle JVM resolver。典型地，一个JVM用classpath来定位Java类，以便被其他程序使用。在数据库中加载Java时，resolver来完成这样的任务。 可以简单地认为resolver就是Oracle版本的classpath。Oracle将核心Java类存储与PUBLIC schema中。PUBLIC，就像你自己的schema一样，将被自动地引入缺省的resolver中。不过，如果需要引用另外一个schema中的类，那就必须提供你自己的resolver规则(spec)，这个可以通过加上-resolver开关来做到。比如，loadjava u scott/tigertest resolve resolver “(* SCOTT) (* PUBLIC) (* ADMIN)”，指明了，在决定class依赖关系时，SCOTT schema，PUBLIC和ADMIN这3个schema将被搜索。 #3.发布(publish)Java类第3步是发布Java类。Java类必须发布，以便可以直接从SQL或者PL/SQL直接访问。通过为其创建、编译一个调用规则来发布一个Java类。调用规则，通常被称为call spec或者PL/SQL包装器(wrapper)，它将Java方法的参数、返回值类型映射成Oracle SQL的数据类型。下面给出的是addEmp方法的调用规则： CREATE OR REPLACE PROCEDURE add_emp (emp_id NUMBER,emp_f_name VARCHAR2, emp_l_name VARCHAR2, emp_salary NUMBER, dept_id NUMBER) AS LANGUAGE JAVA NAME EmpManager.addEmp(int, java.lang.String, java.lang.String, float, int);/ add_emp过程为Java的 EmpManager.addEmp方法提供了一个SQL接口。Java方法，如果有相关包名，则必须使用包含包名的全名，并且，在开发一个调用对则时，Java对象如String也必须使用全名。 作为一般性的规律，一个Java方法如果没有返回值，则被封装成过程，反之，则被封装成函数。现在，在EmpManager中，我们考虑加入第2个Java方法，用于查询一个指定部门的员工人数： /查询一个指定部门的员工人数public static int getEmpCountByDept(int dept_id) Connection conn = DriverManager.getConnection(jdbc:default:connection:); String sql = SELECT COUNT(1) FROM emp WHERE dept_id = ?; int cnt = 0; /Code here to add ResultSet value to cnt, trap SQLException, etc. return cnt; 它的调用规则指定其返回类型为NUMBER。 CREATE OR REPLACE FUNCTION get_emp_count_by_dept (dept_id NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME EmpManager.getEmpCountByDept(int) return int;/ 缺省地，就像标准的PL/SQL过程一样，这些代码(译注：指上面的调用规则)只要有INVOKER权限就可以运行他们，换言之，当前用户有权执行它们。通过增加关键字AUTHID DEFINER，可以让其他用户以创建者的身份来执行它们。 一旦执行，调用规则，则将其他文件作为SCOTT schema的成员加入数据库。 #4.调用过程我们已经开发、装载并发布了Java类。最后一步就是执行他们。缺省地，Java的输出被写入跟踪文件(trace files)。DBMS_JAVA包，Oracle提供的用于管理服务器端Java的工具，可以将输出重定向到SQL*Plus。 SQL SET SERVEROUTPUT ONSQL CALL dbms_java.set_output(2000); 现在，只要执行，Java的输出会显示在SQL *PLUS中， SQL EXECUTE add_emp(1,Joe, Smith,40000.00,1);Creating new employee. PL/SQL procedure successfully completed. 正如你所看到的，从调用者的角度，调用Java存储过程和调用PL/SQL的存储过程或存储函数，并没有明显的区别。 VARIABLE x NUMBER;CALL get_emp_count_by_dept(1) INTO :x;Getting Number of Employees for Dept. Call completed. PRINT x X- 1 SQLException类有getErrorCode()和getErrorMessage()两个方法用于报错处理。Java存储过程中的任何未被捕获的异常将产生ORA-29532告警，Java调用也会被随之终止。至于如何处理异常，则不同的应用可能采用不同的方式。addEmp方法简单地捕获并显示异常。视图插入一个员工到一个无效的部门，将会收到一个错误消息。 SQL execute add_emp(2,Tom, Jackson, 45000.00,2);Creating new employee.ERROR! Adding Employee : ORA-02291: integrity constraint (OPS$AK4353.FK_DEPT_ID) violated - parent key not found 由于有从PL/SQL来调用Java的需要，自然就会想到也有从Java调用PL/SQL的需要，这个可以通过在Java方法中使用CallableStatement对象，而轻易做到。 CallableStatement cstmt = conn.prepareCall(my_plsql_proc); 因此，可以创建一个无缝的环境，使得PL/SQL轻易调用Java，反之亦然。 一种使用方案理解Java存储过程越好，将对你的开发时间越有帮助。一个通常的方法就是，在需要考虑数据库访问效率时(译注：建立数据库连接很耗时间和资源)，就会使用PL/SQL。现在Java就可以更加轻松地做到这点。开发各个类，遵照必要的调用规则即可。 或许有这样的可能，比如一个数据库应用需要与操作系统文件和目录进行交互。Oracle UTL_FIL这个包提供访问操作系统文件的功能极为有限，而Java则有丰富得多的File IO能力，允许开发人员删除文件，增加目录等等。所以为什么不利用它呢？ 再比如，命令行PL/SQL程序的使用者，也可能将job参数存放在一个配置文件中，你可以编写Java方法来读取这些参数： public static String readFile (String usrFile) String fileStr = new String(); try File file = n