如何在Oracle中使用Java儲存過程(詳解)(轉)

Java儲存過程今後在各大資料庫廠商中越來越流行，功能也越來越強大。這裡以Oracle為例，介紹一下java儲存過程的具體用法。

一、如何建立java儲存過程？

通常有三種方法來建立java儲存過程。

1. 使用oracle的sql語句來建立：

e.g. 使用create or replace and compile java source named "<name>" as

後邊跟上java源程式。要求類的方法必須是public static的，才能用於儲存過程。

1. SQL> create or replace and compile java source named "javademo1"
2. 2 as
3. 3 import java.sql.*;
4. 4 public class JavaDemo1
5. 5 {
6. 6 public static void main(String[] argv)
7. 7 {
8. 8 System.out.println("hello, java demo1");
9. 9 }
10. 10 }
11. 11 /
13. Java 已建立。
15. SQL> show errors java source "javademo1"
16. 沒有錯誤。
18. SQL> create or replace procedure javademo1
19. 2 as
20. 3 language java name 'JavaDemo1.main(java.lang.String[])';
21. 4 /
23. 過程已建立。
25. SQL> set serveroutput on
26. SQL> call javademo1();
28. 呼叫完成。
30. SQL> call dbms_java.set_output(5000);
32. 呼叫完成。
34. SQL> call javademo1();
35. hello, java demo1
37. 呼叫完成。
39. SQL> call javademo1();
40. hello, java demo1
41. 呼叫完成。

2. 使用外部class檔案來裝載建立

e.g. 這裡既然用到了外部檔案，必然要將class檔案放到oracle Server的某一目錄下邊。

1. public class OracleJavaProc
2. {
3. public static void main(String[] argv)
4. {
5. System.out.println("It's a Java Oracle procedure.");
6. }
7. }
10. SQL> grant create any directory to scott;
12. 授權成功。
14. SQL> conn scott/tiger@iihero.oracledb
15. 已連線。
16. SQL> create or replace directory test_dir as 'd:/oracle';
18. 目錄已建立。
20. SQL> create or replace java class using bfile(test_dir, 'OracleJavaProc.CLASS')
21. 2 /
23. Java 已建立。
25. SQL> create or replace procedure testjavaproc as language java name 'OracleJavaProc.main(java.lang.String[])';
26. 2 /
28. 過程已建立。
30. SQL> call testjavaproc();
32. 呼叫完成。
34. SQL> execute testjavaproc;
36. PL/SQL 過程已成功完成。
38. SQL> set serveroutput on size 5000
39. SQL> call dbms_java.set_output(5000);
41. 呼叫完成。
43. SQL> execute testjavaproc;
44. It's a Java Oracle procedure.

3. 我推薦的一種方法，直接使用loadjava命令遠端裝載並建立。
先建立一個類, e.g.

1. import java.sql.*;
2. import oracle.jdbc.*;
4. public class OracleJavaProc {
6. //Add a salgrade to the database.
7. public static void addSalGrade(int grade, int losal, int hisal) {
9. System.out.println("Creating new salgrade for EMPLOYEE...");
11. try {
12. Connection conn =
13. DriverManager.getConnection("jdbc:default:connection:");
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 Or
2. acleJavaProc.java
3. arguments: '-u' 'scott/tiger@iihero.oracledb '-v' '-resolve' 'OracleJavaProc.java'
4. creating : source OracleJavaProc
5. loading : source OracleJavaProc
6. resolving: source OracleJavaProc

查詢一下狀態：

1. 連線到:
2. Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
3. With the Partitioning, OLAP and Oracle Data Mining options
4. JServer Release 9.2.0.1.0 - Production
6. SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_type LIKE 'JAVA%';
8. OBJECT_NAME
9. --------------------------------------------------------------------------------
11. OBJECT_TYPE STATUS
12. ------------------------------------ --------------
13. OracleJavaProc
14. JAVA CLASS VALID
16. OracleJavaProc
17. JAVA SOURCE VALID

測試一下儲存過程：

1. SQL> create or replace procedure add_salgrade(id number, losal number, hisal num
2. ber) as language java name 'OracleJavaProc.addSalGrade(int, int, int)';
3. 2 /
5. 過程已建立。
7. SQL> set serveroutput on size 2000
8. SQL> call dbms_java.set_output(2000);
10. 呼叫完成。
12. SQL> execute add_salgrade(6, 10000, 15000);
13. Creating new salgrade for EMPLOYEE...
15. PL/SQL 過程已成功完成。
17. SQL> select * from salgrade where grade=6;
19. GRADE LOSAL HISAL
20. ---------- ---------- ----------
21. 6 10000 15000

二、如何更新你已經編寫的java儲存過程？

假如要往類OracleJavaProc裡新增一個儲存過程方法，如何開發？
正確的步驟應該是先dropjava, 改程式，再loadjava。

e.g.修改OracleJavaProc類內容如下：

1. import java.sql.*;
2. import oracle.jdbc.*;
4. public class OracleJavaProc {
6. // Add a salgrade to the database.
7. public static void addSalGrade(int grade, int losal, int hisal) {
9. System.out.println("Creating new salgrade for EMPLOYEE...");
11. try {
12. Connection conn =
13. DriverManager.getConnection("jdbc:default:connection:");
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. }
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. }
56. }

如何更新呢？

1. D:eclipse3.1workspacedbtest>dropjava -u scott -v OracleJavaProc
3. D:/tiger@iihero.oracledbeclipse3.1workspacedbtest>loadjava -u scott -v -resolve Or
4. acleJavaProc/tiger@iihero.oracledb.java
5. arguments: '-u' 'scott/tiger@iihero.oracledb' '-v' '-resolve' 'OracleJavaProc.java'
6. creating : source OracleJavaProc
7. loading : source OracleJavaProc
8. resolving: source OracleJavaProc

後邊的應用示例：

1. SQL> create or replace function query_hisal(grade number) return number as langu
2. age java name 'OracleJavaProc.getHiSal(int) return int';
3. 2 /
5. 函式已建立。
7. SQL> set serveroutput on size 2000
8. SQL> call dbms_java.set_output(2000);
10. 呼叫完成。
11. SQL> select query_hisal(5) from dual;
13. QUERY_HISAL(5)
14. --------------
15. 9999