PL/SQL集合

在本章中，我們將討論PL/SQL中的集合。集合是具有相同數據類型的有序元素組。 每個元素都由一個唯一的下標來表示它在集合中的位置。

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

• 索引表或關聯數組
• 嵌套的表
• 可變大小的數組或Varray類型

Oracle的每種類型的集合有以下特徵 -

我們已經在“PL/SQL數組”一章中討論了varray。 在本章中，將討論PL/SQL表。

兩種類型的PL/SQL表(即索引表和嵌套表)具有相同的結構，並且使用下標符號來訪問它們的行。 但是，這兩種表在一個方面有所不同， 嵌套表可以存儲在資料庫列中，索引表不能。

索引表

索引表(也稱為關聯數組)是一組鍵 - 值對。 每個鍵都是唯一的，用來定位相應的值。鍵可以是整數或字串。
使用以下語法創建索引表。 在這裏，正在創建一個名為table_name的索引表，其中的鍵是subscript_type，關聯的值是element_type，參考以下語法 -

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

table_name type_name;

示例

以下示例顯示了如何創建一個表來存儲整數值以及名稱，然後列印出相同的名稱列表。

SET SERVEROUTPUT ON SIZE 99999;
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;
/

執行上面示例代碼，得到以下結果 -

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

PL/SQL 過程已成功完成。

示例2

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

CREATE TABLE CUSTOMERS(
   ID   INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

存儲過程代碼如下 -

SET SERVEROUTPUT ON SIZE 99999;
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;
/

執行上面示例代碼，得到以下結果 -

嵌套表

嵌套表就像一個具有任意數量元素的一維數組。但是，嵌套表與數組在以下幾個方面不同 -

• 數組是一個有聲明數量的元素集合，但是一個嵌套的表沒有。嵌套表的大小可以動態增加。
• 數組總是密集的，即它總是具有連續的下標。 嵌套數組最初是密集的，但是當從其中刪除元素時，它可能變得稀疏。

使用以下語法創建嵌套表 -

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

這個聲明類似於索引表的聲明，只是沒有INDEX BY子句。

嵌套表可以存儲在資料庫列中。 它可以進一步用於簡化SQL操作，您可以使用更大的表來連接單列表。關聯數組不能存儲在資料庫中。

示例1
下麵的例子說明了嵌套表的使用 -

SET SERVEROUTPUT ON SIZE 99999;
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 過程已成功完成。

示例2

嵌套表的元素也可以是任何資料庫表的%ROWTYPE或任何資料庫表字段的%TYPE。以下示例說明了這個概念。我們將使用存儲在資料庫中的CUSTOMERS表，參考以下代碼的實現 -

SET SERVEROUTPUT ON SIZE 99999;
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;
/

執行上面示例代碼，得到以下結果 -

集合方法

PL/SQL提供了內置的集合方法，使集合更易於使用。下表列出了方法及其用途 -

集合異常

下表提供了集合異常情況以及何時引發 -