在PL/SQL 开发中调试存储过程和函数的一般性方法

在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、stepover、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()函数抛出的违例已经不是开始在程序块内部捕获的内部违例，而是由用户自己定义的。