PL/SQL 导常处理 游标

导常处理
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;
/

 

游标
１、什么是游标
游标是一个种PL/SQL控制结构；可以对sql语句的处理进行显式控制，便于对表的行数扰

逐条进行处理
２、游标分类
显式　　隐标
３、游标的属性
%FOUND
%ISOPEN
%NOTFOUND
%ROWCOUNT

 

例子１：%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;
/

例子２：%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;
/

例子５：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;
/