[连载]Oracle 12cR2 PL/SQL语言参考 - 05 集合和记录

概述

PL/SQL允许你可以定义两种复合数据类型: 集合(collection)和记录(record)
复合数据类型存储的值拥有内部元素.你可以将整个复合类型的变量作为参数传递给子程序,
你可以单独访问复合类型变量的内部元素,内部元素可以是标量也可以是复合类型
在集合中,内部元素都拥有同样的数据类型,被称为元素(elements),你可以通过唯一索引来 访问集合变量的每一个元素,使用这种语法格式: 变量名(索引)
要创建一个集合变量,你要么定义一个集合类型然后创建一个该类型的变量,要么使用%TYPE
在记录中,内部元素可以拥有不同的数据类型,被称为域(fields),你可以通过它的名字来访 问记录变量中域的语法格式

变量名.域名

要创建一个记录变量,你要么定义一个RECORD类型然后创建一个该类型的变量,要么使用
%ROWTYPE或%TYPE
你可以创建一个包含记录的集合,也可以创建一个包含集合的记录

5.1 集合类型

PL/SQL拥有三种集合类型: 关联数组, 可变数组VARRAY(可变大小), 嵌套表

• 关联数组
  元素数量: 无需指定
  索引类型: 字符串或PLS_INTEGER
  密集或稀疏: 都有
  未初始化的状态: 空的(Empty)
  在哪里定义: PL/SQL块或包
  Can be ADT(Attribute Data Type): 不可以

• 可变数组VARRAY
  元素数量: 需要指定
  索引类型: Integer
  密集或稀疏: 总是密集的
  未初始化的状态: NULL
  在哪里定义: PL/SQL块,包或模式(schema)层级
  Can be ADT: 如果定义在模式(schema)层级则可以

• 嵌套表
  元素数量: 无需指定
  索引类型: Integer
  密集或稀疏: 开始时密集,可以变稀疏
  未初始化的状态: NULL
  在哪里定义: PL/SQL块,包或模式(schema)层级
  Can be ADT: 如果定义在模式(schema)层级则可以
  元素数量
  如果在定义时指定了元素的数量,那这就是集合所能存放的最大元素数
  如果没有指定元素数量,那么集合可以存放的元素的数量只受限于集合索引的数据类型的上限
  密集或稀疏
  一个密集的集合的两个元素之间没有缝隙,每个元素都被定义且有值(值可以是NULL,除非这个
  元素有非空约束),稀疏的集合的元素之间有缝隙
  未初始化的状态
  空(empty)集合可以存在,但没有元素,可以通过调用EXTEND方法来给空集合增加元素
  空值(null)集合不存在,为了将空值集合转换为已存在集合,你必须初始化它,要么通过使其
  为空(empty),要么给它赋非空的值,不能使用EXTEND方法来初始化一个空值集合
  在哪里定义
  在PL/SQL块中定义的集合类型,是本地变量类型,只在该块中可用,如果定义在独立的或包的 子程序中,可以保存在数据库中 在包头中定义的集合类型,是公共变量类型,你可以在包外部引用它,需要使用限定名(包名.类型名) 在模式(schema)层级定义的集合类型是一个独立的类型,你可以使用CREATE TYPE语句来创建它, 它保存在数据库中,除非你使用DROP TYPE语句删除它
  可以成为ADT(Attribute Data Type)
  要想成为一个ADT,集合类型必须是独立的
  将非PL/SQL的复合类型转换为PL/SQL复合类型

  非PL/SQL复合类型 对等的PL/SQL复合类型 哈希表 关联数组 未排序表 关联数组 Set 嵌套表 Bag 嵌套表 Array 可变数组VARRAY

5.2 关联数组

关联数组(以前被称为PL/SQL表或索引表)是一个键值对的集合
每个键是一个唯一性索引,用于定位与之关联的值,语法格式是:

变量名(索引)

索引的数据类型可以是字符类型(VARCHAR2, VARCHAR, STRING或LONG),也可以是PLS_INTEGER
索引以排序的状态被存储,而不是以被创建时的顺序,对于字符类型的索引,排序顺序取决于初始化
参数NLS_SORT和NLS_COMP
像数据库表一样,关联数组可以:

• 是空的(但不是NULL)直到你填充它
• 可以存放不指定数量的元素,你可以不需要直到它们的位置而访问到它们 不像数据库表的方面:
• 不需要磁盘空间或网络操作
• 不能被DML语句操作 样例程序 - 使用字符串作为索引的关联数组

DECLARE
    TYPE population IS TABLE OF NUMBER   -- 关联数组的类型
        INDEX BY VARCHAR2(64);           -- 索引的类型是字符串
    city_population population;          -- 关联数组变量
    i  VARCHAR2(64);                     -- 标量变量
BEGIN
    -- 向关联数组中添加键值对
    city_population('Smallville')  := 2000;
    city_population('Midland')     := 750000;
    city_population('Megalopolis') := 1000000;
    -- 改变与键'Smallville'关联的值
    city_population('Smallville') := 2001;
    -- 打印关联数组
    i := city_population.FIRST;    -- 获得关联数组的第一个元素
    WHILE i IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Population of ' || i || ' is ' || city_population(i)
        );
        i := city_population.Next(i);    -- 获得关联数组的下一个元素
    END LOOP;
END;
/

样例程序 - 返回使用PLS_INTEGER作为索引的关联数组

DECLARE
    TYPE sum_mutiples IS TABLE OF PL_INTEGER
        INDDEX 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_mutiples
    IS
        s sum_mutiples;
    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;
/

5.2.1 声明关联数组常量

要想声明一个关联数组常量,你必须创建一个函数,用来使用初始值填充这个关联数组,
并在常量声明的时候调用这个函数
样例程序如下:

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER
IS
    TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    FUNCTION Init_My_AA RETURN My_AA;
END My_Types;
/
CREATE OR REPLACE PACKAGE BODY My_Types
IS
    FUNCTION Init_My_AA RETURN My_AA
    IS
        Ret My_AA;
    BEGIN
        Ret(-10) := '-ten';
        Ret(0) := 'zero';
        Ret(1) := 'one';
        Ret(2) := 'two';
        Ret(3) := 'three';
        Ret(4) := 'four';
        Ret(9) := 'nine';
        RETURN Ret;
    END Init_My_AA;
END My_Types;
/
DECLARE
    v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();
BEGIN
    DELARE
        Idx PLS_INTEGER := v.FIRST();
    BEGIN
        WHILE Idx IS NOT NULL
        LOOP
            DBMS_OUTPUT.PUT_LINE(
                TO_CHAR(Idx, '999') || LPAD(v(Idx), 7)
            );
            Idx := v.NEXT(Idx);
        END LOOP;
    END;
END;
/

5.2.2 NLS参数值对以字符串作为索引的关联数组的影响

NLS参数(例如NLS_SORT, NLS_COMP和NLS_DATE_FORMAT)会对以字符串作为索引的关联
数组产生影响

• 在填充关联数组后改变NLS参数值
  初始化参数NLS_SORT和NLS_COMP会决定关联数组的字符串索引的存储顺序
  如果你在填充关联数组后改动了这些参数值,那么在你调用集合方法FIRST, LAST, NEXT
  和PRIOR时可能会得到非预期值或抛出异常,如果要避免这种情况,可以在你操作关联数组
  的会话里改回这些参数的原来值

• 不是VARCHAR2的索引数据类型
  以字符串作为索引的关联数组,在声明时字符串类型必须是VARCHAR2或其子类型
  然后,你在填充关联数组时,可以使用其他数据类型作为索引,只要能使用TO_CHAR函数
  将其转换为VARCHAR2类型
  如果你声明的关联数组的索引数据类型不是VARCHAR2及其子类型,那么一定要确保这些
  索引在初始化参数改变时能保持一致并唯一,例如:
  不要使用TO_CHAR(SYSDATE)作为索引,因为如果NLS_DATE_FORMAT参数值改变,那么
  TO_CHAR(SYSDATE)的值可能会改变
  不要使用NVARCHAR2类型,因为有些不同的NVARCHAR2索引可能会转换成同一个VARCHAR2值
  不要使用只在大小写或重音位置上不同的CHAR或VARCHAR2类型索引,因为当NLS_SORT
  或 _CI 或 _AI结尾的参数值改变,这些索引可能会转换成同一个值

• 传递关联数组至远程数据库
  如果你将关联数组作为参数传递给远程数据库,那么本地数据库和远程数据库在NLS_SORT或
  NLS_COMP参数上有不同的设置值,就会:
  集合方法FIRST, LAST, NEXT和PRIOR可能会得到非预期值或抛出异常
  在本地数据库保持唯一的索引,可能在远程数据库中不再保持唯一性,导致抛出
  VALUE_ERROR异常

5.2.3 关联数组的适当使用

关联数组适用于:

• 用作小型的关联的lookup表,当你调用子程序或初始化包时在内存中构建它
• 在本地和远程数据库服务器间传递 关联数组通常用于保存临时数据,为了使它与数据库会话有相同的生命周期,可以在包头中
  声明它,在包体中填充它

5.3 可变数组(可变大小的数组)

可变数组VARRAY的元素数量是变化的,可以从0到声明的最大大小
要想访问可变数组变量的元素,使用语法格式: 变量名(索引) 其中索引的低值是1,高值是
当前拥有元素的数量,如果你增加或删除元素,高值会随之发生变化,但不会超出最大大小
数据库将可变数组变量作为一个单独的对象来存储.如果一个可变数组变量小于4KB,就像
列一样存储于表中,否则会存储于表外,但仍在表空间内
一个未初始化的可变数组变量是一个null集合,你必须初始化它,要么使之为empty,要么
使用非空值填充它
样例程序如下:

DECLARE
    TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
    -- 使用构造器来初始化
    team Foursome := Foursome('John', 'Marry', 'Alberto', 'Juanita');
    PROCEDURE print_team(heading VARCHAR2) IS
    BEGIN
        DBMS_OUPUT.PUT_LINE(heading);
        FOR i IN 1..4 
        LOOP
            DBMS_OUPUT.PUT_LINE(i || '.' || team(i));
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('---')
    END;
BEGIN
    print_team('2001 Team:');
    team(3) := 'Pierre';
    team(4) := 'Yvonne';
    print_team('2005 Team');
    team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
END;
/

5.3.1 可变数组的适当使用

可变数组适用于

• 你已经确切知道元素的最大数量
• 你经常顺序访问的元素 由于需要在同一时间存储和获取所有元素,拥有大量元素的可变数组就变得不太实用

5.4 嵌套表

在数据库中,嵌套表是一种列的类型,可以存放一定数量的无序的行
当你从数据库中获取了嵌套表数据并存入一个PL/SQL嵌套表变量时,PL/SQL会给其中的每一行数据
一个连续索引,从1开始,使用这些索引,你就可以访问嵌套表中的每一行数据,语法格式是:

变量名(索引)

嵌套表中的数据行的顺序并不能保持稳定
嵌套表变量占用的内存可以动态增加或减少
一个未初始化的嵌套表变量是一个null集合,你必须初始化它,要么使之为empty,要么使用非空值
填充它
样例程序1:

DECLARE
    TYPE Roster IS TABLE OF VARCHAR2(15)  -- 嵌套表类型
    -- 使用构造器来初始化
    names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    PROCEDURE print_names (heading VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(heading);
        FOR i IN names.FIRST..names.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE(names(i));
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('---');
    END;
BEGIN
    print_names('Initial Values:');
    names(3) := 'P Perez';
    print_names('Current Values:');
    names := Roster('A Jansen', 'B Gupta');
    print_names('Current Values:');
END;
/

样例程序2:

CREATE OR REPLACE TYPE nt_type IS TABLE NUMBER;
/
CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) AUTHID DEFINER
IS
    i NUMBER;
BEGIN
    i := nt.FIRST;
    if i IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('nt is empty!');
    ELSE
        WHILE i IS NOT NULL
        LOOP
            DBMS_OUTPUT.PUT('nt.(' || i || ') = ');
            DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), 'NULL'));
            i := nt.NEXT(i);
        END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('---');
END print_nt;
/
DECLARE
    nt nt_type := nt_type();  -- 初始化为empty的
BEGIN
    print_nt(nt);
    nt := nt_type(90, 9, 29, 58);
    print_nt(nt);
END;
/

5.4.1 嵌套表和数组的重要区别

在概念上,嵌套表像是拥有任意数量元素的一维数组,但是嵌套表与数组的不同在于:

• 数组在声明时会明确元素数量,而嵌套表不会,它的元素数量可以动态增加
• 数组总是密集的,而嵌套表一开始是密集的,以后可能会变得稀疏,因为你可以删除它的元素

5.4.2 嵌套表的适当使用

嵌套表适用于:

• 元素数量未确定
• 索引值并不连续
• 你必须删除或更新某些元素,但并不是全部元素
  嵌套表数据存储于单独的由系统产生的数据表中,当你访问嵌套表时,数据库会连接
  嵌套表和存储它的数据表,所以这使得嵌套表适合于查询或更新集合部分数据
• You would create a separate lookup table, with multiple entries for each row of the main table, and access it throuth join queries

5.5 集合构造器

集合构造器是系统定义的函数,名字与集合类型的名字相同,可以返回一个该类型的集合值
注意

只有可变数组和嵌套表有构造器,而关联数组没有构造器

构造器调用的语法格式如下:

集合类型([value [, value]...])

如果参数列表是空的,那么会返回一个空(empty)的集合值
可以将返回的集合值赋给具有相同类型的集合变量

5.6 给集合变量赋值

你可以通过以下方式给集合变量赋值:

• 调用构造器来创建一个集合值,并赋值给集合变量
• 使用赋值语句,将另一个已存在的集合变量值赋给一个集合变量
• 以 OUT 或 IN OUT 参数的形式,将集合变量传递给子程序,然后在子程序中给其赋值

5.6.1 数据类型兼容性

你只能将相同类型的集合值赋给集合变量,仅仅拥有相同的元素类型是不够的
样例程序:

DECLARE
    TYPE triplet IS VARRAY(3) OF VARCHAR2(15);
    TYPE trio    IS VARRAY(3) OF VARCHAR2(15);
    group1 triplet := triplet('Jones', 'Wong', 'Marceau');
    group2 triplet;
    group3 trio;
BEGIN
    group2 := group1;  -- 可以成功
    group3 := group1;  -- 会失败
END;
/

5.6.2 给可变数组变量或嵌套表变量赋空值(NULL)

对于可变数组和嵌套表,你可以给其赋NULL值,或者具有相同类型的其他空(NULL)集合
样例程序:

DECLARE
    TYPE dnames_tab IS TABLE OF VARCHAR2(30);
    dept_names dnames_tab := dnames_tab(
        'Shipping', 'Sales', 'Finance', 'Payroll'
    );  -- 初始化为非空值
    empty_set dnames_tab;  -- 没有初始化,因此是NULL值
    PROCEDURE print_dept_names_status IS
    BEGIN
        IF dept_names IS NULL THEN
            DBMS_OUTPUT.PUT_LINE('dept_names is null);
        ELSE
            DBMS_OUTPUT.PUT_LINE('dept_names is not null);
        END IF;
    END print_dept_names_status;
BEGIN
    print_dept_names_status;
    dept_names := empty_set;  -- 赋值一个NULL集合 
    print_dept_names_status;
    dept_names := dnames_tab(
        'Shipping', 'Sales', 'Finance', 'Payroll'
    );  -- 重新初始化
    print_dept_names_status;
END;
/

5.6.3 给嵌套表变量赋集合操作结果(Set Operation Results)

你可以将SQL的MULTISET操作或SET函数调用的结果赋给嵌套表变量
SQL的MULTISET操作符合并两个嵌套表到一个嵌套表中,两个嵌套表中的元素必须拥有可比较的
数据类型 SQL的SET函数传入一个嵌套表参数,并且返回一个相同类型的嵌套表,但该嵌套表中的元素已经
去过重了
样例程序:

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 nested_typ;
    PROCEDURE print_nested_table (nt nested_typ)
    IS
        output VARCHAR2(128);
    BEGIN
        IF nt IS NULL THEN
            DBMS_OUTPUT.PUT_LINE('Result: null set);
        ELSIF nt.COUNT = 0 THEN
            DBMS_OUTPUT.PUT_LINE('Result: empty set');
        ELSE
            FOR i IN nt.FIRST .. nt.LAST
            LOOP
                output := output || nt(i) || ' ';
            END LOOP;
        END IF;
    END print_nested_table;
BEGIN
    answer := nt1 MULTISET UNION nt4;
    print_nested_table(answer);
    answer := nt1 MULTISET UNION nt3;
    print_nested_table(answer);
    answer := nt1 MULTISET UNION DISTINCT nt3;
    print_nested_table(answer);
    answer := nt3 MULTISET INTERSECT nt3;
    print_nested_table(answer);
    answer := nt2 MULTISET INTERSECT DISTINCT nt3;
    print_nested_table(answer);
    answer := SET(nt3);
    print_nested_table(answer);
    answer := nt3 MULTISET EXCEPT nt2;
    print_nested_table(answer);
    answer := nt3 MULTISET EXCEPT DISTINCT nt2;
    print_nested_table(answer);
END;
/

5.7 多维的集合

尽管集合是一维的,但是可以通过嵌套的方式使之变成多维的

5.8 集合的比较

要确定一个集合变量是否小于另一个,你必须定义在那种上下文中"小于"的含义,并且写一个函数,
用于返回TRUE或FALSE
你不能将关联数组与NULL进行比较,也不能在关联数组之间进行比较
除了比较嵌套表相等或不相等以外,你不能使用关系运算符来比较集合变量,这个限制也应用于隐式
的比较,例如,一个集合变量不能出现在DISTINCT, GROUP BY或ORDER BY子句中

5.8.1 将可变数组和嵌套表变量与NULL进行比较

当与NULL值进行比较时,使用 IS [NOT] NULL 操作符
你可以将可变数组和嵌套表与NULL值进行比较,但是不能使用关系运算符的相等(=)或不相等
(<>, !=, ~=, ^=)

5.8.2 嵌套表之间的相等或不相等的比较

当且仅当两个嵌套表拥有相同的元素(顺序无所谓)
如果两个嵌套表拥有相同的嵌套表类型,而且嵌套表的元素类型中不是record类型,那么这两个
嵌套表就可以使用关系运算符来进行比较相等(=)和不相等(<>, !=, ~=, ^=)

5.8.3 对嵌套表使用SQL的MULTISET条件进行比较

你可以使用SQL的MULTISET条件来比较嵌套表变量
样例程序:

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);
    PROCEDURE testify (
        truth BOOLEAN := NULL,
        quantity NUMBER := 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
    testify(truth => (nt1 IN (nt2, nt3, nt4)));
    testify(truth => (nt1 SUBMULTISET OF nt3));
    testify(truth => (nt1 NOT SUBMULTISET OF nt4));
    testify(truth => (4 MEMBER OF nt1));
    testify(truth => (nt3 IS A SET));
    testify(truth => (nt3 IS NOT A SET));
    testify(truth => (nt1 IS EMPTY));
    testify(quantity => (CARDINALITY(nt3)));
    testify(quantity => (CARDINALITY(SET(nt3))));
END;
/

5.9 集合方法

集合方法是PL/SQL的子程序,要么是函数要么是存储过程
注意

对于一个空(NULL)集合,EXISTS方法是仅有的不会抛出异常COLLECTION_IS_NULL的方法

如下是集合方法的列表:

方法      类型        描述
DELETE    存储过程    从集合中删除元素
TRIM      存储过程    从可变数组或嵌套表的尾部删除元素
EXTEND    存储过程    从可变数组和嵌套表的尾部增加元素
EXISTS    函数        如果给定的元素存在于可变数组或嵌套表里就返回TRUE,否则返回FALSE
FIRST     函数        返回集合的第一个索引
LAST      函数        返回集合的最后一个索引
COUNT     函数        返回集合中的元素数量
LIMIT     函数        返回集合可以拥有的元素最大数量
PRIOR     函数        返回给定索引的上一个索引
NEXT      函数        返回给定所以的下一个索引

调用集合方法的语法格式是:

集合名.方法名

集合方法可以在任何PL/SQL子程序(函数或存储过程)可以调用的地方调用,除了在SQL语句中

5.9.1 删除(DELETE)方法

DELETE方法有如下的形式:

• 不带参数的DELETE会删除所有的集合元素,这个操作会立即释放内存资源
• 对于关联数组和嵌套表(但不可用于可变数组)
  DELETE(n) 删除索引 n 对应的元素(如果存在,否则什么都不做)
  DELETE(m,n) 删除索引范围在 m 和 n 之间的对应的元素(如果m和n都存在,
  且m小于等于n,否则什么都不做)
  对于这两种DELETE操作,PL/SQL会保存着被删除的元素的占位符(placeholder),
  因此,你可以重新为被删除的元素索引位置上赋一个有效的值

5.9.2 TRIM方法

TRIM方法从可变数组或嵌套表的尾部删除元素
它有如下的形式:

• TRIM
  从集合的尾部移除一个元素,前提是集合至少有一个元素存在,否则会抛出
  SUBSCRIPT_BEYOND_COUNT异常
• TRIM(n)
  从集合的尾部移除n个元素,前提是集合至少有n个元素存在,否则会抛出
  SUBSCRIPT_BEYOND_COUNT异常
  TRIM可以作用于集合内部的size(大小,即元素数量),像DELETE虽然删除了元素,但是
  还会为被删除的元素保留着占位符,而TRIM会真正删掉"已删除"的元素

5.9.3 EXTEND(扩展)方法

EXTEND是一个存储过程,可以在可变数组或嵌套表的尾部增加元素
扩展的目标可变数组或嵌套表可以是空的(empty),但不可以是NULL
它有如下的形式

• EXTEND
  在可变数组或嵌套表尾部增加一个空的(NULL)元素
• EXTEND(n)
  在可变数组和嵌套表尾部增加n个空的(NULL)元素
• EXTEND(n, i)
  在可变数组和嵌套表尾部增加第i个元素的n个拷贝 如果集合内的元素有非空(NOT NULL)约束的话,EXTEND(n, i)是唯一可用的EXTEND形式
  EXTEND基于集合的内部大小(size)进行操作,也就是说,如果DELETE删除了元素,但实际
  上保留了被删除元素的展位符,那么EXTEND方法会认为元素依然还是存在的

5.9.4 存在(EXISTS)方法

EXISTS是一个函数,它可以告诉你指定的元素是否在可变数组或嵌套表中存在
EXISTS(n)返回TRUE的话表示第n个元素是存在的,返回FALSE表示该元素不存在
如果n超出了索引范围,那么EXISTS只会返回FALSE而不是抛出异常(否则会抛出SUBSCRIPT_OUTSIDE_LIMIT)
对于使用DELETE删掉的元素,EXISTS(n)也会返回FALSE,即使DELETE保存了该元素的占位符

5.9.5 FIRST和LAST方法

FIRST和LAST是函数,返回集合内的元素的索引
如果集合内至少有一个元素,那么FIRST和LAST会返回第一个元素和最后一个元素的索引
对于DELETE删掉的元素,会被忽略掉
如果集合是空的(empty),FIRST和LAST会返回NULL

关联数组的FIRST和LAST方法
对于使用PLS_INTEGER作为索引的关联数组,其中第一个和最后一个元素的索引分别是最小和
最大的值
对于使用字符串作为索引的关联数组,其中第一个和最后一个元素的索引分别是最低(lowest)
和最高(highest)的键值
键值已排过序

可变数组的FIRST和LAST方法

嵌套表的FIRST和LAST方法

5.9.6 COUNT(计数)方法

COUNT方法是一个函数,返回集合内的元素个数(忽略已删除的元素,即使DELETE后保留被删元素的
占位符)

可变数组的COUNT方法

嵌套表的COUNT方法

5.9.7 LIMIT方法

LIMIT是一个函数,返回一个集合可以拥有的最大元素数量,如果集合没有最大元素数,则返回NULL
只有可变数组是有最大大小(size)的,关联数组和嵌套表并没有最大大小

5.9.8 PRIOR和NEXT方法

5.10 包头中定义的集合类型

在包头中定义的集合类型,并不能和本地声明的以及单独声明的集合类型兼容
示例程序1:

CREATE OR REPLACE PACKAGE pkg AS
    TYPE NumList IS TABLE OF NUMBER;          -- 包头中声明的集合类型
    PROCEDURE print_numlist (nums NumList);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg AS
    PROCEDURE print_numlist (nums NumList) IS
    BEGIN
        FOR i IN nums.FIRST .. nums.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE(nums(i));
        END LOOP;
    END;
END pkg;
/
DECLARE
    TYPE NumList IS TABLE OF NUMBER;      -- 匿名块中本地声明的集合类型
    n1 pkg.NumList := pkg.NumList(2, 4);
    n2 NumList := NumList(6, 8);
BEGIN
    pkg.print_numlist(n1);   -- 成功
    pkg.print_numlist(n2);   -- 失败
END;
/

5.11 记录变量

你可以使用如下的任何一种方式来创建记录变量:

• 定义一个RECORD类型,然后再声明一个这种类型的变量
• 使用%ROWTYPE来声明一个记录变量,用于表示一个数据库表或视图的整行或部分字段
• 使用%TYPE来声明一个记录变量,该变量的类型与之前声明过的记录变量相同

5.11.1 记录变量的初始值

记录变量的每个域(field)的初始值是NULL,除非你在定义RECORD类型时为field指定了初始值
对于使用%ROWTYPE或%TYPE声明的记录变量,每个域的初始值都是NULL,不能继承它们引用对象
的初始值

5.11.2 声明记录常量

当声明一个记录常量,你必须创建一个函数,在函数中填充记录的初始值,然后在常量的声明中
调用这个函数
示例程序:

CREATE OR REPLACE PACKAGE My_Types AUTHID CURRENT_USER
IS
    TYPE My_Rec IS RECORD (a NUMBER, b NUMBER);
    FUNCTION Init_My_Rec RETURN My_Rec;
END My_Types;
/
CREATE OR REPLACE PACKAGE BODY My_Types
IS
    FUNCTION Init_My_Rec RETURN My_Rec
    IS
        Rec My_Rec;
    BEGIN
        Rec.a := 0;
        Rec.b := 1;
        RETURN Rec;
    END Init_My_Rec;
END My_Types;
/
DECLARE
    r CONSTANT My_Types.My_Rec := My_Types.Init_My_Rec();
BEGIN
    DBMS_OUTPUT.PUT_LINE('r.a = ' || r.a);
    DBMS_OUTPUT.PUT_LINE('r.b = ' || r.b);
END;
/

5.11.3 RECORED类型

在PL/SQL代码块中定义的RECORD类型是一个本地局部类型,它仅仅在代码块中可用
在包头中定义的RECORD类型是一个公用类型,你可以在包外使用"包名.类型名"来引用它
你不能在模式(schema)层级创建一个RECORD类型,因此,RECORD类型不能是ADT(Attribute
Data Type) 定义一个RECORD类型,需要定义它的名字,以及各个域(field),要定义一个域(field),需要
定义它的名字和数据类型,默认情况下,域(field)的初始值是NULL,你可以为一个域指定
NOT NULL非空约束,同时为其指定一个非空初始值
在包头中定义的RECORD类型,与本地局部代码块中声明的RECORD类型是不兼容的
示例程序1:

DECLARE
    TYPE DeptRecType IS RECORD (
        dept_id   NUMBER(4) NOT NULL := 10,
        dept_name VARCHAR2(30) NOT NULL := 'Administration',
        mgr_id    NUMBER(6) := 200,
        loc_id    NUMBER(4) := 1700
    );
    dept_rec DeptRecType;
BEGIN
    DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec.dept_id);
    DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
    DBMS_OUTPUT.PUT_LINE('mgr_id:    ' || dept_rec.mgr_id);
    DBMS_OUTPUT.PUT_LINE('loc_id:    ' || dept_rec.loc_id);
END;
/

示例程序2:

DECLARE
    TYPE name_rec IS RECORD (
        first employees.first_name%TYPE;
        last  employees.last_name%TYPE;
    );
    TYPE contact IS RECORD (
        name name_rec,                     -- 嵌套记录
        phone employees.phone_number%TYPE
    );
    friend contact;
BEGIN
    friend.name.first := 'John';
    friend.name.last := 'Smith';
    friend.phone = '1-650-555-1234';
    DBMS_OUTPUT.PUT_LINE(
        friend.name.first  || '  ' ||
        friend.name.last   || ', ' ||
        friend.phone
    );
END;
/

5.11.4 使用%ROWTYPE声明记录变量

声明一个表示数据库表中全行的记录变量
语法格式如下

变量名 表或视图名%ROWTYPE;

表或视图中的每一个字段,对应着记录变量中的每一个域,名称和数据类型一一对应
记录变量中的域并不能继承表字段的非空约束或默认值

声明一个表示数据库表中行的部分字段的记录变量
语法格式如下

变量名 游标%ROWTYPE;

一个游标和一个查询关联,查询中选择(SELECT)的每个字段,和记录变量中的每个域一一对应
游标必须是要么是"显式游标",要么是"强游标变量"
示例程序:

DECLARE
    CURSOR c IS
        SELECT first_name, last_name, phone_number
          FROM employees;
    friend c%ROWTYPE;
BEGIN
    friend.name.first := 'John';
    friend.name.last := 'Smith';
    friend.phone_number = '1-650-555-1234';
    DBMS_OUTPUT.PUT_LINE(
        friend.name.first  || '  ' ||
        friend.name.last   || ', ' ||
        friend.phone_number
    );
END;
/

%ROWTYPE属性和虚列(Virtual Columns)
如果你使用%ROWTYPE来定义一个表示数据库表全行的记录变量,如果该表拥有一个虚列,那么
你不能将记录变量的值插入该表,相反,你将那些虚列排除掉才能将记录变量的值插入表中
示例程序:

DROP TABLE plch_departure;
CREATE TABLE plch_departure (
    destination    VARCHAR2(100),
    departure_time DATE,
    delay          NUMBER(10),
    expected       GENERATED ALWAYS AS (departure_time + delay/24/60/60)  -- 虚列
)
DECLARE
    dep_rec plch_departure%ROWTYPE;
BEGIN
    dep_rec.destination := 'X';
    dep_rec.departure_time := SYSDATE;
    dep_rec.delay := 1500;
    INSERT INTO plch_departure VALUES dep_rec;  -- 失败
    INSERT INTO plch_departure (destination, departure_time, delay)
    VALUES (dep_rec.destination, dep_rec.departure_time, dep_rec.delay);  -- 成功
END;
/

%ROWTYPE属性和不可见列(Invisible Columns)
假设你使用%ROWTYPE定义了一个记录变量来表示数据库表中的一行,该表中有不可见的列,
然后你又使不可见的列变为可见的
如果你是使用游标定义的记录变量,那么使不可见列变为可见的,不会改变记录变量的结构
而如果使用了全行%ROWTYPE来定义的记录变量,而且使用 SELECT * INTO 语句来给记录赋值,
那么使不可见列变为可见的后,就改变了记录变量的结构
示例程序1:

CREATE TABLE t (a INT, b INT, c INT INVISIBLE);
INSERT INTO t (a, b, c) VALUES (1, 2, 3);
COMMIT;
DECLARE
    t_rec t%ROWTYPE;   -- t_rec的域是a和b,但没有c
BEGIN
    SELECT * INTO t_rec
      FROM t
     WHERE ROWNUM < 2;
    DBMS_OUTPUT.PUT_LINE('c = ' || t_rec.c)  -- 失败
END;
/

接下来将列 c 变为可见的

ALTER TABLE t MODIFY (c VISIBLE);

上面的程序就不会报错了

5.12 给记录变量赋值

一个记录变量代表的要么是一个记录变量,要么是一个复合变量中的记录元素
对于一个记录变量,你可以为其中的每个域单独赋值
在某些情况下,你可以将一个记录变量的值赋给另一个记录变量
如果记录变量表示了数据库表或视图的全行或部分字段,那么你可以将数据库表或视图中的全行或
部分字段的数据赋给该记录变量

5.12.1 将一个记录变量赋值给另一个记录变量

只有在如下的前提下才能将一个记录变量赋值给另一个记录变量:

• 两个变量具有相同的RECORD类型
• 目标变量使用RECORD类型来声明,源变量使用%ROWTYPE来声明,它们的域的数量和顺序都是匹配的,而且对应的域都有一样的数据类型

5.12.2 将全行或部分字段赋值给记录变量

使用SELECT INTO来将一个表或视图的数据行赋给记录变量
语法格式如下:

SELECT 字段列表 INTO 记录变量名 FROM 表或视图名;

对于SELECT选择的每个字段,记录变量必须都有一个对应的,类型兼容的域(field)
SELECT选择的字段的顺序,必须与记录变量中的对应域的顺序一致

使用FETCH来将一个表或视图的数据行赋给记录变量
语法格式如下

FETCH 游标 INTO 记录变量名;

对于游标查询的每个字段,记录变量必须都有一个对应的,类型兼容的域(field)
游标必须是显式游标或强游标变量

使用SQL语句返回的数据行来给PL/SQL记录变量赋值
SQL语句(INSERT, UPDATE和DELETE)有一个可选的 RETURNING INTO 子句,它可以返回
影响的数据行(affected row)到一个PL/SQL记录变量里
示例程序:

DECLARE
    TYPE EmpRec IS RECORD (
        last_name employees.last_name%TYPE,
        salary    employees.salary%TYPE
    );
    emp_info EmpRec;
    old_salary employees.salary%TYPE;
BEGIN
    SELECT salary 
      INTO old_salary
      FROM employees
     WHERE employee_id = 100;
    UPDATE employees
       SET salary = salary * 1.1
     WHERE employee_id = 100
    RETURNING last_name, salary INTO emp_info;
    DBMS_OUTPUT.PUT_LINE(
        'Salary of ' || emp_info.last_name || ' raised from ' ||
        old_salary || ' to ' || emp_info.salary
    );
END;
/

5.12.3 给记录变量赋值NULL

给记录变量赋值NULL的话,会给它的每个域(field)都赋了NULL
这种赋值操作是递归的,就是说,如果一个域也是记录,那么它的所有域也是NULL值

5.13 记录的比较

记录不可以被测试: 空, 相等, 不相等
下面这些BOOLEAN表达式是非法的:

    My_Record IS NULL
    My_Record_1 = My_Record_2
    My_Record_1 > My_Record_2

你必须写函数来实现这些测试

5.14 向表中插入记录

PL/SQL扩展了SQL语句,可以让你将一个记录变量的值插入数据库表中
记录变量必须要表示数据库表的一行数据
为了有效的将记录的集合插入表中,将一个INSERT语句放入一个FORALL语句中
示例程序:

DROP TABLE schedule;
CRATE TABLE schedule (
    week  NUMBER,
    Mon   VARCHAR2(10),
    Tue   VARCHAR2(10),
    Wed   VARCHAR2(10),
    Thu   VARCHAR2(10),
    Fri   VARCHAR2(10),
    Sat   VARCHAR2(10),
    Sun   VARCHAR2(10)
);
DECLARE
    default_week  schedule%ROWTYPE;
    i             NUMBER;
BEGIN
    default_week.Mon := '0800-1700';
    default_week.Tue := '0800-1700';
    default_week.Wed := '0800-1700';
    default_week.Thu := '0800-1700';
    default_week.Fri := '0800-1700';
    default_week.Sat := 'Day Off';
    default_week.Sun := 'Day Off';
    FOR i IN 1..6
    LOOP
        default_week.week := i;
        INSERT INTO schedule VALUES default_week;
    END LOOP;
END;
/

5.15 使用记录来更新行

PL/SQL扩展了SQL语句,可以让你依据一个记录变量的值更新数据库表的行
记录变量必须要表示数据库表的一行数据
为了有效的将记录的集合更新表,将一个UPDATE语句放入一个FORALL语句中

5.16 记录插入和更新的限制

记录插入和更新数据库表的限制如下:

• 记录变量只允许出现在如下这些地方:
  UPDATE语句的SET子句的右边
  INSERT语句的VALUES子句中
  RETURNING子句的INTO子句中
• 关键字ROW只被允许出现在SET子句的左边,你不能在子查询中使用ROW关键字
• 在UPDATE语句中,如果使用了ROW关键字,只允许一个SET子句
• 如果INSERT语句的VALUES子句里包含了一个记录变量,那么在该子句里就不允许包含别的变量或值了
• 如果在RETURNING子句的INTO子句中包含了一个记录变量,就不允许包含别的变量或值了
• 下面情形是不支持的:
  嵌套RECORD类型
  返回RECORD类型的函数
  使用EXECUTE IMMEDIATE语句的记录插入或更新