Oracle Class7. 异常和游标管理
------------------------2013-5-18------------------------
异常
raise_application_error：错误编号必须介于-20000和-20999之间
错误消息的长度可长达2048个字节。
游标类型
静态游标
隐式游标 sql%found sql%rowcount
显式游标 cur_xxx%found
ref游标
游标属性
%notfound
%found
%rowcount
%isopen
控制显示游标
open
fetch
close
循环游标 显式游标
隐式打开游标
自动从活动集获取行
在处理完所有行时关闭游标
优点
简化代码的编写
游标变量的类型：强游标，弱游标。
游标管理 限制
不能在程序包中声明游标变量
远程子程序不能接受游标变量的值
不能使用比较操作符对游标变量进行相等或不相等测试
不能将空值赋予游标变量
表不能存储游标变量的值
-- 包
create or replace package pkg_Test
is
type type_title_rec is record -- 可以理解为java里面的对象，可以作为参数来传递。--
(
Title c6.a%type,
Price c6.b%type
);
gc_Name_char constant varchar2(12) := 'AZhu';
gv_Age_num number := 18;
function FunGetTitleByTitleID (p_TitleID in c6.a%type) return type_title_rec;
procedure ProGetTitleByTitleID(p_TitleID in c6.a%type,lvv out type_title_rec);
end pkg_Test;
-- 包主体
create or replace package body pkg_Test
is
function FunGetTitleByTitleID -- 函数实现，没有create，如果是过程的话，procedure。
(p_TitleID in c6.a%type)
return type_title_rec
as
lv_title_rec type_title_rec;
begin
select a, b into lv_title_rec
from c6
where a = p_TitleID;
return (lv_title_rec);
end FunGetTitleByTitleID;
procedure ProGetTitleByTitleID
(p_TitleID in c6.a%type,
lvv out type_title_rec
)
as
begin
select a, b into lvv
from c6
where a = p_TitleID;
end ProGetTitleByTitleID;
end pkg_Test;
set serveroutput on; --打开输出，每次重起SQLPlus Worksheet需要重新打开输出。
-- 包函数调用
declare
lv_title_rec pkg_Test.type_title_rec;
begin
lv_title_rec := pkg_Test.FunGetTitleByTitleID('2'); -- 包.函数名
dbms_output.put_line(lv_title_rec.title);
dbms_output.put_line(lv_title_rec.price);
end;
-- 包过程调用
declare
lv_title_rec pkg_Test.type_title_rec;
begin
pkg_Test.ProGetTitleByTitleID('3',lv_title_rec); -- 包.过程名
dbms_output.put_line(lv_title_rec.title);
dbms_output.put_line(lv_title_rec.price);
end;
-- 包中变量和常量的调用及修改值
begin
dbms_output.put_line(pkg_Test.gv_Age_num); -- 包.变量名
dbms_output.put_line(pkg_Test.gc_Name_char); -- 包.常量名
pkg_Test.gv_Age_num := 28;
dbms_output.put_line(pkg_Test.gv_Age_num); -- 包.变量名
end;
where current of c; -- 表示操作的是当前游标，可以做修改或删除。
create or replace function FunGetTitleByTitleID2 -- 函数实现，没有create，如果是过程的话，procedure。
(p_TitleID in c6.a%type)
return type_title_rec
as
lv_title_rec type_title_rec;
begin
select a, b into lv_title_rec
from c6
where a = p_TitleID;
return (lv_title_rec);
end;
create type add_type3 as object(
street varchar2(10), --街道名
city varchar2(10), --城市名
state char(2), --州代码
zip c6.b%type --邮编 --c6.b%type这样写会报错。
);
create type type_title_rec as object -- is record会报错。
(
Title number,
Price varchar2(10)
);
create or replace function FunGetTitleByTitleID2
(p_TitleID in c6.a%type)
return c6.a%type
--return type_title_rec; -- 返回类型在前，创建类型在后？会报错？
-- 在包声明中都定义好了，在包体中可以直接使用了。--
as
type type_title_rec is record -- is record会报错。
(
Title c6.a%type,
Price c6.b%type
);
lv_title_rec type_title_rec;
begin
select a,b into lv_title_rec -- 查询的值必须一一对应，否则报创建的函数带有编译错误。--
from c6
where a = p_TitleID;
--return (lv_title_rec);
return p_TitleID;
end;
create or replace function FunGetTitleByTitleID
(p_TitleID in titles.title_id%type)
return type_title_rec -- 会报错!! 错误：PLS-00498: 在说明一个类型之前对其进行了非法使用
as
type type_title_rec is record
(
Title titles.title%type,
Price titles.price%type
);
lv_title_rec type_title_rec;
begin
select title, price into lv_title_rec
from titles
where title_id = p_TitleID;
return (lv_title_rec);
end;
####解决方法####
create or replace function FunGetTitleByTitleID
(p_TitleID in titles.title_id%type)
--return type_title_rec -- 会报错!! 错误：PLS-00498: 在说明一个类型之前对其进行了非法使用
return titles%rowtype -- 使用rowtype解决。
as
type type_title_rec is record
(
Title_id titles.title_id%type,
Title titles.title%type,
Price titles.price%type,
Type titles.type%type
);
lv_title_rec type_title_rec;
begin
select title_id, title, price,type into lv_title_rec
from titles
where title_id = p_TitleID;
return (lv_title_rec);
end;
####调用方法####
declare
lv_rr titles%rowtype;
begin
lv_rr := FunGetTitleByTitleID(2);
dbms_output.put_line(lv_rr.Title_id);
dbms_output.put_line(lv_rr.Title);
dbms_output.put_line(lv_rr.Price);
dbms_output.put_line(lv_rr.Type);
end;
--创建异常测试表--
create table exp1(
expid int primary key,
expname varchar2(12) unique,
expdate date
);
set serveroutput on; --打开控制台输出--
declare
lv_errnum_num int;
begin
insert into exp1 values(1,'Exp1','01-1月-05');
exception
when others then
dbms_output.put_line('错误编号：' || sqlcode);
dbms_output.put_line('错误描述：' || sqlerrm);
end;
错误编号：-1
错误描述：ORA-00001: 违反唯一约束条件 (NEWLIFEYHJ.SYS_C002167)
insert into exp1 values(1,'Exp1','01-0月-05'); -- 写成0月
错误编号：-1843
错误描述：ORA-01843: 无效的月份
insert into exp1 values('aaa','exp1','01-2月-05'); --'aaa'
错误编号：-1722
错误描述：ORA-01722: 无效数字
declare
lv_errnum_num int;
begin
insert into exp1 values('aaa','exp1','01-2月-05');
exception
when others then
dbms_output.put_line('错误编号：' || sqlcode);
dbms_output.put_line('错误描述：' || sqlerrm);
--if sqlcode = -1 then
--dbms_output.put_line('名字不能重复!!');
--end if;
case sqlcode
when -1 then
dbms_output.put_line('名字不能重复!!');
when -1722 then
dbms_output.put_line('名字必须是数字!!');
end case;
end;
declare
lv_errnum_num int;
MyFirstException exception;
pragma exception_init(MyFirstException,-0001); --绑定异常code
begin
insert into exp1 values(1,'exp1','01-2月-05');
exception
--when dup_val_on_index then
when MyFirstException then
dbms_output.put_line('名字不能重复!!');
end;
declare
DUP_VAL_ON_INDEX2 exception;
pragma exception_init(DUP_VAL_ON_INDEX2,-0001); --系统自己绑定的不需要自己去绑定了!!
begin
insert into exp1 values(1,'exp1','01-1月-05');
exception
when DUP_VAL_ON_INDEX then --这样一样可以捕获的到异常!! 写成DUP_VAL_ON_INDEX2也可以!!
dbms_output.put_line('DoItYourself!');
end;
-- 自定义异常
declare
lv_TitleCount_num numeric(3) := 0;
lv_NoTitles_exp exception;
lv_MoreTitles_exp exception;
begin
select count(title_id) into lv_TitleCount_num
from titles;
dbms_output.put_line(lv_TitleCount_num); --测试语句，打印出当前值是多少。--
if lv_TitleCount_num > 0 then
Raise lv_MoreTitles_exp;
elsif lv_TitleCount_num <= 0 then
Raise lv_NoTitles_exp;
end if;
exception
when lv_NoTitles_exp then
dbms_output.put_line ('No Titles!');
when lv_MoreTitles_exp then
dbms_output.put_line ('More Titles!');
end;
##raise_application_error应用##
declare
lv_i_num int;
JDI_M exception;
JDI_L exception;
pragma exception_init( JDI_M, -20001);
pragma exception_init( JDI_L, -20998);
begin
lv_i_num := 8;
if lv_i_num > 10 then
raise_application_error( -20001, 'Too Many!'); --错误编号必须介于-20000和-20999之间
else
raise_application_error( -20998, 'Too Little!'); --错误编号必须介于-20000和-20999之间
end if;
exception
when JDI_M then
dbms_output.put_line('TM');
when JDI_L then
dbms_output.put_line('TL');
end;
declare
lv_TitleCount_num numeric(3) := 0;
lv_NoTitles_exp exception;
lv_MoreTitles_exp exception;
pragma exception_init(lv_NoTitles_exp,-20001);
pragma exception_init(lv_MoreTitles_exp,-20002);
begin
select count(title_id) into lv_TitleCount_num
from titles;
dbms_output.put_line(lv_TitleCount_num); --测试语句，打印出当前值是多少。--
if lv_TitleCount_num > 0 then
Raise_Application_error(-20002,'书籍太多！');
elsif lv_TitleCount_num <= 0 then
Raise_Application_error(-20001,'没有书籍！');
end if;
--exception --如果没有注释，那么执行打印信息。否则输出Raise_Application_error的定义信息。--
--when lv_NoTitles_exp then
-- dbms_output.put_line ('No Titles!');
--when lv_MoreTitles_exp then
-- dbms_output.put_line ('More Titles!');
end;
