在PL/SQL 開發中調試預存程序和函數的一般性方法

來源:互聯網
上載者:User
預存程序|函數 在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()函數拋出的違例已經不是開始在程式塊內部捕獲的內部違例,而是由使用者自己定義的。




相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。