Oracle异常分为3种:
(1)预定义异常:no_data_found等,是Oracle系统定义的异常.
declare
s_test varchar2
begin
select id into s_test from test; --此时test表无数据
exception
when no_data_found then
raise_application_error(-20001, '没有数据');
end;
(2)自定义异常:自己定义的异常,自己抛,自己捕获.
declare
exp_test exception;
begin
if 1 = 1 then
raise exp_test;
end if;
exception
when exp_test then
raise_application_error(-20002, '自定义异常');
end;
(3)非预定义异常.
--这个例子就不需要自己抛,因为已经把主键冲突的异常给覆盖了,所以只要是主键冲突,那么就是exp_test来代替以前那个了.
declare
exp_test exception;
progam exception_init(exp_test ,-1); --覆盖-1这个异常,-1代表主键冲突
begin
insert into test values('1'); --test表的主键存在1这个值.
exception
when exp_test then
raise_application_error(-20003, '主键冲突');
end;
判断参数的.
create or replace procedure addContent_test
(
s_type in varchar2,
s_count in number,
s_language in varchar2
)
is
exp_type exception;
exp_count exception;
exp_language exception;
begin
if s_type <> 50
and s_type <> 51
and s_type <> 52
and s_type <> 53
and s_type <> 54
and s_type <> 55
and s_type <> 56
and s_type <> 57
and s_type <> 0
and s_type <> 2
and s_type <> 3
and s_type <> 99999999 then
raise exp_type;
end if;
if s_count < 1 or s_count > 100 then
raise exp_count;
end if;
dbms_output.put_line(to_number(instr(s_language, ',',1,2)));
if instr(s_language, ',',1,2) > 0 then
raise exp_language;
end if;
exception
when exp_type then
raise_application_error(-20001,'内容类型有误');
when exp_count then
raise_application_error(-20002, '加入条数过多或过少');
when exp_language then
raise_application_error(-20003, '语言错误');
end;
declare
num_test number(1) default 1;
begin
addContent_test('50','100','en_us');
end;