数组RECORD、VARRAY、TABLE详解

1     RECORD、VARRAY、TABLE的详解

1.1       RECORD

定义记录数据类型。它类似于C语言中的结构数据类型(STRUCTURE)，PL/SQL提供了将几个相关的、分离的、基本数据类型的变量组成一个整体的方法，即RECORD复合数据类型。在使用记录数据类型变量时，需要在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。
    定义记录数据类型的语法如下：
TYPE RECORD_NAME IS RECORD(
V1  DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],
V2  DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],
VN  DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);

1.2       VARRAY

数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY（variable array，即可变数组）。
定义VARRAY数据类型的语法如下：
TYPE VARRAY_NAMEIS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];
其中，varray_name是VARRAY数据类型的名称，size是正整数，表示可以容纳的成员的最大数量，每个成员的数据类型是element_typeo默认时，成员可以取空值，否则需要使用NOT NULL加以限制。

1.3       TABLE

定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于C语言中的二维数组，使得可以在PL/SQL中模仿数据库中的表。
定义记录表类型的语法如下：
TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]
INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];
关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。
BINARY_INTEGER的说明
如语句：TYPE NUMBERS  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”后，NUMBERS类型的下标就是自增长，NUMBERS类型在插入元素时，不需要初始化，不需要每次EXTEND增加一个空间。
而如果没有这句话“INDEXBY BINARY_INTEGER”，那就得要显示对初始化，且每插入一个元素到NUMBERS类型的TABLE中时，都需要先EXTEND。

2     举例

2.1       创建表结构以及数据准备

--组织机构结构表 
CREATE TABLE SF_ORG 
( 
ORG_ID INT NOT NULL, --组织机构主键ID 
ORG_NAME VARCHAR2(50),--组织机构名称 
PARENT_ID INT--组织机构的父级 
) 
 
--一级组织机构 
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0); 
 
--二级部门 
 
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1); 
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1); 
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二级部门4',1); 

2.2       RECORD的使用举例

先定义一个只与SF_ORG表中某几个列的数据类型相同的记录数据类型TYPE_ORG_RECORD，然后声明一个该数据类型的记录变量V_ORG_RECORD，最后用替换变量&ORG_ID接受输入的雇员编码，查询并显示该雇员的这几列中的信息。注意，在使用RECORD数据类型的变量时要用“.”运算符指定记录变量名限定词。

一个记录类型的变量只能保存从数据库中查询出的一行记录，如果查询出了多行记录，就会出现错误。

DECLARE
    TYPE type_org_record IS RECORD(
        v_org_name  sf_org.org_name%TYPE,
        v_parent_id sf_org.parent_id%TYPE);
    v_org_record type_org_record;
BEGIN
    SELECT org_name,
           parent_id
    INTO v_org_record
    FROM sf_org so
    WHERE so.org_id = &org_id;
    dbms_output.put_line('部门名称：' || v_org_record.v_org_name);
    dbms_output.put_line('上级部门编码：' || TO_CHAR(v_org_record.v_parent_id));
END;

2.3       VARRAY的使用举例

先定义一个能保存5个VARCHAR2(25)数据类型的成员的VARRAY数据类型ORG_VARRAY_TYPE，然后声明一个该数据类型的VARRAY变量V_ORG_VARRAY，最后用与ORG_VARRAY_TYPE数据类型同名的构造函数语法给V_ORG_VARRAY变量赋予初值并显示赋值结果。
注意，在引用数组中的成员时．需要在一对括号中使用顺序下标，下标从1开始而不是从0开始。

DECLARE
    TYPE org_varray_type IS VARRAY(5) OF VARCHAR2(25);
    v_org_varray org_varray_type;
BEGIN
    v_org_varray := org_varray_type('1', '2', '3', '4', '5');
    dbms_output.put_line('输出1：' || v_org_varray(1) || '、' || v_org_varray(2) || '、' || v_org_varray(3) || '、' || v_org_varray(4));
    /*
    FOR i IN v_org_varray.first .. v_org_varray.last LOOP
        dbms_output.put_line('输出' || i || '：' || v_org_varray(i));
    END LOOP;
    */
    -- 修改单个值
    dbms_output.put_line('输出2：' || v_org_varray(5));
    v_org_varray(5) := '5001';
    dbms_output.put_line('输出3：' || v_org_varray(5));
END;

2.4       TABLE使用举例

2.4.1      存储单列多行

这个和VARRAY类似。但是赋值方式稍微有点不同，不能使用同名的构造函数进行赋值。具体的如下：

-- 标号数组
DECLARE
    TYPE org_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER; -- 标号数组，不需要动态申请
    v_org_table org_table_type;
BEGIN
    -- 标号数组声明不能整体赋初值，会报错
    --V_ORG_TABLE := ORG_TABLE_TYPE( '1', '2', '3', '4', '5' );
    v_org_table(1) := '1';
    v_org_table(2) := '2';
    v_org_table(3) := '3';
    v_org_table(4) := '4';
    v_org_table(5) := '5';
    dbms_output.put_line('输出1：' || v_org_table(1) || '、' || v_org_table(2) || '、' || v_org_table(3) || '、' || v_org_table(4));
    dbms_output.put_line('输出2：' || v_org_table(5));
    /*    for i in v_org_table.first .. v_org_table.last loop
      dbms_output.put_line('输出'|| i ||'：' || v_org_table(i));
    end loop;*/
END;

-- 非标号数组
DECLARE
    TYPE org_table_type IS TABLE OF VARCHAR2(25); -- 非标号，需要动态初始化，动态extend
    v_org_table org_table_type;
BEGIN
    -- 报错，非标号需要动态初始化，动态extend , 否则会报错
    -- v_org_table(1) := '1';

    -- 初始化1, 声明时赋初值, 可以整体赋值，不需要动态extend
    --V_ORG_TABLE := ORG_TABLE_TYPE( '1', '2', '3', '4', '5' );
    -- 初始化2
    v_org_table := org_table_type();
    v_org_table.extend;
    v_org_table(1) := '1';
    v_org_table.extend;
    v_org_table(2) := '2';
    v_org_table.extend;
    v_org_table(3) := '3';
    v_org_table.extend;
    v_org_table(4) := '4';
    v_org_table.extend;
    v_org_table(5) := '5';
    dbms_output.put_line('输出1：' || v_org_table(1) || '、' || v_org_table(2) || '、' || v_org_table(3) || '、' || v_org_table(4));
    dbms_output.put_line('输出2：' || v_org_table(5));
    /*    for i in v_org_table.first .. v_org_table.last loop
      dbms_output.put_line('输出'|| i ||'：' || v_org_table(i));
    end loop;*/
END;

2.4.2      存储多列多行和ROWTYPE结合使用

采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。

DECLARE
    TYPE t_type IS TABLE OF sf_org%ROWTYPE;
    v_type t_type;
BEGIN
    SELECT org_id,
           org_name,
           parent_id BULK COLLECT
    INTO v_type
    FROM sf_org
    WHERE sf_org.org_id <= 3;

    FOR v_index IN v_type.first .. v_type.last LOOP
        dbms_output.put_line(v_type(v_index).c1 || ' ' || v_type(v_index).c2);
    END LOOP;
END;

2.4.3      存储多列多行和RECORD结合使用

采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。

DECLARE
    TYPE test_emp IS RECORD(
        c1 sf_org.org_name%TYPE,
        c2 sf_org.parent_id%TYPE);
    TYPE t_type IS TABLE OF test_emp;
    v_type t_type;
BEGIN
    SELECT org_name,
           parent_id BULK COLLECT
    INTO v_type
    FROM sf_org
    WHERE sf_org.org_id <= 3;

    FOR v_index IN v_type.first .. v_type.last LOOP
        dbms_output.put_line(v_type(v_index).c1 || ' ' || v_type(v_index).c2);
    END LOOP;
END;

3     问题

VARRAY和TABLE集合不能直接对其进行查询。只能对其进行遍历。