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
;