PL/SQL Collections and Records

最新推荐文章于 2024-03-15 15:00:24 发布

xiaodi_8801

最新推荐文章于 2024-03-15 15:00:24 发布

阅读量2.2k

点赞数

文章标签： collections nested table integer null delete

本文链接：https://blog.csdn.net/xiaodi_8801/article/details/1728458

版权

作者:李笑迪

PL/SQL Collections and Records
1, 什么是PL/SQL Collections 和 Records?
集合和记录是组合类型, 比如array, record, table
一个集合是一个顺序的元素组, 组内的元素拥有统一的类型.
一个记录是一组相关的数据元素存储在字段中, 每一个都有自己的name和datatype, 你可以认为一个record 是一个变量, 能操作一个表的一行. 字段与table的列一致.

2, 理解PL/SQL集合
PL/SQL 提供这些集合类型:
* 关联数组(Associative arrays), also know as index-by tables, 你可以使用数字和字符串作为数组下标查询元素. 这有点类似与其他语言的hash table.
* 内嵌表(Nested tables) 支持一组元素, 他们使用顺序数字作为下标, 你可以指定某种SQL types, 允许内嵌标存储在database table中, 并且通过SQL 来操作.
* V数组(Varrays) 支持一个固定数量的元素集(尽管你可以在运行时改变元素的个数), 他们使用顺序数字做下标, 你可以指定某种SQL types, 允许varrays存储在database table中, 你可以通过SQL来存储或者删除, 但是Varrays 的灵活性要低于内嵌表.

尽管集合只有一维, 但是你可以定义一个或者更多PL/SQL types, 然后在这些类型中定义变量. 你可以在一个存储过程, 函数, 包中定义集合类型, 你可以在函数间传递集合变量参数.

要查询集合的数据, 比查询简单类型要复杂,你可以存储PL/SQL记录或者SQL对象类型在一个集合中, 内嵌表和varrays 能够作为对象类型的属性.

a) 理解内嵌表(Nested table)
PL/SQL内嵌表代表一组值, 你可以把他们看作一个一维数组(没有声明元素的个数), 你可以构建多维数组, 通过创建一个内嵌表,内嵌表中再内嵌表的方式.

在操作系统中, 内嵌表是列类型, Oracle无顺序的存储一行内嵌表, 当你移除一个内嵌表从database 到一个PL/SQL变量, 这行被给予一个顺序的下标(从1开始偏移), 这给予了你类似数组的访问.

内嵌表区别于数组有两个重要的方面:
* 内嵌表没有一声明元素的个数, 而数组需要预先声明元素个数, 内嵌表的size可能自动增长.不过,会限制增长的最大限.
* 内嵌表也许没有连续下标, 然而数组始终是连续下标, 初始化的时候, 内嵌表的下标是连续的, 但是它们可能会变得稀疏, 你能够在一个内嵌表中删除元素,使用DELETE() 过程, NEXT()函数允许你遍历(Iterator)内嵌表.

下面一张图来表示Array VS(versus) Nested Table:
312 | 17 | 99 | 407 | 83 | Fixed
x(1) x(2) x(3) x(4) x(5) Bound

312 | | 99 | 407 | | sparse
x(1) x(3) x(4) Unbounded

b) 理解Varrays
Items of type VARRAY 被称为VARRAYS, 它们允许你引用单独的array元素做操作, 或者作为集合整体做操作. 为了引用一个元素, 你使用标准下标语法. 比如: Grade(3) 表示 varray Grades 的第三个元素.

如图示: Varray Grades
B | C | A | A | C | D | B | | | | Maximun size=10
(1) (2) (3) (4) (5) (6) (7)

一个varray 有最大size, 在定义的时候指定. 下标索引从1开始.

c) 理解关联数组(Associative Arrays, Index-By Tables)
关联数组是一组键-值对, key可以是Integer or String.

下面是一个关联数组得范例:
DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
howmany NUMBER;
which VARCHAR2(64);
BEGIN
country_population('china') := 100000;
country_population('korean') := 10;
country_population('american') := 100;
howmany := country_population('china') + country_population('korean');
dbms_output.put_line(howmany);
which := country_population.FIRST;
dbms_output.put_line(which);
dbms_output.put_line(country_population.LAST);
end;
///
100010
american -- first 是american, 由此看来, 关联数组得order,好像是按照字母表?
korean

Globalization 设置对关联数组得key值得影响:
* 当你定义关联数组得key类型, 你必须使用: varchar2, string, 或者long类型.
* 作为key得值, 你可以指定NCHAR, NVARCHAR2, 甚至DATE类型(date类型需要使用to_char()函数转化为varchar2).
* 你必须谨慎,当你使用其他类型作为key得值时, 比如: 使用SYSDATE, 这个值可能会随着NLS_DATE_FORMATE得改变而改变; NVARCHAR2得值能够转为相同得VARCHAR2值; CHAR得值要注意字符宽度, CHAR类型会自动补位, 所以如果定义char(6)='china', 那么member_date('china') 会返回'ORA-01403: no data found' 错误, 因为指定得'china'(5字节) 不等于'china '(6字节).
* 当你传递一个关联数组作为参数到一个远程database, 客户端和服务器端可能有不同得globalization settings, 当远程数据库执行例如FIRST 和 NEXT操作时, 它会使用自己得字符顺序, 即使与原始得顺序不同. 如果由于字符集不同,导致在远程服务器上, 现在得key已经不唯一, 程序将接受到一个VALUE_ERROR 异常.

d) 理解PL/SQL Records
Records 是一组字段得组合, 类似于table 的一行, %ROWTYPE 属性让你可以定义一个PL/SQL record 代表 table的一行, 不需要列举所有的列. 你的代码将保持有效, 即使列改变了. 如果你希望代表一个列, 从其他的tables, 你可以定义一个视图或者定义一个cursor 来查询正确的列, 然后在视图或者cursor上应用%ROWTYPE.

2, 选择哪种集合类型?
如果你已经使用其他语言编写了业务逻辑,你可以转换这些语言的集合对应PL/SQL集合类型:
* 其他语言的数组对应于 PL/SQL 的Varrays.
* 其他语言的Sets, 对应于 PL/SQL 的Nested tables.
* 其他语言的Hash table, 对应PL/SQL的Associate arrays.

a) 在Nested Tables 和Associative Arrays 中选择?
Nested Tables 和 Associative Arrays 都使用类似的下标, 但是当持久化和参数传递时, 他们有不同的特性(characteristic).
内嵌表能被存储到数据库的一列, 但是关联数组不可以; 内嵌表能指定SQL操作,当你将一个single-
column 表连接到一个大表的时候.
关联数组对关联一个小的查询表, 这个集合在过程被调用,或者package被初始化的时候,能被构造到内存中很合适, 他们适合收集容量未知的信息, 因为他们本身没有固定size, 他们的index 值更灵活, 因为关联数组的下标可以是负值, 能非顺序的,也能使用string来替代数值.
PL.SQL自动转化host arrays 和 assosiative arrays通过使用数字key值, 最有效的方法传递集合to/from 数据库, 是设置数据类型为关联数组.

b)选择Nested Tables 还是 Varrays?
Varrays 是一种很好的选择,当:
* 元素的个数已知
* 元素通常都顺序访问.

当存储到数据库中, varrays 保持他们的顺序和下标.
每一个varray 都作为一个单独对象存储, 要么在一个表的内部作为一列(如果varray<=4kb), 要么在表的外部,但是在同一个tablespace中(如果varray>4kb), 你必须一次整体update or retrieve 所有的元素. 这很适合于一次操作所有元素的需求. 但是你也许会发现对大量的元素使用这种方式来操作很不现实.

Nested tables 是一个很好的选择, 当:
* 下标index,不需要连续
* 元素格式没有固定的大小, 然后, 一个最大限值会被设置.
* 你可能会delete or update一些元素,但不是同时对所有的元素操作.
* You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Nested tables 可能是sparse, 你可以删除元素, 而并不需要移动其他元素.
Nested table 数据被存储在一个separate store table, 数据库将对tables做联结操作, 当你访问内嵌表时, 这使得内嵌表适合查询和更新集合上的某一些元素.对于保存在数据库的内嵌标, 你不能依赖顺序和下标,因为顺序和下标没有保存在数据库中.

3, 定义集合类型和声明集合变量
你可以定义TABLE 和 VARRAY 类型在PL/SQL的declare部分, 子程序,或者package使用TYPE定义.

对于内嵌表和varrays的定义, table和varray的元素的类型可以是任何PL/SQL类型, 除了REF CURSOR.

当定义一个VARRAY类型, 你必须指定最大size, 看看以下的例子:
DECLARE
TYPE calendar is VARRAY(366) OF DATE;

a) 定义一个Associative Array
关联数组允许你插入一个元素,使用key值, key值并不连续, key的类型可以是PLS_INTEGER, BINARY_INTEGER, VARCHAR2, VARCHAR, STRING, LONG

--定义一个关联数组, 元素类型是%ROWTYPE, 索引类型PLS_INTEGER
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
emp_tab EmpTabTyp;
BEGIN
select * into emp_tab(7369) from emp where empno = 7369;
dbms_output.put_line(emp_tab.FIRST);
dbms_output.put_line(emp_tab(7369).ename);
END;
----------------------
7369
smith

b) 声明PL/SQL集合变量
--定义一个内嵌表类型, 元素类型为varchar2(30)
--定义一个varray类型, 元素类型为integer, size=5
--定义一个关联数组类型, 元素类型为number, index类型为PLS_INTEGER;
--定义一个关联数组类型, 元素类型为VARCHAR2(32), index类型为PLS_INTEGER;
--定义一个关联数组类型, 元素类型为VARCHAR2(32), index类型为VARCHAR2(64);
declare
TYPE nested_type IS TABLE OF varchar2(30);
TYPE varray_type IS VARRAY(5) OF integer;
TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
BEGIN
v1 := nested_type('shipping', 'sales', 'finance', 'payroll'); --初始化
v2 := varray_type(1, 2, 3, 4, 5);
v3(99) := 10;
v3(7) := 100;
v4(42) := 'smith';
v4(54) := 'jones';
v5('canada') := 'North America';
END;

可以使用%TYPE来指定为指定变量所声明的集合类型一样, 例如:
declare
TYPE few_depts IS VARRAY(5) OF VARCHAR2(30);    --定义varray, 元素类型为varchar2(30), size=5
some_depts few_depts;                           --声明some_depts为few_depts类型
local_depts some_depts%TYPE;                    --使用%TYPE, 指定local_depts为与some_depts一样的类型
global_depts some_depts%TYPE;
begin
some_depts := few_depts('a', 'b', 'c', 'd', 'e');
dbms_output.put_line(some_depts(1));
local_depts := few_depts('e', 'b', 'f');
dbms_output.put_line(local_depts(1));
end;

你可以声明集合作为过程或函数的一个parameter, 如:
--声明一个过程参数为内嵌表
create or replace package personnel
as
TYPE staf_list IS TABLE OF emp.empno%TYPE;
procedure award_bonuses(emp_nos staf_list);
end personnel;
/
create or replace package body personnel
as
procedure award_bonuses(emp_nos staf_list)
is
begin
    for i in emp_nos.FIRST..emp_nos.LAST
    loop
      update emp set sal = sal + 1 where emp.empno = emp_nos(i);
    end loop;
   end;
end;
/
--调用personnle的award_bonuses过程,这里注意如何将good_emp声明为包内的集合类型(包名.集合名)
declare
good_emp personnel.staf_list;
begin
good_emp := personnel.staf_list(7369, 7499, 7521);
personnel.award_bonuses(good_emp);
end;
/

为了指定元素类型,你可以使用%type, 它提供了一个变量或者一个数据库列的类型. 同样的,你可以使用%ROWTYPE, 它提供了一个游标或数据库表的行类型, 例如:
declare
TYPE EmpList IS TABLE OF emp.empno%TYPE; --定义内嵌表, 元素类型和emp表的mepno字段类型一致
CURSOR c1 IS select empno from emp; --定义游标C1
TYPE Senior_Salespeople IS VARRAY(10) OF emp%ROWTYPE; --定义VARRAY, 类型与emp表的行类型一致.
CURSOR c2 IS select ename from emp; --定义游标C2
TYPE NameList is VARRAY(20) OF c2%ROWTYPE; --定义VARRAY, 类型与游标C2的行类型一致.
begin
null;
end;

使用一个RECORD类型,来作为VARRAY类型中元素的类型:
DECLARE   TYPE name_rec IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25) );
   TYPE names IS VARRAY(250) OF name_rec;
BEGIN
   NULL;
END;

还可以使用NOT NULL来对元素的值进行约束:
declare
TYPE EmpList IS TABLE OF emp.empno%TYPE NOT NULL;
v_emps EmpList := EmpList(166, 167, 168);
begin
v_emps(1) := null;
end;
/
这里指定v_emps(1) := null, 将会抛出一个异常, PLS-00382: expression is of wrong type.

4, 初始化
在你初始化之前, 内嵌表和varray 自动为null值. 初始化内嵌表和varray, 你可以使用一个构造器, 这个构造器是系统自定义函数, 名字与集合类型名称一致, 这个集合构造函数允许传入元素值进去.

你必须显式的调用一个构造器,为每一个内嵌表和varray类型; 而关联数组, 是第三种类型的集合, 不需要使用构造器

由于一个内嵌表本身没有声明size长度, 你可以在初始化的时候put 足够多的元素.

a)使用构造器初始化一个内嵌表:
DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(32);
dept_names nested_type;
BEGIN
dept_names := nested_type('shipping', 'finance', 'sales');
END;
/

b) 除非你强加于NOT NULL 约束, 否则你可以传递NULL元素, 在一个构造器中,下面的集合构造器包含NULL 元素:
DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
   dept_names dnames_tab;
   TYPE dnamesNoNulls_type IS TABLE OF VARCHAR2(30) NOT NULL;
BEGIN
   dept_names := dnames_tab('Shipping', NULL,'Finance', NULL);
-- If dept_names was of type dnamesNoNulls_type, we could not include
-- null values in the constructor
END;
/

c) 你可以在声明集合类型的时候,初始化集合类型,这是一个好的编程习惯:
DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
   dept_names dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll');
BEGIN
   NULL;
END;
/

d) 如果你调用一个构造器,但是不传递任何参数,你会获取一个空的, 但是non-null集合:
DECLARE
   TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
   dept_names dnames_var;
BEGIN
   IF dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Before initialization, the varray is null.');
-- While the varray is null, we cannot check its COUNT attribute.
--   DBMS_OUTPUT.PUT_LINE('It has ' || dept_names.COUNT || ' elements.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Before initialization, the varray is not null.');
   END IF;
   dept_names := dnames_var(); -- initialize empty varray
   IF dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('After initialization, the varray is null.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('After initialization, the varray is not null.');
      DBMS_OUTPUT.PUT_LINE('It has ' || dept_names.COUNT || ' elements.');
   END IF;
END;
/

5, 引用集合元素
语法: collection_name(subscript), 这里下标在大多数情况下是整数, 在关联数组中,也可能是varchar2类型.
通常允许的下标范围是:
* 对于内嵌标, 1..2147483647(即1..2^32, the upper limit of PLS_INTEGER)
* 对于varray, 1..size_limit, (size_limit<=2147483647)
* 对于使用数值作为key的关联数组, (-2^32..2^32)
* 对于使用字符作为key的关联数组, 值的长度由varchar2指定.

a) 一个简单的例子, 展示如何引用一个内嵌表的元素:
DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster := Roster('A', 'B', 'C', 'D');
PROCEDURE verify_name(the_name VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(the_name);
END;
BEGIN
FOR i IN names.FIRST..names.LAST
LOOP
    IF names(i) = 'D' THEN
      DBMS_OUTPUT.PUT_LINE(names(i));         -- 这里引用了内嵌表的第三个元素
    END IF;
END LOOP;
verify_name(names(3));
END;

b) 展示如何在一个函数调用里面, 引用一个关联数组的元素:
DECLARE
TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
n PLS_INTEGER := 5;
sn PLS_INTEGER := 10;
m PLS_INTEGER := 3;
FUNCTION get_sum_multiples(
    multiple IN PLS_INTEGER,
    num IN PLS_INTEGER
) RETURN sum_multiples
IS
    s sum_multiples;
BEGIN
    FOR i IN 1..num LOOP
      s(i) := multiple * (( i * (i + 1)) / 2) ;
    END LOOP;
    RETURN s;
END get_sum_multiples;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
    to_char(m) || ' is ' || to_char(get_sum_multiples(m, sn)(n)));
END;

6, 集合赋值(Assigning Collections)
语法:
collection_name(subscript) := expression;

你可以使用例如SET, MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT 来传递一个内嵌表作为一个指定的语句的一部分.

对一个集合元素进行赋值, 可能在这如下情况遭遇异常:
* 如果下标是NULL, 或者是不可转换的数据类型, PL/SQL抛出预定义异常:VALUE_ERROR, 通常情况,下标必须是一个整数, 关联数组则允许定义为varchar2下标.
* 如果下标引用一个未初始化的元素, PL/SQL抛出 SUBSCRIPT_BEYOND_COUNT;
* 如果集合自动为null, PL/SQL抛出 COLLECTION_IS_NULL.

a) 下面一个简单的例子,显示必须拥有相同的集合类型,才能进行赋值工作,光元素类型一致是不够的:
DECLARE
TYPE last_name_type IS VARRAY(3) OF VARCHAR2(64);
TYPE first_name_type IS VARRAY(3) OF VARCHAR2(64);
group1 last_name_type := last_name_type('jone', 'wong', 'mar');
group2 last_name_type := last_name_type('ken', 'pat', 'sin');
group3 first_name_type := first_name_type('trev', 'malcom', 'zusi');
BEGIN
group1 := group2; --运行这句正常
--group3 := group2; --运行这句抛出异常:PLS-00382: expression is of wrong type
END;
如上, group1 和group2都是last_name_type集合类型, group3是first_name_type类型,因此赋值失败.

b) 如果你将一个空的内嵌表赋值给另一个内嵌表/varray, 那么另一个内嵌表将需要重新初始化.
DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
-- This nested table has some values
   dept_names dnames_tab := dnames_tab('Shipping','Sales','Finance','Payroll');
-- This nested table is not initialized ("atomically null").
   empty_set dnames_tab;
BEGIN
-- At first, the initialized variable is not null.
   if dept_names IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('OK, at first dept_names is not null.');
   END IF;
-- Then we assign a null nested table to it.
   dept_names := empty_set;
-- Now it is null.
   if dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('OK, now dept_names has become null.');
   END IF;
-- We must use another constructor to give it some values.
   dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END;
/
如果上面的例子不重新初始化, 会抛出异常ORA-06531: Reference to uninitialized collection

c) 展示一些ANSI-标准的操作, 你可以应用在内嵌表中:
DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1, 2, 3, 3);
nt2 nested_typ := nested_typ(3, 2, 1);
nt3 nested_typ := nested_typ(2, 3, 1, 3);
nt4 nested_typ := nested_typ(1, 2, 4);
answer nested_typ;
PROCEDURE print_nested_table(the_nt nested_typ)
IS
    output varchar2(128);
BEGIN
    if the_nt is null then
      dbms_output.put_line('Results:<NULL>');
      return;
    end if;
    if the_nt.COUNT = 0 then
      dbms_output.put_line('Results:empty set');
       return;
    end if;
    for i in the_nt.FIRST..the_nt.LAST
    loop
      output := output || the_nt(i) || ' ';
    end loop;
    dbms_output.put_line('Results:' || output);
end;
begin
answer := nt1 MULTISET UNION nt4;                  -- 并集, (1, 2, 3, 1, 2, 4)
print_nested_table(answer);
answer := nt1 MULTISET UNION nt3;                  -- 并集, (1, 2, 3, 2, 3, 1,3)
print_nested_table(answer);
answer := nt1 MULTISET UNION DISTINCT nt3;         -- 并集(去除重复集) (1, 2, 3)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT nt3;              -- 交集(1, 2, 3)
print_nested_table(answer);
answer := nt2 MULTISET INTERSECT DISTINCT nt3;     -- 交集(去除重复集) (1, 2, 3)
print_nested_table(answer);
answer := SET(nt3);                                -- 类似于distinct (2, 3, 1)
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT nt2;                 -- 包含在nt3, 但不包含在nt2中的子集
print_nested_table(answer);
answer := nt3 MULTISET EXCEPT DISTINCT nt2;
print_nested_table(answer);
end;
--------------------------
Results:1 2 3 1 2 4
Results:1 2 3 2 3 1 3
Results:1 2 3
Results:3 2 1
Results:3 2 1
Results:2 3 1
Results:3
Results:empty set

* 对于每个嵌套表的多重集运算符，都只会从每个集合中选择一个值来进行合并或者排除。也就是说在两个嵌套表中如果出现重复的值，那么对于重复的值只有一个会被除去。例如，num_tab_typ(1,1,2,3) MULTISET EXCEPT num_tab_typ(1,2,3) 的运行结果将会是num_tab_typ(1).

d) 下面一个例子, 给VARRAY赋值复杂的数据类型:
DECLARE
TYPE emp_name_rec is RECORD (
    ename     emp.ename%TYPE,
    hiredate    emp.hiredate%TYPE
    );

   TYPE EmpList_arr IS VARRAY(10) OF emp_name_rec;
   SeniorSalespeople EmpList_arr;

   CURSOR c1 IS SELECT ename, hiredate FROM emp; -- 这里因为只希望应用行的ename, hiredate字段,所以定义一个游标
   Type NameSet IS TABLE OF c1%ROWTYPE;           -- 然后将NameSet的元素类型, 声明为CURSOR%TYPE.
   SeniorTen NameSet;
   EndCounter NUMBER := 10;

BEGIN
SeniorSalespeople := EmpList_arr();
SELECT ename, hiredate BULK COLLECT INTO SeniorTen FROM
     emp WHERE job = 'salesman' ORDER BY hiredate;
IF SeniorTen.LAST > 0 THEN
    IF SeniorTen.LAST < 10 THEN EndCounter := SeniorTen.LAST;
    END IF;
    FOR i in 1..EndCounter LOOP
      SeniorSalespeople.EXTEND(1);
      SeniorSalespeople(i) := SeniorTen(i);                --元素的赋值,只要元素一致,就可以.
      DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).ename|| ', '
       ||
       SeniorSalespeople(i).hiredate);
    END LOOP;
END IF;
END;
/
* 使用批量装载SELECT <column_list> BULK COLLECT INTO <variable_list>的时候，Oracle会一次性的将结果集装载到PGA中再进行下步操作。如果结果集较大并且OS物理内存紧张的话，可能会导致ORA-4030错误和严重的SWAP.

* 另外, 如果不使用游标, 而是直接: Type NameSet IS TABLE OF emp%rowtype; 声明NameSet的元素为emp的行类型时, 在进行select ..BULK COLLECT INTO SeniorTen .. 查询的时候,会抛出异常: PL/SQL: ORA-00913: too many values; 而在SeniorSalespeople(i) := SeniorTen(i) 语句也会抛出异常: PLS-00382: expression is of wrong type.

e) 下面一个例子, 给TABLE 赋值复杂的数据类型
declare
TYPE ename_rec IS RECORD (
ename emp.ename%TYPE,
hiredate emp.hiredate%TYPE
);
TYPE EmpName_tab IS TABLE OF ename_rec;
seniorsales EmpName_tab;
TYPE EmpCurType IS REF CURSOR;
emp_cur EmpCurType;
begin
open emp_cur for select ename, hiredate from emp where job='salesman' order by hiredate;

fetch emp_cur BULK COLLECT INTO seniorsales;

close emp_cur;

if seniorsales is null then
dbms_output.put_line('fetch no row, seniorsales is null');
end if;

if seniorsales.LAST > 0 then
    for i in seniorsales.FIRST..seniorsales.LAST
    loop
      dbms_output.put_line(seniorsales(i).ename || ' ' || seniorsales(i).hiredate);
    end loop;
else
    dbms_output.put_line('fetch no row, seniorsales is empty');
end if;
end;
/

**另外, 如果给定job='salesman3'(这个条件肯定不存在), 那么返回得seniorsales 不为null, 而是为空集合(empty, 即seniorsales.LAST=0),

7, 集合的比较
a) 检测是否一个集合为空
declare
TYPE emp_name_rec IS RECORD(
    ename emp.ename%TYPE,
    hiredate emp.ename%TYPE
);
TYPE EmpList_tab IS TABLE OF emp_name_rec;
goodemps EmpList_tab;
begin
if goodemps is null then
    dbms_output.put_line('goodemp is null');
else
    dbms_output.put_line('goodemp is not null');
end if;
end;
/
goodemp is null

b)比较两个内嵌表, 例子显示了对两个内嵌表进行等于和不等于的比较, 对于集合的比较, 不在乎他们的顺序,只是比较集合内的元素是否一致.
declare
TYPE dnames_tab IS TABLE OF VARCHAR2(20);
dept1 dnames_tab := dnames_tab('ship', 'sales', 'finance', 'payroll');
dept2 dnames_tab := dnames_tab('sales', 'finance', 'ship', 'payroll');
dept3 dnames_tab := dnames_tab('sales', 'finance', 'payroll');
begin
if dept1 = dept2 then
    dbms_output.put_line('dept1 = dept2, although there order is not the same');
else
    dbms_output.put_line('dept1 != detp2, although there element is same');
end if;
if dept1 = dept3 then
    dbms_output.put_line('dept1 = dept3 , but why?');
else
    dbms_output.put_line('dept1 != dept3, i guess it''s right');
end if;
end;
/

c) 使用Set 操作, 比较内嵌表, 下面的例子使用ANSI-标准 set 操作:d
declare
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1, 2, 3);
nt2 nested_typ := nested_typ(3, 2, 1);
nt3 nested_typ := nested_typ(2, 3, 1, 3);
nt4 nested_typ := nested_typ(1, 2, 4);
answer boolean;
howmany number;
procedure testify(truth boolean default null, quantity number default null) is
begin
    if truth is not null then
      dbms_output.put_line(case truth when true then 'True' when false then 'False' end);
    end if;
    if quantity is not null then
      dbms_output.put_line(quantity);
    end if;
end;
begin
answer := nt1 IN (nt2, nt3, nt4);      --True, 因为nt1=nt2, 如果nt2增加一个元素, 那么返回false
testify(truth => answer);

answer := nt1 SUBMULTISET OF nt3; --true, nt1 是否nt2的子集
testify(truth => answer);

answer := nt1 NOT SUBMULTISET OF nt4; -- true, nt1 不是 nt4的子集
testify(truth => answer);

howmany := CARDINALITY(nt3); -- nt3 个元素个数 , 4
testify(quantity => howmany);

howmany := CARDINALITY(SET(nt3)); -- nt3的元素个数(distinct), 3
testify(quantity => howmany);

answer := 4 MEMBER OF nt1; -- false, 4 不是nt1的元素
testify(truth =>answer);

answer := nt3 IS A SET; -- false, nt3 的集合元素值有冗余,不是唯一的
testify(truth => answer);

answer := nt1 IS EMPTY;
testify(truth =>answer); -- false, nt1 的元素不为空.
end;
/

* x in (x1, x2, x3..), 只要x等于x1, x2, x3中的任意一个, 那么返回true.

8, 使用多级集合类型
你可以创建一个集合类型,作为一个集合类型的元素. 比如,你可以创建一个 table of varray, varray of varray, varray of table. 等等.

当创建一个nested table of nested table 作为一个SQL的列, 您需要查询CREATE TABLE 语法看如何存储到表.

a) 多级VARRAY
declare
   TYPE t1 IS VARRAY(10) OF INTEGER;
   TYPE nt1 IS VARRAY(10) OF t1;
   va t1 := t1(1, 2, 3);
   nva nt1 := nt1(va, t1(2, 3), t1(1), va);
   i integer;
   val t1;
begin
   i := nva(2)(2);
   dbms_output.put_line('nva(2)(2) is :' || i);

   nva.EXTEND;      -- 必须, 否则会抛出RA-06533: Subscript beyond count, 在这之前nva.COUNT=4
   nva(5) := t1(5, 6);
   i := nva(5)(2);
   dbms_output.put_line('[before replace]nva(5)(2) is :' || i);
   nva(5)(2) := 0;
   i := nva(5)(2);
   dbms_output.put_line('[after replace]nva(5)(2) is :' || i);

   nva(5).EXTEND; -- 对元素5 的varray做extend.
   nva(5)(3) := 3;
   i := nva(5)(3);
   dbms_output.put_line('[extend]nva(5)(3) is :' || i);
end;
/

** 这里发现, 对于一个varray(10)的集合, 如果在初始化的时候只初始化3个元素, 那么以后的元素要增加的话,需要使用.EXTEND方法.

b) 多级内嵌表
DECLARE
TYPE tb1 IS TABLE OF VARCHAR2(20);
TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
TYPE Tv1 IS VARRAY(10) OF INTEGER;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
vtb1 tb1 := tb1('one', 'three');
vntb1 ntb1 := ntb1(vtb1);
vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); -- table of varray elements
BEGIN
vntb1.EXTEND;
vntb1(2) := vntb1(1);
-- delete the first element in vntb1
vntb1.DELETE(1);
-- delete the first string from the second table in the nested table
vntb1(2).DELETE(1);
END;
/

c) 多级关联数组
DECLARE
TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
-- the following is index-by table of index-by tables
TYPE ntb1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;
TYPE va1 IS VARRAY(10) OF VARCHAR2(20);
-- the following is index-by table of varray elements
TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;
v1 va1 := va1('hello', 'world');
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1; -- empty table
BEGIN
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3(34) := va1(33, 456, 656, 343);
-- assign an empty table to v2(35) and try again
v2(35) := v5;
v2(35)(2) := 78; -- it works now
END;
/

9, 使用集合方法
集合方法使集合的操作更简单, 这些方法包括:COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, TRIM.

* 集合方法不能在SQL statment中调用
* EXTEND, TRIM 方法不能用在关联数组
* EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT是函数, EXTEND, TRIM, DELETE是过程.
* EXISTS, PRIOR, NEXT, TRIM EXTEND, DELETE的参数与集合的小标一致, 通常是整数, 关联数组可能是字符串.
* 只有EXISTS能被应用在NULL 集合, 如果你应用其他的方法在一个NULL 集合, PL/SQL将抛出COLLECITON_IS_NULL异常.

a) EXISTS(n)
返回true, 如果n元素存在, 否则, 返回false.

通过联合EXISTS with DELETE, 你可以对稀疏内嵌表工作; 你同样可以使用EXISTS 来避免引用一个不存在的元素.
如果传递的小标溢出(out-of-range) ,EXISTS返回false, 来取代抛出SUBSCRIPT_OUTSIDE_LIMIT异常.
如果集合is null, 仍然可以应用exists方法, 而不会抛出异常.

eg:
declare
   TYPE NumList IS TABLE OF INTEGER;
   n NumList := NumList(1, 3, 5, 7);
begin
   n.DELETE(2);
   if n.EXISTS(1) then
     dbms_output.put_line('#1 exists');
   end if;
   if n.exists(2) then
     dbms_output.put_line('#2 exists');
   else
     dbms_output.put_line('#2 not exists, and no raise exception,good');
   end if;
   if n.exists(99) then
     dbms_output.put_line('#99 exists, impossible');
   else
     dbms_output.put_line('#99 is not exists, it''s out-of-range, but no raise exception');
   end if;
end;
/

b) COUNT:
返回当前集合包含的元素个数

eg:
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
   DBMS_OUTPUT.PUT_LINE('There are ' || n.COUNT || ' elements in N.');
   n.EXTEND(3); -- Add 3 new elements at the end.
   DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
   n := NumList(86,99); -- Assign a completely new value with 2 elements.
   DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
   n.TRIM(2); -- Remove the last 2 elements, leaving none.
   DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
END;
/
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 2 elements in N.
Now there are 0 elements in N.

对于varray, COUNT总是等于LAST, 你可以增加或者减少varray的size 使用EXTEND 和 TRIM方法, COUNT也将因此而改变.

对于内嵌表, COUNT 通常等于LAST, 但是, 如果从内嵌表中间删除元素, COUNT 将小于LAST; 使用不带参数的DELETE方法, 将设置COUNT = 0(此时集合是empty, 并不是null);

c) LIMIT
对于内嵌表和关联数组, LIMIT返回NULL, 对于varray, LIMIT返回元素能够包含的最大长度(通常在varray定义的时候指定).

eg:
declare
TYPE NumList IS VARRAY(5) OF INTEGER;
vnum NumList := NumList(1, 2, 3);
begin
dbms_output.put_line('limit=' || vnum.LIMIT);
vnum.EXTEND(2);
vnum(4) := 4;
vnum(5) := 5;
dbms_output.put_line('limit=' || vnum.LIMIT);
for i in vnum.FIRST..vnum.LAST loop
dbms_output.put_line(vnum(i));
end loop;

vnum.TRIM(3);
dbms_output.put_line('limit=' || vnum.LIMIT);
for i in vnum.FIRST..vnum.LAST loop
dbms_output.put_line(vnum(i));
end loop;
END;
/
limit=5
limit=5
1 2 3 4 5
limit=5
1 2

d) FIRST / LAST
FIRST / LAST 方法返回first 和last 的index number.

对于关联数组, 使用varchar2 key value, 返回lowest 和 highest 键值, 默认的排序基于binary, 如果NLS_COMP 初始化设置为ANSI, 那么排序着基于NLS_SORT的初始化参数.

如果集合为empty , FIRST 和LAST 返回null; 如果集合只有一个元素, FIRST=LAST.

eg:
eclare
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1, 2, 3);
offset NUMBER;
begin
dbms_output.put_line('FIRST=' || n.FIRST);
dbms_output.put_line('LAST=' || n.LAST);
FOR i IN n.FIRST .. n.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
END LOOP;

n.delete(2);
dbms_output.put_line('FIRST=' || n.FIRST);
dbms_output.put_line('LAST=' || n.LAST);

--第一种循环方式, 对于稀疏表
FOR i IN n.FIRST .. n.LAST
LOOP
    if n.EXISTS(i) then   -- 这里,如果不加exists方法, 当循环到n(2) 的时候, 直接引用将抛出异常ORA-01403: no data found
      DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
    end if;
END LOOP;

--第二种循环方式, 对于稀疏表(推荐)
if n is not null then
    offset := n.FIRST;
    while offset is not null loop
      DBMS_OUTPUT.PUT_LINE('Element #' || offset || ' = ' || n(offset));
      offset := n.NEXT(offset);
    end loop;
end if;
end;
/

对于VARRAY, FIRST 总是等于1, LAST 总是等于COUNT.
对于nested table, 通常FIRST=1, LAST=COUNT, 但是如果从内嵌表删除了元素, FIRST 可能比1大, LAST 可能也比COUNT大.

当扫描元素, FIRST 和LAST都会忽略删除的了元素.

e) PRIOR / NEXT
prior(n) 返回集合中比参数n 小的索引值, NEXT(n) 返回比参数n 大的索引值, 如果没有更小的值, prior(n)返回null, 如果没有更大的值, next(n) 返回null.

对于关联数组, 使用varchar2 作为索引值, 这些方法返回合适的key值, index顺序基于binary排序(如果NLS_COMP初始化值是ANSI, 那么排序顺序基于NLS_SORT指定的参数)

使用这些方法, 比使用loop 小标的方法更值得依赖, 因为元素可能在loop的过程中被insert/delete.

eg:
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
   DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2));
   DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2));
   n.DELETE(3); -- Delete an element to show how NEXT can handle gaps.
   DBMS_OUTPUT.PUT_LINE('Now the element after #2 is #' || n.NEXT(2));
   IF n.PRIOR(n.FIRST) IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Can''t get PRIOR of the first element or NEXT of the last.');
   END IF;
END;
/

你还可以使用PRIOR or NEXT 来遍历所有索引是顺序下标的集合. eg:
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,3,5,7);
   counter INTEGER;
BEGIN
   n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps, the loop logic is more extensive. We start at
-- the first element, and keep looking for the next element until there are no more.
   counter := n.FIRST;
   WHILE counter IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE('Counting up: Element #' || counter || ' = ' ||
                             n(counter));
      counter := n.NEXT(counter);
   END LOOP;
-- Run the same loop in reverse order.
   counter := n.LAST;
   WHILE counter IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE('Counting down: Element #' || counter || ' = ' ||
                             n(counter));
      counter := n.PRIOR(counter);
   END LOOP;
END;
/

f) extend
如果要增加一个内嵌表或varray数组的size, 使用EXTEND.

这个过程有三种使用形式:
* EXTEND: appends one null element to a collection.
* EXTEND(n) : appends n null elements to a collection.
* EXTEND(n,i) : appends n copies of the ith element to a collection.

你不能使用EXTEND方法, 应用到一个没有初始化的集合. 如果你对一个table / varray 强征了一个NOT NULL约束, 你不能使用前面两种使用形式.

EXTEND操作的是集合的内部size, 如果EXTEND 遇到deleted element, 它在统计的时候会包括他们. PL/SQL对删除的元素保留一个占位符. 所以你能够re-create them by assigning new values.

eg:
这个方法有三种使用形式:
* EXTEND 扩充一个空元素到数组
* EXTEND(N) 扩充n个空元素到数组
* EXTEND(N, i) 扩充n个空元素, 使用第i个元素做填充

eg:
declare
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1, 2, 3, 4, 5);
procedure print_list(theList NumList)
is
    output varchar2(255);
begin
    if theList.count = 0 then
      dbms_output.put_line('collection is empty');
    else
      --for i in theList.FIRST..theList.LAST loop
        --output := output || nvl(to_char(theList(i)), 'null') || ' ';
      --end loop;
      dbms_output.put_line(output || '    count=' || theList.count || ' last=' || theList.LAST);
    end if;
end;
begin
print_list(n);
n.delete(5);
n.delete(4);
print_list(n);

n.extend(3);
print_list(n);
end;
/
count=5 last=5
count=3 last=3
count=6 last=8   --这里, 由于deleted element有占位符, 所有count=6, 但是LAST=8

当包含删除元素的时候, 内嵌表的内部size 不同于 COUNT / LAST 的值(只是针对delete(n), 而不是无参的delete), 比如, 如果你初始化了5个元素, 你删除掉了2 和 5, 那么内部size=5, LAST=4, COUNT=3. 因为LAST 和COUNT在使用的时候, 会忽略deleted element.

g) trim
这个方法有两种使用形式:
* TRIM : removes one element from the end of a collection. 从一个集合元素末尾移除一个元素
* TRIM(n) : removes n elements from the end of a collection. 从一个集合元素末尾移除n个元素

如果你想移除所有的元素, 使用DELETE 不带参数.

eg:
declare
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1, 2, 3, 5, 7, 11);
procedure print_list(theList NumList)
is
    output varchar2(128);
begin
    if n.COUNT = 0 then
      dbms_output.put_line('collection is empty');
    else
      for i in theList.FIRST..theList.LAST loop
        output := output || NVL(TO_CHAR(theList(i)), 'NULL') || ' ';
      end loop;
      dbms_output.put_line(output);
    end if;
end;
begin
print_list(n);
-- 移除尾部两个, 之后, count=last=4
n.trim(2);
print_list(n);

--移除count个, 之后, count=last=0
n.trim(n.count);
print_list(n);

--尝试移除x个, x>count, 会抛出 ORA-06533: Subscript beyond count
begin
    n := NumList(1, 2, 3);
    n.trim(100);
    exception
      when SUBSCRIPT_BEYOND_COUNT then
        dbms_output.put_line('i guess there werent 100 ele.');
end;

--尝试先调用删除, 然后调用trim, 这里trim(2) 移除的是4和一个占位符, 而不是4和2
n := NumList(1, 2, 3, 4);
n.delete(4);
print_list(n);
n.trim(2);
print_list(n);

end;
/

注意, 当使用trim之后, 会改变LAST 和 COUNT. 所在在这个基础上使用for i in n.FIRST..n.LAST loop 不会抛出异常.

如果n太大, trim(n) 会抛出一个SUBSCRIPT_BEYOUND_COUNT异常.

trim操作的是集合的内部size, 如果trim遭遇到deleted element. 它在统计的时候会包含他们. 这通常是指使用delete(n), 但是对无参数的DELETE无效. 无参数的DELETE 是移除所有的元素.

通常来说, 对于将内嵌表作为固定尺寸的array的时候使用DELETE, 当作为堆的时候使用trim和extend.
由于PL/SQL不会为trimmed element保留占位符, 你不能简单的为一个trimmed element重新分配新值.

h) delete
使用delete方法有三种形式:
* DELETE : 不带参数, 移除所有元素,设置count=0;
* DELETE(n) : 从关联数组或内嵌表移除一个元素. 如果n为null, delete(n) does nothing.
* DELETE(m,n) : 移除所有的从m..n范围的元素, 从一个关联数组, 或者内嵌表. 如果m>n, 或者m,n 为null, delete(m, n) does nothing.

eg:
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
   TYPE NickList IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(32);
   nicknames NickList;
BEGIN
   n.DELETE(2);    -- deletes element 2
   n.DELETE(3,6); -- deletes elements 3 through 6
   n.DELETE(7,7); -- deletes element 7
   n.DELETE(6,3); -- does nothing since 6 > 3
   n.DELETE;      -- deletes all elements
   nicknames('Bob') := 'Robert';
   nicknames('Buffy') := 'Esmerelda';
   nicknames('Chip') := 'Charles';
   nicknames('Dan') := 'Daniel';
   nicknames('Fluffy') := 'Ernestina';
   nicknames('Rob') := 'Robert';
-- following deletes element denoted by this key
   nicknames.DELETE('Chip');
-- following deletes elements with keys in this alphabetic range
   nicknames.DELETE('Buffy','Fluffy');
END;
/

varray 始终有连续的下标, 所以你不能使用delete(n)方法删除元素, 不过你可以使用不带参数的DELETE方法删除所有元素.

如果一个元素不存在, delete(n) 将忽略它,而不是抛出异常, PL/SQL保留一个占位符给deleted element, 所有你可以通过赋一个新值, 替换那个占位符, 不过这个操作只适用于delete(n), 对于无参delete, 是完全将所有元素删除,并不保留占位符.

DELETE方法让你能够保留稀疏的内嵌表 , 你可以保存sparse nested tables 到数据库, 就像其他的nested table一样.

9, 避免集合异常
常见的集合异常如下:
* COLLECTION_IS_NULL : 你尝试去操作一个null 的集合
* NO_DATA_FOUND: 一个下标代表的元素已经被删除了, 或者在关联表中的一个不存在的元素
* SUBSCRIPT_BEYOND_COUNT: 下标的大小超过集合元素的个数.
* SUBSCRIPT_OUTSIDE_LIMIT: 下标超过允许的范围. 比如,对一个varray(5),使用了extend(6).呵呵
* VALUE_ERRORL: 下标为null 或者不能转换为key type. 这个异常可能会发生在定义key为 PLS_INTEGER, 而下标的值超出PLS_INTEGER的range.
eg:
DECLARE
TYPE WordList IS TABLE OF VARCHAR2(5);
words WordList;
err_msg VARCHAR2(100);
PROCEDURE display_error IS
BEGIN
    err_msg := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE('Error message = ' || err_msg);
END;
BEGIN
BEGIN
    words(1) := 10; -- Raises COLLECTION_IS_NULL
-- A constructor has not been used yet.
-- Note: This exception applies to varrays and nested tables,
-- but not to associative arrays which do not need a constructor.
    EXCEPTION
      WHEN OTHERS THEN display_error;
END;
-- After using a constructor, we can assign values to the elements.
    words := WordList('1st', '2nd', '3rd'); -- 3 elements created
-- Any expression that returns a VARCHAR2(5) is valid.
    words(3) := words(1) || '+2';
BEGIN
    words(3) := 'longer than 5 characters'; -- Raises VALUE_ERROR
-- The assigned value is too long.
    EXCEPTION
      WHEN OTHERS THEN display_error;
END;
BEGIN
    words('B') := 'dunno'; -- Raises VALUE_ERROR
-- The subscript (B) of a nested table must be an integer.
-- Note: Also, NULL is not allowed as a subscript.
    EXCEPTION
      WHEN OTHERS THEN display_error;
END;
BEGIN
    words(0) := 'zero'; -- Raises SUBSCRIPT_OUTSIDE_LIMIT
-- Subscript 0 is outside the allowed subscript range.
    EXCEPTION
      WHEN OTHERS THEN display_error;
END;
BEGIN
    words(4) := 'maybe'; -- Raises SUBSCRIPT_BEYOND_COUNT
-- The subscript (4) exceeds the number of elements in the table.
-- To add new elements, call the EXTEND method first.
    EXCEPTION
      WHEN OTHERS THEN display_error;
END;
BEGIN
    words.DELETE(1);
    IF words(1) = 'First' THEN NULL; END IF; -- Raises NO_DATA_FOUND
-- The element with subcript (1) has been deleted.
    EXCEPTION
      WHEN OTHERS THEN display_error;
END;
END;
/
Error message = ORA-06531: Reference to uninitialized collection
Error message = ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Error message = ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Error message = ORA-06532: Subscript outside of limit
Error message = ORA-06533: Subscript beyond count
Error message = ORA-01403: no data found