oracle系统预定义异常的使用,oracle系统预定义异常和用户自定义异常

create table stuinfo1

(

stuid int primary key,

stuname varchar(10) not null,

stusex char(2) check(stusex='男' or stusex='女'),

stubir date

);

insert into stuinfo values ('001','张三','男',to_date('1999-1-1','yyyy-mm-dd'));

insert into stuinfo values ('002','里斯','男',to_date('1997-12-1','yyyy-mm-dd'));

insert into stuinfo values ('003','王五','女',to_date('1995-3-4','yyyy-mm-dd'));

insert into stuinfo values ('004','哈哈','男',to_date('2000-7-5','yyyy-mm-dd'));

insert into stuinfo values ('005','哈哈','男',to_date('1993-7-5','yyyy-mm-dd'));

--系统预定义异常

declare

v_name stuinfo.stuname%type;

begin

select stuname into v_name from stuinfo;

--exception

-- when too_many_rows then

-- dbms_output.put_line('查询的结果多余一条，不能赋值');

end;

--用户自定义异常

declare

money_error exception;

money1 int := 1000;

money2 int := &转账金额;

begin

if money1-money2 < 0 then

raise_application_error(-20001,'余额不足');

--raise money_error;

else

dbms_output.put_line('转账成功');

end if;

--exception

-- when money_error then

-- dbms_output.put_line('余额不足');

end;

--非预定义异常

declare

myerror exception;

pragma exception_init(myerror,-1);

begin

insert into stuinfo values ('001','aa','女',to_date('1995-3-4','yyyy-mm-dd'));

exception

when myerror then

dbms_output.put_line('违反主键唯一约束');

end;

insert into stuinfo1 values (1,'sdf','男','');

select * from tb1;

drop table tb1;

--动态sql

begin

execute immediate 'create table tb1(tid int)';

end;

declare

sqlString varchar2(200);

tId int:=2;

begin

sqlString:='insert into tb1 values (:1)';

execute immediate sqlString using tId;

end;

select * from stuinfo;

--隐式游标

begin

DBMS_OUTPUT.PUT_LINE('插入前：'||SQL%ROWCOUNT);

update stuinfo set stusex = '男';

DBMS_OUTPUT.PUT_LINE('插入后：'||SQL%ROWCOUNT);

END;