PL/SQL - 集合



本章將討論 PL/SQL 中的集合。集合是具有相同資料型別的一組有序元素。每個元素都由一個唯一的下標標識,該下標表示其在集合中的位置。

PL/SQL 提供三種集合型別:

  • 按索引的表或關聯陣列
  • 巢狀表
  • 可變大小陣列或 VARRAY

Oracle 文件為每種型別的集合提供了以下特性:

集合型別 元素數量 下標型別 稠密或稀疏 建立位置 可以是物件型別屬性
關聯陣列(或按索引的表) 無界 字串或整數 任意一種 僅在 PL/SQL 塊中
巢狀表 無界 整數 初始稠密,可以變為稀疏 在 PL/SQL 塊中或在模式級別
可變大小陣列 (VARRAY) 有界 整數 始終稠密 在 PL/SQL 塊中或在模式級別

我們已經在'PL/SQL 陣列'章節中討論了 VARRAY。本章將討論 PL/SQL 表。

兩種 PL/SQL 表(即按索引的表和巢狀表)具有相同的結構,並且它們的行的訪問使用下標表示法。但是,這兩種型別的表在一個方面有所不同;巢狀表可以儲存在資料庫列中,而按索引的表則不能。

按索引的表

按索引的表(也稱為關聯陣列)是一組鍵值對。每個鍵都是唯一的,用於查詢相應的 value。鍵可以是整數或字串。

按索引的表使用以下語法建立。這裡,我們正在建立一個名為table_name按索引的表,其鍵將是`subscript_type`型別,關聯的值將是`element_type`型別

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

示例

以下示例顯示瞭如何建立一個表來儲存整數 value 以及名稱,然後列印相同的名稱列表。

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 James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

示例

按索引的表的元素也可以是任何資料庫表的%ROWTYPE或任何資料庫表字段的%TYPE。以下示例說明了這個概念。我們將使用儲存在我們資料庫中的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_lis t(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. 

示例

巢狀表的元素也可以是任何資料庫表的%ROWTYPE或任何資料庫表字段的%TYPE。以下示例說明了這個概念。我們將使用儲存在我們資料庫中的 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.No.ame%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 提供內建的集合方法,使集合更容易使用。下表列出了這些方法及其用途:

序號 方法名稱和用途
1

EXISTS(n)

如果集合中存在第 n 個元素,則返回 TRUE;否則返回 FALSE。

2

COUNT

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

3

LIMIT

檢查集合的最大大小。

4

FIRST

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

5

LAST

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

6

PRIOR(n)

返回集合中在索引 n 之前的索引號。

7

NEXT(n)

返回在索引 n 之後的索引號。

8

EXTEND

向集合追加一個空元素。

9

EXTEND(n)

向集合追加 n 個空元素。

10

EXTEND(n,i)

向集合追加第 i 個元素的 n 個副本。

11

TRIM

從集合末尾刪除一個元素。

12

TRIM(n)

從集合末尾刪除 n 個元素。

13

DELETE

刪除集合中的所有元素,將 COUNT 設定為 0。

14

DELETE(n)

從具有數字鍵的關聯陣列或巢狀表中刪除第nth個元素。如果關聯陣列具有字串鍵,則刪除對應於鍵值的元素。如果n為空,則DELETE(n)不執行任何操作。

15

DELETE(m,n)

從關聯陣列或巢狀表中刪除範圍m..n內的所有元素。如果m大於nmn為空,則DELETE(m,n)不執行任何操作。

集合異常

下表提供了集合異常及其引發的情況:

集合異常 引發的情況
COLLECTION_IS_NULL 嘗試操作原子空集合。
NO_DATA_FOUND 下標指定已刪除的元素,或關聯陣列中不存在的元素。
SUBSCRIPT_BEYOND_COUNT 下標超過集合中的元素數量。
SUBSCRIPT_OUTSIDE_LIMIT 下標超出允許的範圍。
VALUE_ERROR 下標為空或不可轉換為鍵型別。如果鍵定義為PLS_INTEGER範圍,並且下標在此範圍之外,則可能會發生此異常。
廣告