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;
exception when no_data_found then
v_result:=-604;return;--管理者帐号不存在
end;
END IF;
--检查用户是否已经存在
--自定义异常
begin
SELECT COUNT(1) INTO v_count FROM USER_REGISTER WHERE REG_EMAIL = v_reg_email;
if v_count = 1 then
raise MyException;
end if;
exception when MyException then
v_result:=-511;return;--重复
end;
--获取USERID
begin
SELECT IS_ID,IS_USER_ID INTO v_isid,v_userid
FROM (SELECT IS_ID,IS_USER_ID FROM USER_ID_SELECT ORDER BY SYS_GUID())
WHERE ROWNUM = 1;
exception when no_data_found then
v_result:=-516;return;--获取失败
END;
SET TRANSACTION READ WRITE;-----创建事务
BEGIN
--删除已经分配的USERID
DELETE USER_ID_SELECT WHERE IS_ID = v_isid;
--插入注册信息
INSERT INTO USER_REGISTER(REG_USER_ID,REG_EMAIL,REG_USER_PWD,REG_IP,REG_DATE)
VALUES(v_userid,v_reg_email,v_reg_pwd,v_reg_ip,v_reg_date);
EXCEPTION
WHEN OTHERS THEN
begin rollback;v_result:=-512; end;--RAISE_APPLICATION_ERROR(-20512,'数据库内部错误!');
END;
v_user_id := v_userid;
v_result:= 0;
commit;
END;
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;
exception when no_data_found then
v_result:=-604;return;--管理者帐号不存在
end;
END IF;
--检查用户是否已经存在
--自定义异常
begin
SELECT COUNT(1) INTO v_count FROM USER_REGISTER WHERE REG_EMAIL = v_reg_email;
if v_count = 1 then
raise MyException;
end if;
exception when MyException then
v_result:=-511;return;--重复
end;
--获取USERID
begin
SELECT IS_ID,IS_USER_ID INTO v_isid,v_userid
FROM (SELECT IS_ID,IS_USER_ID FROM USER_ID_SELECT ORDER BY SYS_GUID())
WHERE ROWNUM = 1;
exception when no_data_found then
v_result:=-516;return;--获取失败
END;
SET TRANSACTION READ WRITE;-----创建事务
BEGIN
--删除已经分配的USERID
DELETE USER_ID_SELECT WHERE IS_ID = v_isid;
--插入注册信息
INSERT INTO USER_REGISTER(REG_USER_ID,REG_EMAIL,REG_USER_PWD,REG_IP,REG_DATE)
VALUES(v_userid,v_reg_email,v_reg_pwd,v_reg_ip,v_reg_date);
EXCEPTION
WHEN OTHERS THEN
begin rollback;v_result:=-512; end;--RAISE_APPLICATION_ERROR(-20512,'数据库内部错误!');
END;
v_user_id := v_userid;
v_result:= 0;
commit;
END;