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