------------------------2013-5-21------------------------
定義表的類型
type tablename is table of col_def index by binary_integer;
聲明表的類型
Tablename tablename

pl/sql表
臨時使用，像數組一樣的對象。
包含一列和一個主鍵
不能對列和主鍵進行命名
列可以是任何標量數據類型
主鍵必須是binary_integer類型
大小沒有限制

引用：tablename(key_value)
賦值：tablename(key_value) := expression;

記錄 %rowtype來聲明記錄。

定義記錄類型
type typename is record(列定義 ...);
聲明記錄類型
recordtype typename;
引用記錄
recordtype.columnname
賦值
recordtype.columnname := expression;

pl/sql的運行
oracle使用兩個引擎來運行pl/sql塊和sql語句
pl/sql引擎運行過程語句。
sql引擎運行sql語句。

聲明嵌套表
type tablename IS TABLE OF tabletype;

嵌套表與索引表差異
嵌套表????索引表
使用sql來操縱并存儲在數據庫中?不可能
下標范圍：1到2147483647??下標范圍：-2147483647到2147483647
可用is null操作符來驗證??不可驗證
可用extend和trim方法??不可用

聲明可變數組
TYPE typename IS VARRAY(Maximum_size) OF element_type(not null);
-typename是可變數組名
-Maximum_size設置了元素的數目
-element_type不能為boolean,ref游標,表或者另一種可變數組類型。

-----------------------------------------------
exists(n) 第n個元素存在，返回true
count 總數
limit 最大數目
first 第一個元素???（下標值）
last? 最后一個元素???（下標值）
prior(x)? 第x個元素之前的元素???? ?（下標值）
next(x)? 第x個元素之后的元素??（下標值）
extend(x,y) 追加x個第y個元素的副本
trim(x)? 從集合的末尾處截斷x個元素
delete 刪除部分或全部元素
-----------------------------------------------

##forall應用舉例##
-- 采用ForAll對變量進行批量邦定，然后一次發送
declare
? type tt is table of int index by binary_integer;
? lv_tt tt;
? t1 char(5);
? t2 char(5);
? t3 char(5);
? procedure get_time(t out number)
? is
? begin
??? --取得當前時間
???? select to_char(sysdate, 'sssss') into t from dual;
? end;
begin

? for i in 1 .. 100000
? loop
??? lv_tt(i) := i;
? end loop;
?
? get_time(t1);
?
? for i in 1 .. 100000
? loop
??? insert into ttt2 values (lv_tt(i));???
? end loop;
???
? get_time(t2);
?
? forall i in 1 .. 100000
??? insert into ttt2 values (lv_tt(i));???
?
? get_time(t3);
?
? dbms_output.put_line('未批量邦定耗時：');
? dbms_output.put_line(t2 - t1);
? dbms_output.put_line('批量邦定耗時：');
? dbms_output.put_line(t3 - t2);

end;

未批量邦定耗時：
10
批量邦定耗時：
0

##bulk collect##
declare
? -- PL/SQL表（索引表）
? type ttt is table of int index by binary_integer;
? lv_tt ttt;
begin

? select a BULK Collect into lv_tt from tt;?????? --BULK Collect

? for i in 1 .. 3
? loop
??? dbms_output.put_line(lv_tt(i));
? end loop;
???
end;

create table tt
(
a int,??? -- 自動增長/在觸發器中實現 ???
b int
)

create or replace trigger triInsertTT
before insert
on tt
for each row
begin
? :New.a := 1;
end;

set serveroutput on;
--動態sql
declare
i int;
begin
--execute immediate 'create table aqq(i int)';
--execute immediate 'insert into aqq values(1) ';
i := 13;
execute immediate 'insert into aqq values('|| i ||') ';????? --這樣寫才正確
--execute immediate 'insert into aqq values(i) ';??????????? --ORA-00984: 列在此處不允許
--'insert into a values (i)';??????????????????????????????? --PLS-00490: 非法語句
end;

select * from aqq;

declare
type tt is table of int index by binary_integer;
lv_t tt;
begin
? for i in 5 .. 10?? --從哪個值開始都可以。
? loop
??? lv_t(i) := i;
? end loop;
?
? for i in 5 .. 10?? --從哪個值開始都可以。
? loop
??? dbms_output.put_line(lv_t(i));
? end loop;
end;

declare
type tr is record
(
a int,
b int
);
type tt is table of tr index by binary_integer;
lv_t tt;
begin
? for i in 2 .. 10?? --從哪個值開始都可以。
? loop
??? lv_t(i).a := i * 2;
??? lv_t(i).b := i * 3;
? end loop;
?
? for i in 2 .. 10?? --從哪個值開始都可以。
? loop
??? dbms_output.put(lv_t(i).a || ' --- ');??? --輸出不換行。
??? dbms_output.put_line(lv_t(i).b);
? end loop;
end;

#type嵌套的情況#
declare
type tr1 is record
(
b int
);
type tr is record
(
a tr1
);
type tt is table of tr index by binary_integer;
lv_t tt;
begin
? for i in 4 .. 10?? --從哪個值開始都可以。
? loop
??? lv_t(i).a.b := i;
? end loop;
?
? for i in 4 .. 10?? --從哪個值開始都可以。 如果索引值不對，報ORA-01403: 未找到數據。
? loop
??? dbms_output.put_line(lv_t(i).a.b);
? end loop;
end;

##存在問題??? ##
declare
type t is record
(
a int
--b varchar2(10)
);
type tt is table of t index by binary_integer;
lv_t2 tt;
begin
? select a into lv_t2 from c6;?? --bulk collect? --PLS-00597: INTO 列表中的表達式 'LV_T2'? 類型錯誤
? for i in 1 .. lv_t2.count
? loop
??? dbms_output.put(lv_t2(i).a || ' --- ');
??? --dbms_output.put_line(lv_t2(i).b);
? end loop;
end;

##存在問題??? ##
declare
type t is record
(
a int,
b varchar2(10)
);
type tt is table of t index by binary_integer;
lv_tt tt;
begin
? select a,b bulk collect into lv_tt from c6;?? --bulk collect? --PLS-00597: INTO 列表中的表達式 'LV_T2'? 類型錯誤
? dbms_output.put_line(lv_tt.count || '~~~~~~' );
? for i in 1 .. lv_tt.count
? loop
??? dbms_output.put(lv_tt(i).a || ' --- ');
??? dbms_output.put_line(lv_tt(i).b);
? end loop;
end;
???

declare
type tt is table of varchar2(24) index by binary_integer;
lv_tt tt;
begin
? select b bulk collect into lv_tt from c6;?? --bulk collect可以--
? dbms_output.put_line(lv_tt.count || '~~~~~~' );
? for i in 1 .. lv_tt.count
? loop
??? dbms_output.put(lv_tt(i) || ' --- ');
? end loop;
end;

--集合 記錄 對象（抽象數據類型）
declare
type address_rec is record?????????? --自定義類型
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
lv_address_rec address_rec;
begin
? lv_address_rec.state := 'HN';
? lv_address_rec.city := 'ZZ';
? dbms_output.put_line(lv_address_rec.state);
? dbms_output.put_line(lv_address_rec.city);
end;

--建立程序包，僅包含一個自定義數據類型。
create or replace package pkg_school
is
type address_rec is record?????????? --自定義類型
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
end pkg_school;

--簡化以上的代碼--
declare
lv_address_rec pkg_school.address_rec;????? --使用程序包來取代
begin
? lv_address_rec.state := 'HN';
? lv_address_rec.city := 'ZZ';
? dbms_output.put_line(lv_address_rec.state);
? dbms_output.put_line(lv_address_rec.city);
end;

--創建學生表，使用自定義類型。
create table student
(
no char(4),
name varchar2(8),
address pkg_school.address_rec?? --報錯：ORA-00902: 無效數據類型
);

create table student10
(
no char(4),
name varchar2(8),
address address_rec5???????????? --要使用永久性對象，自定義類型才可以。
);

--添加值
insert into STUDENT10 values ('0001','ANiu',address_rec5('HN','ZZ','TL','0001'));

select no,name,address.state from STUDENT10;??? --這樣寫會報錯。address.state? ORA-00904: 無效列名
select * from STUDENT10;
select no,name,address from STUDENT10;

#這樣寫才可以，通過別名的方式。#
select no,name,s.address.state from STUDENT10 s;

--添加，通過pl/sql方式。
declare
lv_address address_rec5;
begin
? lv_address := address_rec5('GD','ZZ','TL','0001');
? insert into STUDENT10 values ('0001','ANiu',lv_address);?? --通過變量的方式，lv_address
end;

--通過點分法修改address_rec5
update student10 s set s.address.state = 'HK' where s.no='0001';

--刪除
delete student10 s where s.address.state = 'HK';

--定義索引，在自定義抽象數據類型的字段上，優化查詢性能。
create index street_name_idx on student10(address.street_name);

--查看用戶定義的索引
select index_name,index_type,table_name,table_type from user_indexes where table_name = 'STUDENT10';

?

--#這樣創建表會報錯??#
declare
type address_rec is record?????????? --自定義類型
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
begin
? create table students
? (
? no char(4),
? name varchar2(8),
? address address_rec
? );
end;

--創建類型
create type address_rec4 as record?? --會報錯? as object
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);

declare
type address_rec4 is record? --暫態對象
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
begin
dbms_output.put_line('aaa');
end;

create type address_rec4 as object?? --需要寫成object,永久性對象。
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);

--刪除自定義抽象數據類型
drop type address_rec4;
--強制刪除
drop type address_rec4 force;

--使用自定義類型
declare
lv_address address_rec5;
begin
lv_address := address_rec5('HN','ZZ','TL','0001');
dbms_output.put_line(lv_address.state);
dbms_output.put_line(lv_address.city);
dbms_output.put_line(lv_address.street_name);
dbms_output.put_line(lv_address.street_no);
end;

select column_name,data_type from user_tab_columns where table_name = 'student10';? --查詢不了結果。
select column_name,data_type from user_tab_columns where table_name = 'STUDENT10';? --表名要大寫才可以。

--查詢類型屬性。
select attr_name,length,attr_type_name from user_type_attrs where type_name = 'ADDRESS_REC5';

--查看對象依賴性
desc user_dependencies;

select name,type from user_dependencies where referenced_name = 'ADDRESS_REC5';

--##對象表的概念##
create table Address of address_rec5;

-- 查看
desc Address;
-- 添加
insert into Address values(address_rec5('HN','ZZ','TL','0001'));
insert into Address values('SH','PJ','XH','0005');?????????????? --也可以這樣添加值。
-- 查詢
select * from address;

--通過value函數可以將查詢結果作為一個對象的集合返回
select value(a) from address a;?? --有要求的，不是任何一個表都可以，而是要基于對象表創建的表才可以。

--調用
declare
s1 address_rec5;? --類型，而不是Address，否則會報錯。
begin
select value(s) into s1 from address s where s.state = 'SH';
dbms_output.put_line(s1.state);
dbms_output.put_line(s1.city);
dbms_output.put_line(s1.street_name);
dbms_output.put_line(s1.street_no);
end;

insert into Address values(address_rec5('GD','GZ','XA','0001'));
insert into Address values(address_rec5('XX','XA','BJ','0004'));

--oid
--oracle自動分配給對象表中每一條對象記錄的一個唯一標示號
--通過ref來進行調用
select ref(a) from address a;? --有幾條記錄就返回幾條a地址值.如
REF(A)
--------------------------------------------------------------------------------
00002802096D5EE0A11F2C4C828CE4DB0E4B9DBA51592BB9591600436887DFDE706F178DBC004086
E60000

00002802094F82C9267C2C4BE6B21E9851001E74B3592BB9591600436887DFDE706F178DBC004086
E60001

0000280209C955BEBAA7D04674995B5996900A6330592BB9591600436887DFDE706F178DBC004086
E60002

#OID應用#? deref()函數? Dangling

##可變數組varray##
--可變數組
declare
-- 定義可變數組 varing array =>varray
type authors_ary is varray(5) of varchar2(12);
-- 聲明數組變量，通過構造函數初始化
lv_authors authors_ary := authors_ary('A','B','C');
begin
? --通過索引下標來調用
? dbms_output.put_line(lv_authors(1));
? dbms_output.put_line(lv_authors(2));
? dbms_output.put_line(lv_authors(3));
end;

-- 創建可變類型
create or replace type authors_ary is varray(5) of varchar2(12);

create table bs(
? bid varchar2(4),
? authors authors_ary
)

insert into bs values('001',authors_ary('A','B','C'));
insert into bs values('002',authors_ary('D','E','F'));
insert into bs values('003',authors_ary('G','H','I'));
insert into bs values('004',authors_ary('J','K','L'));

-- select * from bs;

select * from bs where bid = '001';
select b.bid,b.authors_ary(1) from bs b where bid = '001';? --這樣子是取不了的??

--構造一個隱式的游標
begin
? for lv_book_rec in (select * from bs)
? loop
??? --dbms_output.put( ' ---- ');
??? dbms_output.put_line (lv_book_rec.bid || '========');
??? for lv_index in 1 .. lv_book_rec.authors.count
??? loop
????? dbms_output.put(lv_book_rec.authors(lv_index));??????? --這樣子是可以取的。
??? end loop;
??? dbms_output.put_line ( '========');
? end loop;
end;

?

-- 可變數組的函數
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
? dbms_output.put_line('Limit:' || lv_authors.limit);??? --定義的數組大小
? dbms_output.put_line('Count:' || lv_authors.count);
? if lv_authors.exists(1) then
??? dbms_output.put_line(lv_authors(1));
? end if;
? dbms_output.put_line(lv_authors(2));
? dbms_output.put_line(lv_authors(3));
? if lv_authors.exists(4) then
??? dbms_output.put_line(lv_authors(4));
? else
??? dbms_output.put_line('沒有第4項');
? end if;
end;

Limit:5
Count:3
A
B
C
沒有第4項

PL/SQL 過程已成功完成。

#完善以上代碼，加了大小超出報錯異常及通過extend添加新項#
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
? dbms_output.put_line('Limit:' || lv_authors.limit);??? --定義的數組大小
? dbms_output.put_line('Count:' || lv_authors.count);
? if lv_authors.exists(1) then
??? dbms_output.put_line(lv_authors(1));
? end if;
? dbms_output.put_line(lv_authors(2));
? dbms_output.put_line(lv_authors(3));
? lv_authors.extend;??????????????????????????? --擴充，延伸。
? lv_authors(4) := 'DDD';
? --通過extend添加新項。
? lv_authors.extend;????--lv_authors.extend(1); 與 lv_authors.extend; 等效。
??????--lv_authors.extend(1,1); 第一個參數表示在4的基礎上新加一個，第二個參數表示引用原來數組里面的第一個值。整體作為第5個元素。
? lv_authors(5) := 'e';
? --lv_authors.extend;
? --lv_authors(6) := 'f';
? --lv_authors(6) := 'f';?????????????????????? --ORA-06533: 下標超出數量
? if lv_authors.exists(4) then
??? dbms_output.put_line(lv_authors(4));
? else
??? dbms_output.put_line('沒有第4項');
? end if;
? if lv_authors.exists(5) then
??? dbms_output.put_line(lv_authors(5));
? else
??? dbms_output.put_line('沒有第5項');
? end if;
end;

?

--循環可變數組的函數
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
? for lv_index in 1 .. lv_authors.count
? loop
??? dbms_output.put_line(lv_index);
? end loop;
end;

##隱式游標##
begin
?for lv_Title_type in (select cid, cname from class6)
?loop
? dbms_output.put_line ('========');
? --dbms_output.put_line (lv_Title_type.cid);
? --dbms_output.put_line (lv_Title_type.cname);
?end loop;
?-- 關閉游標
end;
---------ORACLE6.1.txt

Oracle集合分為兩類
-- 可變數組大小 type type_name is varray(limit) of datatype not null
特點：固定大小，數據連續存儲。
-- 嵌套表 type table_name is table of data_type
特點：可變大小，數據松散存儲。

declare
type typ_authors is varray(3) of varchar2(12);??? --容量大小是varray后面的值，而不是數據類型varchar2(12)?
-- 調用構造函數初始化
au_ary typ_authors := typ_authors();
begin
? if au_ary is null then
??? dbms_output.put_line('au_ary is null!');??? --打印
? else
??? dbms_output.put_line('au_ary is not null!');
? end if;

? dbms_output.put_line('Limit: ' || au_ary.limit);? --3
? dbms_output.put_line('Count: ' || au_ary.count);? --0
? au_ary.extend;
? au_ary(1) := 'A';
? au_ary.extend;
? au_ary(2) := 'B';
? dbms_output.put_line(au_ary(1));? --A
? dbms_output.put_line(au_ary(2));? --B
? -- 通過Extend添加新項
? au_ary.extend;
? au_ary(3) := 'C';
? dbms_output.put_line(au_ary(3));? --C
? dbms_output.put_line('Limit: ' || au_ary.limit);? --3
? dbms_output.put_line('Count: ' || au_ary.count);? --3
? au_ary.trim(1);? --截掉最后一個元素
? dbms_output.put_line('Limit: ' || au_ary.limit);? --3
? dbms_output.put_line('Count: ' || au_ary.count);? --2
? if au_ary.exists(3) then
??? dbms_output.put_line('au_ary(3) 存在！');
? else
??? dbms_output.put_line('au_ary(3) 不存在！');???? --打印
? end if;
? dbms_output.put_line('1 -- > Next: ' || au_ary.Next(1)); --B? 輸出為2？
? dbms_output.put_line('Last: ' || au_ary.Last);?????????? --B? 輸出為2？
end;

#通過以下實例發現：lv_authors.Next(1),lv_authors.Last返回的都是下標值。#
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
? dbms_output.put_line('Limit:' || lv_authors.limit);??? --定義的數組大小
? dbms_output.put_line('Count:' || lv_authors.count);
? if lv_authors.exists(1) then
??? dbms_output.put_line(lv_authors(1));
? end if;
? dbms_output.put_line(lv_authors(2));
? dbms_output.put_line(lv_authors(3));
?
? dbms_output.put_line('First: ' || lv_authors.First);??????????
? dbms_output.put_line('First: ' || lv_authors(lv_authors.First));??
?
? --dbms_output.put_line('1 -- > Prior: ' || lv_authors.prior(1));???????????? --ORA-06502: PL/SQL: 數字或值錯誤 :? NULL 索引表鍵值
? --dbms_output.put_line('1 -- > Prior: ' || lv_authors(lv_authors.prior(1)));
?
? dbms_output.put_line('1 -- > Prior: ' || lv_authors.prior(2));
? dbms_output.put_line('1 -- > Prior: ' || lv_authors(lv_authors.prior(2)));
?
? dbms_output.put_line('1 -- > Next: ' || lv_authors.Next(1));
? dbms_output.put_line('1 -- > Next: ' || lv_authors(lv_authors.Next(1)));
?
? dbms_output.put_line('Last: ' || lv_authors.Last);??????????
? dbms_output.put_line('Last: ' || lv_authors(lv_authors.Last));??????
end;

#嵌套表#
declare
type student_typ_tbl is table of varchar2(12);
stu_tbl student_typ_tbl;
j number;
begin
? if stu_tbl is null then
??? dbms_output.put_line('stu_tbl is null!');? --打印
??? stu_tbl := student_typ_tbl();????????????? --初始化
? else
??? dbms_output.put_line('stu_tbl is not null!');
? end if;
? dbms_output.put_line('Limit: ' || stu_tbl.limit); --沒有值
? dbms_output.put_line('Count: ' || stu_tbl.count); --0
? for i in 1 .. 10
? loop
??? stu_tbl.extend;??? --附加值
??? stu_tbl(i) := i;
? end loop;
? stu_tbl.delete(1);?? --刪除元素1
? stu_tbl.delete(4);?? --刪除元素4
? --for i in 1 .. 10
? --for i in 1 .. stu_tbl.count
? --for i in 1 .. stu_tbl.last
? j := stu_tbl.first;
? for i in 1 .. stu_tbl.count
? loop
??? --if stu_tbl.exists(i) then
??? --? dbms_output.put_line('stu_tbl(' || i || '): ' || stu_tbl(i));
??? --end if;
??? dbms_output.put_line('stu_tbl(' || j || '): ' || stu_tbl(j));
??? j := stu_tbl.next(j);
? end loop;
? dbms_output.put_line('Limit: ' || stu_tbl.limit); --沒有值
? dbms_output.put_line('Count: ' || stu_tbl.count); --8
end;

--索引表
declare
type teachers_type_tbl is table of varchar2(12) index by binary_integer;
teacher_tbl teachers_type_tbl;
begin
? teacher_tbl(1) := 'A';
? teacher_tbl(9) := 'B';
? dbms_output.put_line(teacher_tbl(1));
? dbms_output.put_line(teacher_tbl(9));
end;

##對象命名規范和對象體##
create or replace type rectangle is object
(
width number(5,2),
height number(5,2),
map member function area return number?? --需要定義成map或者是order（編譯報錯?），對象間才可以比較大小。
);

create or replace type body rectangle is

? map member function area
? return number
? is
? begin
??? return (width * height);
? end area;

end;

--調用
declare
lv_rect rectangle := rectangle(10,10);
begin
??? DBMS_OUTPUT.Put_Line( 'lv_rect.area' );?
??? DBMS_OUTPUT.Put_Line( lv_rect.area );?
end;

--添加了map之后，才可以比較大小，否則會報錯。
declare
lv_rect1 rectangle := rectangle(10, 10);
lv_rect2 rectangle := rectangle(20, 20);
begin
? if lv_rect1 > lv_rect2 then
??? DBMS_OUTPUT.Put_Line( '1 > 2' );?
? else
??? DBMS_OUTPUT.Put_Line( '1 < 2' );?
? end if;
end;

##order方式## 注意function區別
--類型規范
create or replace type rectangle is object
(
width number(5,2),
height number(5,2),
order member function area(r rectangle) return int
);
--類型體
create or replace type body rectangle is

? order member function area(r rectangle)
? return int
? is
? begin
??? case
??? when (width * height) < (r.width * r.height) then return -1;
??? when (width * height) > (r.width * r.height) then return 1;
??? when (width * height) = (r.width * r.height) then return 0;
??? end case;
? end area;

end;

-- 實例????????? 對象規范和對象體。???
-- 堆棧(stack)

http://hi.baidu.com/no20win/item/7a97825a4e2ef0a3acc857af ?

Oracle Class10. 集合和成員函數(pl/sql表和記錄,嵌套表和可變數組,成員函數和過程)