oracle中變長數組varray,巢狀表格,集合使用方法_oracle

建立變長數群組類型

CREATE TYPE varray_type AS VARRAY(2) OF VARCHAR2(50); 

這個變長數組最多可以容納兩個資料，資料的類型為 varchar2(50)

更改元素類型的大小或精度

可以更改變長數群組類型和巢狀表格類型 元素的大小。

ALTER TYPE varray_type MODIFY ELEMENT TYPE varchar2(100) CASCADE;

CASCADE選項吧更改傳播到資料庫中的以來對象。也可以用 INVALIDATE 選項使依賴對象無效

增加變長數組的元素數目

ALTER TYPE vrray_nameMODIFY LIMIT 5 CASCADE;

使用變長數組

CREATE TABLE table_name(column_name type,var_col_name varray_type);

獲得變長數組的資訊

DESC[RIBE] varray_type;SELECT * FROM user_varraysWHERE type_name = varray_name;

DESC 獲得的是 varray_type AS VARRAY(2) OF VARCHAR2(50)

填充變長數組元素

INSERT INTO table_name VALUES( value, varray_type(  'xxxx',  'xx',  'x'));

可以一次向變長數組添加多個資料。

尋找變長數組元素

SELECT *FROM table_Name;

如果變長數組中的元素有多個，會一起輸出，輸出的資料列是一個長列，跟包含對象的表一樣。

更改變長數組元素

要想更改變長數組的一個元素，需要把其他元素一起更改，整個變長數組作為一個整體來的。

UPDATE table_nameSET var_col_name = varray_type('xxx','xxxxxx')WHERE expr1;

建立巢狀表格類型

CREATE TYPE table_type AS TABLE OF type;

其中type 可以為任何類型，包括varray 和 object ，通常object 居多。

使用巢狀表格類型

CREATE TABLE table_name( column_name type, tab_col_name table_type)NESTED TABLE table_col_nameSTORE AS next_table_name [TABLESPACE user_name];

建立巢狀表格的時候要為巢狀表格類型另外建立一個表來儲存資料， NESTED 以下的部分就是在幹這事。那個表的名稱為: next_table_name

TABLESPACE 可以將另外建立表放到另外的空間。

獲得表資訊

SET DESCRIBE DEPTH 2DESC[RIBE] table_name;

也可以直接通過資料字典來獲得巢狀表格的資訊

SELECT *FROM user_nested_tablesWHERE table_name = xxxx;

填充、尋找巢狀表格元素

跟變長數組方法一樣

更改巢狀表格元素

跟變長數組不同，巢狀表格的元素可以單獨更改：可以插入、更改和刪除巢狀表格元素。

插入：

INSERT INTO TABLE( SELECT tab_col_name FROM table_name WHERE expr) VALUES(  table_type('xxxx') ));

更改：

UPDATE TABLE( SELECT tab_col_name FROM table_name WHERE expr) TSET VALUE(T) = table_type(  'xxxx'))WHERE  VALUE(T) = table_type(  expr2  );

T 為獲得需要修改的那行資料對應的 巢狀表格位置，WHERE 為判斷語句，如果table_type 為object類型，expr2 這樣寫： 'x','xx','xxx'

刪除:

DELETE FROM TABLE( SELECT tab_col_name FROM table_name WHERE expr)TWHERE VALUE(T) = table_type(  expr2 );

集合方法

EXISTS(N)	如果第n個元素存在，返回TRUE
COUNT	該函數集合元素的數目
DELETE DELETE(n) DELETE(n,m)	刪除集合元素 l 刪除所有元素 l 刪除第n個元素 l 刪除n到m的元素
FIRST	返回集合第一個（最小的）元素索引號，如果集合為空白，返回NULL
LAST	返回集合中最後一個（最大的）元素索引號，如果集合為空白，返回NULL
NEXT(n)	返回集合當前元素的下n元素的索引號，如果它不存在就返回NULL
PRIOR(n)	返回集合當前元素的前n元素的索引號，如果它不存在就返回NULL
LIMIT	返回varray中建立元素的最大個數
EXTEND EXTEND(n) EXTEND(n,m)	增加集合的大小。 l 添加一個，設為空白 l 添加n個，設為空白 l 添加n個，設為m
TRIM TRIM(n)	從集合末尾處刪除元素 l 刪除一個 l 刪除n個

調用方法是： tab_col_name.COUNT

巢狀表格運算子操作

例如：

var_tab_1 table_type;
var_tab_2 table_type;
var_tab_3 table_type;
reslut BOOLEAN;

var_tab_1 :=table_type('1','2');
var_tab_2 :=table_type('3','4');
var_tab_3 :=table_type('2','1');

result:= var_tab_1 =var_tab_3  result 為true;
result:= var_tab_2 <>var_tab_3  result 為true;

IN 和 NOT IN 運算子
用於檢測一個巢狀表格的內容是否出現在令一個巢狀表格的內容中。
result:= var_tab_1 IN (var_tab_3); result 為 TRUE;
result:= var_tab_2 NOT IN (var_tab_3); result 為 TRUE;

SUBMULITSET 子集運算子
檢查一個巢狀表格的內容是否為另外一個巢狀表格的子集
result:= var_tab_1 SUBMULITSET OF var_tab_3 result 為TRUE;

MULTISET 集合運算子
返回的是一個嵌套集
MULTISET UNION
MULTISET INTERSECT
MULTISET EXCEPT
並 交 差 ，另外還有
ALL 全部
DISTINCT 去重
var_tab1 := var_tab2 MUSTISET UNION ALL var_tab2

CARDINALITY 獲得巢狀表格中元素數目
CARDINALITY(var_tab1)
（跟count 有什麼不同。。- -！）

MEMBER OF 運算子
檢測巢狀表格的一個元素是否存在
'xxx' MEMBER OF var_tab1; 返回BOOLEAN

SET 運算子
將傳入的巢狀表格去重後返回
var_tab1 := SET (var_tab2);

IS A SET
判斷時候符合每個元素都不同
result:= var_tab1 IS A SET;

IS EMPTY
判斷巢狀表格是否為空白

COLLECT 運算子
將值列表作為巢狀表格返回，可以配合 CAST 運算子將返回的巢狀表格強制轉換為一種巢狀表格類型。

SELECT COLLECT(column_name)FROM TABLEWHERE expr;

POWERMULTISET

獲得巢狀表格的子巢狀表格

SELECT *FROM TABLE( POWERMULTISET(table_tpye('1','2')));

獲得

複製代碼 代碼如下:

table_type('1')
table_type('2')
table_type('1','2')

注意：PS/SQL 不支援這個

POWERMULTISET_BY_CARDINALITY

獲得指定長度以下的巢狀表格

SELECT *FROM TABLE( POWERMULTISET_BY_CARDINALITY(table_tpye('1','2'),2));

獲得

複製代碼 代碼如下:

table_type('1','2')

PS/SQL 不支援這個