在PL/SQL 開發中調試存儲過程和函數的一般性方法
摘要: Oracle 在PLSQL中提供的強大特性使得數據庫開發人員可以在數據庫端完成功能足夠復雜的任務, 本文將結合Oracle提供的相關程序包(package)以及一個非常優秀的第三方開發工具來介紹在PLSQL中開發及調試存儲過程的方法，當然也適用于函數。
版權聲明: 本文可以任意轉載，轉載時請務必以超鏈接形式標明文章原始出處和作者信息。
原文出處: http://www.aiview.com/notes/ora_using_proc.htm
作者: 張洋 Alex_doesAThotmail.com
最后更新: 2003-8-2
目錄 準備工作 從一個最簡單的存儲過程開始 調試存儲過程 在存儲過程中寫日志文件 捕獲違例
Oracle 在PLSQL中提供的強大特性使得數據庫開發人員可以在數據庫端完成功能足夠復雜的任務, 本文將結合Oracle提供的相關程序包(package)以及一個非常優秀的第三方開發工具來介紹在PLSQL中開發及調試存儲過程的方法，當然也適用于函數。
本文所采用的軟件版本和環境:
服務器: Oracle 8.1.2 for Solaris 8
PL/SQL Developer 4.5
準備工作
在開始之前, 假設您已經安裝好了Oracle的數據庫服務, 并已經建立數據庫, 設置好監聽程序, 以允許客戶端進行連接; 同時您已經擁有了一臺設置好本地Net服務名的開發客戶機, 并已經安裝好PL/SQL Developer開發工具的以上版本或者更新.
在下面的示例代碼中,我們使用Oracle數據庫默認提供的示例表 scott.dept 和 scott.emp. 建表的語句如下：
create table SCOTT.DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
create table SCOTT.EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
從一個最簡單的存儲過程開始
我們現在需要編寫一個存儲過程, 輸入一個部門的編號, 要求取得屬于這個部門的所有員工信息, 包括員工編號和姓名. 員工的信息通過一個cursor返回給應用程序.
create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR
) as
begin
open curEmp for
select empno,
ename
from scott.emp
where deptno = in_deptNo;
end usp_getEmpByDept;
上面我們定義了兩個參數, 其中第二個參數需要利用cursor返回員工信息, PLSQL中提供了REF CURSOR的數據類型, 可以采用兩種方式進行定義, 一種是強類型,一種是弱類型, 前者在定義時指定cursor返回的數據類型, 后者可以不指定, 由數據庫根據查詢語句進行動態綁定.
在使用前必須首先使用TYPE關鍵字進行定義, 我們把數據類型REF_CURSOR定義在自定義的程序包中: pkg_const
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
end pkg_const;
注意: 這個包需要在創建上面的存儲過程之前被編譯, 因為存儲過程用到了包中定義的數據類型.
調試存儲過程
使用PL/SQL Developer 登錄數據庫, 用戶名scott, 密碼默認為: tiger. 將包和存儲過程分別編譯, 然后在左側瀏覽器的procedure欄目下找到新建的存儲過程, 點擊右鍵, 選擇"Test"/"測試", 在下面添好需要輸入的參數值, 按快捷鍵F8直接運行存儲過程, 執行完成之后, 可以點開返回參數旁邊的按鈕查看結果集.
如果存儲過程內部語句較復雜, 可以按F9進入存儲過程進行跟蹤調試. PL/SQL Developer提供與通用開發工具類似的跟蹤調試功能, 分為step、step over、step out 等多種方式， 對于變量也可進行trace或者手動賦值。
在存儲過程中寫日志文件
以上方法可以在開發階段對編寫和調試存儲過程提供最大限度的方便，但為了在系統測試或者生產環境中確認我們的代碼是否正常工作時，就需要記錄log。
PLSQL提供了一個UTL_FILE包，通過定義UTL_FILE包中的FILE_TYPE類型，可以獲得一個文件句柄，通過此句柄可以實現一般的文件操作功能。但默認的數據庫參數是不允許使用UTL_FILE包的，需要手動進行配置，使用GUI的管理工具或者手工編輯INIT.ORA文件，找到 "utl_file_dir" 參數，如果沒有，則添加一行，修改成如下：
utl_file_dir=@#/usr/tmp@#
或者
utl_file_dir=*
第一種方式限定了在UTL_FILE包中可以存取的目錄，第二種方式則不進行限定。無論哪種方式，都要保證運行數據庫實例的用戶，一般是oracle，擁有此目錄的存取權限，否則在使用包的過程中會報出錯誤信息。
注意等號左右不要留空格，可能會引起解析錯誤，導致設置無效。
下面在上面的存儲過程中加入記錄log的代碼：
create or replace procedure usp_getEmpByDept(
in_deptNo in number,
out_curEmp out pkg_const.REF_CURSOR
) as
fi utl_file.file_type;
begin
if( pkg_const.DEBUG ) then
fi := utl_file.fopen( pkg_const.LOG_PATH, to_char( sysdate, @#yyyymmdd@# ) || @#.log@#, @#a@# );
utl_file.put_line( fi, @# ****** calling usp_getEmpByDept begin at @# || to_char( sysdate, @#hh24:mi:ss mm-dd-yyyy@# ) || @# ******@# );
utl_file.put_line( fi, @# INPUT:@# );
utl_file.put_line( fi, @# in_chID => @# || in_chID );
end if;
open curEmp for
select empno,
ename
from scott.emp
where deptno = in_deptNo;
if( pkg_const.DEBUG ) then
utl_file.put_line( fi, @# RETURN:@# );
utl_file.put_line( fi, @# out_curEmp: unknown@# );
utl_file.put_line( fi, @# ****** usp_getEmpByDept end at @# || to_char( sysdate, @#hh24:mi:ss mm-dd-yyyy@# ) || @# ******@# );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
exception
when others then
if( pkg_const.DEBUG ) then
if( utl_file.is_open( fi )) then
utl_file.put_line( fi, @# ERROR:@# );
utl_file.put_line( fi, @# sqlcode = @# || sqlcode );
utl_file.put_line( fi, @# sqlerrm = @# || sqlerrm );
utl_file.put_line( fi, @# ****** usp_getEmpByDept end at @# || to_char( sysdate, @#hh24:mi:ss mm-dd-yyyy@# ) || @# ******@# );
utl_file.new_line( fi, 1 );
utl_file.fflush( fi );
utl_file.fclose( fi );
end if;
end if;
/* Raise the exception for caller. */
raise_application_error( -20001, sqlcode || @#|@# || sqlerrm );
end usp_getEmpByDept;
在上面的代碼中，我們又引用了兩個新的常量：
DEBUG
LOG_PATH
分別定義了調試開關參數和文件路徑參數，對此，我們需要修改我們前面定義的程序包：
create or replace package pkg_const as
type REF_CURSOR is ref cursor;
DEBUG constant boolean := true;
LOG_PATH constant varchar2(256) := @#/usr/tmp/db@#;
end pkg_const;
在代碼塊的起始處，將輸入參數的名稱與值成對的記入log文件，在代碼塊的正常退出部分，將輸出參數的名稱和數值也成對的記錄下來，如果程序非正常退出，則在exception 的處理部分，把錯誤代碼及錯誤信息寫入log文件。一般使用這些信息就可以較迅速的找出程序運行中出現的大部分錯誤。
注意：如果返回參數的類型是cursor，是無法在存儲過程內部將返回的結果集一條一條寫入log文件的，此時應當結合在調用程序中記錄的log信息，下面具體分析一下上述代碼：
fopen() 函數使用給定的路徑和文件名，新建文件或者打開已有的文件，這取決于最后一個參數, 當使用@#a@#作為參數時，如果給定的文件不存在，則以此文件名新建文件，并以寫@#w@#方式打開，返回一個文件句柄。
上面代碼以天為單位建立日志文件，并且，不同存儲過程之間共享log文件，這種方式的優點是可能通過查看log文件追溯出程序的調用順序和邏輯。實際應用中，應根據不同的需求，具體分析，可以使用更復雜的log文件生成策略。
put_line() 函數用于寫入字符到文件，并在字符串的結尾加入換行符，若不想換行，使用put()函數。
new_line() 函數用于生成指定數目的空行，上面對文件的修改寫在一個緩沖區內，執行fflush() 將立即將buffer中的內容寫入文件，當你希望在文件還未關閉之前就需要讀取已經作出的改變時，調用此函數。
is_open() 函數用于判斷一個文件句柄的狀態，最后用完一定記得把打開的文件關閉，調用fclose() 函數，并且應把這個語句加入exception的處理中，防止過程非正常退出時留下未關閉的文件句柄。
捕獲違例
在PLSQL中，你可以通過兩個內建的函數sqlcode 和sqlerrm 來找出發生了哪類錯誤并且獲得詳細的message信息，在內部違例發生時，sqlcode返回從-1至－20000之間的一個錯誤號，但有一個例外，僅當內部違例no_data_found 發生時，才會返回一個正數 100。當用戶自定義的違例發生時，sqlcode返回＋1，除非用戶使用 pragma EXCEPTION_INIT 將自定義違例綁定一個自定義的錯誤號。當沒有任何違例拋出時，sqlcode返回0。
下面是一個簡單的捕獲違例的例子：
declare
i number(3);
begin
select 100/0 into i from dual;
exception
when zero_divide then
...
end;
在上面的exception 中我們使用others 關鍵字捕獲所有未明確指定的違例，并進行記錄log處理，同時我們必須在做完這些處理之后，把違例再次拋出給調用程序，調用函數：
raise_application_error()，此函數向調用程序返回一個用戶自定義的錯誤號碼和錯誤信息，第一個參數指定一個錯誤號碼，由用戶自行定義，但必須限定在-20000至－20999之間，避免與Oracle內部定義exception的錯誤號碼沖突，第二個參數需要返回一個字符串，這里我們使用它返回我們上面捕獲的錯誤號碼和錯誤描述。
注意：通過raise_application_error()函數拋出的違例已經不是開始在程序塊內部捕獲的內部違例，而是由用戶自己定義的。
|