全面探討PL/SQL的複合資料型別(轉)[@more@]

　　PL/SQL有兩種複合資料結構：記錄和集合。記錄由不同的域組成，集合由不同的元素組成。在本文中我們將討論記錄和集合的型別、怎樣定義和使用記錄和集合。

　　PL/SQL 記錄

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

　　建立記錄

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

　　顯式定義記錄

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

　　TYPE record_type IS RECORD (field_definition_list);

　　field_definition_list是由逗號分隔的列表。

　　域定義的語法如下：

　　field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]

　　域名必須服從與表或列的命名規則相同的命名規則。下面我們看一個例子：

　　DELCARETYPE stock_quote_rec IS RECORD(symbol stock.symbol%TYPE,bid NUMBER(10,4),ask NUMBER(10,4),volume NUMBER NOT NULL:=0,exchange VARCHAR2(6) DEFAULT 'NASDAQ');real_time_quote stock_quote_rec;variable

　　域定義時的%TYPE屬性用於引用資料庫中的表或檢視的資料型別和大小,而在此之前程式不知道型別和大小。在上面的例子中記錄域在編譯時將被定義為與列SYMBOL相同的資料型別和大小,當程式碼中要使用來自資料庫中的資料時，在變數或域定義中最好使用%TYPE來定義。

　　隱式定義記錄

　　隱式定義記錄中，我們不用描述記錄的每一個域。這是因為我們不需要定義記錄的結構，不需要使用TYPE語句，相反在宣告記錄變數時使用%ROWTYPE命令定義與資料庫表，檢視，遊標有相同結構的記錄，與TYPE命令相同的是它是一種定義獲得資料庫資料記錄的好方法。

　　DECLAREaccounter_info accounts%ROWTYPR;CURSOR xactions_cur(acct_no IN VARCHAR2) ISSELECT action,timestamp,holdingFROM portfoliosWHERE account_nbr='acct_no';xaction_info xactions_cur%ROWTYPE;variable

　　有一些PL/SQL指令在使用隱式定義記錄時沒有使用%ROWTYPE屬性，比如遊標FOR迴圈或觸發器中的:old和:new記錄。

　　DELCARECURSOR xaction_cur ISSELECT action,timeamp,holdingFROM portfoliosWHERE account_nbr='37';BEGINFOR xaction_rec in xactions_curLOOPIF xactions_rec.holding='ORCL'THENnotify_shareholder;END IF;END LOOP;

　　使用記錄

　　使用者可以給記錄賦值、將值傳遞給其他程式。記錄作為一種複合資料結構意味作他有兩個層次可用。使用者可以引用整個記錄，使用select into或fetch轉移所有域，也可以將整個記錄傳遞給一個程式或將所有域的值賦給另一個記錄。在更低的層次，使用者可以處理記錄內單獨的域，使用者可以給單獨的域賦值或者在單獨的域上執行布林表示式，也可以將一個或更多的域傳遞給另一個程式。

　　引用記錄

　　記錄由域組成，訪問記錄中的域使用點（.）符號。我們使用上面的例子看看

　　DELCARETYPE stock_quote_rec IS RECORD(symbol stock.symbol%TYPE,bid NUMBER(10,4),ask NUMBER(10,4),volume NUMBER NOT NULL:=0,exchange VARCHAR2(6) DEFAULT 'NASDAQ');TYPE detailed_quote_rec IS RECORD(quote stock_quote_rec,timestamp date,bid_size NUMBER,ask.size NUMBER,last_tick VARCHAR2(4));real_time_detail detail_quote_rec;BEGINreal_time_detail.bid_size:=1000;real_time_detail.quote.volume:=156700;log_quote(real_time_detail.quote);

　　給記錄賦值

　　給記錄或記錄中的域賦值的方法有幾種，可以使用SELECT INTO或FETCH給整個記錄或單獨的域賦值，可以將整個記錄的值賦給其他記錄，也可以透過給每個域賦值來得到記錄，以下我們透過例項講解每一種賦值方法。

　　1、使用SELECT INTO

　　使用SELECT INTO給記錄賦值要將記錄或域放在INTO子串中，INTO子串中的變數與SELECT中列的位置相對應。

　　例：

　　DECLAREstock_info1 stocks%ROWTYPE;stock_info2 stocks%ROWTYPE;BEGINSELECT symbol,exchangeINTO stock_info1.symbol,stock_info1.exchangeFROM stocksWHERE symbol='ORCL';SELECT * INTO stock_info2 FROM stocksWHERE symbol='ORCL';

　　2、使用FETCH

　　如果SQL語句返回多行資料或者希望使用帶引數的遊標，那麼就要使用遊標，這種情況下使用FETCH語句代替INSTEAD INTO是一個更簡單、更有效率的方法，但在安全性較高的包中FETCH的語法如下：

　　FETCH cursor_name INTO variable;

　　我們改寫上面的例子：

　　DECLARECURSOR stock_cur(symbol_in VARCHAR2) ISSELECT symbol,exchange,begin_dateFROM stockWHERE symbol=UPPER(symbol_in);stock_info stock_cur%ROWTYPEBEGINOPEN stock_cur('ORCL');FETCH stock_cur INTO stock_info;

　　使用賦值語句將整個記錄複製給另一個記錄是一項非常有用的技術，不過記錄必須精確地被宣告為相同的型別，不能是基於兩個不同的TYPE語句來獲得相同的結構。

　　例：

　　DECLARETYPE stock_quote_rec IS RECORD(symbol stocks.symbol%TYPE,bid NUMBER(10,4),ask number(10,4),volume NUMBER);TYPE stock_quote_too IS RECORD(symbol stocks.symbol%TYPE,bid NUMBER(10,4),ask number(10,4),volume NUMBER);--這兩個記錄看上去是一樣的，但實際上是不一樣的stock_one stocks_quote_rec;stock_two stocks_quote_rec;--這兩個域有相同的資料型別和大小stock_also stock_rec_too；--與stock_quote_rec是不同的資料型別BEGINstock_one.symbol:='orcl';stock_one.volume:=1234500;stock_two：=stock_one;--正確syock_also：=stock_one;--錯誤，資料型別錯誤stock_also.symbol:=stock_one.symbol;stock_also.volume:=stock_one.volume;

　　記錄不能用於INSERT語句和將記錄直接用於比較，下面兩種情況是錯誤的：

　　INSERT INTO stocks VALUES (stock_record);

　　和

　　IF stock_rec1>stock_rec2 THEN

　　要特別注意考試中試題中有可能用%ROWTYPE來欺騙你，但這是錯誤的，記住這一點。還有可能會出現用記錄排序的情況，ORACLE不支援記錄之間的直接比較。對於記錄比較，可以採用下面的兩個選擇：

　　. 設計一個函式，該函式返回scalar資料型別，使用這個函式比較記錄，如

　　IF sort_rec(stock_one)>sort_rec(stock_two) THEN

　　. 可以使用資料庫物件，資料庫物件可以使用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表集合的定義語法如下：

　　TYPE type_name IS TABLE OF element_type [NOT NULL] INDEXBY BINARY_INTERGET;

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

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

　　DECLARETYPE symbol_tab_typ IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;symbol_tab symbol_tab_typ;BEGIN

　　巢狀表

　　巢狀表非常類似於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集合。

　　TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OFelement_type [NOT NULL]

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

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

　　使用集合

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

　　. 操作整個集合

　　. 訪問集合中的單個元素

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

　　collection(subscript)

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

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

　　. VARRAY始終保持緊密

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

　　初始化、刪除、引用集合

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

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

　　例：　

　　DECLARETYPE symbol_tab_typ IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;TYPE account_tab_typ IS TABLE OF account%ROWTYPE INDEX BY BINARY_INTEGER;symbol_tab symbol_tab_typ;account_tab account_tab_typ;new_acct_tab account_tab_typ;BEGIN--初始化集合元素147和-3SELECT * INTO account_tab(147)FROM accounts WHERE account_nbr=147;SELECT * INTO account_tab(-3)FROM accounts WHERE account_nbr=3003;IF account_tab(147).balance<500 THENchang_maintenance_fee(147);END IFnew_acct_tab:=account_tab;symbol_tab(1):="ORCL";symbol_tab(2):="CSCO";symbol_tab(3):="SUNM";publish_portfolio(symbol_tab);

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

　　例：

　　DECLARETYPE stock_list IS TABLE OF stock.symbol%TYPE;TYPE top10_list IS VARRAY (10) OF stocks.symbol%TYPE;biotech_stocks stock_list;tech_10 top10_list;BEGIN--非法，集合未初始化。biotech_stocks(1):='AMGN';IF biotech_stocks IS NULL THEN--初始化集合biotech_stocks：=('AMGN','BGEN',IMCL','GERN',CRA');END IF;tech_10:=top10_list('ORCL',CSCO','MSFT','INTC','SUNW','IBM',NULL,NULL);IF tech_10(7) IS NULL THENtech_10(7):='CPQ';ENDtech_10(8):='DELL';

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

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

　　例：

　　DECLARETYPE stock_quote_rec IS RECORD(symbol stock.symbol%TYPE,bid NUMBER(10,4),ask NUMBER(10,4),volume NUMBER NOT NULL:=0);TYPE stock_tab_typ IS TABLE OF stock_quote_rec;quote_list stock_tab_typ;single_quote stock_quote_rec;BEGINsingle_quote.symbol:='OPCL';single_quote.bid:=100;single_quote.ask:=101;single_quote.volume:=25000;--合法quote_list:=stock_tab_typ(single_quote);--不合法quote_list:=stock_tab_typ('CSCO',75,76,3210000);DBMS_OUTPUT.LINE(quote_list(1).bid);

集合的方法

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

　　collection.method

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10748419/viewspace-954156/，如需轉載，請註明出處，否則將追究法律責任。