PLSQL学习(二) 数组专题
PLSQL中提供了三种数据的形式,功能还是比较强大的。但是总的来说,PLSQL中的数组知识掌握最简单的那种,其他大致了解就可以了。因为从实际应用的角度来说,在PLSQL中用到数组的几率不是很大。这主要是由于PLSQL跟数据库的紧密结合特性所决定的,数据库的表可以很轻易得实现基本的数组功能。当然这是我个人的一点理解,不一定正确。下面是详细介绍
1、PLSQL中的数组共分三类:
I - 嵌套表(Nested tables)
TYPE nested_type IS TABLE OF VARCHAR2 ( 30 ) [not null];
-- 值为 varchar2 的数组,下标为默认 int ;
特征:可删除信息,下标不变
II - 变长数组(Variable-Sized Arrays)
TYPE Calendar IS VARRAY ( 366 ) OF NUMBER ;
--366 个 number 数组,下标 1-366( 不是 0-365)
特征:一般在可预知数组个数的情况下使用,类似其他语言的Array
III- 索引表(Associative Array)
TYPE population_type IS TABLE OF NUMBER
INDEX BY VARCHAR2 ( 64 );
-- 下标是 varchar2 ,值是 number 的数组
特征:可使用不连续数字、负数、字符作为下标,长度大小可变
在创建数组时便分配内存,无需之后申请
index by 只能跟BINARY_INTEGER、PLS_INTEGER、VARCHAR2三种类型
2、数组中的赋值错误问题:
DECLARE
TYPE wordlist IS TABLE OF VARCHAR2 ( 5 );
words wordlist;
BEGIN
words( 1 ) := 10 ; --COLLECTION_IS_NULL, 未赋空间
words := wordlist( 10 , 20 , 30 ); -- 进行赋值
words( 1 ) := 'yes' ; -- 正确
words( 2 ) := words( 1 ) || 'no' ; -- 正确
words( 3 ) := 'longer than 5 characters' ; --VALUE_ERROR ,字符过长
words( 'B' ) := 'dunno' ; --VALUE_ERROR ,下标错误
words( 4 ) := 'maybe' ; --SUBSCRIPT_BEYOND_COUNT ,下标超限
END ;
在声明 words wordlist; 之后数组未初始化,完全没有用处
必须进行初始化,如:
words := wordlist( 10 , 20 , 30 );--定值初始化;
words := wordlist();--空值初始化,任何数组必须先初始化;
words.extend(n);--末尾增加N位空间,不加N则为默认增加1位;
然后可以赋值
3、数组相关集合函数:
1、EXISTS(判断第i位是否存在)
IF courses.EXISTS(i) THEN
courses(i) := new_course;
END IF ;
2、COUNT(数组中的元素个数)
FOR i IN 1 .. courses.COUNT LOOP ...
注意:COUNT会忽略已经被删除的元素
3、LIMIT(集合的最大容量)
IF (projects.COUNT + 15 ) < projects.LIMIT THEN ...
注意:LIMIT一般只对变长数组有效(其他两类均返回NULL)
4、FIRST和LAST(第一个和最后一个元素的下标)
FOR i IN courses.FIRST .. courses.LAST LOOP ...
注意:返回值是下标,而不是值!在遍历元素时,FIRST和LAST都会忽略被删除的元素
5、PRIOR和NEXT(返回索引为n的前驱/后驱下标)
n := courses.PRIOR(courses.FIRST); --assigns NULL to n
注意:返回值是下标,而不是值!在遍历元素时,FIRST和LAST都会忽略被删除的元素
6、EXTEND(扩大集合容量)
courses.EXTEND( m , n ); --将第n个元素的值复制m份加到集合末端
注意:m默认为1,n默认为null,m包含被删除元素
7、TRIM(缩减集合容量)
courses.TRIM( 3 ); -- 与 extend 相反
8、DELETE(删除集合元素)
courses.DELETE -- 删除全部
courses.DELETE( 2 ) -- 删除第 2 个元素
courses.DELETE( 2 , 5 ) -- 删除第 2 到第 5 个元素
注意:使用delete的时候必须要结合3中数组的不同特征!
4、Exception的类型及原因:
COLLECTION_IS_NULL --- 调用一个空集合的方法 , 集合未被初始化
NO_DATA_FOUND --- 下标索引指向一个被删除的元素,或是关联数组中不存在的元素
SUBSCRIPT_BEYOND_COUNT --- 下标索引值超过集合中的元素个数
SUBSCRIPT_OUTSIDE_LIMIT --- 下标索引超过允许范围之外
VALUE_ERROR --- 下标索引值为空,或是不是指定的下标类型
5、关于数组的特有批量绑定ForAll
1、语法结构:
FORALL i IN pnums.FIRST .. pnums.LAST
INSERT INTO partno VALUES (pnums(i)); ---注意:不用再Loop了
2、可使用%BULK_ROWCOUNT属性来计算FORALL语句所影响到的行数
IF SQL % BULK_ROWCOUNT ( 3 ) = 0 THEN ...
表示如果第3次操作没有对数据影响的行数为0话……
注意%BULK_ROWCOUNT的值是可以大于1的,比如批量插入等
3、使用%BULK_EXCEPTIONS属性来控制FORALL异常
DECLARE
TYPE numlist IS TABLE OF NUMBER ;
num_tab numlist := numlist( 10 , 0 , 11 , 12 , 30 , 0 , 20 , 199 , 2 , 0 , 9 , 1 );
ERRORS NUMBER ;
dml_errors EXCEPTION ;
PRAGMA EXCEPTION_INIT (dml_errors, - 24381 );
BEGIN
FORALL i IN num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp
WHERE sal > 500000 / num_tab(i);
EXCEPTION
WHEN dml_errors THEN
ERRORS := SQL %BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.put_line( 'Number of errors is ' || ERRORS );
FOR i IN 1 .. ERRORS LOOP
DBMS_OUTPUT.put_line( 'Error '
|| i
|| ' occurred during '
|| 'iteration '
|| SQL % BULK_EXCEPTIONS (i).ERROR_INDEX);
DBMS_OUTPUT.put_line( 'Oracle error is '
|| SQLERRM (- SQL % BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP ;
END ;
转载自:http://www.blogjava.net/decode360/category/40027.html
PLSQL中提供了三种数据的形式,功能还是比较强大的。但是总的来说,PLSQL中的数组知识掌握最简单的那种,其他大致了解就可以了。因为从实际应用的角度来说,在PLSQL中用到数组的几率不是很大。这主要是由于PLSQL跟数据库的紧密结合特性所决定的,数据库的表可以很轻易得实现基本的数组功能。当然这是我个人的一点理解,不一定正确。下面是详细介绍
1、PLSQL中的数组共分三类:
I - 嵌套表(Nested tables)
TYPE nested_type IS TABLE OF VARCHAR2 ( 30 ) [not null];
-- 值为 varchar2 的数组,下标为默认 int ;
特征:可删除信息,下标不变
II - 变长数组(Variable-Sized Arrays)
TYPE Calendar IS VARRAY ( 366 ) OF NUMBER ;
--366 个 number 数组,下标 1-366( 不是 0-365)
特征:一般在可预知数组个数的情况下使用,类似其他语言的Array
III- 索引表(Associative Array)
TYPE population_type IS TABLE OF NUMBER
INDEX BY VARCHAR2 ( 64 );
-- 下标是 varchar2 ,值是 number 的数组
特征:可使用不连续数字、负数、字符作为下标,长度大小可变
在创建数组时便分配内存,无需之后申请
index by 只能跟BINARY_INTEGER、PLS_INTEGER、VARCHAR2三种类型
2、数组中的赋值错误问题:
DECLARE
TYPE wordlist IS TABLE OF VARCHAR2 ( 5 );
words wordlist;
BEGIN
words( 1 ) := 10 ; --COLLECTION_IS_NULL, 未赋空间
words := wordlist( 10 , 20 , 30 ); -- 进行赋值
words( 1 ) := 'yes' ; -- 正确
words( 2 ) := words( 1 ) || 'no' ; -- 正确
words( 3 ) := 'longer than 5 characters' ; --VALUE_ERROR ,字符过长
words( 'B' ) := 'dunno' ; --VALUE_ERROR ,下标错误
words( 4 ) := 'maybe' ; --SUBSCRIPT_BEYOND_COUNT ,下标超限
END ;
在声明 words wordlist; 之后数组未初始化,完全没有用处
必须进行初始化,如:
words := wordlist( 10 , 20 , 30 );--定值初始化;
words := wordlist();--空值初始化,任何数组必须先初始化;
words.extend(n);--末尾增加N位空间,不加N则为默认增加1位;
然后可以赋值
3、数组相关集合函数:
1、EXISTS(判断第i位是否存在)
IF courses.EXISTS(i) THEN
courses(i) := new_course;
END IF ;
2、COUNT(数组中的元素个数)
FOR i IN 1 .. courses.COUNT LOOP ...
注意:COUNT会忽略已经被删除的元素
3、LIMIT(集合的最大容量)
IF (projects.COUNT + 15 ) < projects.LIMIT THEN ...
注意:LIMIT一般只对变长数组有效(其他两类均返回NULL)
4、FIRST和LAST(第一个和最后一个元素的下标)
FOR i IN courses.FIRST .. courses.LAST LOOP ...
注意:返回值是下标,而不是值!在遍历元素时,FIRST和LAST都会忽略被删除的元素
5、PRIOR和NEXT(返回索引为n的前驱/后驱下标)
n := courses.PRIOR(courses.FIRST); --assigns NULL to n
注意:返回值是下标,而不是值!在遍历元素时,FIRST和LAST都会忽略被删除的元素
6、EXTEND(扩大集合容量)
courses.EXTEND( m , n ); --将第n个元素的值复制m份加到集合末端
注意:m默认为1,n默认为null,m包含被删除元素
7、TRIM(缩减集合容量)
courses.TRIM( 3 ); -- 与 extend 相反
8、DELETE(删除集合元素)
courses.DELETE -- 删除全部
courses.DELETE( 2 ) -- 删除第 2 个元素
courses.DELETE( 2 , 5 ) -- 删除第 2 到第 5 个元素
注意:使用delete的时候必须要结合3中数组的不同特征!
4、Exception的类型及原因:
COLLECTION_IS_NULL --- 调用一个空集合的方法 , 集合未被初始化
NO_DATA_FOUND --- 下标索引指向一个被删除的元素,或是关联数组中不存在的元素
SUBSCRIPT_BEYOND_COUNT --- 下标索引值超过集合中的元素个数
SUBSCRIPT_OUTSIDE_LIMIT --- 下标索引超过允许范围之外
VALUE_ERROR --- 下标索引值为空,或是不是指定的下标类型
5、关于数组的特有批量绑定ForAll
1、语法结构:
FORALL i IN pnums.FIRST .. pnums.LAST
INSERT INTO partno VALUES (pnums(i)); ---注意:不用再Loop了
2、可使用%BULK_ROWCOUNT属性来计算FORALL语句所影响到的行数
IF SQL % BULK_ROWCOUNT ( 3 ) = 0 THEN ...
表示如果第3次操作没有对数据影响的行数为0话……
注意%BULK_ROWCOUNT的值是可以大于1的,比如批量插入等
3、使用%BULK_EXCEPTIONS属性来控制FORALL异常
DECLARE
TYPE numlist IS TABLE OF NUMBER ;
num_tab numlist := numlist( 10 , 0 , 11 , 12 , 30 , 0 , 20 , 199 , 2 , 0 , 9 , 1 );
ERRORS NUMBER ;
dml_errors EXCEPTION ;
PRAGMA EXCEPTION_INIT (dml_errors, - 24381 );
BEGIN
FORALL i IN num_tab.FIRST .. num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp
WHERE sal > 500000 / num_tab(i);
EXCEPTION
WHEN dml_errors THEN
ERRORS := SQL %BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.put_line( 'Number of errors is ' || ERRORS );
FOR i IN 1 .. ERRORS LOOP
DBMS_OUTPUT.put_line( 'Error '
|| i
|| ' occurred during '
|| 'iteration '
|| SQL % BULK_EXCEPTIONS (i).ERROR_INDEX);
DBMS_OUTPUT.put_line( 'Oracle error is '
|| SQLERRM (- SQL % BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP ;
END ;
转载自:http://www.blogjava.net/decode360/category/40027.html