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