在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 (50) OF VARCHAR2 (16); --定义项目列表变长数组
project_list projectlist := projectlist ('网站', 'ERP', 'CRM', 'CMS');
BEGIN
IF project_list.EXISTS (5) --判断一个不存在的元素值
THEN --如果存在,则输出元素值
DBMS_OUTPUT.put_line ('元素存在,其值为:' || project_list (5));
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 (20); --员工名称嵌套表
TYPE deptno_table IS TABLE OF NUMBER (2); --部门编号嵌套表
deptno_info deptno_table;
emp_name_info emp_name_table := emp_name_table ('张小三', '李斯特');
BEGIN
deptno_info:=deptno_table(); --构造一个不包含任何元素的嵌套表
deptno_info.EXTEND(5); --扩展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 (50) OF VARCHAR2 (16); --定义项目列表变长数组
project_list projectlist := projectlist ('网站', 'ERP', 'CRM', 'CMS');
BEGIN
DBMS_OUTPUT.put_line ('变长数组的上限值为:' || project_list.LIMIT); -- 50
project_list.EXTEND(8);
DBMS_OUTPUT.put_line ('变长数组的当前个数为:' || project_list.COUNT); -- 12
END;
FIRST和LAST方法
FIRST和LAST分别返回集合中第一个和最后一个元素的索引数字,而不是该元素的值。如果集合为空,则FIRST和LAST将返回NULL。
使用如:
DECLARE
TYPE projectlist IS VARRAY (50) OF VARCHAR2 (16); --定义项目列表变长数组
project_list projectlist := projectlist ('网站', 'ERP', 'CRM', 'CMS');
BEGIN
DBMS_OUTPUT.put_line ('project_list的第1个元素下标:' || project_list.FIRST
); --查看第1个元素的下标,输出1
project_list.EXTEND (8); --扩展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 (12)
INDEX BY PLS_INTEGER; --定义索引表类型
v_emp idx_table; --定义索引表变量
i PLS_INTEGER; --定义循环控制变量
BEGIN
v_emp (1) := '史密斯'; --随机的为索引表赋值
v_emp (20) := '克拉克';
v_emp (40) := '史瑞克';
v_emp (-10) := '杰瑞';
--获取集合中第-10个元素的下一个值
DBMS_OUTPUT.put_line ('第-10个元素的下一个值:' || v_emp (v_emp.NEXT (-10)));
--获取集合中第40个元素的上一个值
DBMS_OUTPUT.put_line ('第40个元素的上一个值:' || v_emp (v_emp.PRIOR (40)));
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 (10); --定义嵌套表
--定义课程嵌套表变量
courses courselist;
i PLS_INTEGER;
BEGIN
courses := courselist ('生物', '物理', '化学'); --初始化元素
courses.DELETE (3); --删除第3个元素
courses.EXTEND; --追加一个新的NULL元素
courses (4) := '英语';
courses.EXTEND(5,1); --把第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 (10); --定义嵌套表
--定义课程嵌套表变量
courses courselist;
i PLS_INTEGER;
BEGIN
courses := courselist ('生物', '物理', '化学','音乐','数学','地理');--初始化元素
courses.TRIM(2); --删除集合末尾的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); --删除第2个元素
DBMS_OUTPUT.PUT_LINE('当前的元素个数:'||courses.COUNT); --显示元素个数
courses.EXTEND; --扩展1个元素
DBMS_OUTPUT.PUT_LINE('当前的元素个数:'||courses.COUNT); --显示元素个数
courses(courses.LAST):='语文'; --为最后1个元素赋值
courses.DELETE(4,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;