如何在Oracle中使用Java存储过程(详解)一、如何缔造java存储过程？通常有三种步骤来缔造java存储过程。

1. 使用oracle的sql语句来缔造：e.g. 使用create or replace and compile java source named "" as后边跟上java源程序。

要求类的步骤必须是public static的，威力用于存储过程。

1.SQL> create or replace and compile java source named "javademo1"2. as3. import java.sql.*;4. public class JavaDemo15. {6. public static void main(String[] argv)7. {8. System.out.println("hello, java demo1");9. }10. }11. /12.13.Java 已14.15.缔造。

16.17.SQL> show errors java source "javademo1"18.没有19.20.舛误。

21.22.SQL> create or replace procedure javademo123. 2 as24.3 language java name JavaDemo1.main(ng.String[]);25. 4 /26.27.过程已28.30.31.SQL> set serveroutput on32.SQL> call javademo1();33.34.调用35.实现。

36.37.SQL> call dbms_java.set_output(5000);38.39.调用40.实现。

41.42.SQL> call javademo1();43.hello, java demo144.45.调用46.实现。

47.48.SQL> call javademo1();49.hello, java demo150.调用51.实现。

2. 使用外部class文件来装载缔造e.g. 这里既然用到了外部文件，必定要将class文件放到oracle Server 的某一目录下边。

1.public class OracleJavaProc2.{3. public static void main(String[] argv)4. {5. System.out.println("Its a Java Oracle procedure.");6. }7.}8.9.10.SQL> grant create any directory to scott;11.12.授权13.顺利。

14.15.SQL> conn scott/tiger@iihero.oracledb17.SQL> create or replace directory test_dir as d:/oracle;18.19.目录已20.21.缔造。

22.23.SQL> create or replace java class using bfile(test_dir, OracleJavaProc.CLASS)24. 2 /25.26.Java 已27.28.缔造。

29.30.SQL> create or replace procedure testjavaproc as language javaname OracleJavaProc.main(ng.String[]);31. 2 /32.33.过程已34.35.缔造。

36.37.SQL> call testjavaproc();38.39.调用40.实现。

41.42.SQL> execute testjavaproc;43.44.PL/SQL 过程已45.顺利46.实现。

47.48.SQL> set serveroutput on size 500049.SQL> call dbms_java.set_output(5000);50.51.调用52.实现。

53.54.SQL> execute testjavaproc;55.Its a Java Oracle procedure.3. 我推举的一种步骤，直接使用loadjava命令远程装载并缔造。

先缔造一个类, e.g.1.import java.sql.*;2.import oracle.jdbc.*;3.4.public class OracleJavaProc {5.6. //Add a salgrade to the database.7. public static void addSalGrade(int grade, int losal, int hisal) {8.9. System.out.println("Creating new salgrade for EMPLOYEE...");10.11. try {12. Connection conn =13. DriverManager.getConnection("jdbc:default:connection:");14.15. String sql =16. "INSERT INTO salgrade " +17. "(GRADE,LOSAL,HISAL) " +18. "VALUES(?,?,?)";19. PreparedStatement pstmt = conn.prepareStatement(sql);20. pstmt.setInt(1,grade);21. pstmt.setInt(2,losal);22. pstmt.setInt(3,hisal);23. pstmt.executeUpdate();24. pstmt.close();25. }26. catch(SQLException e) {27. System.err.println("ERROR! Adding Salgrade: "28. + e.getMessage());29. }30. }31.}使用loadjava命令将其装载到服务器端并编译：1.D:eclipse3.1workspacedbtest>loadjava -u scott/tiger@iihero.oracledb -v -resolve Or2.acleJavaProc.java3.arguments: -u scott/tiger@iihero.oracledb -v -resolve OracleJavaProc.java4.creating : source OracleJavaProc5.loading : source OracleJavaProc6.resolving: source OracleJavaProc查问一下状态：1.衔接到:2.Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production3.With the Partitioning, OLAP and Oracle Data Mining options4.JServer Release 9.2.0.1.0 - Production5.SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_type LIKE JAVA%;6.7.OBJECT_NAME8.--------------------------------------------------------------------------------9.10.OBJECT_TYPE STATUS11.--------------------------------------------------12.OracleJavaProc13.JAVA CLASS VALID14.15.OracleJavaProc16.JAVA SOURCE VALID测试一下存储过程：1.SQL> create or replace procedure add_salgrade(id number, losalnumber, hisal num2.ber) as language java name OracleJavaProc.addSalGrade(int, int,int);3. 2 /4.5.过程已6.7.缔造。

8.9.SQL> set serveroutput on size 200010.SQL> call dbms_java.set_output(2000);11.12.调用13.实现。

14.15.SQL> execute add_salgrade(6, 10000, 15000);16.Creating new salgrade for EMPLOYEE...17.18.PL/SQL 过程已19.顺利20.实现。

21.22.SQL> select * from salgrade where grade=6;23.24. GRADE LOSAL HISAL25.---------- ---------- ----------26. 6 10000 15000二、如何更新你已经编写的java存储过程？如果要往类OracleJavaProc里增加一个存储过程步骤，如何开辟？准确的步骤应该是先dropjava, 改程序，再loadjava 。

e.g. 批改OracleJavaProc类内容如下：1.import java.sql.*;2.import oracle.jdbc.*;3.4.public class OracleJavaProc {5.6. // Add a salgrade to the database.7. public static void addSalGrade(int grade, int losal, int hisal) {8.9. System.out.println("Creating new salgrade for EMPLOYEE...");10.11. try {12. Connection conn =13. DriverManager.getConnection("jdbc:default:connection:");14.15. String sql =16. "INSERT INTO salgrade " +17. "(GRADE,LOSAL,HISAL) " +18. "VALUES(?,?,?)";19. PreparedStatement pstmt = conn.prepareStatement(sql);20. pstmt.setInt(1,grade);21. pstmt.setInt(2,losal);22. pstmt.setInt(3,hisal);23. pstmt.executeUpdate();24. pstmt.close();25. }26. catch(SQLException e) {27. System.err.println("ERROR! Adding Salgrade: "28. + e.getMessage());29. }30. }31.32. public static int getHiSal(int grade)33. {34. try {35. Connection conn =36. DriverManager.getConnection("jdbc:default:connection:");37. String sql = "SELECT hisal FROM salgrade WHERE grade =?";38. PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setInt(1, grade);39. ResultSet rset = pstmt.executeQuery();40. int res = 0;41. if (rset.next())42. {43. res = rset.getInt(1);44. }45. rset.close();46. return res;47. }48. catch (SQLException e)49. {50. System.err.println("ERROR! Querying Salgrade: "51. + e.getMessage());52. return -1;53. }54. }55.56.}如何更新呢？1.D:eclipse3.1workspacedbtest>dropjava -u scott -v OracleJavaProc2.3.D:/tiger@iihero.oracledbeclipse3.1workspacedbtest>loadjava -u scott -v -resolve Or4.acleJavaProc/tiger@iihero.oracledb.java5.arguments: -u scott/tiger@iihero.oracledb -v -resolve OracleJavaProc.java6.creating : source OracleJavaProc7.loading : source OracleJavaProc8.resolving: source OracleJavaProc后边的利用示例：1.SQL> create or replace function query_hisal(grade number) return number as langu2.age java name OracleJavaProc.getHiSal(int) return int;3. 2 /4.5.函数已6.7.缔造。