oracle集合类型详解
- 1.集合類(lèi)型
- 1.1關(guān)聯(lián)數(shù)組
- 1.2嵌套表
- 1.3 可變長(zhǎng)的數(shù)組
- 2.聲明集合類(lèi)型
- 2.1聲明關(guān)聯(lián)數(shù)組
- 2.2聲明嵌套表
- 2.3聲明VARRAY
- 3.集合變量的聲明和初始化
- 3.1集合變量的聲明
- 3.2集合變量的初始化
- 3.2.1通過(guò)構(gòu)造函數(shù)的顯示初始化
- 3.2.2直接賦值時(shí)的隱式初始化
- 3.2.3通過(guò)FETCH操作的隱式初始化
- 3.2.4通過(guò)BULK COLLECT語(yǔ)句的隱式初始
- 4.集合方法
- 5.集合類(lèi)型對(duì)比
- 6.集合示例
- 6.1關(guān)聯(lián)數(shù)組示例
- 6.2嵌套表示例
- 6.3VARRAY實(shí)例
昨天才知道oracle數(shù)據(jù)庫(kù)中也有集合類(lèi)型，廢話不多少，直接上文。
所謂集合是一種類(lèi)似于列表或者一維數(shù)組的數(shù)據(jù)結(jié)構(gòu)。PL/SQL提供了三種集合類(lèi)型：關(guān)聯(lián)數(shù)據(jù)組（索引表），嵌套表和VARRAY（可變長(zhǎng)數(shù)組）。
1.集合類(lèi)型
1.1關(guān)聯(lián)數(shù)組
關(guān)聯(lián)數(shù)組（也稱為索引表）是一組鍵值對(duì)。每個(gè)密鑰都是唯一的，并且被用于定位相應(yīng)的值。鍵可以是整數(shù)或字符串。只能用于PL/SQL環(huán)境。
1.2嵌套表
從概念上講，嵌套表像一個(gè)元素?cái)?shù)量任意的一維數(shù)組。
在數(shù)據(jù)庫(kù)中，嵌套表是存儲(chǔ)一組值的列類(lèi)型，數(shù)據(jù)庫(kù)存儲(chǔ)嵌套表的行是沒(méi)有特定順序的。當(dāng)你從數(shù)據(jù)中提取嵌套表到PL/SQL變量時(shí)，該行給出連續(xù)從1開(kāi)始的下標(biāo)。通過(guò)這些類(lèi)似數(shù)組下標(biāo)訪問(wèn)獨(dú)立的行。
嵌套表不同于數(shù)組的重要方面：
數(shù)組需要聲明元素的個(gè)數(shù)，而嵌套表不需要。嵌套表的大小可以動(dòng)態(tài)增加。數(shù)組總是密集的，嵌套表剛開(kāi)始是密集的，但是后面有可能會(huì)變成稀疏的。因?yàn)槟銜?huì)從嵌套表中刪除元素。
1.3 可變長(zhǎng)的數(shù)組
可變長(zhǎng)的數(shù)組是一個(gè)VARRAY數(shù)據(jù)類(lèi)型的集合。當(dāng)你聲明VARRAY類(lèi)型的時(shí)候，就必須指定同時(shí)指定它能夠包含的最大元素個(gè)數(shù)。VARRAY可以包含可變數(shù)據(jù)的元素，從零到最大值。VARRAY索引有一個(gè)固定定的下限1和一個(gè)可擴(kuò)展的上限。和嵌套表類(lèi)型一樣的是，它們都可以用于PL/SQL和數(shù)據(jù)庫(kù)。但是和嵌套表不一樣的是，在向VARRAY中保存數(shù)據(jù)或者提取數(shù)據(jù)時(shí)，它的元素是有序的。
2.聲明集合類(lèi)型
在使用一個(gè)集合之前，我們必須先聲明它。有兩種方法可以申明一個(gè)集合類(lèi)型：
通過(guò)TYPE語(yǔ)句在一個(gè)PL/SQL程序中聲明集合類(lèi)型。通過(guò)CREATE TYPE語(yǔ)句在數(shù)據(jù)中定義一個(gè)嵌套表或者VARRAY類(lèi)型，這個(gè)類(lèi)型就是一個(gè)模式級(jí)別的對(duì)象。這種類(lèi)型就可以用作數(shù)據(jù)庫(kù)表的列的數(shù)據(jù)類(lèi)型，可以用作對(duì)象類(lèi)型的屬性，也可以用于聲明PL/SQL變量。
2.1聲明關(guān)聯(lián)數(shù)組
關(guān)聯(lián)數(shù)組的TYPE語(yǔ)句的語(yǔ)法如下：
TYPE table_type_name IS TABLE OF datatype [ NOT NULL] INDEX BY index_type;其中：
table_type_name是你所創(chuàng)建的集合類(lèi)型的名字，datatype是集合中唯一一列的數(shù)據(jù)類(lèi)型，index_type是用來(lái)組織集合內(nèi)容的索引的數(shù)據(jù)類(lèi)型。而集合唯一以列的數(shù)據(jù)類(lèi)型可以是下面這些：
標(biāo)量數(shù)據(jù)類(lèi)型：任何被PL/SQL支持的標(biāo)量數(shù)據(jù)類(lèi)型，比如VARCHAR2，CLOB，POSITIVE，DATE，或者BOOLEAN。錨定數(shù)據(jù)類(lèi)型：這種數(shù)據(jù)類(lèi)型是從一個(gè)數(shù)據(jù)庫(kù)表的列，之前已經(jīng)定義的變量或者帶有%TYPE屬性的游標(biāo)表達(dá)式推導(dǎo)出來(lái)的數(shù)據(jù)類(lèi)型。我們也可以定義用%ROWTYPE聲明或者根據(jù)一個(gè)用戶定義的記錄類(lèi)型來(lái)定義一個(gè)記錄的集合。復(fù)雜的數(shù)據(jù)類(lèi)型：從Oracle 9i數(shù)據(jù)庫(kù)R2版本開(kāi)始，你也可以把對(duì)象類(lèi)型和集合類(lèi)型作為集合的數(shù)據(jù)類(lèi)型。
集合語(yǔ)法中的index_type定義索引下標(biāo)的數(shù)據(jù)類(lèi)型。在Oracle 9i數(shù)據(jù)庫(kù)版本R2之前，只能是INDEX BY PLS_INTEGER。從Oracle 9i數(shù)據(jù)庫(kù)版本R2開(kāi)始，INDEX BY的數(shù)據(jù)類(lèi)型可以是BINARY_INTEGER、及它的子類(lèi)型、VARCHAR2(N)或者VARCHAR2列或變量的%TYPE錨定類(lèi)型。
2.2聲明嵌套表
可以數(shù)據(jù)庫(kù)內(nèi)或者PL/SQL代碼塊中聲明嵌套表類(lèi)型。
在數(shù)據(jù)庫(kù)內(nèi)創(chuàng)建一個(gè)嵌套表類(lèi)型：
CREATE [OR REPLACE] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];刪除數(shù)據(jù)庫(kù)內(nèi)的嵌套表類(lèi)型：
DROP TYPE type_name [FORCE];在PL/SQL中聲明一個(gè)嵌套表類(lèi)型：
TYPE type_name IS TABLE OF element_datatype [ NOT NULL ];其中：
OR REPLACE：允許我們重創(chuàng)建一個(gè)已經(jīng)存在的類(lèi)型。通過(guò)在語(yǔ)法中加上REPLACE的方式來(lái)重建類(lèi)型，而不是先刪除后再重新創(chuàng)建，可以把所有已經(jīng)授予的權(quán)限都完整的保留下來(lái)。type_name：一個(gè)合法的SQL或者PL/SQL標(biāo)志符。這也是我們以后在聲明變量或者列時(shí)會(huì)用到的標(biāo)識(shí)符。element_datatype:這是集合元素的數(shù)據(jù)類(lèi)型。集合內(nèi)所有元素都是一種類(lèi)型的，可以是大部分標(biāo)量數(shù)據(jù)類(lèi)型、對(duì)象類(lèi)型、或者REF對(duì)象類(lèi)型。如果集合的元素是對(duì)象，對(duì)象類(lèi)型本身不能再帶有一個(gè)集合屬性。如果你創(chuàng)建了一個(gè)其元素是RECORD類(lèi)型的集合，記錄的字段只能是標(biāo)量或者是獨(dú)享。明確的不可用于集合的數(shù)據(jù)類(lèi)型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL數(shù)據(jù)類(lèi)型)。NOT NULL：表明這種類(lèi)型的變量不能有任何空元素。不過(guò)，集合本身可可以是原子級(jí)的空（未初始化）。FORCE：這個(gè)關(guān)鍵字告訴數(shù)據(jù)庫(kù)的是，當(dāng)要?jiǎng)h除這個(gè)類(lèi)型時(shí)，就算是其他類(lèi)型中還有對(duì)這個(gè)類(lèi)型的引用，也要強(qiáng)行刪除這個(gè)類(lèi)型。比如，如果在一個(gè)對(duì)象類(lèi)型的定義中用到了某個(gè)特殊的集合類(lèi)型，你可以使用FORCE關(guān)鍵字來(lái)強(qiáng)行刪除這個(gè)集合類(lèi)型。
2.3聲明VARRAY
和嵌套表類(lèi)型的聲明一樣，可以數(shù)據(jù)庫(kù)內(nèi)或者PL/SQL代碼塊中聲明VARRAY類(lèi)型。
在數(shù)據(jù)庫(kù)內(nèi)創(chuàng)建一個(gè)VARRAY類(lèi)型：
CREATE [OR REPLACE] TYPE type_name AS | IS VARRAY (max_elements) OF element_datatype [ NOT NULL ];刪除數(shù)據(jù)庫(kù)內(nèi)的VARRAY類(lèi)型：
DROP TYPE type_name [FORCE];在PL/SQL中聲明一個(gè)VARRAY類(lèi)型：
TYPE type_name IS VARRAY (max_elements) OF element_datatype [ NOT NULL ];其中：
OR REPLACE：允許我們重創(chuàng)建一個(gè)已經(jīng)存在的類(lèi)型。通過(guò)在語(yǔ)法中加上REPLACE的方式來(lái)重建類(lèi)型，而不是先刪除后再重新創(chuàng)建，可以把所有已經(jīng)授予的權(quán)限都完整的保留下來(lái)。type_name：一個(gè)合法的SQL或者PL/SQL標(biāo)志符。這也是我們以后在聲明變量或者列時(shí)會(huì)用到的標(biāo)識(shí)符。element_datatype:這是集合元素的數(shù)據(jù)類(lèi)型。集合內(nèi)所有元素都是一種類(lèi)型的，可以是大部分標(biāo)量數(shù)據(jù)類(lèi)型、對(duì)象類(lèi)型、或者REF對(duì)象類(lèi)型。如果集合的元素是對(duì)象，對(duì)象類(lèi)型本身不能再帶有一個(gè)集合屬性。如果你創(chuàng)建了一個(gè)其元素是RECORD類(lèi)型的集合，記錄的字段只能是標(biāo)量或者是獨(dú)享。明確的不可用于集合的數(shù)據(jù)類(lèi)型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL數(shù)據(jù)類(lèi)型)。NOT NULL：表明這種類(lèi)型的變量不能有任何空元素。不過(guò)，集合本身可可以是原子級(jí)的空（未初始化）。max_elements:VARRAY中元素的最大數(shù)量，這個(gè)值一旦聲明就不能更改。FORCE：這個(gè)關(guān)鍵字告訴數(shù)據(jù)庫(kù)的是，當(dāng)要?jiǎng)h除這個(gè)類(lèi)型時(shí)，就算是其他類(lèi)型中還有對(duì)這個(gè)類(lèi)型的引用，也要強(qiáng)行刪除這個(gè)類(lèi)型。比如，如果在一個(gè)對(duì)象類(lèi)型的定義中用到了某個(gè)特殊的集合類(lèi)型，你可以使用FORCE關(guān)鍵字來(lái)強(qiáng)行刪除這個(gè)集合類(lèi)型。
3.集合變量的聲明和初始化
3.1集合變量的聲明
一旦我們創(chuàng)建好了集合類(lèi)型，我們就可以根據(jù)這個(gè)集合類(lèi)型聲明該類(lèi)型的變量。一個(gè)集合變量聲明格式如下：
collection_name collection_type [:=collection_type(...)];其中，collection_name是集合變量的名字，collection_type具有兩層含義，它即代表著一個(gè)先前已經(jīng)聲明的集合類(lèi)型的名字，同時(shí)也代表著（如果是嵌套表或者VARRAY的話）和該類(lèi)型同名的構(gòu)造函數(shù)。
構(gòu)造函數(shù)的名字和類(lèi)型的名字是相同的，并且接收一個(gè)用逗號(hào)分隔的元素列表作為參數(shù)。如果我們聲明的是一個(gè)嵌套表或者VARRAY變量，我們?cè)谑褂弥氨仨氁葘?duì)這個(gè)變量進(jìn)行初始化。
3.2集合變量的初始化
對(duì)于嵌套表類(lèi)型集合變量和VARRAY類(lèi)型集合變量，在使用集合變量之前必須要進(jìn)行初始化，而對(duì)于關(guān)聯(lián)數(shù)組類(lèi)型不需要初始化。下面主要討論嵌套表和VARRY的初始化。
3.2.1通過(guò)構(gòu)造函數(shù)的顯示初始化
通過(guò)構(gòu)造函數(shù)顯示地給集合變量初始。例如：
declarevnt_employee nt_employee :=nt_employee(); --不帶參數(shù)的構(gòu)造函數(shù)初始化vnt_employee nt_employee :=nt_employee('張三','李四','王五'); --帶參數(shù)的構(gòu)造函數(shù)初始化beginnull;end;3.2.2直接賦值時(shí)的隱式初始化
如果兩個(gè)集合實(shí)例是基于同一集合類(lèi)型，我們可以把其中一個(gè)實(shí)例的全部?jī)?nèi)容拷貝給另一個(gè)，這就相當(dāng)于進(jìn)行了初始化。例如：
declarevnt_employee nt_employee :=nt_employee('James','Lucy','Jordan');vnt_foregin_employee nt_employee;beginvnt_foregin_employee := vnt_employee；end;3.2.3通過(guò)FETCH操作的隱式初始化
在使用FETCH或者 SELECT INTO語(yǔ)句從數(shù)據(jù)庫(kù)提取提取一個(gè)集合并保存到一個(gè)集合變量時(shí)，集合變量會(huì)自動(dòng)初始化，就像直接賦值一樣。
declarevnt_colors nt_color;beginselect colors into vnt_colors from color_models; --表color_models的color列是嵌套表類(lèi)型。end;3.2.4通過(guò)BULK COLLECT語(yǔ)句的隱式初始
使用BULK COLLECT INTO語(yǔ)句批量提取數(shù)據(jù)并保存到一個(gè)集合變量，集合變量會(huì)自動(dòng)初始化，就像直接賦值一樣。
declarevnt_employee nt_employee; --未初始化beginselect e.ename bulk collect intovnt_employee from emp e;end;declarecursor cur_employee is select e.ename from emp e;vnt_employee nt_employee; --未初始化beginopen cur_employee;fetch cur_employee bulk collect into vnt_employee;close cur_employee;end;4.集合方法
Oracle提供了提供許多內(nèi)置的函數(shù)和過(guò)程可以用于獲取集合的信息或者修改集合的內(nèi)容，這些方法也叫做集合方法。下面給出這些方法的完整列表：
方法（函數(shù)或者過(guò)程）
說(shuō) 明
COUNT函數(shù)
返回集合中現(xiàn)有元素的數(shù)量
DELETE過(guò)程
從集合中移除一個(gè)或者多個(gè)元素。如果不是重復(fù)移除，會(huì)減少COUNT的值，對(duì)于VARRAY，你只能刪除集合的所有元素
EXISTS函數(shù)
根據(jù)某個(gè)指定的元素是否已經(jīng)在集合中，返回TURE或者FALES
EXTEND過(guò)程
增加嵌套表或者VARRAY中元素的個(gè)數(shù)，同時(shí)增加COUNT的值
FIRST、LAST函數(shù)
返回可用的最小（FIRST）和最大（LAST）集合下標(biāo)
LIMIT函數(shù)
返回VARRAY中允許ude最大元素?cái)?shù)量
PRIOD、NEXT函數(shù)
返回緊挨著指定的下標(biāo)之前(PRIOD)或者之后(NEXT)的下標(biāo)值。你應(yīng)該總是用PRIOD和NEXT在集合內(nèi)遍歷，尤其在使用稀疏（或者可能是稀疏）集合時(shí)更是如此
TRIM過(guò)程
從集合的尾部（定義的最大下標(biāo)）移除集合元素
之所以把這些過(guò)程叫做方法，是因?yàn)槭褂眠@些集合內(nèi)置程序的語(yǔ)法不同于調(diào)用過(guò)程和函數(shù)的正規(guī)語(yǔ)法。
5.集合類(lèi)型對(duì)比
6.集合示例
6.1關(guān)聯(lián)數(shù)組示例
set serverout on --開(kāi)啟sqlplus屏幕打印功能 DECLARETYPE nt_foregn_employee IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;--聲明 nt_foregn_employee為關(guān)聯(lián)集合類(lèi)型 相對(duì)于創(chuàng)建一個(gè)集合類(lèi)型--這里由于varchar2(30)類(lèi)型是數(shù)據(jù)庫(kù)自帶的，就不需要我們單獨(dú)創(chuàng)建-- 這里key為binary_integer 類(lèi)型 value 為varchar2(30)vnt_foregn_employees nt_foregn_employee; --聲明 vnt_foregn_employees為 nt_foregn_employee類(lèi)型v_row NUMBER; BEGINvnt_foregn_employees(-230002) := 'SCOTT'; --往 vnt_foregn_employees中添加元素 相當(dāng)于 map(-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方法獲取集合中的一個(gè)行號(hào)v_row := vnt_foregn_employees.first;WHILE (v_row IS NOT NULL) LOOP--遍歷集合中的元素dbms_output.put_line(v_row || ':' || vnt_foregn_employees(v_row));v_row := vnt_foregn_employees.next(v_row);END LOOP; END; /在稀疏集合中，經(jīng)常需要使用NEXT方法遍歷集合，提取數(shù)據(jù)。
查看執(zhí)行結(jié)果，看到遍歷結(jié)果和我們添加結(jié)果一樣
在稀疏集合中，經(jīng)常需要使用NEXT方法遍歷集合，提取數(shù)據(jù)。
6.2嵌套表示例
DECLARETYPE nt_employee IS TABLE OF emp%ROWTYPE;--聲明nt_employee為嵌套表emp集合類(lèi)型vnt_employees nt_employee := nt_employee(); --構(gòu)造函數(shù)顯示初始化c_big_number NUMBER := power(2, 31);l_start_time PLS_INTEGER;CURSOR cur_employee IS --從emp表取數(shù)游標(biāo)SELECT * FROM emp;vrt_employees cur_employee%ROWTYPE;BEGINOPEN cur_employee;LOOPFETCH cur_employeeINTO vrt_employees;EXIT WHEN cur_employee%NOTFOUND;vnt_employees.extend;--嵌套表extend擴(kuò)展一個(gè)元素vnt_employees(vnt_employees.last) := vrt_employees;--添加元素END LOOP;CLOSE cur_employee;--循環(huán)遍歷元素FOR i IN 1 .. vnt_employees.count LOOP--v_data.count表示集合中元素的個(gè)數(shù)--vnt_employees 一條記錄相當(dāng)于一個(gè)實(shí)體,列名相對(duì)于是實(shí)體屬性，通過(guò)點(diǎn)的方式獲取dbms_output.put_line('empnofrom:' || vnt_employees(i).empno ||' ename:' || vnt_employees(i).ename || ' deptno:' || vnt_employees(i).deptno);--把滿足條件的客戶信息打印到屏幕END LOOP;dbms_output.put_line('嵌套表vnt_employees中元素個(gè)數(shù):'||vnt_employees.count); -- 打印集合的元素總數(shù) END; / --sqlplus執(zhí)行過(guò)程命令執(zhí)行結(jié)果
6.3VARRAY實(shí)例
--創(chuàng)建集合類(lèi)型nt_course 用于存放學(xué)生課程表 create or replace type nt_course is varray(5) of varchar2(100); / --創(chuàng)建表students，表中引用了可變集合類(lèi)型nt_course 存放學(xué)生和課程信息 create table students( student_name varchar2(20) , cource nt_course);declare vnt_nt_courses nt_course := nt_course();--構(gòu)造函數(shù)初始化begin vnt_nt_courses.extend(2);-- 集合增加2個(gè)元素 vnt_nt_courses(1) := 'English';--跟一維數(shù)組添加元素一樣 vnt_nt_courses(2) := 'Chinese';-- 把集合數(shù)據(jù)添加到students表中 insert into students (student_name, cource) values ('chiclewu', vnt_nt_courses); commit;-- 提交事務(wù) end; / select * from table (select s.cource from students s);– 可以使用TABLE函數(shù)把一個(gè)集合映射成數(shù)據(jù)庫(kù)表.例如，要獲取student表中課程列的記錄。
這里用table的原因就是集合是不能顯示的，
SELECT t.student_name, t.cource FROM students t;看到，通過(guò)集合元素可以實(shí)現(xiàn)在同一表中一條記錄對(duì)應(yīng)多條記錄。
我們可以認(rèn)為其實(shí)這里是兩個(gè)表，
students表中存放學(xué)生和選課信息，而選課信息就是我們所說(shuō)的集合 也相當(dāng)于是一個(gè)表
本文轉(zhuǎn)自：https://www.2cto.com/database/201312/264383.html
總結(jié)
以上是生活随笔為你收集整理的oracle集合类型详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： WebStorm安装方法
- 下一篇： R语言安装第三方包