oracle集合类型详解

曾经终败给现在 2022-01-30 09:04 672阅读 0赞

###  ###

*  1.集合类型
 *   *  1.1关联数组
     *  1.2嵌套表
     *  1.3 可变长的数组
 *  2.声明集合类型
 *   *  2.1声明关联数组
     *  2.2声明嵌套表
     *  2.3声明VARRAY
 *  3.集合变量的声明和初始化
 *   *  3.1集合变量的声明
     *  3.2集合变量的初始化
     *  3.2.1通过构造函数的显示初始化
     *  3.2.2直接赋值时的隐式初始化
     *  3.2.3通过FETCH操作的隐式初始化
     *  3.2.4通过BULK COLLECT语句的隐式初始
 *  4.集合方法
 *  5.集合类型对比
 *  6.集合示例
 *   *  6.1关联数组示例
     *  6.2嵌套表示例
     *  6.3VARRAY实例

昨天才知道oracle数据库中也有集合类型，废话不多少，直接上文。

所谓集合是一种类似于列表或者一维数组的数据结构。PL/SQL提供了三种集合类型：关联数据组（索引表），嵌套表和VARRAY（可变长数组）。

# 1.集合类型 #

## 1.1关联数组 ##

关联数组（也称为索引表）是一组键值对。每个密钥都是唯一的，并且被用于定位相应的值。键可以是整数或字符串。只能用于PL/SQL环境。

## 1.2嵌套表 ##

从概念上讲，嵌套表像一个元素数量任意的一维数组。

在数据库中，嵌套表是存储一组值的列类型，数据库存储嵌套表的行是没有特定顺序的。当你从数据中提取嵌套表到PL/SQL变量时，该行给出连续从1开始的下标。通过这些类似数组下标访问独立的行。

嵌套表不同于数组的重要方面：

数组需要声明元素的个数，而嵌套表不需要。嵌套表的大小可以动态增加。数组总是密集的，嵌套表刚开始是密集的，但是后面有可能会变成稀疏的。因为你会从嵌套表中删除元素。

## 1.3 可变长的数组 ##

可变长的数组是一个VARRAY数据类型的集合。当你声明VARRAY类型的时候，就必须指定同时指定它能够包含的最大元素个数。VARRAY可以包含可变数据的元素，从零到最大值。VARRAY索引有一个固定定的下限1和一个可扩展的上限。和嵌套表类型一样的是，它们都可以用于PL/SQL和数据库。但是和嵌套表不一样的是，在向VARRAY中保存数据或者提取数据时，它的元素是有序的。

# 2.声明集合类型 #

在使用一个集合之前，我们必须先声明它。有两种方法可以申明一个集合类型：

通过TYPE语句在一个PL/SQL程序中声明集合类型。通过CREATE TYPE语句在数据中定义一个嵌套表或者VARRAY类型，这个类型就是一个模式级别的对象。这种类型就可以用作数据库表的列的数据类型，可以用作对象类型的属性，也可以用于声明PL/SQL变量。

## 2.1声明关联数组 ##

关联数组的TYPE语句的语法如下：

TYPE table_type_name IS TABLE OF datatype [ NOT NULL] INDEX BY index_type;

其中：

table\_type\_name是你所创建的集合类型的名字，datatype是集合中唯一一列的数据类型，index\_type是用来组织集合内容的索引的数据类型。而集合唯一以列的数据类型可以是下面这些：

标量数据类型：任何被PL/SQL支持的标量数据类型，比如VARCHAR2，CLOB，POSITIVE，DATE，或者BOOLEAN。锚定数据类型：这种数据类型是从一个数据库表的列，之前已经定义的变量或者带有%TYPE属性的游标表达式推导出来的数据类型。我们也可以定义用%ROWTYPE声明或者根据一个用户定义的记录类型来定义一个记录的集合。复杂的数据类型：从Oracle 9i数据库R2版本开始，你也可以把对象类型和集合类型作为集合的数据类型。  
集合语法中的index\_type定义索引下标的数据类型。在Oracle 9i数据库版本R2之前，只能是INDEX BY PLS\_INTEGER。从Oracle 9i数据库版本R2开始，INDEX BY的数据类型可以是BINARY\_INTEGER、及它的子类型、VARCHAR2(N)或者VARCHAR2列或变量的%TYPE锚定类型。

## 2.2声明嵌套表 ##

可以数据库内或者PL/SQL代码块中声明嵌套表类型。

在数据库内创建一个嵌套表类型：

CREATE [OR REPLACE] TYPE type_name AS | IS TABLE OF element_datatype [ NOT NULL ];

删除数据库内的嵌套表类型：

DROP TYPE type_name [FORCE];

在PL/SQL中声明一个嵌套表类型：

TYPE type_name IS TABLE OF element_datatype [ NOT NULL ];

其中：

OR REPLACE：允许我们重创建一个已经存在的类型。通过在语法中加上REPLACE的方式来重建类型，而不是先删除后再重新创建，可以把所有已经授予的权限都完整的保留下来。type\_name：一个合法的SQL或者PL/SQL标志符。这也是我们以后在声明变量或者列时会用到的标识符。element\_datatype:这是集合元素的数据类型。集合内所有元素都是一种类型的，可以是大部分标量数据类型、对象类型、或者REF对象类型。如果集合的元素是对象，对象类型本身不能再带有一个集合属性。如果你创建了一个其元素是RECORD类型的集合，记录的字段只能是标量或者是独享。明确的不可用于集合的数据类型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL数据类型)。NOT NULL：表明这种类型的变量不能有任何空元素。不过，集合本身可可以是原子级的空（未初始化）。FORCE：这个关键字告诉数据库的是，当要删除这个类型时，就算是其他类型中还有对这个类型的引用，也要强行删除这个类型。比如，如果在一个对象类型的定义中用到了某个特殊的集合类型，你可以使用FORCE关键字来强行删除这个集合类型。

## 2.3声明VARRAY ##

和嵌套表类型的声明一样，可以数据库内或者PL/SQL代码块中声明VARRAY类型。

在数据库内创建一个VARRAY类型：

CREATE [OR REPLACE] TYPE type_name AS | IS VARRAY (max_elements) OF element_datatype [ NOT NULL ];

删除数据库内的VARRAY类型：

DROP TYPE type_name [FORCE];

在PL/SQL中声明一个VARRAY类型：

TYPE type_name IS VARRAY (max_elements) OF element_datatype [ NOT NULL ];

其中：

OR REPLACE：允许我们重创建一个已经存在的类型。通过在语法中加上REPLACE的方式来重建类型，而不是先删除后再重新创建，可以把所有已经授予的权限都完整的保留下来。type\_name：一个合法的SQL或者PL/SQL标志符。这也是我们以后在声明变量或者列时会用到的标识符。element\_datatype:这是集合元素的数据类型。集合内所有元素都是一种类型的，可以是大部分标量数据类型、对象类型、或者REF对象类型。如果集合的元素是对象，对象类型本身不能再带有一个集合属性。如果你创建了一个其元素是RECORD类型的集合，记录的字段只能是标量或者是独享。明确的不可用于集合的数据类型包括BOOLEAN、NCHAR、NCLOB、NVARCHAR2、REF CURSOR、TABLE和VARRAY(非SQL数据类型)。NOT NULL：表明这种类型的变量不能有任何空元素。不过，集合本身可可以是原子级的空（未初始化）。max\_elements:VARRAY中元素的最大数量，这个值一旦声明就不能更改。FORCE：这个关键字告诉数据库的是，当要删除这个类型时，就算是其他类型中还有对这个类型的引用，也要强行删除这个类型。比如，如果在一个对象类型的定义中用到了某个特殊的集合类型，你可以使用FORCE关键字来强行删除这个集合类型。

# 3.集合变量的声明和初始化 #

## 3.1集合变量的声明 ##

一旦我们创建好了集合类型，我们就可以根据这个集合类型声明该类型的变量。一个集合变量声明格式如下：

collection_name collection_type [:=collection_type(...)];

其中，collection\_name是集合变量的名字，collection\_type具有两层含义，它即代表着一个先前已经声明的集合类型的名字，同时也代表着（如果是嵌套表或者VARRAY的话）和该类型同名的构造函数。

构造函数的名字和类型的名字是相同的，并且接收一个用逗号分隔的元素列表作为参数。如果我们声明的是一个嵌套表或者VARRAY变量，我们在使用之前必须要先对这个变量进行初始化。

## 3.2集合变量的初始化 ##

对于嵌套表类型集合变量和VARRAY类型集合变量，在使用集合变量之前必须要进行初始化，而对于关联数组类型不需要初始化。下面主要讨论嵌套表和VARRY的初始化。

## 3.2.1通过构造函数的显示初始化 ##

通过构造函数显示地给集合变量初始。例如：

declare
    
    vnt_employee nt_employee :=nt_employee(); --不带参数的构造函数初始化
    
    vnt_employee nt_employee :=nt_employee('张三','李四','王五'); --带参数的构造函数初始化
    
    begin
    
    null;
    
    end;

## 3.2.2直接赋值时的隐式初始化 ##

如果两个集合实例是基于同一集合类型，我们可以把其中一个实例的全部内容拷贝给另一个，这就相当于进行了初始化。例如：

declare
    
    vnt_employee nt_employee :=nt_employee('James','Lucy','Jordan');
    
    vnt_foregin_employee nt_employee;
    
    begin
    
    vnt_foregin_employee := vnt_employee；
    
    end;

## 3.2.3通过FETCH操作的隐式初始化 ##

在使用FETCH或者 SELECT INTO语句从数据库提取提取一个集合并保存到一个集合变量时，集合变量会自动初始化，就像直接赋值一样。

declare
    
    vnt_colors nt_color;
    
    begin
    
    select colors into vnt_colors from color_models; --表color_models的color列是嵌套表类型。
    
    end;

## 3.2.4通过BULK COLLECT语句的隐式初始 ##

使用BULK COLLECT INTO语句批量提取数据并保存到一个集合变量，集合变量会自动初始化，就像直接赋值一样。

declare
    
    vnt_employee nt_employee; --未初始化
    
    begin
    
    select e.ename bulk collect intovnt_employee from emp e;
    
    end;
    
    declare
    
    cursor cur_employee is select e.ename from emp e;
    
    vnt_employee nt_employee; --未初始化
    
    begin
    
    open cur_employee;
    
    fetch cur_employee bulk collect into vnt_employee;
    
    close cur_employee;
    
    end;

# 4.集合方法 #

Oracle提供了提供许多内置的函数和过程可以用于获取集合的信息或者修改集合的内容，这些方法也叫做集合方法。下面给出这些方法的完整列表：

方法（函数或者过程）

说 明

COUNT函数

返回集合中现有元素的数量

DELETE过程

从集合中移除一个或者多个元素。如果不是重复移除，会减少COUNT的值，对于VARRAY，你只能删除集合的所有元素

EXISTS函数

根据某个指定的元素是否已经在集合中，返回TURE或者FALES

EXTEND过程

增加嵌套表或者VARRAY中元素的个数，同时增加COUNT的值

FIRST、LAST函数

返回可用的最小（FIRST）和最大（LAST）集合下标

LIMIT函数

返回VARRAY中允许ude最大元素数量

PRIOD、NEXT函数

返回紧挨着指定的下标之前(PRIOD)或者之后(NEXT)的下标值。你应该总是用PRIOD和NEXT在集合内遍历，尤其在使用稀疏（或者可能是稀疏）集合时更是如此

TRIM过程

从集合的尾部（定义的最大下标）移除集合元素

之所以把这些过程叫做方法，是因为使用这些集合内置程序的语法不同于调用过程和函数的正规语法。

# 5.集合类型对比 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]

# 6.集合示例 #

## 6.1关联数组示例 ##

set serverout on --开启sqlplus屏幕打印功能
    DECLARE
        TYPE nt_foregn_employee IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
        --声明 nt_foregn_employee为关联集合类型 相对于创建一个集合类型
        --这里由于varchar2(30)类型是数据库自带的，就不需要我们单独创建
        -- 这里key为binary_integer 类型 value 为varchar2(30)
        vnt_foregn_employees nt_foregn_employee; --声明 vnt_foregn_employees为 nt_foregn_employee类型
        v_row                NUMBER;
    BEGIN
        vnt_foregn_employees(-230002) := 'SCOTT'; --往 vnt_foregn_employees中添加元素 相当于 map(-230002,'SCOTT');
        vnt_foregn_employees(-23) := 'JONES';
        vnt_foregn_employees(1) := 'ALLEN';
        vnt_foregn_employees(5934) := 'CLARK';
        vnt_foregn_employees(13342) := 'ADAMS';
        vnt_foregn_employees(8234223) := 'KING';
    
        --使用FIRST方法获取集合中的一个行号
        v_row := vnt_foregn_employees.first;
    
        WHILE (v_row IS NOT NULL) LOOP
            --遍历集合中的元素
            dbms_output.put_line(v_row || ':' || vnt_foregn_employees(v_row));
            v_row := vnt_foregn_employees.next(v_row);
        END LOOP;
    END;
    /

在稀疏集合中，经常需要使用NEXT方法遍历集合，提取数据。  
查看执行结果，看到遍历结果和我们添加结果一样  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]

在稀疏集合中，经常需要使用NEXT方法遍历集合，提取数据。

## 6.2嵌套表示例 ##

DECLARE
        TYPE nt_employee IS TABLE OF emp%ROWTYPE;--声明nt_employee为嵌套表emp集合类型
        vnt_employees nt_employee := nt_employee(); --构造函数显示初始化
        c_big_number  NUMBER := power(2, 31);
        l_start_time  PLS_INTEGER;
    
        CURSOR cur_employee IS   --从emp表取数游标
            SELECT * FROM emp;
        vrt_employees cur_employee%ROWTYPE;
    
    BEGIN
        OPEN cur_employee;
        LOOP
            FETCH cur_employee
                INTO vrt_employees;
            EXIT WHEN cur_employee%NOTFOUND;
            vnt_employees.extend;--嵌套表extend扩展一个元素
            vnt_employees(vnt_employees.last) := vrt_employees;--添加元素
        END LOOP;
        CLOSE cur_employee;
        --循环遍历元素
        FOR i IN 1 .. vnt_employees.count LOOP
            --v_data.count表示集合中元素的个数
            --vnt_employees 一条记录相当于一个实体,列名相对于是实体属性，通过点的方式获取
        dbms_output.put_line('empnofrom:' || vnt_employees(i).empno ||
                    ' ename:' || vnt_employees(i).ename || ' deptno:' || vnt_employees(i).deptno);
            --把满足条件的客户信息打印到屏幕
        END LOOP;
       dbms_output.put_line('嵌套表vnt_employees中元素个数:'||vnt_employees.count); -- 打印集合的元素总数
    END;
    /  --sqlplus执行过程命令

执行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]

## 6.3VARRAY实例 ##

--创建集合类型nt_course 用于存放学生课程表
    create or replace type nt_course is varray(5) of varchar2(100);
    /
    --创建表students，表中引用了可变集合类型nt_course  存放学生和课程信息
    create table students( student_name varchar2(20) , cource nt_course);
    
    declare
    vnt_nt_courses nt_course := nt_course();--构造函数初始化
    
    begin
    vnt_nt_courses.extend(2);-- 集合增加2个元素
    vnt_nt_courses(1) := 'English';--跟一维数组添加元素一样
    vnt_nt_courses(2) := 'Chinese';
     -- 把集合数据添加到students表中
    insert into students
    (student_name, cource)
    values
    ('chiclewu', vnt_nt_courses);
    commit;-- 提交事务
    end;
    /
    select * from table (select s.cource from students s);

– 可以使用TABLE函数把一个集合映射成数据库表.例如，要获取student表中课程列的记录。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 3]

这里用table的原因就是集合是不能显示的，

SELECT t.student_name,  t.cource  FROM students t;

![在这里插入图片描述][20190518233834620.png]

看到，通过集合元素可以实现在同一表中一条记录对应多条记录。

我们可以认为其实这里是两个表，  
students表中存放学生和选课信息，而选课信息就是我们所说的集合 也相当于是一个表

本文转自：[https://www.2cto.com/database/201312/264383.html][https_www.2cto.com_database_201312_264383.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]: /images/20220130/b5ccbc9782de4f069f92fa9d7e0171f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]: /images/20220130/4dbac91e9f854a57bb6ad4eb13034475.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]: /images/20220130/a1f1b692bdeb4b5cba8f0edebe612f6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 3]: /images/20220130/bb8cdb3d529c4afba4df9030e18fd719.png
[20190518233834620.png]: /images/20220130/aac896d44d6c4b8395f145889b1186eb.png
[https_www.2cto.com_database_201312_264383.html]: https://www.2cto.com/database/201312/264383.html