Oracle集合类型
所謂集合是一種類似于列表或者一維數組的數據結構。PL/SQL提供了三種集合類型：關聯數據組（索引表），嵌套表和VARRAY（可變長數組）。
?
1.集合類型
1.1關聯數組
關聯數組（也稱為索引表）是一組鍵值對。每個密鑰都是唯一的，并且被用于定位相應的值。鍵可以是整數或字符串。只能用于PL/SQL環境。
?
1.2嵌套表
從概念上講，嵌套表像一個元素數量任意的一維數組。
在數據庫中，嵌套表是存儲一組值的列類型，數據庫存儲嵌套表的行是沒有特定順序的。當你從數據中提取嵌套表到PL/SQL變量時，該行給出連續從1開始的下標。通過這些類似數組下標訪問獨立的行。
嵌套表不同于數組的重要方面：
- 數組需要聲明元素的個數，而嵌套表不需要。嵌套表的大小可以動態增加。
- 數組總是密集的，嵌套表剛開始是密集的，但是后面有可能會變成稀疏的。因為你會從嵌套表中刪除元素。
?
1.3 可變長的數組
可變長的數組是一個VARRAY數據類型的集合。當你聲明VARRAY類型的時候，就必須指定同時指定它能夠包含的最大元素個數。VARRAY可以包含可變數據的元素，從零到最大值。VARRAY索引有一個固定定的下限1和一個可擴展的上限。和嵌套表類型一樣的是，它們都可以用于PL/SQL和數據庫。但是和嵌套表不一樣的是，在向VARRAY中保存數據或者提取數據時，它的元素是有序的。
?
2.聲明集合類型
在使用一個集合之前，我們必須先聲明它。有兩種方法可以申明一個集合類型：
- 通過TYPE語句在一個PL/SQL程序中聲明集合類型。
- 通過CREATE TYPE語句在數據中定義一個嵌套表或者VARRAY類型，這個類型就是一個模式級別的對象。這種類型就可以用作數據庫表的列的數據類型，可以用作對象類型的屬性，也可以用于聲明PL/SQL變量。
?
2.1聲明關聯數組
關聯數組的TYPE語句的語法如下：
TYPE table_type_name IS TABLE OF datatype [ NOT NULL] INDEX BY index_type;
其中：
??? table_type_name是你所創建的集合類型的名字，datatype是集合中唯一一列的數據類型，index_type是用來組織集合內容的索引的數據類型。而集合唯一以列的數據類型可以是下面這些：
- 標量數據類型：任何被PL/SQL支持的標量數據類型，比如VARCHAR2，CLOB，POSITIVE，DATE，或者BOOLEAN。
- 錨定數據類型：這種數據類型是從一個數據庫表的列，之前已經定義的變量或者帶有%TYPE屬性的游標表達式推導出來的數據類型。我們也可以定義用%ROWTYPE聲明或者根據一個用戶定義的記錄類型來定義一個記錄的集合。
- 復雜的數據類型：從Oracle 9i數據庫R2版本開始，你也可以把對象類型和集合類型作為集合的數據類型。
集合語法中的index_type定義索引下標的數據類型。在Oracle 9i數據庫版本R2之前，只能是INDEX BY PLS_INTEGER。從Oracle 9i數據庫版本R2開始，INDEX BY的數據類型可以是BINARY_INTEGER、及它的子類型、VARCHAR2(N)或者VARCHAR2列或變量的%TYPE錨定類型。
?
2.2聲明嵌套表
可以數據庫內或者PL/SQL代碼塊中聲明嵌套表類型。
在數據庫內創建一個嵌套表類型：
CREATE [OR REPLACE] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];
刪除數據庫內的嵌套表類型：
DROP TYPE type_name [FORCE];
?
在PL/SQL中聲明一個嵌套表類型：
TYPE type_name IS? TABLE OF element_datatype [ NOT NULL ];
其中：
- ?OR REPLACE：允許我們重創建一個已經存在的類型。通過在語法中加上REPLACE的方式來重建類型，而不是先刪除后再重新創建，可以把所有已經授予的權限都完整的保留下來。
- type_name：一個合法的SQL或者PL/SQL標志符。這也是我們以后在聲明變量或者列時會用到的標識符。
- element_datatype:這是集合元素的數據類型。集合內所有元素都是一種類型的，可以是大部分標量數據類型、對象類型、或者REF對象類型。如果集合的元素是對象，對象類型本身不能再帶有一個集合屬性。如果你創建了一個其元素是RECORD類型的集合，記錄的字段只能是標量或者是獨享。明確的不可用于集合的數據類型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL數據類型)。
- NOT NULL：表明這種類型的變量不能有任何空元素。不過，集合本身可可以是原子級的空（未初始化）。
- FORCE：這個關鍵字告訴數據庫的是，當要刪除這個類型時，就算是其他類型中還有對這個類型的引用，也要強行刪除這個類型。比如，如果在一個對象類型的定義中用到了某個特殊的集合類型，你可以使用FORCE關鍵字來強行刪除這個集合類型。
?
2.3聲明VARRAY
和嵌套表類型的聲明一樣，可以數據庫內或者PL/SQL代碼塊中聲明VARRAY類型。
在數據庫內創建一個VARRAY類型：
CREATE [OR REPLACE] TYPE type_name AS | IS?VARRAY?(max_elements)?OF element_datatype [ NOT NULL ];
刪除數據庫內的VARRAY類型：
DROP TYPE type_name [FORCE];
在PL/SQL中聲明一個VARRAY類型：
TYPE type_name IS??VARRAY?(max_elements)?OF element_datatype [ NOT NULL ];
其中：
- OR REPLACE：允許我們重創建一個已經存在的類型。通過在語法中加上REPLACE的方式來重建類型，而不是先刪除后再重新創建，可以把所有已經授予的權限都完整的保留下來。
- type_name：一個合法的SQL或者PL/SQL標志符。這也是我們以后在聲明變量或者列時會用到的標識符。
- element_datatype:這是集合元素的數據類型。集合內所有元素都是一種類型的，可以是大部分標量數據類型、對象類型、或者REF對象類型。如果集合的元素是對象，對象類型本身不能再帶有一個集合屬性。如果你創建了一個其元素是RECORD類型的集合，記錄的字段只能是標量或者是獨享。明確的不可用于集合的數據類型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL數據類型)。
- NOT NULL：表明這種類型的變量不能有任何空元素。不過，集合本身可可以是原子級的空（未初始化）。
- max_elements:VARRAY中元素的最大數量，這個值一旦聲明就不能更改。
- FORCE：這個關鍵字告訴數據庫的是，當要刪除這個類型時，就算是其他類型中還有對這個類型的引用，也要強行刪除這個類型。比如，如果在一個對象類型的定義中用到了某個特殊的集合類型，你可以使用FORCE關鍵字來強行刪除這個集合類型。
?
3.集合變量的聲明和初始化
3.1集合變量的聲明
一旦我們創建好了集合類型，我們就可以根據這個集合類型聲明該類型的變量。一個集合變量聲明格式如下：
collection_name collection_type [:=collection_type(...)];
其中，collection_name是集合變量的名字，collection_type具有兩層含義，它即代表著一個先前已經聲明的集合類型的名字，同時也代表著（如果是嵌套表或者VARRAY的話）和該類型同名的構造函數。
構造函數的名字和類型的名字是相同的，并且接收一個用逗號分隔的元素列表作為參數。如果我們聲明的是一個嵌套表或者VARRAY變量，我們在使用之前必須要先對這個變量進行初始化。
?
3.2集合變量的初始化
對于嵌套表類型集合變量和VARRAY類型集合變量，在使用集合變量之前必須要進行初始化，而對于關聯數組類型不需要初始化。下面主要討論嵌套表和VARRY的初始化。
3.2.1通過構造函數的顯示初始化
通過構造函數顯示地給集合變量初始。例如：
declare
vnt_employee nt_employee :=nt_employee(); --不帶參數的構造函數初始化
vnt_employee nt_employee :=nt_employee('張三','李四','王五'); --帶參數的構造函數初始化
begin
?? null;
end;
?
3.2.2直接賦值時的隱式初始化
如果兩個集合實例是基于同一集合類型，我們可以把其中一個實例的全部內容拷貝給另一個，這就相當于進行了初始化。例如：
declare
vnt_employee nt_employee :=nt_employee('James','Lucy','Jordan');
vnt_foregin_employee nt_employee;
begin
???? vnt_foregin_employee := vnt_employee；
end;
?
3.2.3通過FETCH操作的隱式初始化
在使用FETCH或者 SELECT INTO語句從數據庫提取提取一個集合并保存到一個集合變量時，集合變量會自動初始化，就像直接賦值一樣。
declare
vnt_colors nt_color;
begin
select colors into vnt_colors from color_models;? --表color_models的color列是嵌套表類型。
end;
?
3.2.4通過BULK COLLECT語句的隱式初始
使用BULK COLLECT INTO語句批量提取數據并保存到一個集合變量，集合變量會自動初始化，就像直接賦值一樣。
declare
vnt_employee nt_employee; --未初始化
begin
?? select?e.ename bulk collect intovnt_employee??from emp e;
end;
?
declare
cursor? cur_employee is select e.ename from emp e;
vnt_employee? nt_employee; --未初始化
begin
?? open cur_employee;
?? fetch cur_employee bulk collect into vnt_employee;
?? close cur_employee;
end;
?
4.集合方法
Oracle提供了提供許多內置的函數和過程可以用于獲取集合的信息或者修改集合的內容，這些方法也叫做集合方法。下面給出這些方法的完整列表：
| 方法（函數或者過程） | ?? 說? 明 |
| COUNT函數 | 返回集合中現有元素的數量 |
| DELETE過程 | 從集合中移除一個或者多個元素。如果不是重復移除，會減少COUNT的值，對于VARRAY，你只能刪除集合的所有元素 |
| EXISTS函數 | 根據某個指定的元素是否已經在集合中，返回TURE或者FALES |
| EXTEND過程 | 增加嵌套表或者VARRAY中元素的個數，同時增加COUNT的值 |
| FIRST、LAST函數 | 返回可用的最小（FIRST）和最大（LAST）集合下標 |
| LIMIT函數 | 返回VARRAY中允許ude最大元素數量 |
| PRIOD、NEXT函數 | 返回緊挨著指定的下標之前(PRIOD)或者之后(NEXT)的下標值。你應該總是用PRIOD和NEXT在集合內遍歷，尤其在使用稀疏（或者可能是稀疏）集合時更是如此 |
| TRIM過程 | 從集合的尾部（定義的最大下標）移除集合元素 |
?
之所以把這些過程叫做方法，是因為使用這些集合內置程序的語法不同于調用過程和函數的正規語法。
?
5.集合類型對比
| ??? 屬?????性???? | 關? 聯? 數? 組 | 嵌? 套? 表 | ?可變長數組 |
| 維度 | 一維 | 一維 | 一維 |
| 是否可用于SQL | 不可用 | 可用 | 可用 |
| 是否可作為表中列的數據類型 | 不可用 | 可以；數據是在“行外”保存的（一個獨立的表） | 可以；數據保存在“行內”（在同一個表中） |
| 未初始化時的狀態 | 空（不能是NULL）；元素是為定義 | 自動就是NULL的；對元素的引用是非法的 | 自動就是NULL；對元素的引用是非法的 |
| 初始化 | 在聲明時自動完成 | 通過構造函數，或者賦值，或者fetch操作完成 | 通過構造函數，或者賦值，或者fetch操作完成 |
| 在PL/SQL中元素的引用方式 | BINARY_INTEGER以及其子類型 | VARCHAR2（Oracle 9i數據庫R2版本或者更高版本） | 1到2 147 483 647間的整數 |
| 是否稀疏 | 是 | 開始不是；經過刪除后就成稀疏了 | 不是 |
| 是否有界 | 無界 | 可以擴展 | 有界 |
| 可以隨時對任意一個元素賦值 | 可以 | 不可以，可以需要用EXTEND進行擴展 | 不可以；可以用EXTEND進行擴展，而且擴展時不能超出上邊界 |
| 擴展的方法 | 給一個新下標指向的元素賦值 | 使用內置的EXTEND過程（或者TRIM進行壓縮），沒有預定義的最大值 | 使用內置EXTEND過程（或者TRIM進行壓縮），但是最大只能到聲明的最大尺寸 |
| 可以比較相等與否 | 不可以 | 可以，要求是Oracle 10g或者以后的版本 | 不可以 |
| 是否可以通過集合操作符進行操作 | 不可以 | 可以，要求是Oracle 10g或者以后的版本 | 不可以 |
| 存取數據時是否會保留順序或者下標 | N/A | 不保留 | 保留 |
?
6.集合示例
6.1關聯數組示例
declare
? type nt_foregn_employee is table of varchar2(30) index by binary_integer;
? vnt_foregn_employees nt_foregn_employee;
? v_row??????????????? number;
begin
? vnt_foregn_employees(-230002) := 'SCOTT';
? vnt_foregn_employees(-23) := 'JONES';
? vnt_foregn_employees(1) := 'ALLEN';
? vnt_foregn_employees(5934) := 'CLARK';
? vnt_foregn_employees(13342) := 'ADAMS';
? vnt_foregn_employees(8234223) := 'KING';
? --使用FIRST方法獲取集合中的一個行號
? v_row := vnt_foregn_employees.first;
? while (v_row is not null) loop
??? dbms_output.put_line(vnt_foregn_employees(v_row));
??? v_row := vnt_foregn_employees.next(v_row);
? end loop;
end;
?
在稀疏集合中，經常需要使用NEXT方法遍歷集合，提取數據。
?
6.2嵌套表示例
declare
? type nt_employee is table of emp%rowtype;
? vnt_employees nt_employee := nt_employee(); --構造函數顯示初始化
? c_big_number? number := power(2, 31);
? l_start_time? pls_integer;
? cursor cur_employee is
??? select * from emp;
? vrt_employees cur_employee%rowtype;
begin
? open cur_employee;
? loop
??? fetch cur_employee
????? into vrt_employees;
??? exit when cur_employee%notfound;
??? vnt_employees.extend;
??? vnt_employees(vnt_employees.last) := vrt_employees;
? end loop;
? close cur_employee;
?
? dbms_output.put_line(vnt_employees.count);
end;
?
6.3VARRAY實例
create or? replace type nt_course is varray(5) of varchar2(100);
create table students( student_name varchar2(20) , cource nt_course);
declare
? vnt_nt_courses nt_course := nt_course();
begin
? vnt_nt_courses.extend(2);
? vnt_nt_courses(1) := 'English';
? vnt_nt_courses(2) := 'Chinese';
? insert into students
??? (student_name, cource)
? values
??? ('chiclewu', vnt_nt_courses);
?????
end;
可以使用TABLE函數把一個集合映射成數據庫表.例如，要獲取student表中課程列的記錄。
select * from table (select s.cource from students s);
?
參考：
Oracle PL/SQL程序設計（第五版） Steven Feuersterin & Bill Pribyl著 張曉明譯
?
?
總結
以上是生活随笔為你收集整理的Oracle集合类型的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： g723源码详细分析-18-丢包补偿
- 下一篇： 自然语言处理从零到入门 NLP