PL/SQL集合

集合是一個有序組具有相同的數據類型的元素。每個元素進行標識的唯一標表示其在集合中的位置。

PL/SQL提供了三種集合類型:

  • 索引表或關聯數組

  • 嵌套表

  • 可變大小的數組或變長數組

Oracle文檔提供了每種類型的集合的以下特徵:

集合類型

元素數量

下標類型

密集或稀疏

在哪裏創建

可以是對象類型屬性

關聯數組(或索引表)

無界

字符串或整數

兩種都可以

只有在PL/SQL塊

No

嵌套表

無界

整數

開始密集,可以變得稀疏

無論是在PL/SQL塊或模式級別

Yes

可變大小的數組(變長數組)

有界

整數

總是密集

無論是在PL/SQL塊或模式級別

Yes

我們已經在前討論變長數組參見'PL/SQL數組「。在本章中將討論PL/SQL表。

這兩種類型的PL/SQL表即,索引表和嵌套表,都具有相同的結構以及行使用下標符號訪問。然而,這兩種類型的表有不同一個方面:嵌套表可以存儲在數據庫中的列,但索引表不能。

索引表

一個索引表(也叫關聯數組)是一組鍵 - 值對。每個鍵是唯一的,並且用於定位對應的值。鍵可以是整數或字符串。

使用以下語法創建一個索引表。在這裏,我們正在創建一個索引表命名錶名,它的鍵是subscript_type類型以及對應值爲 element_type 類型

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name;

例子:

下面的示例演示如何創建一個表來存儲整數值和鍵名稱,它打印的名稱相同的列表。

DECLARE TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); salary_list salary; name VARCHAR2(20); BEGIN -- adding elements to the table salary_list('Rajnish') := 62000; salary_list('Minakshi') := 75000; salary_list('Martin') := 100000; salary_list('James') := 78000; -- printing the table name := salary_list.FIRST; WHILE name IS NOT null LOOP
dbms_output.put_line ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP; END; /

當上述代碼在SQL提示符執行時,它產生了以下結果:

Salary of Rajnish is 62000
Salary of Minakshi is 75000
Salary of Martin is 100000
Salary of James is 78000

PL/SQL procedure successfully completed.

例子:

一個索引表的元素也可以是任何數據庫表字段%TYPE或任何數據庫表%ROWTYPE。下面的例子說明了這個概念。我們將使用存儲在數據庫中CUSTOMERS表:

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+

DECLARE CURSOR c_customers is select name from customers; TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer; name_list c_list; counter integer :=0; BEGIN FOR n IN c_customers LOOP
counter := counter +1; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter|| '):'||name_list(counter)); END LOOP; END; /

當上述代碼在SQL提示符執行時,它產生了以下結果:

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed

嵌套表

嵌套表是就像一個一維數組的元素的數量。然而,嵌套表不同於在以下幾方面的數組:

  • 數組具有元素的聲明的數量,但嵌套表不用聲明大小。嵌套表的大小可以動態地增加。

  • 數組總是密集的,也就是說,它總是有連續的下標。嵌套數組初始是緻密,但是刪除它的元素時就會變成稀疏。

使用以下語法創建一個嵌套表:

TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name type_name;

這個聲明是類似聲明的索引表中,但沒有INDEX BY子句。

嵌套表可以被存儲在數據庫中的列,所以它可用於簡化在那裏加入一個較大的表中的單個列,使用SQL操作。關聯數組不能存儲在數據庫中。

例子:

下面的例子說明使用嵌套表:

DECLARE TYPE names_table IS TABLE OF VARCHAR2(10); TYPE grades IS TABLE OF INTEGER; names names_table; marks grades; total integer; BEGIN names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line('Total '|| total || ' Students'); FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); end loop; END; /

當上述代碼在SQL提示符執行時,它產生了以下結果:

Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92

PL/SQL procedure successfully completed.

例子:

嵌套表的元素也可以是任何數據庫表字段%TYPE和任何數據庫表或%ROWTYPE。下面的例子說明了這個概念。我們將使用存儲在數據庫中的CUSTOMERS表:

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+

DECLARE CURSOR c_customers is SELECT name FROM customers; TYPE c_list IS TABLE of customers.name%type; name_list c_list := c_list(); counter integer :=0; BEGIN FOR n IN c_customers LOOP
counter := counter +1; name_list.extend; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); END LOOP; END; /

當上述代碼在SQL提示符執行時,它產生了以下結果:

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.

集合方法

PL/SQL提供,使集合更容易使用內置集合方法。下表列出了這些方法和用途:

S.N.

方法名稱及用途

1

EXISTS(n)
返回TRUE,如果一個集合中的第n個元素的存在;否則返回FALSE

2

COUNT
返回集合當前包含的元素數量

3

LIMIT
檢查集合的最大尺寸

4

FIRST
返回在使用整數下標集合的第一個(最小的)索引號

5

LAST
返回在使用整數下標集合的最後一個(最大的)索引號

6

PRIOR(n)
返回集合中索引n前面的索引號

7

NEXT(n)
返回索引n的下一個索引號

8

EXTEND
追加1個空元素到集合

9

EXTEND(n)
追加n個空元素到集合

10

EXTEND(n,i)
追加的第i個元素的集合的n個拷貝

11

TRIM
移除集合的末尾一個元素

12

TRIM(n)
移除一個集合末尾的n個元素

13

DELETE
移除一個集合中的所有元素,設置計數爲0

14

DELETE(n)
移除關聯數組用數字鍵或嵌套表中的第n個元素。如果關聯數組有一個字符串鍵,對應於該鍵的值的元素將被刪除。如果n爲空,DELETE(N)什麼都不做

15

DELETE(m,n)
移除關聯數組或嵌套表範圍m..n所有元素。如果m大於n或如果m大於或n是零,DELETE(M,N)什麼也不做

集合異常

下表提供了集合異常和引發,當它們:

集合異常

引發情況

COLLECTION_IS_NULL

嘗試在一個原子空(null)的集合操作

NO_DATA_FOUND

下標指定被刪除的元素,或關聯數組的一個不存在的元素

SUBSCRIPT_BEYOND_COUNT

下標超過了一個集合的元素數

SUBSCRIPT_OUTSIDE_LIMIT

下標超出了允許的範圍

VALUE_ERROR

下標爲空(null)或不可轉換的鍵類型。如果該鍵被定義爲PLS_INTEGER範圍,下標是在該範圍之外可能會出現這樣的異常