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;
|