oracle存储过程中的异常处理(精)

1.问题来源 Oracle中可以用dbms_output.put_line来打印提示信息，但是很容

易缓冲区就溢出了。可以用DBMS_OUTPUT.ENABLE(1000000;来设置缓冲区的

大小。但是有大小，就有可能再溢出（程序写得太烂，错误不断，不好意思）。

于是想把异常信息写到一个表中。 2.建表这个容易 create table wErrorLog

( procedure_name varchar2(50 not null ,err_msg varchar2(255 not null ,sys_err_code varchar2(10 not null ,sys_err_msg varchar2(1000 not null ,create_time date not null ; comment on table wErrorLog is 'log表,用于记录存储过程的错误'; comment on column wErrorLog.procedure_name is '过程名,出错的存储过程或函数'; comment on column wErrorLog.err_msg is '自定义出错信息'; comment on column wErrorLog.sys_err_code is 'Oracle系统的出错代码'; comment on column wErrorLog.sys_err_msg is 'Oracle系

统的出错信息'; comment on column wErrorLog.create_time is '错误发生时间'; 3.存储

过程 CREATE OR REPLACE PROCEDURE prc_err_log ( i_procedure_name

Varchar2 ,i_err_msg Varchar2 --写日志的过程,Albert Song 2005-06-28 --注意本过程

没有进行commit或rollback操作 --用法 --Exception -- WHEN OTHERS -- Then -- rollbak; -- prc_err_log('prc_err_log','写日志表错误'; -- commit; As v_sqlcode

Varchar(10; v_sqlerrm Varchar(1000; Begin v_sqlcode:=Sqlcode; v_sqlerrm:=Sqlerrm; Insert Into wErrorLog Values(i_procedure_name,i_err_msg,v_sqlcode,v_sqlerrm,Sysdate; Exception WHEN OTHERS Then v_sqlcode:=Sqlcode; v_sqlerrm:=Sqlerrm; Insert Into wErrorLog Values('prc_err_log','写日志表错误',v_sqlcode,v_sqlerrm,Sysdate; END; 4.

使用 create or replace procedure prc_test As v_data varchar2(255; Begin Insert Into wErrorLog Values('prc,'错误','test','test',Sysdate; Select err_msg Into v_data from wErrorLog where err_msg='no err msg'; Exception When Others Then Rollback;

prc_err_log('prc_test','测试prc_err_log'; Commit; end ; 5.测试 exec prc_test; select * from wErrorLog; 6.说明为什么不能在prc_err_log中commit？目的是可以用在这样的地方 create or replace procedure prc_test_transaction As v_in Varchar2(255; begin Insert Into testsql Values('11','55'; If 1=1 Then begin Select code Into v_in From testsql Where code='12323'; exception when others then

prc_err_log('prc_test_transaction','testsql表中不存在code为12323的记录'; end; END

IF; ... commit; Exception WHEN OTHERS Then rollback; prc_err_log('prc_err_log','出现了未知的错误'; commit; end ; 这种情况下，如果在第一个prc_err_log处commit 会将已经执行的操作提交了。后记：我的目的只有一个，就是

详细地记录程序的运行过程，最好是能知道哪一行程序出了异常。现在可以在err_msg里记录一些自定义的变量来跟踪程序状态了。刚学Oracle不久，我觉得应该有更好的方法，但是我没有找到，自己也没有创造出来。 dbms_output有个

new_line不知是不是可以防止缓冲区溢出呢？ /*之前一直以为oracle的过程是没有return的,所以症结就在碰到异常退出过程我试过把所有异常都在前面自定义,在前面出异常的地方,raise MyException,但是执行的时候会报错.提示是碰到异常没得到处理. */ CREATE OR REPLACE PROCEDURE USER_CREATE ( v_recom_user varchar2, --推荐人 v_create_user varchar2, --创建者 v_balance_user varchar2, --结算者v_manage_user varchar2, --管理者 v_reg_email varchar2, --注册邮件 v_reg_pwd varchar2, --注册密码 v_reg_ip varchar2, --注册ip v_reg_date date, --注册日期

v_user_id out number, --用户ID v_result out varchar2 --结果返回参数 IS

v_recom_userid number(15; v_create_userid number(15; v_balance_userid number(15; v_manage_userid number(15; v_userid number(15; v_count number(1:=0; v_isid number(15; MyException EXCEPTION; BEGIN v_result:=0; begin --检查创建者SELECT REG_USER_ID INTO v_create_userid FROM USER_REGISTER WHERE REG_EMAIL = v_create_user; exception when no_data_found then v_result:=-

601;return;--创建者帐号不存在 END; --检查推荐者 if v_recom_user is not null then begin SELECT REG_USER_ID INTO v_recom_userid FROM USER_REGISTER WHERE REG_EMAIL = v_recom_user; exception when no_data_found then v_result:=-602;return;--推荐人帐号不存在 end; end if; --检查结算者 if v_balance_user is not null then begin SELECT REG_USER_ID INTO v_balance_userid FROM

USER_REGISTER WHERE REG_EMAIL = v_balance_user; exception when

no_data_found then v_result:=-603;return;--结算者帐号不存在 end; END IF; --检查管理者 if v_manage_user is not null then begin SELECT REG_USER_ID INTO

v_manage_userid FROM USER_REGISTER WHERE REG_EMAIL = v_manage_user;