在PL/SQL提供的集合方法中,EXISTS,COUNT,LIMIT,FIRST,LAST,PRIOR,NEXT是函數;EXTEND,TRIM,DELETE是過程。EXISTS,PRIOR,NEXT,TRIM,EXTEND,DELETE對應的參數是集合的下标索引。
集合方法隻能在PL/SQL中使用,不能在SQL語句中使用。
隻有EXISTS能用于空集合,如果在空集合上調用其他方法,PL/SQL就會抛出異常
COLLECTION_IS_NULL
。
EXISTS
EXISTS方法用于判斷集合中指定的元素是否存在。存在則傳回True,否則傳回False。
使用這個方法主要用于在通路一個未配置設定值的下标元素時,避免Oracle彈出NO DATA FOUND這樣的錯誤。
當傳遞的下标越界時,EXISTS會傳回False,而不會引發下标超出界限的異常。
使用如:
DECLARE
TYPE projectlist IS VARRAY () OF VARCHAR2 (); --定義項目清單變長數組
project_list projectlist := projectlist ('網站', 'ERP', 'CRM', 'CMS');
BEGIN
IF project_list.EXISTS () --判斷一個不存在的元素值
THEN --如果存在,則輸出元素值
DBMS_OUTPUT.put_line ('元素存在,其值為:' || project_list ());
ELSE
DBMS_OUTPUT.put_line ('元素不存在'); --如果不存在,顯示元素不存在
END IF;
END;
COUNT
COUNT方法能傳回集合中包含的元素個數,該函數在判斷集合的目前元素個數時非常有用,因為集合的目前大小不總是能夠确定,特别是對于嵌套表和索引表來說。
對于變長數組來說,COUNT值和LAST方法值恒等,但對于嵌套表來說,正常情況下COUNT值會和LAST值相等。但是,當我們從嵌套表中間删除一個元素時,COUNT值就會比LAST值小。
在計算元素的個數時,COUNT方法會跳過已被删除的元素。
使用如:
DECLARE
TYPE emp_name_table IS TABLE OF VARCHAR2 (); --員工名稱嵌套表
TYPE deptno_table IS TABLE OF NUMBER (); --部門編号嵌套表
deptno_info deptno_table;
emp_name_info emp_name_table := emp_name_table ('張小三', '李斯特');
BEGIN
deptno_info:=deptno_table(); --構造一個不包含任何元素的嵌套表
deptno_info.EXTEND(); --擴充5個元素
DBMS_OUTPUT.PUT_LINE('deptno_info的元素個數為:'||deptno_info.COUNT); --5
DBMS_OUTPUT.PUT_LINE('emp_name_info的元素個數為:'||emp_name_info.COUNT); --2
END;
LIMIT
LIMIT方法用于傳回集合元素的最大個數,對于變長數組來說,因為其元素個數固定,可以傳回變長數組所允許的最大元素個數。而對于嵌套表和索引表來說,由于其元素個數沒有限制,是以調用該方法将總是傳回NULL。
使用如:
DECLARE
TYPE projectlist IS VARRAY () OF VARCHAR2 (); --定義項目清單變長數組
project_list projectlist := projectlist ('網站', 'ERP', 'CRM', 'CMS');
BEGIN
DBMS_OUTPUT.put_line ('變長數組的上限值為:' || project_list.LIMIT); -- 50
project_list.EXTEND();
DBMS_OUTPUT.put_line ('變長數組的目前個數為:' || project_list.COUNT); -- 12
END;
FIRST和LAST方法
FIRST和LAST分别傳回集合中第一個和最後一個元素的索引數字,而不是該元素的值。如果集合為空,則FIRST和LAST将傳回NULL。
使用如:
DECLARE
TYPE projectlist IS VARRAY () OF VARCHAR2 (); --定義項目清單變長數組
project_list projectlist := projectlist ('網站', 'ERP', 'CRM', 'CMS');
BEGIN
DBMS_OUTPUT.put_line ('project_list的第個元素下标:' || project_list.FIRST
); --檢視第1個元素的下标,輸出1
project_list.EXTEND (); --擴充8個元素
DBMS_OUTPUT.put_line ( 'project_list的最後一個元素的下标:'
|| project_list.LAST
); --檢視最後1個元素的下标,輸出12
END;
PRIOR和NEXT方法
PRIOR會傳回集合中特定索引值參數的元素的前一個索引值,NEXT會傳回集合中的特定索引值參數所指向的元素的下一個索引值。如果特定的元素沒有前一個或後一個值,那麼PRIOR或NEXT就會傳回NULL值。
PRIOR和NEXT通常用來使用循環周遊所有的元素值,這種周遊方法比通過固定的下标索引更加可靠,因為在循環過程中,有些元素可能被插入或删除。特别是索引表,因為它的下标索引可能是不連續的。
使用PRIOR和NEXT循環周遊索引表中的元素,如:
DECLARE
TYPE idx_table IS TABLE OF VARCHAR ()
INDEX BY PLS_INTEGER; --定義索引表類型
v_emp idx_table; --定義索引表變量
i PLS_INTEGER; --定義循環控制變量
BEGIN
v_emp () := '史密斯'; --随機的為索引表指派
v_emp () := '克拉克';
v_emp () := '史瑞克';
v_emp (-) := '傑瑞';
--擷取集合中第-10個元素的下一個值
DBMS_OUTPUT.put_line ('第-個元素的下一個值:' || v_emp (v_emp.NEXT (-)));
--擷取集合中第40個元素的上一個值
DBMS_OUTPUT.put_line ('第個元素的上一個值:' || v_emp (v_emp.PRIOR ()));
i := v_emp.FIRST; --定位到第1個元素的下标
WHILE i IS NOT NULL --開始循環直到下标為NULL
LOOP --輸出元素的值
DBMS_OUTPUT.put_line ('v_emp(' || i || ')=' || v_emp (i));
i := v_emp.NEXT (i); --向下移動循環指針,指向下一個下标
END LOOP;
END;
EXTEND方法
EXTEND可以為嵌套表和變長數組擴充元素,不能用于索引表,主要有三種使用形式:
- EXTEND:在集合末端添加一個空元素。
- EXTEND(n):在集合末端添加n個空元素。
- EXTEND(n,i):把第i個元素複制n份,添加到集合的末端。
如果一個集合未使用構造文法進行初始化,是不能使用EXTEND進行擴充的。如果嵌套表或變長數組添加了NOT NULL限制,也不能使用EXTEND的前面兩種形式。
使用示例:
DECLARE
TYPE courselist IS TABLE OF VARCHAR2 (); --定義嵌套表
--定義課程嵌套表變量
courses courselist;
i PLS_INTEGER;
BEGIN
courses := courselist ('生物', '實體', '化學'); --初始化元素
courses.DELETE (); --删除第3個元素
courses.EXTEND; --追加一個新的NULL元素
courses () := '英語';
courses.EXTEND(,); --把第1個元素拷貝5份添加到末尾
i:=courses.FIRST;
WHILE i IS NOT NULL LOOP --循環顯示結果值
DBMS_OUTPUT.PUT_LINE('courses('||i||')='||courses(i));
i:=courses.NEXT(i);
END LOOP;
END;
TRIM方法
TRIM方法用來從嵌套表或變長數組的尾端删除元素:
- TRIM:從集合末端删除一個元素。
- TRIM(n):從集合末端删除n個元素。
DECLARE
TYPE courselist IS TABLE OF VARCHAR2 (); --定義嵌套表
--定義課程嵌套表變量
courses courselist;
i PLS_INTEGER;
BEGIN
courses := courselist ('生物', '實體', '化學','音樂','數學','地理');--初始化元素
courses.TRIM(); --删除集合末尾的2個元素
DBMS_OUTPUT.PUT_LINE('目前的元素個數:'||courses.COUNT); --顯示元素個數
courses.EXTEND; --擴充1個元素
courses(courses.COUNT):='國文'; --為最後1個元素指派
courses.TRIM; --删除集合末尾的最後1個元素
i:=courses.FIRST;
WHILE i IS NOT NULL LOOP --循環顯示結果值
DBMS_OUTPUT.PUT_LINE('courses('||i||')='||courses(i));
i:=courses.NEXT(i);
END LOOP;
END;
DELETE方法
DELETE用于從索引表和嵌套表中删除一個或多個元素:
- DELETE:删除集合中所有元素。
- DELETE(n):從以數字做主鍵的所有表或者嵌套表中删除第n個元素。如果索引表有一個字元串鍵,對應該鍵值得元素就會被珊瑚。如果n為空,DELETE(n)不會做任何事情。
- DELETE(m,n):從索引表或嵌套表中把索引範圍m到n的所有元素删除。如果m大于n或m和n中有一個為空,那麼DELETE(m,n)就不做任何事情。
由于變長數組的元素個數固定,是以在變長數組上使用DELETE是非法的。
在内部,如果DELETE發現被删除的元素不存在,将隻是簡單的忽略它,并不會抛出異常,PL/SQL會為被删除的元素保留一個占位符(也就是說标記删除),以便可以重新為被删除的元素指派。
但是COUNT方法會忽略掉已标記為删除的元素,可以通過FIRST、LAST、NEXT、PRIOR來擷取被删除元素的詳細資訊,如:
DECLARE
TYPE courselist IS TABLE OF VARCHAR2 (10); --定義嵌套表
--定義課程嵌套表變量
courses courselist;
i PLS_INTEGER;
BEGIN
courses := courselist ('生物', '實體', '化學','音樂','數學','地理');--初始化元素
courses.DELETE(); --删除第2個元素
DBMS_OUTPUT.PUT_LINE('目前的元素個數:'||courses.COUNT); --顯示元素個數
courses.EXTEND; --擴充1個元素
DBMS_OUTPUT.PUT_LINE('目前的元素個數:'||courses.COUNT); --顯示元素個數
courses(courses.LAST):='國文'; --為最後1個元素指派
courses.DELETE(,courses.COUNT); --删除集合第4個到最後一個元素
i:=courses.FIRST;
WHILE i IS NOT NULL LOOP --循環顯示結果值
DBMS_OUTPUT.PUT_LINE('courses('||i||')='||courses(i));
i:=courses.NEXT(i);
END LOOP;
END;