oracle 三種集合類型比較（index-by表，巢狀表格，數組）

Oracle三種集合資料類型(索引表，巢狀表格，VARRAY 數組)的比較-PLSQL—之三 2009-12-05 16:21

PL/SQL中沒有數組的概念，他的集合資料類型和數組是相似的。在7.3以前的版本中只有一種集合，稱為PL/SQL表，在這之後又有兩種集合資料類型:巢狀表格和varray。其中varray集合中的元素是有數量限制的，index_by表和巢狀表格是沒有這個限制的。index-by表是稀疏的，也就是說下標可以不連續，varray類型的集合則是緊密的，他的下標沒有間隔。index_by表不能儲存在資料庫中，但是巢狀表格和varray可以被儲存在資料庫中。
集合在使用時必須先使用type進行定義方可使用

1.index_by表

type type_name is table of element_type [NOT NULL] index by binary_integer

2.巢狀表格

type type_name is table of element_type [NOT NULL]

3.varray

type type_name is [varray ¦varying array](max_size) of element_type[NOT NULL]

一，index_by表

TYPE TYPE1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

1.使用的時候需要先賦值後讀取，至少也要先初期化一下，否則會出現異常：ORA-01403: no data found。

2.這種數組不需要事先指定上限，下標可以不連續，可以是0或負數。

例：v1 TYPE1;

v1(-1) := '-1';
v1(0) := '0';
v1(1) := '1';
DBMS_OUTPUT.put_line(v1(-1)); --訪問合法

DBMS_OUTPUT.put_line(v1(2)); --訪問非法

二，巢狀表格

TYPE TYPE2 IS TABLE OF VARCHAR2(10);

54com.cn

1.必須進行初期化，否則會出現異常：ORA-06531: Reference to uninitialized collection

2.初期化方法：

54ne.com

v1 TYPE2 := TYPE2(); --聲明時初期化數組為空白

54com.cn

v2 TYPE2 := TYPE2('1','2','3','4','5'); --聲明時初期化數組為5個元素

v1 := TYPE2(); --初期化後數組為空白

v2 := TYPE2('1','2','3','4','5'); --初期化後數組為5個元素

3.數組元素的訪問：

下標從1開始，不能超過數組所有元素的總和，當下標超出允許範圍時，出現異常：ORA-06532: Subscript outside of limit

因為不能訪問空數組，所以空數組的場合，必須進行數組擴充。 54ne.com

例：v1.EXTEND;

V1(1):= ‘1’; --訪問合法 54com.cn

v1(2):= ‘2’; --訪問非法，之前必須再次執行v1.EXTEND; 網管網bitsCN_com

例：v2的下標範圍是1～5。

v2(5):= ‘Hello’; --訪問合法

DBMS_OUTPUT.put_line(v2(6)); --訪問非法

三，Varray

feedom.net

TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);

由於類型定義時的元素個數限制，所以TYPE3的變數在使用時最大的元素個數不能超過5個。

與巢狀表格基本相同（略）

四，集合內建函數

集合還有很多內建函數，這些函數稱為方法，調用方法的文法如下： 54ne.com

collection.method

下表中列出oracle中集合的方法

方法 描述 使用限制

COUNT 返回集合中元素的個數 網管網bitsCN.com

DELETE 刪除集合中所有元素 54com.cn

DELETE(x) 刪除元素下標為x的元素，如果x為null,則集合保持不變 對VARRAY非法

DELETE(x,y) 刪除元素下標從X到Y的元素，如果X>Y集合保持不變 對VARRAY非法

EXIST(x) 如果集合元素x已經初始化，則返回TRUE, 否則返回FALSE 中國網管聯盟www_bitscn_com

EXTEND 在集合末尾添加一個元素 對Index_by非法 54com.cn

EXTEND(x) 在集合末尾添加x個元素 對Index_by非法

EXTEND(x,n) 在集合末尾添加元素n的x個副本 對Index_by非法

FIRST 返回集合中的第一個元素的下標號，對於VARRAY集合始終返回1。 feedom.net

LAST 返回集合中最後一個元素的下標號, 對於VARRAY傳回值始終等於COUNT。

LIMIT 返回VARRY集合的最大的元素個數，對於巢狀表格和Index_by集合無用。

NEXT(x) 返回在元素x之後及緊挨著它的元素的值，如果該元素是最後一個元素，則返回null。

PRIOR(x) 返回集合中在元素x之前緊挨著它的元素的值，如果該元素是第一個元素，則返回null。

TRIM 從集合末端開始刪除一個元素 對index_by不合法

TRIM(x) 從集合末端開始刪除x個元素 對index_by不合法

 

 

第二種解釋：

/* 回顧oracle 複合類型 (記錄和集合) */
/* 1.記錄是為了更方便的處理單行多列，而集合是為了更方便的處理單列多行 */
-- （一 ）記錄
/* 1. 首先建立一個簡單表開始 */
create table test_complex(
id varchar2(20),
username varchar2(20)
);
-- 向test_complex 表插入幾條記錄
insert into test_complex values('1111111','aaaaa');
insert into test_complex values('2222222','bbbbb');
insert into test_complex values('3333333','ccccc');

/* 2. 建立一個記錄類型 record
記錄類型有兩種定義方式：顯式定義和隱式定義。
*/
--顯示定義
declare
--1.顯示定義
type t_record is record (
id test_complex.id%type,
username test_complex.username%type
);
r_record t_record ; -- 聲明一個記錄類型變數r_record

--2.隱式定義
r_record test_complex%rowtype;

--遊標定義
cursor c_cursor(v_id) is select id,username from test_complex t where t.id=v_id;
--3.遊標定義記錄變數
r_record2 c_cursor%rowtype;

begin

for v in (select * from test_complex) loop
r_record.id := v.id;
r_record.username := v.username;
dbms_output.put_line('id='|| r_record.id ||'username='||r_record.username);
end loop;
exception when others then
raise_application_error(-20201,'程式錯誤 for !');
end;

--- select * from tab;
--- select * from test_complex
-- （二） 集合
/* 集合主要包括三種: 索引表，巢狀表格，以及數組(array)
1. Index表不能儲存在資料庫中（儲存在記憶體），但巢狀表格和VARRAY可以被儲存在資料庫中。
索引表文法如下；
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER;
關鍵字是INDEX BY BINARY_INTEGER，沒有這個關鍵字，那麼集合將是一個巢狀表格。由於不儲存在資料庫中，
element_type可以是任何合法的PL/SQL資料類型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。
索引表的下標可以為負，並且元素個數沒有限制（索引有三種類型 pls_integer,binary_integer 10g開始允許使用varchar2 ）
一 ：pls_integer 和 binary_integer 一樣可以儲存 -2^31-2^31
1.儲存效能高
2.pls_integer 溢出時會拋出異常，而binary_integer 如果指派給number 則不拋出異常
3.pls_integer 直接由cpu 運算比binary_integer（由oracle 類比執行）快
*/
type t_tab is table of test_complex%rowtype index by binary_integer;
r_tab t_tab;
SELECT id,username INTO r_tab(0) FROM test_complex WHERE id='1111111';
---------------------------------------------------------------------------------------------------------------
/* 2.巢狀表格定義文法如下：
巢狀表格非常類似於Index_by表，建立的文法也非常相似。只是沒有INDEX BY BINARY_INTEGER子串
TYPE type_name IS TABLE OF element_type [NOT NULL];
儲存在一個資料庫中的巢狀表格並不與表中的其它資料存放在同一個資料區塊中，它們實際上被存放在第二個表中。
從資料庫中取回的巢狀表格也不保證元素的順序。集合資料是離線儲存的，所以巢狀表格適合大型集合。
區別1：巢狀表格下標從1開始並且元素格式不限
區別2：索引表類型不能作為表列的資料類型使用，但巢狀表格可以
區別3：巢狀表格類型變數使用是必須先被構造方法初始化才能使用
當使用巢狀表格作為表列是必須用 eg: create type t_tbl is table of varchar2(2),並且必須要為列指定專門的儲存表
create type t__tbl is table of varchar2(10);
/
create table tab_t (
username varchar2(8),
phone t__tab1
) nested table phone store as t__tab1;

如果要處理多行多列則可以採用記錄表
eg: type t__tbl is table of tab_t%rowtype index by binary_integer;
*/
type t_n_tab is table of test_complex.id%type ;
r_n_tab t_n_tab;
r_n_tab := r_n_tab('11111','22222');--初始化賦值
---------------------------------------------------------------------------------------------------------------
/* 3.VARRAY定義文法如下：（定長數組，下標從1開始）
TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OF element_type [NOT NULL];
max_size是一個整數，用於標示VARRAY集合擁有的最多元素數目。VARRAY集合的元素數量可以低於max_size,但不能超過max_size。
element_type是一維元素的資料類型，如果element_type是記錄，那麼這個記錄只能使用標量資料欄位（與嵌套標相似）。
VARRAY儲存在資料庫中時與表中的其他資料存放在同一個資料區塊中，元素的順序儲存在VARRAY中。
集合是線上儲存的，VARRAY很適合於小型集合。

巢狀表格和VARRAY都能作為列儲存在資料庫表中，所以集合自身可以為NULL,當集合為NULL時，所以也不能引用集合中的元素。
可以使用IS NULL操作符檢測集合是否為NULL。
*/
type t_array is array(100) of test_complex.id%type ; --可以作為表列
r_array t_array;
r_array := r_array('11111','22222');--初始化賦值
-------------------------------------------------------------------------------------------------------------------
/* 4： 大量繫結：執行單次sql 操作能完成所有元素額資料 ，可以提高效能
forall 有三種文法如下
1.forall index in low_bound,up_bount sql_statement;
2.forall index int indices of collection [between low_bound and up_bound] sql_statement;
3.forall index in values of index_collection sql_statement;

for i in indices of t_tabl -- indices of (oracle 10g 特性)用於跳過 'null'

1.bulk collect into 子句用於取得批量資料，只試用於 select into ,fetch into ,dml語句
2.forall 語句上使用values of 子句 用於從其他集合中集合下標的值(目的是可以指定插入)子集
forall i in values of index_tab sql_statement

3.sql%bulk_rowcount 用於第i個元素所作用的函數
eg; sql%bulk_rowcount(2) 第二個元素作用的行是多少
*/

----------常用函數------------------------------------------------------------------------------------------------
/* COUNT===返回集合中元素的個數
DELETE========刪除集合中所有元素
DELETE(x)=====刪除元素下標為x的元素=====對VARRAY非法
DELETE(x,y)===刪除元素下標從X到Y的元素====對VARRAY非法
EXIST(x)======如果集合元素x已經初始化，則返回TRUE, 否則返回FALSE
EXTEND========在集合末尾添加一個元素=======對Index_by非法
EXTEND(x)=====在集合末尾添加x個元素================對Index_by非法
EXTEND(x,n)===在集合末尾添加元素n的x個副本==============對Index_by非法
FIRST=========返回集合中的第一個元素的下標號，對於VARRAY集合始終返回1。
LAST==========返回集合中最後一個元素的下標號, 對於VARRAY傳回值始終等於COUNT.
LIMIT=========返回VARRY集合的最大的元素個數===========Index_by集合和巢狀表格無用
NEXT(x)=======返回在第x個元素之後及緊挨著它的元素的值，如果x是最後一個元素，返回null.
PRIOR(x)======返回在第x個元素之前緊挨著它的元素的值，如果x是第一個元素，則返回null。
TRIM==========從集合末端開始刪除一個元素=====對於index_by不合法
TRIM(x)=======從集合末端開始刪除x個=====對index_by不合法
*/</p>