PL/SQL複合資料型別

[Ref: ]

PL/SQL 記錄　　記錄是PL/SQL的一種複合資料結構，scalar資料型別和資料型別只是簡單的在包一級進行預定義，但複合資料型別在使用前必須被定義，記錄之所以被稱為複合資料型別是因為他由域這種由資料元素的邏輯組所組成。域可以是scalar資料型別或其他記錄型別，它與c語言中的結構相似，記錄也可以看成表中的資料行，域則相當於表中的列，在表和虛擬表（檢視或查詢）中非常容易定義和使用，行或記錄中的每一列或域都可以被引用或單獨賦值，也可以透過一個單獨的語句引用記錄所有的域。在儲存過程或函式中記錄也可能有引數。

　　建立記錄

　　在PL/SQL中有兩種定義方式：顯式定義和隱式定義。一旦記錄被定義後，宣告或建立定義型別的記錄變數，然後才是使用該變數。隱式宣告是在基於表的結構或查詢上使用%TYPE屬性，隱式宣告是一個更強有力的工具，這是因為這種資料變數是動態建立的。

　　顯式定義記錄

　　顯式定義記錄是在PL/SQL程式塊中建立記錄變數之前在宣告部分定義。使用type命令定義記錄，然後在建立該記錄的變數。語法如下：

　　. 可以使用資料庫物件，資料庫物件可以使用order或map方法定義，允許oracle對複合資料型別進行比較。關於資料庫物件的討論已經超越了本文的範圍，要詳細瞭解資料庫物件，可以查閱oracle手冊。

PL/SQL集合　　集合與其他語言中的陣列相似，在ORACLE7.3及以前的版本中只有一種集合稱為PL/SQL表，這種型別的集合依然保留，就是索引（INDEX_BY）表，與記錄相似，集合在定義的時候必須使用TYPE語句，然後才是建立和使用這種型別的變數。

　　集合的型別

　　PL/SQL有三種型別的集合

　　. Index_by表

　　. 巢狀表

　　. VARRAY

　　這三種型別的集合之間由許多差異，包括資料繫結、稀疏性(sparsity)、資料庫中的儲存能力都不相同。繫結涉及到集合中元素數量的限制，VARRAY集合中的元素的數量是有限，Index_by和巢狀表則是沒有限制的。稀疏性描述了集合的下標是否有間隔，Index_by表總是稀疏的，如果元素被刪除了巢狀表可以是稀疏的，但VARRAY型別的集合則是緊密的，它的下標之間沒有間隔。

　　Index_by表不能儲存在資料庫中，但巢狀表和VARRAY可以被儲存在資料庫中。

　　雖然這三種型別的集合有很多不同之處，但他們也由很多相似的地方：

　　　. 都是一維的類似陣列的結構

　　　. 都有內建的方法

　　　. 訪問由點分隔

　　Index_by表

　　Index_by表集合的定義語法如下：

　　這裡面重要的關鍵字是INDEX BY BINARY_INTERGET，沒有這個關鍵字，那麼集合將是一個巢狀表，element_type可以是任何合法的PL/SQL資料型別，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合型別對資料庫的資料型別都有限制，但Index_by表不能儲存在資料庫中，所以沒有這些限制。

　　一旦定義了index_by表，就可以向建立其他變數那樣建立index_by表的變數：

　　巢狀表

　　巢狀表非常類似於Index_by表，建立的語法也非常相似。使用TYPE語句，只是沒有INDEX BY BINARY_INTEGER子串。

　　TYPE type_name IS TABLE OF element_type [NOT NULL]

　　NOT NULL選項要求集合所有的元素都要有值，element_type可以是一個記錄，但是這個記錄只能使用標量資料型別欄位以及只用於資料庫的資料型別（不能是PLS_INTEGER,BOOLEAN或SIGNTYPE）。

　　巢狀表和VARRAY都能作為列儲存在資料庫表中，所以集合自身而不是單個的元素可以為NULL,ORACLE稱這種整個集合為NULL的為"自動設定為NULL(atomically NULL)"以區別元素為NULL的情況。當集合為NULL時，即使不會產生異常，使用者也不能引用集合中的元素。使用者可以使用IS NULL***作符檢測集合是否為NULL。

　　儲存在一個資料庫中的巢狀表並不與表中的其它資料存放在同一個資料塊中，它們實際上被存放在第二個表中。正如沒有order by子句select語句不能保證返回任何有順序的資料，從資料庫中取回的巢狀表也不保證元素的順序。由於集合資料是離線儲存的，對於大型集合巢狀表是一個不錯的選擇。

　　VARRAY

　　VARRAY或資料變數都有元素的限制。想起他集合一樣VARRAY定義仍然使用TYPE語句，但關鍵字VARRAY或VARRYING ARRAY告訴ORACLE這是一個VARRAY集合。

　　max_size是一個整數，用於標示VARRAY集合擁有的最多元素數目。VARRAY集合的元素數量可以低於max_size,但不能超過max_size。element_type是一維元素的資料型別，如果element_type是記錄，那麼這個記錄只能使用標量資料欄位（與巢狀標相似）。NOT NULL子串表示集合中的每一個元素都必須有值。

　　與巢狀表相似，VARRAY能夠自動為NULL,可以使用IS NULL***作符進行檢測。與巢狀表不同的是，當VARRAY儲存在資料庫中時與表中的其他資料存放在同一個資料塊中。正象列的排序儲存在表的SELECT*中一樣元素的順序儲存在VARRAY中。同樣由於集合是線上儲存的，VARRAY很適合於小型集合。

使用集合　　象記錄一樣，集合可以在兩個層面上使用：

　　　. ***作整個集合

　　　. 訪問集合中的單個元素

　　第一種情況使用集合名，第二種情況使用下標：

　　　collection(subscrīpt)

　　index_by表的下標是兩為的整數，可以為正也可以為負，範圍是：-2147483647--2147483647。巢狀表和VARRAY表示元素在集合中的位置，使用者很難靈活設計下標，這是因為：

　　　. 巢狀表開始是緊密的（相對於疏鬆）

　　　. VARRAY始終保持緊密

　　　. 這兩種集合的下標都由1開始

　　初始化、刪除、引用集合

　　使用集合之前必須要初始化，對於Index_by表初始化是自動進行的，但是對於巢狀表和VARRAY就必須使用內建的建構函式。如果重新呼叫，巢狀表和VARRAY自動置NULL，這不只是元素置NULL，而是整個集合置NULL。給集合內的元素賦值需要使用下標符號。將一個集合的值賦給另一個集合，只需要簡單的使用賦值***作符。

　　Index_by集合初始化是最簡單的，只要涉及其中的一個元素集合就被初始化了。

　　例：　

　　巢狀表和VARRAY由建構函式初始化，建構函式和集合的名字相同，同時有一組引數，每個引數對應一個元素，如果引數為NULL,那麼對應的元素就被初始化為NULL,如果建立了元素，但沒有填充資料，那麼元素將保持null值，可以被引用，但不能保持資料。如果元素沒有初始化，那麼就不能引用該元素。

　　例：

　　在這個例子中，巢狀表BIOTECH_STOCKS初始化有5個元素，VARRAY tech_10集合最多能有10 個元素，但建構函式只建立了8個元素，其中還有兩個元素是NULL值，並程式中給他們賦值。

　　初始化基於記錄的集合，就必須將記錄傳遞給建構函式，注意不能只是簡單的將記錄的域傳遞給建構函式。

　　例：

集合的方法　　除了建構函式外,集合還有很多內建函式，這些函式稱為方法。呼叫方法的語法如下：

　　　collection.method

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

　　關於集合之間的比較

　　集合不能直接用於比較，要比較兩個集合，可以設計一個函式，該函式返回一個標量資料型別。

　　但可以比較在集合內的兩個元素。

使用正確的迴圈控制 [Ref: ]

當你在 PL/SQL 中處理一個被索引的表時，很多情況下你不能確定表上是否存在索引，所以你不能使用最明顯的 FOR 迴圈方式來迴圈表中的值。例如：

Declare
type my list_type is table of number index by pls_integer;
mylist mylist_type;
begin
-- start at 2, instead of 1
mylist(2) := 2;
mylist(3) := 3;
-- skip 4 and 5
mylist(6) := 6;
for i in 1..mylist.count loop
dbms_output.put_line(mylist(i));
end loop;
end;
/

前面的程式碼將返回 ORA-01403 錯誤：由於缺少索引1，沒有發現資料。

如果你嘗試使用 .FIRST/.LAST 修正這個問題，那麼你將自動地從第一個索引開始迴圈，直到到達最後一個索引，如下：

Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(5) := 6;
for i in mylist.first .. mylist.last loop
dbms_output.put_lin(mylist(i));
end loop;
end;
/
然而

然而，你依然得到一個 ORA-01403 錯誤：由於跳過值，沒有發現資料。要進一步避免這種錯誤，你可以在訪問索引前使用 EXISTS 方法測試它是否存在：

Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
for i in mylist.first..mylist.last loop
if (mylist.exists(i)) then
dbms_output.put_line(mylist(i));
end if;
end loop;
end;
/

或者，你可以透過.NEXT 屬性使用常規的 LOOP 迴圈來迭代一個表上存在的索引——但是你必須宣告迴圈計數器：

Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
i pls_integer;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
i := mylist.first;
loop
dbms_output.put_line(mylist(i));
exit when i = mylist.last;
i := mylist.next(i);
end loop;
end;
/

當你不確定哪些值已經被刪除或裝載而導致缺少索引時，可以使用上面這兩個有效的方法作為預設方法。但是對於使用 FORALL 語句的大量***作，這兩種方法都不能正確執行，原因是 FORALL 語句並不是一個真正的迴圈，而且其語法只能帶一個下加界，一個上邊界和一個 SQL DML 語句。

然而，Oracle 10g中引入了兩個新子句，這兩個子句允許你避免這種侷限性。向 FORALL 語法新增一個 INDICES OF 子句允許你自動地迴圈一個表中的所有值，而無須擔心索引是否存在：

Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
foralli in indices of mylist
insert into mynumtable values(mylist(i));
end;
/

另外還有一個 valueS OF 子句，該子句使用你的巢狀表的值，或者被表索引的值作為迴圈的下標：

Declare
type mysubs_type is table of pls_integer index by pls_integer;
type mylist_type is table of number index by pls_integer;
mysubs mylist_type;
mylist mylist_type;
begin
mylist(2) := 2;
mysubs(10) := 2; -- point to mylist(2)
mylist(3) := 3;
mysubs(20) := 3; -- point to mylist(3)
mylist(6) := 6;
mysubs(30) := 6; -- point to mylist(6)
mysubs(40) := 3; -- point to mylist(3), again!
foralli in values ofmysubs
insert into mynumtable values(mylist(i));
end;
/

注意這個語法允許你控制對一個特定記錄的順序訪問，甚至重複訪問或者略過對它的訪問。