导常处理
1、什么是异常
在PL/SQL中的一个警告或错误的情形都被称为异常。包括:编译时刻错误和运行时刻
错误。
2、异常分类
系统异常
自定义异常
3、异常结构
EXCEPTION
WHEN...THEN
例子1:
declare
test varchar2(10);
begin
select a into test from abc where a='a';
dbms_output.put_line(test);
exception
when no_data_found then
dbms_output.put_line('没有数据');
end;
/
常见系统异常
DUP_VAL_ON_INDEX 向有唯一约束的表中插入重复行
NO_DATA_FOUND 在一个SELECT INTO语句中无返回值
TOO_MANAY_ROWS SELECT INTO 语句返回了多行
VALUE_ERROR 一个算法、转换、截断或大小约束发生错误
ZERO_DIVIDE 发生被零除
例子:自定义异常
declare
tname varchar2(10);
e exception;
begin
select a into tname from abc where b='123';
if tname<>'s' then
raise e;
end if;
dbms_output.put_line(tname);
exception
when e then
dbms_output.put_line('错误,不是需要的数据');
end;
/
复合变量:记录
什么是记录
记录是由几个相关值构成的复合变量,常用于支持SELECT语句的返回值。使用记录可以
将一行数据看成一个单元进行处理,而不必将每一列单独处理。
记录的声明
TYPE type_name IS RECORD(
Variable_name datetype[,
Variable_name datetype[,
...
);
Real_name type_name;
例子:
declare
type myrecord is record(
a varchar2(10),
b varchar2(100));
real_record myrecord;
begin
select a,b into real_record from abc;
dbms_output.put_line(real_record.a||','||real_record.b);
end;
/
例子2:%type 对应表中的字段类型
declare
type myrecord is record(
a abc.a%type,
b varchar2(100));
real_record myrecord;
begin
select a,b into real_record from abc;
dbms_output.put_line(real_record.a||','||real_record.b);
end;
/
例子3:%rowtype 对应表中的所有字段类型
declare
real_record abc%rowtype;
begin
select * into real_record from abc;
dbms_output.put_line(real_record.A||','||real_record.B);
end;
/
游标
1、什么是游标
游标是一个种PL/SQL控制结构;可以对sql语句的处理进行显式控制,便于对表的行数扰
逐条进行处理
2、游标分类
显式 隐标
3、游标的属性
%FOUND
%ISOPEN
%NOTFOUND
%ROWCOUNT
例子1:%found
declare
cursor mycur is
select * from abc;
myrecord abc%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.A||','||myrecord.B);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
例子2:%nofound
declare
cursor cur_para(id varchar2) is
select A from abc where A=id;
t_name abc.A%type;
begin
open cur_para('abc');
loop
fetch cur_para into t_name;
exit when cur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close cur_para;
end;
/
例子3:
declare
cursor cur_para(id varchar2) is
select A from abc where A=id;
begin
dbms_output.put_line('*********结果集为:******');
for cur in cur_para('abc') loop
dbms_output.put_line(cur.A);
end loop;
end;
/
例子4:%isopen
declare
t_name abc.A%type;
cursor cur(id varchar2) is
select A from abc where A=id;
begin
if cur%isopen then
dbms_output.put_line('游标已经被打开!');
else
open cur('abc');
end if;
fetch cur into t_name;
close cur;
dbms_output.put_line(t_name);
end;
/
例子4:%rowcount
declare
t_name abc.A%type;
cursor mycur is
select A from abc;
begin
open mycur;
loop
fetch mycur into t_name;
exit when mycur%notfound or mycur%notfound is null;
dbms_output.put_line('游标mycur的rowcount是:'||mycur%rowcount);
end loop;
close mycur;
end;
/
例子5:update数据
declare
cursor cur is
select A from abc for update;
text varchar2(10);
begin
open cur;
fetch cur into text;
while cur%found loop
update abc set A=A||'_t' where current of cur;
fetch cur into text;
end loop;
close cur;
end;
/
例子6:显示游标数据
begin
for cur in(select A from abc) loop
dbms_output.put_line(cur.A);
end loop;
end;
/