Oracle的数据类型-初探集合数据类型_oracle list数据类型

Oracle的数据类型-初探集合数据类型

前言

在最近的工作中需要对Oracle数据库到Gaussdb数据进行迁移评估，涉及到Oracle数据类型的兼容性评估，但是有许多数据类型时我们不常使用的，因此查找文档学习的过程，顺便整理并做一下笔记。本文档将帮助大家初步了解Oracle集合数据类型及其使用方法。在本文档中，您将学到如何创建集合数据类型以及插入该数据类型。希望通过这份文档，能够帮助大家更好地理解Oracle数据库的集合数据类型，能够更加轻松地操作相关数据类型。官网文档参考Oracle 23c，本地环境使用19c版本验证。

介绍

Oracle目前支持数组和嵌套表集合数据类型。数组是元素的有序集合。嵌套表可以包含任意数量的元素，并且是无序的。如果只需要存储固定数量的元素，或者按顺序循环遍历元素，或者经常需要将整个集合作为值检索和操作，那么可以使用varray。如果需要在集合上运行高效查询，处理任意数量的元素，或者执行大量插入、更新或删除操作，那么可以使用嵌套表。

nested table类型

1、创建嵌套的表类型以及引用该类型的表

使用CREATE TYPE和CREATE TYPE BODY创建一个嵌套的表类型。下面将演示如何创建person_type对象和people_type作为person_type对象的嵌套表类型，以及people_tab表。

CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) );
/

CREATE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the put_line procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.put_line(TO_CHAR(idno) || ' - '  || name || ' - '  || phone);
  END;
END;
/

CREATE TYPE people_typ AS TABLE OF person_typ; -- nested table type
/

CREATE TABLE people_tab (
    group_no NUMBER,
    people_column people_typ )
    NESTED TABLE people_column STORE AS people_column_nt;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2、如何插入嵌套表类型

可以在SQL语句中使用构造函数方法，将值插入到嵌套表中。

INSERT INTO people_tab VALUES (
            100,
            people_typ( person_typ(1, '李四', '13025695869'),
            person_typ(2, '王五', NULL)));
1
2
3
4

另外也支持将空值插入嵌套表实例

INSERT INTO people_tab VALUES (10, people_typ()); 
1

3、查询插入后的结果，使用方式和查询普通表一样，但是在客户端

SQL> select * from people_tab;

  GROUP_NO PEOPLE_COLUMN(IDNO, NAME, PHONE)
---------- --------------------------------------------------------------------------------
       100 PEOPLE_TYP(PERSON_TYP(1, '??', '13025695869'), PERSON_TYP(2, '??', NULL))
	10 PEOPLE_TYP()
1
2
3
4
5
6

4、更新嵌套表的数据

嵌套表列的的分段操作是利用子查询提取嵌套表来实现，这样的对INSERT、UPDATE或DELETE语句可以应用于嵌套表而不是顶级表

UPDATE TABLE(SELECT p.PEOPLE_COLUMN 
                  FROM people_tab p
                  WHERE p.GROUP_NO = 100) e   
   SET value(e) = person_typ(3, '赵六', '13025695869')
   WHERE e.idno = 2;

SQL> select * from people_tab;

  GROUP_NO PEOPLE_COLUMN(IDNO, NAME, PHONE)
---------- --------------------------------------------------------------------------------
       100 PEOPLE_TYP(PERSON_TYP(1, '??', '13025695869'), PERSON_TYP(3, '??????', '13025695
	   869'))

	    10 PEOPLE_TYP()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5、删除嵌套表的数据

DELETE FROM TABLE(SELECT p.PEOPLE_COLUMN 
                  FROM people_tab p
                  WHERE p.GROUP_NO = 100) e
   WHERE e.idno = 1;
SQL> select * from people_tab;

  GROUP_NO PEOPLE_COLUMN(IDNO, NAME, PHONE)
---------- --------------------------------------------------------------------------------
       100 PEOPLE_TYP(PERSON_TYP(3, '??????', '13025695869'))
	10 PEOPLE_TYP()
1
2
3
4
5
6
7
8
9
10

varray

数组是一组有序的数据元素。给定数组的所有元素都具有相同的数据类型或声明的数据类型的子类型。每个元素都有一个索引，索引是一个数字，对应于元素在数组中的位置。索引号用于访问特定的元素。

在定义数组时，要指定它可以包含的最大元素数，不过稍后可以更改这个数字。数组中元素的个数就是数组的大小。

1、创建varray数据类型

CREATE TYPE phone_typ AS OBJECT (
    country_code   VARCHAR2(2), 
    area_code      VARCHAR2(3),
    ph_number      VARCHAR2(7));

CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;

CREATE TABLE dept_phone_list (
  dept_no NUMBER(5), 
  phone_list phone_varray_typ);

1
2
3
4
5
6
7
8
9
10
11

2、插入varray数据类型的数据

INSERT INTO dept_phone_list VALUES (
   100,
   phone_varray_typ( phone_typ ('01', '650', '5550123'),
                      phone_typ ('01', '650', '5550148'),
                      phone_typ ('01', '650', '5550192')));
1
2
3
4
5

3、查询结果集

SQL> select * from dept_phone_list;

   DEPT_NO PHONE_LIST(COUNTRY_CODE, AREA_CODE, PH_NUMBER)
---------- ----------------------------------------------------------------------------------------------------
       100 PHONE_VARRAY_TYP(PHONE_TYP('01', '650', '5550123'), PHONE_TYP('01', '650', '5550148'), PHONE_TYP('01
	   ', '650', '5550192'))

1
2
3
4
5
6
7

4、更新varray的数据

对于varray数据类型，Oracle不支持对VARRAY列进行分段更新即不支持更新VARRAY中的单个元素，必须更新整个VARRAY类型的数组

  UPDATE dept_phone_list SET phone_list =
  					phone_varray_typ( phone_typ ('10', '210', '5550123'),
                      phone_typ ('10', '210', '5550148'),
                      phone_typ ('10', '210', '5550192'))
    WHERE dept_no=100;

SQL> select * from dept_phone_list; 

   DEPT_NO PHONE_LIST(COUNTRY_CODE, AREA_CODE, PH_NUMBER)
---------- ----------------------------------------------------------------------------------------------------
       100 PHONE_VARRAY_TYP(PHONE_TYP('10', '210', '5550123'), PHONE_TYP('10', '210', '5550148'), PHONE_TYP('10
	   ', '210', '5550192'))
1
2
3
4
5
6
7
8
9
10
11
12

5、删除varrayd的数据

 INSERT INTO dept_phone_list VALUES (
   101,
   phone_varray_typ( phone_typ ('20', '100', '123456'),
                      phone_typ ('20', '100', '123457')));  
SQL>  DELETE FROM dept_phone_list WHERE dept_no=101;

1 row deleted.

SQL> select * from dept_phone_list;

   DEPT_NO PHONE_LIST(COUNTRY_CODE, AREA_CODE, PH_NUMBER)
---------- ----------------------------------------------------------------------------------------------------
       100 PHONE_VARRAY_TYP(PHONE_TYP('10', '210', '5550123'), PHONE_TYP('10', '210', '5550148'), PHONE_TYP('10
	   ', '210', '5550192'))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

总结

其实集合类型的场景很多，但是在实际的应用开发使用的频率很小，想来大概的原因有如下3点：

1、研发或者架构师对于这些数据类型了解较少（也包括我自己）

2、遇到可以使用集合类型的场景，习惯通过建立子表的方式来实现

3、对于研发或者运维人员上手也必将容易

4、应用使用集合类型有哪些收益，比如是否有性能提升等等，也不是很明确