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

Oracle Class7. 異常和游標管理