ORACLE中RECORD、VARRAY、TABLE的使用詳解，oraclevarray

ORACLE中RECORD、VARRAY、TABLE的使用詳解，oraclevarray
1     說明1.1       RECORD

定義記錄資料類型。它類似於C語言中的結構資料類型(STRUCTURE)，PL/SQL提供了將幾個相關的、分離的、基礎資料型別 (Elementary Data Type)的變數組成一個整體的方法，即RECORD複合資料型別。在使用記錄資料類型變數時，需要在聲明部分先定義記錄的組成、記錄的變數，然後在執行部分引用該記錄變數本身或其中的成員。

   定義記錄資料類型的文法如下：

TYPE RECORD_NAME IS RECORD(

V1 DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],

V2 DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],

VN DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);

1.2       VARRAY

數組是具有相同資料類型的一群組成員的集合。每個成員都有一個唯一的下標，它取決於成員在數組中的位置。在PL/SQL中，數組資料類型是VARRAY（variable array，即可變數組）。

定義VARRAY資料類型的文法如下：

TYPE VARRAY_NAMEIS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];

其中，varray_name是VARRAY資料類型的名稱，size是正整數，表示可以容納的成員的最大數量，每個成員的資料類型是element_typeo預設時，成員可以取空值，否則需要使用NOT NULL加以限制。

 

1.3       TABLE

定義記錄表（或索引表）資料類型。它與記錄類型相似，但它是對記錄類型的擴充。它可以處理多行記錄，類似於C語言中的二維數組，使得可以在PL/SQL中模仿資料庫中的表。

定義記錄表類型的文法如下：

TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]

INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];

關鍵字INDEX BY表示建立一個主鍵索引，以便引用記錄表變數中的特定行。

BINARY_INTEGER的說明

如語句：TYPE NUMBERS  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”後，NUMBERS類型的下標就是自增長，NUMBERS類型在插入元素時，不需要初始化，不需要每次EXTEND增加一個空間。

而如果沒有這句話“INDEXBY BINARY_INTEGER”，那就得要顯示對初始化，且每插入一個元素到NUMBERS類型的TABLE中時，都需要先EXTEND。

 

2     舉例2.1       建立表結構以及資料準備

 

--組織機構結構表CREATE TABLE SF_ORG(ORG_ID INT NOT NULL, --組織機構主鍵IDORG_NAME VARCHAR2(50),--組織機構名稱PARENT_ID INT--組織機構的父級)--一級組織機構INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一級部門1',0);--二級部門INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二級部門2',1);INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二級部門3',1);INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二級部門4',1);

2.2       RECORD的使用舉例

先定義一個只與SF_ORG表中某幾個列的資料類型相同的記錄資料類型TYPE_ORG_RECORD，然後聲明一個該資料類型的記錄變數V_ORG_RECORD，最後用替換變數&ORG_ID接受輸入的僱員編碼，查詢並顯示該僱員的這幾列中的資訊。注意，在使用RECORD資料類型的變數時要用“.”運算子指定記錄變數名限定詞。

   一個記錄類型的變數只能儲存從資料庫中查詢出的一行記錄，如果查詢出了多行記錄，就會出現錯誤。

 

DECLARE   TYPE TYPE_ORG_RECORD IS RECORD(  V_ORG_NAME SF_ORG.ORG_NAME%TYPE,  V_PARENT_ID SF_ORG.PARENT_ID%TYPE);  V_ORG_RECORD TYPE_ORG_RECORD;BEGIN  SELECT ORG_NAME,PARENT_ID INTO V_ORG_RECORD  FROM SF_ORG SO  WHERE SO.ORG_ID=&ORG_ID;  DBMS_OUTPUT.PUT_LINE('部門名稱：' || V_ORG_RECORD.V_ORG_NAME);  DBMS_OUTPUT.PUT_LINE('上層業務編碼：' || TO_CHAR(V_ORG_RECORD.V_PARENT_ID));END;

2.3       VARRAY的使用舉例

先定義一個能儲存5個VARCHAR2(25)資料類型的成員的VARRAY資料類型ORG_VARRAY_TYPE，然後聲明一個該資料類型的VARRAY變數V_ORG_VARRAY，最後用與ORG_VARRAY_TYPE資料類型同名的建構函式文法給V_ORG_VARRAY變數賦予初值並顯示賦值結果。

注意，在引用數組中的成員時．需要在一對括弧中使用順序下標，下標從1開始而不是從0開始。

 

DECLARE   TYPE ORG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR2(25);  V_ORG_VARRAY ORG_VARRAY_TYPE;BEGIN  V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5');  DBMS_OUTPUT.PUT_LINE('輸出1：' || V_ORG_VARRAY(1) || '、'|| V_ORG_VARRAY(2) || '、'|| V_ORG_VARRAY(3) || '、'|| V_ORG_VARRAY(4));  DBMS_OUTPUT.PUT_LINE('輸出2：' || V_ORG_VARRAY(5));  V_ORG_VARRAY(5) := '5001';  DBMS_OUTPUT.PUT_LINE('輸出3：' || V_ORG_VARRAY(5));END;

2.4       TABLE使用舉例2.4.1      儲存單列多行

這個和VARRAY類似。但是賦值方式稍微有點不同，不能使用同名的建構函式進行賦值。具體的如下：

 

DECLARE   TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25)  INDEX BY BINARY_INTEGER;  V_ORG_TABLE ORG_TABLE_TYPE;BEGIN  V_ORG_TABLE(1) := '1';  V_ORG_TABLE(2) := '2';  V_ORG_TABLE(3) := '3';  V_ORG_TABLE(4) := '4';  V_ORG_TABLE(5) := '5';  DBMS_OUTPUT.PUT_LINE('輸出1：' || V_ORG_TABLE(1) || '、'|| V_ORG_TABLE(2) || '、'|| V_ORG_TABLE(3) || '、'|| V_ORG_TABLE(4));  DBMS_OUTPUT.PUT_LINE('輸出2：' || V_ORG_TABLE(5));END;

2.4.2      儲存多列多行和ROWTYPE結合使用

採用bulkcollect可以將查詢結果一次性地載入到collections中。而不是通過cursor一條一條地處理。

 

DECLARE    TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE;   V_TYPE  T_TYPE; BEGIN    SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE    FROM SF_ORG    WHERE SF_ORG.ORG_ID <= 3;    FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP        DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);    END LOOP; END;

2.4.3      儲存多列多行和RECORD結合使用

採用bulkcollect可以將查詢結果一次性地載入到collections中。而不是通過cursor一條一條地處理。

 

DECLARE    TYPE TEST_EMP IS RECORD   (    C1 SF_ORG.ORG_NAME%TYPE,    C2 SF_ORG.PARENT_ID%TYPE   );      TYPE T_TYPE IS TABLE OF TEST_EMP;   V_TYPE  T_TYPE; BEGIN    SELECT ORG_NAME,  PARENT_ID BULK COLLECT INTO V_TYPE    FROM SF_ORG    WHERE SF_ORG.ORG_ID <= 3;    FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP        DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);    END LOOP; END;

3     問題

VARRAY和TABLE集合不能直接對其進行查詢。只能對其進行遍曆。

oracle的table與array與record類型常用？？

不常用, 畢竟東西寫得越複雜, 調試和後期的移交工作也就越複雜.
實際上, 一個設計良好的數學模型可以使一切變得很簡單.
 
怎操縱oracle表中的巢狀表格列，varray列與物件類型列

恩，先給你說更新的問題，一次可以更新和插入一筆記錄，也可以大量繫結，多筆更新和插入。
給你舉個插入資料的例子（多筆記錄插入），其它自己琢磨去。其它的細節方面自己去處理吧。

my_temp emp_type；--定義集合變數
begin
for i in 1..10 loop ---初始化賦值
my_temp(i):=i; ---初始化賦值
end loop; ---初始化賦值
forall i in 1..my_temp.count ---insert你要的table裡 （這可不是迴圈哦是批量處理）
insert into employee ---insert你要的table裡
values (my_temp(i)) ---insert你要的table裡

end；

其它的，都一個模式