gbase8s spl异常捕获机制

gbase8s数据库在某些版本开始基本已完全兼容oracle plsql

以下为gbase8s原生语法，仅代表个人观点

一、异常捕获机制

1 位置

ON EXCEPTION 语句是一个声明性语句，不是可执行语句。出于此原因，ON EXCEPTION 必须在任何可执行语句之前并且任何 DEFINE 语句之前。

因为 SPL 例程的主体是语句块，所以 ON EXCEPTION 语句进程在例程的开头出现。

2 on exception 作用域

drop table if exists t1;
drop table if exists t_err;
drop procedure if exists p6;
create table t1(id int primary key);
insert into t1 values(3);

create table t_err(c1 int,c2 int,c3 varchar(64));
create procedure p6() 
define code int;
define isamcode int;
define mesg varchar(64);

on exception set code,isamcode,mesg
insert into t_err values(code,isamcode,mesg);
end exception;
begin

    begin
        begin
    insert into t1 values(1);
    insert into t1 values(2);
    insert into t1 values(3);
        end;
    end;

end;
insert into t1 values(4);
end procedure ;

以上写法将应用于全局

drop table if exists t1;
drop table if exists t_err;
drop procedure if exists p6;
create table t1(id int primary key);
insert into t1 values(3);

create table t_err(c1 int,c2 int,c3 varchar(64));
create procedure p6() 
define code int;
define isamcode int;
define mesg varchar(64);


begin
on exception set code,isamcode,mesg
insert into t_err values(code,isamcode,mesg);
end exception;
    begin
        begin
    insert into t1 values(1);
    insert into t1 values(2);
    insert into t1 values(3);
        end;
    end;

end;
insert into t1 values(3);
end procedure ;

以上写法生效于on exception 所在语句块，包含语句块中的语句块

3 on exception with resume 作用域

drop table if exists t1;
drop table if exists t_err;
drop procedure if exists p6;
create table t1(id int primary key);
insert into t1 values(3);

create table t_err(c1 int,c2 int,c3 varchar(64));
create procedure p6() 
define code int;
define isamcode int;
define mesg varchar(64);


begin
on exception set code,isamcode,mesg
insert into t_err values(code,isamcode,mesg);
end exception with resume;
    begin
        begin
    insert into t1 values(1);
    insert into t1 values(2);
    insert into t1 values(3);
    insert into t1 values(4);
    insert into t1 values(5);
        end;
    end;

end;

end procedure ;

以上代码只结果为 1,2,3 with resume 失效

drop table if exists t1;
drop table if exists t_err;
drop procedure if exists p6;
create table t1(id int primary key);
insert into t1 values(3);

create table t_err(c1 int,c2 int,c3 varchar(64));
create procedure p6() 
define code int;
define isamcode int;
define mesg varchar(64);


begin

    begin
        begin
        on exception set code,isamcode,mesg
        insert into t_err values(code,isamcode,mesg);
        end exception with resume;
    insert into t1 values(1);
    insert into t1 values(2);
    insert into t1 values(3);
    insert into t1 values(4);
    insert into t1 values(5);
        end;
    end;

end;

end procedure ;

以上代码结果为1,2,3,4,5 with resume 生效

drop table if exists t1;
drop table if exists t_err;
drop procedure if exists p6;
create table t1(id int primary key);
insert into t1 values(3);

create table t_err(c1 int,c2 int,c3 varchar(64));
create procedure p6() 
define code int;
define isamcode int;
define mesg varchar(64);
define i int;

begin
for i in (1 to 10)
begin
on exception set code,isamcode,mesg
insert into t_err values(code,isamcode,mesg);
end exception with resume;
    begin
        begin
    insert into t1 values(i);
        end;
    end;
end;
end for;

end;

end procedure ;

若 exception with resume 处于for while..等循环中则此循环中生效，不论是否具有嵌套语句块

可见 on exception with resume 的作用域仅为 on exception 所在 语句块，不包活语句块中的嵌套语句块

二、自定义异常

drop table if exists t1;
drop table if exists t_err;
drop procedure if exists p6;
create table t1(id int primary key);
insert into t1 values(3);

create table t_err(c1 int,c2 int,c3 varchar(64));
create procedure p6() 
define code int;
define isamcode int;
define mesg varchar(64);
begin
on exception set code,isamcode,mesg
insert into t_err values(code,isamcode,mesg);
end exception with resume;
    begin
        begin
    raise exception -746,0,'746 error code';
        end;
    end;
end;
end procedure ;

在 on exception 作用域内可捕获自定义异常，gbase8s自定义异常的错误号为 -746