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]
域名必須服從與表或列的命名規則相同的命名規則。下面我們看一個例子:
DELCARE
TYPE 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命令相同的是它是一種定義獲得數據庫數據記錄的好方法。
DECLARE
accounter_info accounts%ROWTYPR;
CURSOR xactions_cur(acct_no IN VARCHAR2) IS
SELECT action,timestamp,holding
FROM portfoliOS
WHERE account_nbr='acct_no'
;
xaction_info xactions_cur%ROWTYPE;
variable
有一些PL/SQL指令在使用隱式定義記錄時沒有使用%ROWTYPE屬性,比如游標FOR循環或觸發器中的:old和:new記錄。
DELCARE
CURSOR xaction_cur IS
SELECT action,timeamp,holding
FROM portfoliOS
WHERE account_nbr='37'
;
BEGIN
FOR xaction_rec in xactions_cur
LOOP
IF xactions_rec.holding='ORCL'
THEN
notify_shareholder;
END IF;
END LOOP;
使用記錄
用戶可以給記錄賦值、將值傳遞給其他程序。記錄作為一種復合數據結構意味作他有兩個層次可用。用戶可以引用整個記錄,使用select into或fetch轉移所有域,也可以將整個記錄傳遞給一個程序或將所有域的值賦給另一個記錄。在更低的層次,用戶可以處理記錄內單獨的域,用戶可以給單獨的域賦值或者在單獨的域上運行布爾表達式,也可以將一個或更多的域傳遞給另一個程序。
引用記錄
記錄由域組成,訪問記錄中的域使用點(.)符號。我們使用上面的例子看看
DELCARE
TYPE 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;
BEGIN
real_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中列的位置相對應。
例:
DECLARE
stock_info1 stocks%ROWTYPE;
stock_info2 stocks%ROWTYPE;
BEGIN
SELECT symbol,Exchange
INTO stock_info1.symbol,stock_info1.Exchange
FROM stocks
WHERE symbol='ORCL';
SELECT * INTO stock_info2 FROM stocks
WHERE symbol='ORCL';
2、使用FETCH
如果SQL語句返回多行數據或者希望使用帶參數的游標,那麼就要使用游標,這種情況下使用FETCH語句代替INSTEAD INTO是一個更簡單、更有效率的方法,但在安全性較高的包中FETCH的語法如下:
FETCH cursor_name INTO variable;
我們改寫上面的例子:
DECLARE
CURSOR stock_cur(symbol_in VARCHAR2) IS
SELECT symbol,Exchange,begin_date
FROM stock
WHERE symbol=UPPER(symbol_in);
stock_info stock_cur%ROWTYPE
BEGIN
OPEN stock_cur('ORCL');
FETCH stock_cur INTO stock_info;
使用賦值語句將整個記錄復制給另一個記錄是一項非常有用的技術,不過記錄必須精確地被聲明為相同的類型,不能是基於兩個不同的TYPE語句來獲得相同的結構。
例:
DECLARE
TYPE 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是不同的數據類型
BEGIN
stock_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] INDEX
BY BINARY_INTERGET;
這裡面重要的關鍵字是INDEX BY BINARY_INTERGET,沒有這個關鍵字,那麼集合將是一個嵌套表,element_type可以是任何合法的PL/SQL數據類型,包括:PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合類型對數據庫的數據類型都有限制,但Index_by表不能存儲在數據庫中,所以沒有這些限制。
一旦定義了index_by表,就可以向創建其他變量那樣創建index_by表的變量:
DECLARE
TYPE 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) OF
element_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集合初始化是最簡單的,只要涉及其中的一個元素集合就被初始化了。
例:
DECLARE
TYPE 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和-3
SELECT * 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 THEN
chang_maintenance_fee(147);
END IF
new_acct_tab:=account_tab;
symbol_tab(1):="ORCL";
symbol_tab(2):="CSCO";
symbol_tab(3):="SUNM";
publish_portfolio(symbol_tab);
嵌套表和VARRAY由構造函數初始化,構造函數和集合的名字相同,同時有一組參數,每個參數對應一個元素,如果參數為NULL,那麼對應的元素就被初始化為NULL,如果創建了元素,但沒有填充數據,那麼元素將保持null值,可以被引用,但不能保持數據。如果元素沒有初始化,那麼就不能引用該元素。
例:
DECLARE
TYPE 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 THEN
tech_10(7):='CPQ';
END
tech_10(8):='DELL';
在這個例子中,嵌套表BIOTECH_STOCKS初始化有5個元素,VARRAY tech_10集合最多能有10 個元素,但構造函數只創建了8個元素,其中還有兩個元素是NULL值,並程序中給他們賦值。
初始化基於記錄的集合,就必須將記錄傳遞給構造函數,注意不能只是簡單的將記錄的域傳遞給構造函數。
例:
DECLARE
集合的方法
TYPE 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;
BEGIN
single_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中集合的方法
方法 描述 使用限制 COUNT 返回集合中元素的個數 DELETE 刪除集合中所有元素 DELETE() 刪除元素下標為x的元素,如果x為null,則集合保持不變 對VARRAY非法 DELETE(,) 刪除元素下標從X到Y的元素,如果X>Y集合保持不變 對VARRAY非法 EXIST() 如果集合元素x已經初始化,則返回TRUE, 否則返回FALSE EXTEND 在集合末尾添加一個元素 對Index_by非法 EXTEND() 在集合末尾添加x個元素 對Index_by非法 EXTEND(,) 在集合末尾添加元素n的x個副本 對Index_by非法 FIRST 返回集合中的第一個元素的下標號,對於VARRAY集合始終返回1。 LAST 返回集合中最後一個元素的下標號, 對於VARRAY返回值始終等於COUNT. LIMIT 返回VARRY集合的最大的元素個數,對於嵌套表和對於嵌套表和Index_by為null Index_by集合無用 NEXT() 返回在元素x之後及緊挨著它的元素的值,如果該元素是最後一個元素,則返回null. PRIOR() 返回集合中在元素x之前緊挨著它的元素的值,如果該元素是第一個元素,則返回null。 TRI M 從集合末端開始刪除一個元素 對於index_by不合法 TRIM() 從集合末端開始刪除x個元素 對index_by不合法
關於集合之間的比較
集合不能直接用於比較,要比較兩個集合,可以設計一個函數,該函數返回一個標量數據類型。
IF stock_list1>stock_list2 ----非法
IF sort_collection(stock_list1)>sort_collection(stock_list2) THEN --合法
但可以比較在集合內的兩個元素。