在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
