如何開發ORACLE預存程序

在我的上一個銀行項目中，我接到編寫ORACLE預存程序的任務，我是程式員，腦袋裡只有一些如何使用CALLABLE介面調用預存程序的經驗，一時不知如何下手，我查閱了一些資料，通過實踐發現編寫ORACLE預存程序是非常不容易的工作，即使上路以後，調試和驗證非常麻煩。簡單地講，Oracle預存程序就是儲存在Oracle資料庫中的一個程式。

　　一. 概述

　　Oracle預存程序開發的要點是：

　　? 使用Notepad文字編輯器，用Oracle PL/SQL程式設計語言寫一個預存程序;

　　? 在Oracle資料庫中建立一個預存程序;

　　? 在Oracle資料庫中使用SQL*Plus工具運行預存程序;

　　? 在Oracle資料庫中修改預存程序;

　　? 通過編譯錯誤調試預存程序;

　　? 刪除預存程序;

　　二.環境配置

　　包括以下內容：

　　? 一個文字編輯器Notepad;

　　? Oracle SQL*Plus工具，提交Oracle SQL和PL/SQL 陳述式到Oracle database。

　　? Oracle 10g express資料庫，它是免費使用的版本;

　　需要的技巧：

　　? SQL基礎知識,包括插入、修改、刪除等

　　? 使用Oracle's SQL*Plus工具的基本技巧;

　　? 使用Oracle's PL/SQL 程式設計語言的基本技巧;

　　三.寫一個預存程序

　　預存程序使用Oracle's PL/SQL 程式語言編寫，讓我們寫一個什麼工作都不做的預存程序，我們可以編譯和運行它而不用擔心會對資料庫產生任何損害。

　　在Notepad, 寫下：

　　CREATE OR REPLACE PROCEDURE skeleton

　　IS

　　BEGIN

　　NULL;

　　END;

　　把檔案存為skeleton.sql.

　　讓我們一行行遍曆這個預存程序:

　　1 CREATE OR REPLACE PROCEDURE skeleton

　　2 IS

　　3 BEGIN

　　4 NULL;

　　5 END;

　　行1:

　　CREATE OR REPLACE PROCEDURE 是一個SQL語句通知Oracle資料庫去建立一個叫做skeleton預存程序, 如果存在就覆蓋它;

　　行2:

　　IS關鍵詞表明後面將跟隨一個PL/SQL體。

　　行3:

　　BEGIN關鍵詞表明PL/SQL體的開始。

　　行4:

　　NULL PL/SQL語句表明什麼事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;

　　行5:

　　END關鍵詞表明PL/SQL體的結束

　　四.建立一個預存程序

　　SQL語句CREATE OR REPLACE PROCEDURE在Oracle資料庫中建立、編譯和儲存一個預存程序。

　　從Window開啟SQL*Plus並且從SQL*Plus登入到你的資料庫;開啟skeleton.sql檔案.

　　在SQL>命令提示字元下輸入以下命令：

　　SQL>@skeleton

　　SQL>/

　　SQL*Plus裝載skeleton.sql檔案的內容到SQL*Plus緩衝區並且執行SQL*Plus語句;SQL*Plus 會通知你預存程序已經被成功地建立。

　　現在你的預存程序被建立，編譯和儲存在你的Oracle資料庫，我們可以運行它。

　　五.運行一個預存程序

　　從SQL*Plus 命令列提示符運行你的預存程序使用EXECUTE命令，如下:

　　SQL> EXECUTE skeleton;

　　SQL*Plus 輸出一下資訊確信預存程序成功執行: PL/SQL procedure successfully completed.

　　你也可以在一個無名PL/SQL塊內運行你的預存程序，在SQL*Plus命令提示字元下，它看起來像：

　　SQL> BEGIN

　　2 SKELETON;

　　3 END;

　　4 /

　　現在我們已經運行了我們的預存程序，我們如何修改它呢?

　　六.修改一個預存程序

　　讓我們寫一個輸出字串“Hello World!”的預存程序，用Notepad開啟你的skeleton.sql 檔案，. 用DBMS_OUTPUT.PUT_LINE 程序呼叫去替換NULL語句，如下所示:

　　CREATE OR REPLACE PROCEDURE skeleton

　　IS

　　BEGIN

　　DBMS_OUTPUT.PUT_LINE('Hello World!');

　　END;

　　儲存到檔案skeleton.sql.

　　從SQL*Plus命令列, 開啟檔案skeleton.sql .

　　SQL> @skeleton

　　SQL>

　　1 CREATE OR REPLACE PROCEDURE skeleton

　　2 IS

　　3 BEGIN

　　4 DBMS_OUTPUT.PUT_LINE('Hello World!');

　　5* END;

　　SQL> /

　　SQL*Plus 通知你預存程序成功建立並輸出提示資訊：Procedure created.

　　SQL>

　　用EXECUTE 命令運行你的預存程序:

　　SQL> EXECUTE skeleton;

　　SQL*Plus顯示預存程序運行成功：PL/SQL procedure successfully completed.

　　我們想要的輸出字串 "Hello World!"沒有出來，在顯示一個DBMS_OUTPUT.PUT_LINE 結果前需要運行一個SET命令，在SQL*Plus 命令列提示符,鍵入：

　　SQL> SET SERVEROUTPUT ON

　　再次執行你的預存程序：

　　SQL> EXECUTE skeleton;

　　現在結果輸出了：Hello World!

　　PL/SQL procedure successfully completed.

　　七.調試一個預存程序

　　當調試一個預存程序時，遵循一樣的步驟，修改SQL檔案，建立預存程序，執行預存程序，根據編譯器反饋的出錯資訊進行修改，這一步是非常繁瑣的，需要依靠經驗。

　　在實際的商用預存程序的開發調試過程中，由於涉及很多表、類型、游標、迴圈、條件等複雜的邏輯，和PL/SQL語句的靈活運用，編譯時間會產生很多錯誤提示資訊，程式員在根據這些錯誤資訊定位，進行修正，再編譯最後得到正確的結構;

　　八.放棄一個預存程序

　　如果在資料庫中你不在需要一個預存程序你可以刪除它，SQL語句 DROP PROCEDURE 完成從資料庫中刪除一個預存程序，DROP PROCEDURE 在SQL中被歸類為資料定義語言 (Data Definition Language)(DDL) 類操作，其他的例子有CREATE, ALTER, RENAME 和TRUNCATE。.

　　在SQL*Plus 命令提示字元下，使用DROP PROCEDURE SQL 陳述式刪除你的叫做skeleton的預存程序：

　　SQL> DROP PROCEDURE skeleton;

　　SQL*Plus assures us the procedure has been removed:

　　Procedure dropped.

　　總結

　　本文詳細討論了如何使用Oracle工具開發Oracle商用預存程序的步驟。最後在預存程序的使用中可能是程式直接調用，也可能被觸發器調用。