[Oracle] 相同型態的資料集合 - Collection

何謂 Collection ?

之前有提到 record 型態，可以讓使用者自訂不同的型別集合；而 collection 則是相同資料型態的集合，簡單來說可以將它視為 Array。

collection 是用來存放一群相同型態的資料用，由於可以直接透過索引(index)或是鍵值(key)直接存取，因此速度很快；而在 Oracle 中提供三種不同的 collection：

• index-by table
  高彈性且高效能的 collection 型態，也是最常用的。
  
  
• nested table
  適合用來處理龐大資料的 collection 型態。
  
  
• VARRAY
  適合用來處理少數資料的 collection 型態。

這裡介紹較為常用的 index-by table，其存在的型態可以是 Array(用索引值取得值)，或是 key-value 的集合(用 key 取得值)。

 

為何 Collection 效能優越?

要了解 collection 為什麼要能優越，就必須先知道 collection 是如何運作的，使用 collection 的方式大致如下：

1. 定義 cursor，用來取得資料庫的資料用
   
   
2. 定義 index-by table，並指定所包含的資料型態為 cursor 所取得的資料(通常為 %ROWTYPE)
   
   
3. 定義 collection 變數，型態為步驟二所定義的 index-by table
   
   
4. 開啟 cursor，並一次將所有資料取出並存入步驟三所定義的 collection 變數後，關閉 cursor
   
   
5. 後續處理與應用 collection 變數中所包含的所有資料

從上面可以知道 collection 是與 cursor 搭配使用的，將資料從資料庫取出後便暫存於記憶體中，因此處理起來的效能才會優越。

 

使用範例

以下用範例說明：
<br />CREATE OR REPLACE PROCEDURE collectionTest AS<br /> --定義 cursor(取得員工與工作資訊)<br /> CURSOR employees_jobs_cursor IS<br /> SELECT e.first_name, e.last_name, e.job_id<br /> FROM emlpoyees e<br /> ORDER BY e.job_id, e.last_name, e.first_name<br /> <br /> --定義 cursor(取得工作資訊)<br /> CURSOR jobs_cursor IS<br /> SELECT j.job_id, j.job_title<br /> FROM jobs j;<br /><br /> --定義 collection 資料結構 (employees_jobs_type)<br /> TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE INDEX BY PLS_INTEGER;<br /> --定義儲存 collection 資料的 table 變數<br /> employees_jobs employees_job_type;<br /> <br /> --定義 collection 資料結構 (jobs_type)<br /> TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE INDEX BY PLS_INTEGER;<br /> --定義儲存 collection 資料的 table 變數<br /> jobs jobs_type;<br /> <br /> --定義 collection 資料結構 (job_titles_type)<br /> TYPE job_titles_type IS TABLE OF jobs.job_title%TYPE INDEX BY jobs_job_id%TYPE;<br /> --定義儲存 collection 資料的 table 變數<br /> job_titles job_titles_type;<br />BEGIN<br /> --透過 cursor 取得資料，並置於 collection 變數中 ====== (start)<br /> --開啟 cursor 並從資料庫取得資料<br /> OPEN employees_jobs_cursor;<br /> <br /> --將取得的資料塞入 collection 變數中<br /> FETCH employees_job_cursor BULK COLLECT INTO employees_jobs;<br /> <br /> --關閉 cursor<br /> CLOSE employees_jobs_cursor;<br /> <br /> OPEN jobs_cursor;<br /> FETCH job_cursor BULK COLLECT INTO jobs;<br /> CLOSE jobs_cursor;<br /> --透過 cursor 取得資料，並置於 collection 變數中 ====== (end)<br /> <br /> --將 cursor 取得的 job 資訊塞入 job_titles collection 中<br /> FOR i IN 1..jobs.COUNT() LOOP<br /> job_titles(jobs(i).job_id) := jobs(i).job_title;<br /> END LOOP;<br /> <br /> --列出員工資訊<br /> FOR i IN 1..employees_jobs.COUNT() LOOP<br /> DBMS_OUTPUT.PUT_LINE(RPAD(employees_jobs(i).employee_id, 10) || RPAD(employees_jobs(i).first_name, 15) || RPAD(employees_jobs(i).last_name, 15) || job_titles(employees(i).job_id));<br /> END LOOP;<br /> <br /> <br /> --使用 collection 中的 FIRST() 與 NEXT()<br /> DECLARE i jobs.job_id%TYPE := job_titles.FIRST();<br /> WHILE i IS NOT NULL LOOP<br /> DBMS_OUTPUT.PUT_LINE(RPAD(job_titles(i).job_id, 10) || job_titles(i).job_title);<br /> i := job_titles.NEXT(i);<br /> END LOOP;<br />END collectionTest;<br />