PL/SQL 联合数组与嵌套表

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组或嵌套表来完成,其特点是类似于单列数据库表。在Oracle 9i 之前称为PL/SQL索引表,9i 之后称之为联合数组。嵌套表也是集合 类型中的一种,下面分别介绍这两种集合数据类型的使用方法。

一、联合数组

1、联合数组的特性
	类似于一张简单的SQL表,按照主键进行检索数据
	其数据行并不是按照预定义的顺序存储。当使用变量来检索其数据时,每行数据会分配一个连续的下标且从1开始。
	下标可以为负值,且下标的数据类型支持binary_integer,pls_integer,varchar2
	其数据元素个数无限制
	不能作为表列的数据类型使用,即只能作为PL/SQL复合数据类型使用
	存放的数据类型是临时数据,故不支持insert,select into 等SQL语句,等同于SQL server中的表变量
		
2、语法		
	TYPE type_name IS TABLE OF element_type [NOT NULL]  -->element_type为联合数组元数据指定数据类型(先使用TYPE声明表结构)
	INDEX BY key_type;                                  -->元素下标的数据类型
	table_name TYPE_NAME;                               -->再使用声明的TYPE类型声明实际数组名
		
3、示例
--使用PLS_INTEGE类型联合数组
scott@CNMMBO> DECLARE                                   -->定义游标       
  2     CURSOR name_cur IS
  3        SELECT dname
  4        FROM   dept
  5        WHERE  deptno < 40;
  6  
  7     TYPE name_type IS TABLE OF dept.dname%TYPE      -->定义一个联合数组类型,且其下标的数据类型为PLS_INTEGER
  8                          INDEX BY PLS_INTEGER;
  9  
 10     dname_tab   name_type;                          -->声明类型为name_type的联合数组dname_tab
 11     v_counter   INTEGER := 0;
 12  BEGIN
 13     FOR name_rec IN name_cur
 14     LOOP
 15        v_counter   :=
 16           v_counter
 17           + 1;
 18        dname_tab( v_counter ) := name_rec.dname;      -->对联合数据进行循环赋值
 19        DBMS_OUTPUT.put_line(   'Dname is('
 20                             || v_counter
 21                             || '):'
 22                             || dname_tab( v_counter ) );   -->输出联合数组中的所有元素
 23     END LOOP;
 24  END;
 25  /
Dname is(1):ACCOUNTING
Dname is(2):RESEARCH
Dname is(3):SALES

PL/SQL procedure successfully completed.

--使用VARCHAR2类型联合数组
scott@CNMMBO> DECLARE
  2     TYPE score_type IS TABLE OF NUMBER
  3                           INDEX BY VARCHAR2( 10 );    -->注意此处声明的联合数组的下标数据类型为varchar2
  4  
  5     score_tab   score_type;
  6  BEGIN
  7     score_tab( 'SCOTT' ) := 95;                       -->对不同下标分别进行赋值
  8     score_tab( 'JOHN' ) := 98;
  9     score_tab( 'ROBINSON' ) := 96;
 10     DBMS_OUTPUT.put_line( 'First element is '
 11                          || score_tab.FIRST );        -->输出联合数组score_tab中的第一个元素的下标
 12     DBMS_OUTPUT.put_line( 'last element is '
 13                          || score_tab.LAST );         -->输出联合数组score_tab中的最后一个元素的下标
 14     DBMS_OUTPUT.put_line( 'The score of Scott is '
 15                          || score_tab( 'SCOTT' ) );   -->输出下标为SCOTT的元素的值
 16  END;
 17  /
First element is JOHN                                     -->注意此处输出的是JOHN(第一个和最后一个是按字母排序得到的)
last element is SCOTT
The score of Scott is 95

PL/SQL procedure successfully completed.

--下标超范围的情况
scott@CNMMBO> DECLARE
  2     TYPE score_type IS TABLE OF NUMBER
  3                           INDEX BY VARCHAR2( 10 );
  4  
  5     score_tab   score_type;
  6  BEGIN
  7     score_tab( 'SCOTT' ) := 95;
  8     score_tab( 'JOHN' ) := 98;
  9     score_tab( 'ROBINSON' ) := 96;
 10     DBMS_OUTPUT.put_line( 'The score of Jason is  '
 11                          || score_tab( 'JASON' ) );
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found                              -->当下标超范围时,将收到ORA-01403错误
ORA-06512: at line 10

二、嵌套表

1、嵌套表的特点:
	元素下表从1开始,个数没有限制.即元素个数可以动态增长
	嵌套表的数组元素值可以是稀疏的,即可以使得中间的某个元素没有赋值
	嵌套表的语法与联合数组类似,不同的是仅仅是少了index by子句   
	嵌套表必需先初始化,然后才能引用其中的元素。如果初始化为空值,则后续需要使用extend来扩展其大小  
	嵌套表初始化时为密集的,但允许有间隙,即允许使用内置过程delete从嵌套表中删除元素
	嵌套表类型可以作为表列的数据类型来使用

2、语法
		TYPE type_name IS TABLE OF element_type [NOT NULL];   -->element_type为索引表元数据指定数据类型(先使用TYPE声明表结构) 
		table_name TYPE_NAME;                                 -->再使用声明的TYPE类型声明实际嵌套表       

3、示例
--声明嵌套表并输出嵌套表的实际内容   		
scott@CNMMBO> DECLARE
  2     CURSOR name_cur IS
  3        SELECT dname
  4        FROM   dept
  5        WHERE  deptno < 40;
  6  
  7     TYPE name_type IS TABLE OF dept.dname%TYPE;
  8  
  9     dname_tab   name_type := name_type( );   -->需要初始化,否则将收到 ORA-06531:Reference to uninitialized collection.
 10     v_counter   INTEGER := 0;
 11  BEGIN
 12     FOR name_rec IN name_cur
 13     LOOP
 14        v_counter   :=
 15           v_counter
 16           + 1;
 17        dname_tab.EXTEND;         -->需要扩展,否则将收到 ORA-06533: Subscript beyond count
 18        dname_tab( v_counter ) := name_rec.dname;
 19        DBMS_OUTPUT.put_line(   'Dname ('
 20                             || v_counter
 21                             || ') is :'
 22                             || dname_tab( v_counter ) );
 23     END LOOP;
 24  END;
 25  /
Dname (1) is :ACCOUNTING
Dname (2) is :RESEARCH
Dname (3) is :SALES

PL/SQL procedure successfully completed.

--将嵌套表作为表列的数据类型来使用
scott@CNMMBO> CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 );
  2  /

Type created.

scott@CNMMBO> CREATE TABLE tb_tmp       -->创建表tb_tmp
  2  (
  3     empno   NUMBER( 4 )
  4   , ename   VARCHAR2( 10 )
  5   , mail    mail_type               -->列mail的类型为mail_type
  6  )
  7  NESTED TABLE mail                 -->注意此处需要指定嵌套表的存储方式
  8     STORE AS mail_tab;

Table created.

--为嵌套表插入数据
scott@CNMMBO> insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual;  -->传统方式插入失败
insert into tb_tmp select 8888,'Jack','Jack@yahoo.com,Jack@163.com' from dual
                                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR

scott@CNMMBO> insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual;  -->分割字符串方式插入失败
insert into tb_tmp select 8888,'Jack',q'['Jack@yahoo.com','Jack@163.com']' from dual
                                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR

scott@CNMMBO> insert into tb_tmp select 8888,'Jack',mail_type('Jack@yahoo.com','Jack@163.com') from dual; -->插入时指定嵌套表类型 

1 row created.

scott@CNMMBO> commit;

Commit complete.

--查看存在嵌套表数据类型的表中的记录
scott@CNMMBO> col mail format a30
scott@CNMMBO> select * from tb_tmp;                    -->查询数据时带有嵌套表的类型

     EMPNO ENAME      MAIL
---------- ---------- ------------------------------
      8888 Jack       MAIL_TYPE('Jack@yahoo.com', 'J
                      ack@163.com')

scott@CNMMBO> DECLARE
  2     mail_tab   mail_type;    -->声明一个mail_type数据类型
  3  BEGIN
  4     SELECT mail
  5     INTO   mail_tab          -->将数据保存到 mail_tab 变量中
  6     FROM   tb_tmp
  7     WHERE  empno = 8888;
  8  
  9     FOR i IN 1 .. mail_tab.COUNT   -->轮循输出嵌套表类型中的值
 10     LOOP
 11        DBMS_OUTPUT.put_line( 'Jack mail address is '
 12                             || mail_tab( i ) );
 13     END LOOP;
 14  END;
 15  /
Jack mail address is Jack@yahoo.com
Jack mail address is Jack@163.com

PL/SQL procedure successfully completed.

--更新表中含有嵌套表类型中的值
scott@CNMMBO> DECLARE
  2     mail_tab   mail_type := mail_type( 'Jackson@yahoo.com', 'Jackson@163.com' );
  3  BEGIN
  4     UPDATE tb_tmp
  5     SET    mail         = mail_tab;
  6  
  7     COMMIT;
  8     DBMS_OUTPUT.put_line( 'Jack mail address was updated' );
  9  END;
 10  /
Jack mail address was updated

PL/SQL procedure successfully completed.

scott@CNMMBO> select * from tb_tmp;

     EMPNO ENAME      MAIL
---------- ---------- ------------------------------
      8888 Jack       MAIL_TYPE('Jackson@yahoo.com',
                       'Jackson@163.com')

--删除表中存在嵌套表类型的记录
scott@CNMMBO> delete from tb_tmp where empno=8888;

1 row deleted.

scott@CNMMBO> commit;

Commit complete.

scott@CNMMBO> select * from tb_tmp;

no rows selected                       
--> Author: Robinson Cheng
--> Blog: http://blog.csdn.net/robinson_0612

三、总结 1、不论是联合数组还是嵌套表,应当以集合的方式来看待与处理 2、联合数组在声明其类型时需要指定index by子句,而嵌套表则不需要 3、联合数组和嵌套表两者元素个数无限制    4、联合数组不需要初始化,而嵌套表则需要对其进行初始化。其次是嵌套表初始化赋空值的情形则后续需要使用extend方式来扩展集合尺寸的大小 5、联合数组不能作为表上列的数据类型,而嵌套表无此限制

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端儿

一种排序

现在有很多长方形,每一个长方形都有一个编号,这个编号可以重复;还知道这个长方形的宽和长,编号、长、宽都是整数;现在要求按照一下方式排序(默认排序规则都是从小到大...

1022
来自专栏Java成神之路

Oracle学习笔记_02_基本SQL

        SQL 语言大小写不敏感。         SQL 可以写在一行或者多行 关键字不能被缩写也不能分行         各子句一般要分行写。 ...

912
来自专栏王磊的博客

MongoDB Query 的几个方法

Query.All("name", "a", "b");//通过多个元素来匹配数组 Query.And(Query.EQ("name", "a"), Query...

3078
来自专栏linjinhe的专栏

MySQL数据类型

2264
来自专栏木子昭的博客

<导图>Mysql常用查询语法

普通查询 查看整个表 格式: select * from 表名; 示例: select * from students; 查询指定字段 格式 ...

3347
来自专栏杨建荣的学习笔记

MySQL数据类型(r3笔记第87天)

今天在本地装了一个MySQL的学习环境,简单的熟悉了一下。准备开始好好学习MySQL了。 学习编程语言我都是从数据类型入手。每种编程语言的数据类型都有自己的特点...

29610
来自专栏吾爱乐享

软件测试之学习mysql的查询功能select及高级查询(重中之重)

1272
来自专栏栗霖积跬步之旅

第十章:创建计算字段

创建在数据库表中的数据一般不是应用程序所需要的格式。  我们需要从数据库中检索出转换、计算或格式化过的数据。  计算字段并不实际存在于数据库表中,计算字段是运行...

1735
来自专栏阿杜的世界

MySQL文档阅读(一)-数字类型

MySQL支持很多系列的SQL数据类型:数字类型(numeric types)、日期和时间类型(date and time types)、字符串类型(字符和字节...

511
来自专栏西安-晁州

mybatis学习之高级映射

一对一映射查询 1、数据库执行脚本: /* SQLyog Ultimate v12.09 (64 bit) MySQL - 5.7.11-log : Datab...

2800

扫码关注云+社区

领取腾讯云代金券