Oracle8i中使用Java語言來開發儲存過程

一. 儲存過程簡介

儲存過程是儲存在資料庫中的一段儲存程式。當建立儲存過程時，系統會對其進行編譯，並將執行程式碼儲存到資料庫中。

1. 設計儲存過程的方針

1) 在定義儲存過程時，要使用其完成單一、相對集中的任務。 
2) 在定義儲存過程時，不要定義已經由其它特徵所提供功能的過程。例如，不要定義強制資料完整性的過程（使用完整性約束）。

2. 儲存過程的優點

1) 安全性

當建立了儲存過程之後，可以將執行該過程的許可權授予其它使用者，從而使得他可以執行特定的資料庫操作，而不能訪問其它模式物件（例如表）。例如，你可以將執行過程（更新表）的許可權授予其它使用者，但不授予它們直接訪問該表的許可權。

2) 效能

① 儲存過程只被傳送到資料庫一次，相對於SQL語句或PL/SQL塊而言，其網路通訊量更小。 
② 當呼叫儲存過程時，資料庫會直接執行該儲存過程，無需進行編譯。相對於SQL語句或PL/SQL塊而言，其執行速度更快。

3) 記憶體分配

儲存過程充分利用了Oracle共享記憶體的能力。在將儲存過程裝載到記憶體中後，多個使用者可以同時呼叫該儲存過程，從而降低了應用對Oracle的實際記憶體需求。

4) 生產力

儲存過程提高了開發生產力。通過將公共集合編寫為儲存過程，避免了冗餘程式碼，從而提高了開發生產力。例如，我們可以編寫用於插入、更新、刪除DEPT表的過程，此後應用可以直接呼叫這些過程，而無需重寫SQL語句。當管理資料的方法發生變化時，只需要修改過程，而不需要對應用進行任何修改。

二. Java儲存過程

在以前的Oracle版本中，開發儲存過程是通過PL/SQL來完成的。而在Oracle8i版本中，我們不僅可以使用PL/SQL開發儲存過程，而且還可以使用Java語言來開發儲存過程。

1. PL/SQL與Java儲存過程比較

與PL/SQL相比，使用Java語言開發儲存過程有以下優點：

1) Java語言具有更強大的運算能力，提供了更多的運算方法。當要完成進行復雜運算的儲存過程時，使用JSP(Java Storage Procedure)將是你最好的選擇。 
2) PL/SQL只能用於Oracle資料庫，而Java語言可以應用於更多的資料庫系統（如Sybase、DB2、Informix等等），所以Java儲存過程將具有更好的相容性、可移植性。

2. JSP(Java Storage Procedure)分類

Java儲存過程包括過程、函式、觸發器以及物件方法四種型別。

3. 呼叫JSP的四種方法

1) CALL語法
2) DML語句
3) PL/SQL塊、子程式、包
4) 由觸發器隱含呼叫

三 Java儲存過程的開發步驟

1. 編寫Java原始碼

當開發Java儲存過程時，首先應該編寫Java原始碼。

注意事項：

① 一般都寫成public static方法作為Java儲存過程 
② 如果從SQL呼叫Java的例項方法。就必須在Oracle中建立Oracle Objects物件，用Java方法實現物件的方法。這個技術使用CREATE TYPE BODY命令。

2. 裝載Java程式碼及類到Oracle8i資料庫中

在編寫了Java原始碼之後，接下來應該將Java程式碼及相應的Java類裝載到Oracle8i資料庫中。

裝載Java程式碼及類到RDBMS有以下兩種方法：

1) 使用loadjava工具，通過該工具可以快速裝載Java原始碼（.java）、Java二進位制程式碼（.class）以及Java打包檔案（.jar）。 
2) 使用CREATE Java、ALTER Java裝載Java程式碼。

其中，前一種方法相對簡單，並且我們推薦你使用這種方法。 如果藉助於像PL/SQL Developer和JDeveloper來開發JSP裝載過程就更簡單，比如在PL/SQL DEveloper的SQL視窗第一行加上 "create or replace and compile java source named Foo as" 點執行工具按鈕，PL/SQL Developer就知道是要把Java程式碼裝載到Oracle中

3. 生成呼叫說明

在裝載了Java類之後，接下來應該生成對public static方法的呼叫說明，最終完成Java儲存過程的開發工作。就是宣告哪些Java類方法要暴露給資料庫呼叫。

完成上述步驟之後，就完成了Java儲存過程的開發工作，然後就可以呼叫並執行該Java儲存過程了。

下面將逐一分紹用Java開發幾種儲存過程的步驟，例項中的程式碼及操作均可在 Oracle 8.1.6 中成功執行。

四：使用Java開發過程

過程用於執行某種操作。需要注意的是，過程所對應的Java方法返回值必須為空（void）。

下面講述完成上述任務的方法及過程： （前三個步驟在開發函式、包、觸發器、物件方法基本一致，所以在以後的介紹中將不再重複）

1. 編寫Java原始碼

程式清單如下（HelloJSP.java）：(這段程式碼在開發其他幾種過程的時候也要用到)

 

1. import java.io.*;   
2. import java.net.*;   
3. import java.sql.*;   
4.   
5. import oracle.jdbc.driver.OracleDriver;   
6.   
7. public class HelloJSP {   
8.   
9.   
10.     // 有返回值，要說明為資料庫的函式,有引數   
11.     public static String javaFunction(String userName) {   
12.         // 取系統、資料記錄資訊等返回   
13.         return "Hello " + userName;   
14.     }   
15.   
16.   
17.     //  有返回值，要說明為資料庫的函式,無引數   
18.     public static String javaFunction1() {   
19.            
20.         try {   
21.             //通過URL連線讀取http://java.sun.com:80上的內容   
22.             // 注意，執行前需要指定使用者對Socket java.sun.com:80的訪問許可權,解析與連線   
23.             // 在sqlplus中以system使用者呼叫dbms_java包的grant_permission過程，命令如下：   
24.             // call dbms_java.grant_permission('SCOTT','java.net.SocketPermission',   
25.             // 'java.sun.com:80', 'resolve,connect, accept');   
26.              URL url = new URL("http://java.sun.com");   
27.             InputStream is = url.openStream();   
28.             BufferedReader br = new BufferedReader(new InputStreamReader(is));   
29.                
30.             //返回讀取到的第一行字串   
31.             return br.readLine();   
32.         } catch (Exception e) {   
33.             return e.getMessage();   
34.         }   
35.     }   
36.   
37.     // 無返回值，要說明為資料庫的過程   
38.     // 執行成功後將會在c:/看到一個檔案a,內容為你所輸入的引數字元呂串   
39.     public static void javaProcedure(String userName) {   
40.         // todo something,如維護資料記錄，讀取系統資訊等   
41.         try {   
42.   
43.             // 取得連線就可以做你要的資料庫操作，用預設連線   
44.             Connection conn = new OracleDriver().defaultConnection();   
45.             conn.close();   
46.         } catch (SQLException e) {   
47.         }   
48.   
49.         try {   
50.             // 注意，執行前需要指定使用者對檔案c:/a至少有寫的許可權   
51.             // 在sqlplus中以system使用者呼叫dbms_java包的grant_permission過程，命令如下：   
52.             // call dbms_java.grant_permission('SCOTT','java.io.FilePermission','c:/a','read,write');   
53.             OutputStream os = new FileOutputStream("c:/a");   
54.             os.write(userName.getBytes());   
55.             os.close();   
56.         } catch (IOException e) {   
57.         }   
58.     }   
59.   
60.   
61.     public static void javaTrigger(int deptNo, String oldName,   
62.                                    String newName) {   
63.         // to do something   
64.         // 我們可以直接呼叫上面的方法，也往產生一個檔案c:/a,看看效果   
65.         try {   
66.             javaProcedure("Dept No: "+deptNo+" Old name: "+oldName+" New name: "+newName);   
67.         }   
68.         catch (Exception e) {   
69.                
70.         }   
71.     }   
72. }  

import java.io.*; import java.net.*; import java.sql.*; import oracle.jdbc.driver.OracleDriver; public class HelloJSP { // 有返回值，要說明為資料庫的函式,有引數 public static String javaFunction(String userName) { // 取系統、資料記錄資訊等返回 return "Hello " + userName; } // 有返回值，要說明為資料庫的函式,無引數 public static String javaFunction1() { try { //通過URL連線讀取http://java.sun.com:80上的內容 // 注意，執行前需要指定使用者對Socket java.sun.com:80的訪問許可權,解析與連線 // 在sqlplus中以system使用者呼叫dbms_java包的grant_permission過程，命令如下： // call dbms_java.grant_permission('SCOTT','java.net.SocketPermission', // 'java.sun.com:80', 'resolve,connect, accept'); URL url = new URL("http://java.sun.com"); InputStream is = url.openStream(); BufferedReader br = new BufferedReader(new InputStreamReader(is)); //返回讀取到的第一行字串 return br.readLine(); } catch (Exception e) { return e.getMessage(); } } // 無返回值，要說明為資料庫的過程 // 執行成功後將會在c:/看到一個檔案a,內容為你所輸入的引數字元呂串 public static void javaProcedure(String userName) { // todo something,如維護資料記錄，讀取系統資訊等 try { // 取得連線就可以做你要的資料庫操作，用預設連線 Connection conn = new OracleDriver().defaultConnection(); conn.close(); } catch (SQLException e) { } try { // 注意，執行前需要指定使用者對檔案c:/a至少有寫的許可權 // 在sqlplus中以system使用者呼叫dbms_java包的grant_permission過程，命令如下： // call dbms_java.grant_permission('SCOTT','java.io.FilePermission','c:/a','read,write'); OutputStream s = new FileOutputStream("c:/a"); os.write(userName.getBytes()); os.close(); } catch (IOException e) { } } public static void javaTrigger(int deptNo, String oldName, String newName) { // to do something // 我們可以直接呼叫上面的方法，也往產生一個檔案c:/a,看看效果 try { javaProcedure("Dept No: "+deptNo+" Old name: "+oldName+" New name: "+newName); } catch (Exception e) { } } }

 

2. 裝載Java程式碼及類到Oracle8i資料庫中

在編寫了Java原始碼之後，就可以將Java物件裝載到Oracle8i資料庫中了。下面是完成這項任務的方法：

你的Java類中用到了哪些包，就應設定好相應的classpath環境變數，　例如：用以下命令

set classpath=%classpath%;%ORA_HOME%/jdbc/lib/classes12.zip;%ORA_HOME%/sqlj/lib/runtime.zip;%ORA_HOME%/sqlj/lib/translator.zip

實際操作loadjava時並沒有你上面那樣設定classpath也行，當前的classpath是CLASSPATH=.;E:\Oracle\Ora81\orb\classes\yoj.jar;E:\Oracle\Ora81\orb\classes\share.zip，其中這兩個包也沒有包含上面程式碼引入的類

裝載命令用：

loadjava -user scott/tiger@orcl -o -v -f -r d:/jsp/HelloJSP.java

引數說明：
-o     使用OCI8 JDBC介面
-v     顯示執行過程
-f     強制裝載
-r     編譯並解析類
最後一個引數就是原始檔路徑

3. 發行Java，生成呼叫說明

在裝載了Java類後，就可以發行該Java類，並生成呼叫其方法的過程說明了（當然首先要登入到資料庫了）

1)

1. CREATE OR REPLACE PROCEDURE JavaProcedure(userName IN VARCHAR2)   
2.        AUTHID CURRENT_USER  
3.        AS LANGUAGE JAVA NAME 'HelloJSP.javaProcedure(java.lang.String)';   
4.    /  

CREATE OR REPLACE PROCEDURE JavaProcedure(userName IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'HelloJSP.javaProcedure(java.lang.String)'; /

 

4. 呼叫JSP

在生成了呼叫Java方法的過程說明之後，我們就可以呼叫JSP了。例如：

call JavaProcedure('Unmi');

成功執行後將會在C盤根目錄下產生一個檔案a,內容為"Unmi".注意必須賦於SCOTT對檔案c:/a的寫許可權，後面會有一個單獨專題來講解OracleJVM許可權。

五. 使用Java開發函式

函式用於返回特定資料。說明使用Java開發函式的方法。與使用Java開發過程不一樣的就在釋出呼叫說明與呼叫略有不同。前兩步與上同

3. 發行Java，生成呼叫說明（先也要登入到資料庫了）(下面分別生有有參和無參的函式說明)

1)

1. CREATE OR REPLACE FUNCTION JavaFunction(userName IN VARCHAR2)    
2.       RETURN VARCHAR2   
3.       AUTHID CURRENT_USER  
4.       AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction(java.lang.String) return java.lang.String';   
5.    /  

CREATE OR REPLACE FUNCTION JavaFunction(userName IN VARCHAR2) RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction(java.lang.String) return java.lang.String'; /

 

和

1. CREATE OR REPLACE FUNCTION JavaFunction1    
2.       RETURN VARCHAR2   
3.       AUTHID CURRENT_USER  
4.       AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction1() return java.lang.String';   
5.    /  

CREATE OR REPLACE FUNCTION JavaFunction1 RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction1() return java.lang.String'; /

 

4. 呼叫JSP

在生成了呼叫Java方法的函式說明之後，就可以呼叫這些函式了

對函式的呼叫方式在最頂層可以有兩種,call和sql中

1) call dbms_output.put_line(JavaFunction('Unmi'))
2) select JavaFunction1() from dual

六. 使用Java開發包

Java類用於封裝Java方法，與此類似，包用於封裝過程和函式等。仍然以上面的程式碼為例。前兩步與上同

3. 發行Java，生成呼叫說明

在裝載了Java類後，就可以發行該Java類，並生成呼叫其方法的包了。 包名為HelloJSP。

1)

1. CREATE OR REPLACE PACKAGE HelloJSP as  
2.         FUNCTION  JavaFunction(userName IN VARCHAR2) return VARCHAR2;   
3.         FUNCTION  JavaFunction1 return VARCHAR2;   
4.         PROCEDURE JavaProcedure(userName IN VARCHAR2);   
5.         PROCEDURE JavaTrigger(deptNo IN NUMBER,oldName IN VARCHAR2,newName IN VARCHAR2);   
6.     END HelloJSP ;   
7.     /  

CREATE OR REPLACE PACKAGE HelloJSP as FUNCTION JavaFunction(userName IN VARCHAR2) return VARCHAR2; FUNCTION JavaFunction1 return VARCHAR2; PROCEDURE JavaProcedure(userName IN VARCHAR2); PROCEDURE JavaTrigger(deptNo IN NUMBER,oldName IN VARCHAR2,newName IN VARCHAR2); END HelloJSP ; /

 

2)

1. CREATE OR REPLACE PACKAGE BODY HelloJSP as  
2.         FUNCTION JavaFunction(userName IN VARCHAR2)    
3.            RETURN VARCHAR2   
4.            AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction(java.lang.String) return java.lang.String';   
5.   
6.         FUNCTION JavaFunction1    
7.            RETURN VARCHAR2   
8.            AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction1() return java.lang.String';   
9.   
10.         PROCEDURE JavaProcedure(userName IN VARCHAR2)   
11.           AS LANGUAGE JAVA NAME 'HelloJSP.javaProcedure(java.lang.String)';   
12.   
13.         PROCEDURE javaTrigger(deptNo IN NUMBER, oldName IN VARCHAR2, newName IN VARCHAR2)   
14.            AS LANGUAGE JAVA NAME 'HelloJSP.javaTrigger(int, java.lang.String, java.lang.String)';   
15.     end HelloJSP ;   
16.     /  

CREATE OR REPLACE PACKAGE BODY HelloJSP as FUNCTION JavaFunction(userName IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction(java.lang.String) return java.lang.String'; FUNCTION JavaFunction1 RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'HelloJSP.javaFunction1() return java.lang.String'; PROCEDURE JavaProcedure(userName IN VARCHAR2) AS LANGUAGE JAVA NAME 'HelloJSP.javaProcedure(java.lang.String)'; PROCEDURE javaTrigger(deptNo IN NUMBER, oldName IN VARCHAR2, newName IN VARCHAR2) AS LANGUAGE JAVA NAME 'HelloJSP.javaTrigger(int, java.lang.String, java.lang.String)'; end HelloJSP ; /

 

建立包的主體時，定義函式或過程體時不能加 "AUTHID CURRENT_USER"

4. 呼叫JSP

在生成了呼叫Java方法的包後，就可以呼叫這些方法所對應的函式和過程了。例如：

1) call dbms_output.put_line(HelloJSP.JavaFunction('Unmi'));
2) select HelloJSP.JavaFunction1() from dual;
3) call HelloJSP.JavaProcedure('Unmi');
4) call HelloJSP.JavaTrigger(10,'Kypfos','Unmi');

七. 使用Java開發觸發器

觸發器是一段儲存程式，當執行特定修改操作時，會觸發它，並執行其中的儲存程式。下面以表dept有記錄變動時執行某個操作為例，說明使用Java開發觸發器的方法。準確點講這裡所謂的使用Java開發觸發器仍然是屬於開發儲過程的範疇，Java方法仍然是宣告為資料庫的儲存過程。

3. 發行Java，生成呼叫說明

在裝載了Java類後，就可以發行該Java類，並生成呼叫其方法的過程說明及觸發器了。下面是完成該項任務的方法：

從下面兩個步驟可以看出，觸發器呼叫Java程式還必須藉助於儲存過程。由真正的資料庫觸發器傳參到Java儲存過程。

1)

1. CREATE OR REPLACE PROCEDURE JavaTrigger(deptNo IN NUMBER, oldName IN VARCHAR2, newName IN VARCHAR2)   
2.        AUTHID CURRENT_USER  
3.        AS LANGUAGE JAVA NAME 'HelloJSP.javaTrigger(int, java.lang.String, java.lang.String)';   
4.    /  

CREATE OR REPLACE PROCEDURE JavaTrigger(deptNo IN NUMBER, oldName IN VARCHAR2, newName IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'HelloJSP.javaTrigger(int, java.lang.String, java.lang.String)'; /
2)

1. CREATE OR REPLACE TRIGGER CallJavaTrigger   
2.       AFTER update OR delete OR insert ON dept   
3.       FOR EACH ROW   
4.       call JavaTrigger(:old.deptno,:old.dname,:new.dname)   
5.    /  

CREATE OR REPLACE TRIGGER CallJavaTrigger AFTER update OR delete OR insert ON dept FOR EACH ROW call JavaTrigger(:old.deptno,:old.dname,:new.dname) /

 

4. 呼叫JSP

在建立了觸發器之後，當修改作者工資時會自動呼叫其中的儲存程式。例如：

update dept set dname=dname||'X' where deptno=20

八.使用Java開發物件方法

物件型別是一種使用者自定義的資料結構，它可以將資料型別、函式以及過程封裝到該資料結構中。物件方法是指物件型別中的函式和過程，

Java物件類必須實現SQLData介面中的getSQLTypeName()、readSQL(SQLInput stream,String typeName)、writeSQL(SQLOut stream)方法

因為在資料庫中建立物件在實際開發中比較少用，所以具體的操作方法這裡略去，以後用到時再細究。

九. 使用JDeveloper開發JSP

用JDeveloper開發Java儲存過程，因為JDeveloper是一個Java IDE，同Oracle又是一家的，能結合的不好嗎。基本過程是：

1) 配置好到Oracle資料庫的連線
2) 編寫好你要嵌入到Oracle中的Java類
3) 工程中New一個Loadjava and Java Store Procedures的deployment Profile
4) 選取要釋出類的方法，JDeveloper會根據你的方法有無引數決定是過程還是函式
5) Deploy to 到你配置的資料庫上

在Depolyment - Log 中會顯示Depoly時的資訊，如生成呼叫說明的語句。

總結：自己按上面的步驟一步步操作，常常會碰到一些問題，而且我都還不知道應該怎麼去確定是什麼問題，而用jDeveloper來開發Java儲存過程，就非常之簡單了。而且我還發現JDeveloper給我們開發Java儲存過程帶來了無比的方便，而且還是一款很棒的Java IDE,支援Struts、JSF、EJB、WebService、TopLink、Swing/AWT、還能畫UML圖，執行速度也很快。 在JDeveloper中還能夠列出已載入到資料庫中的Java Classes,而PL/SQL Developer只能看到已載入的Java Source,所以如果用loadjava載入的是一個class檔案，在PL/SQL Developer中就是不可見了 。