1.Oracle中的异常处理:
exception
when others then
异常的处理
end
例如:
create or replace procedure sp_query_test(p_id in int,
p_errorcode in out int,
p_errormsg in out varchar2)
is
p_sql varchar(3000);
begin
p_errorcode:=0;
p_errormsg :='ok';
p_sql:='insert into test (id,iname,icode,iaddr) select name,code,addr from test2 where id='||p_id;
EXECUTE IMMEDIATE p_sql;
commit;
exception
when others then
p_errorcode := p_id;
p_errormsg := '插入失败!';
rollback;
end;
end;
/
2.sqlserver的异常处理
sqlserver的错误处理借用于全局变量@@error,当此变量值为非0时,说明存储过程发生了异常
例如:
create or replace procedure sp_query_test(@p_id int,
@p_errorcode out int,
@p_errormsg out varchar(100))
is
@p_sql varchar(3000);
begin
set @p_errorcode=0;
set @p_errormsg ='ok';
set @p_sql='insert into test (id,iname,icode,iaddr) select name,code,addr from test2 where id='+ char(p_id);
begin tran
EXECUTE IMMEDIATE p_sql;
set @P_ERRORCODE=@P_ERRORCODE+@@error
if @P_ERRORCODE <> 0 begin
rollback tran
GOTO ErrorHandler
end
else begin
commit tran
return 0
end
ErrorHandler:
begin
set @P_ERRORCODE = 1000
set @P_ERRORMSG = '数据导入失败!'
end;
3.DB2的异常处理
db2的异常处理相对比较简单,在定义局部变量的时候加上以下代码就可以了
DECLARE exit handler FOR sqlexception
BEGIN
异常处理
end;
注意:这段代码必须添加到定义局部变量的地方,不然会报错的
例如:
create or replace procedure sp_query_test(p_id in int,
p_errorcode in out int,
p_errormsg in out varchar2)
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE p_sql varchar(3000);
DECLARE exit handler FOR sqlexception
BEGIN
set p_errorcode = p_id;
set p_errormsg = '插入失败!';
end;
set p_errorcode=0;
set p_errormsg ='ok';
set p_sql='insert into test (id,iname,icode,iaddr) select name,code,addr from test2 where id='||p_id;
EXECUTE IMMEDIATE p_sql;
commit;
end P1