Oracle数据库 Oracle集合

一、Oracle集合的定义

集合是Oracle中的一种数据类型，存放一组数据类型相同的数据。

二、集合的组成

由 下标 和 值组成。
下标的类型包含 数字类型（整数类型、pls_integer和binary_integer） 和 字符串类型。
值的类型是数据库中所有的类型（基本数据类型、记录类型（record、%rowtype、%type）和集合类型）。
pls_integer和binary_integer相当于number类型，但比number类型占用内存较少、算术运算速度更快。
pls_integer进行的运算发生溢出时,会触发异常,执行是由硬件cpu来运行的,执行速度快.binary_integer不会触发异常,类型变量时由Oracle来执行的,不会溢出,但是执行慢,都是整形类型。

三、集合的类型

集合是一个比较广义的概念，在PL/SQL中提供了3种类型的集合：
索引表：可以通过数字或字符串作为下标来查找其中的元素，只能在PL/SQL中使用。
嵌套表：拥有索引表的所有特性，但是下标只能是数据类型（连续的整数）。在PL/SQL和数据库中均可使用。
变长数组：下标只能是数字类型，创建时需要指定最大长度。在PL/SQL和数据库中均可使用。

集合常用的属性和方法

集合属性/方法	描述
first	取集合第一个元素的下标
last	取集合最后一个元素的下标
next（下标）	取集合当前元素的下一个元素的下标
prior（下标）	取集合当前元素的上一个元素的下标
count	得到集合中元素的个数
delete	删除集合中的元素
limit	去集合最大元素的个数（变长数组）

3.1 索引表类型

3.1.1 索引表的语法

--定义索引表类型的语法：
type 类型名称 is table of 元素值的数据类型 index by 下标的数据类型;
--索引变量的声明
变量名 类型名;
--索引变量的使用
变量名(下标);

3.1.2 索引表的使用案例

案例1：定义一个索引表类型（下标为数字类型），用定义的索引表类型声明一个索引变量用于存放三个姓名，并输出存放的第一个姓名

declare
  type type1 is table of varchar2(30) index by pls_integer;
  names type1;
begin
  names(1):='小曹同学1';
  names(2):='小曹同学2';
  names(3):='小曹同学3';
  dbms_output.put_line(names(1));
end;

案例2：定义一个索引表类型（下标为字符串类型）并使用，测试集合的常用属性和方法

declare
  type type2 is table of varchar2(30) index by varchar2(10);
  names type2;
begin
  names('a'):='小曹同学1';
  names('b'):='小曹同学2';
  names('c'):='小曹同学3';
  dbms_output.put_line(names.first); --a
  dbms_output.put_line(names.last); --c
  dbms_output.put_line(names.next(names.first)); --b
  dbms_output.put_line(names.prior(names.last)); --b
  dbms_output.put_line(names.count); --3
  names.delete;
end;

3.1.3 语句循环遍历

集合提供了 bulk collect 语句获取表中数据。
通过 bulk collect 语句存入集合中的元素下标从1开始且是连续的，且集合下标必须是数字类型。

语法1

select 列.. bulk collect into 集合变量 from 表 where条件

--案例：
declare
  type i_empno is table of emp.empno%type index by pls_integer;
  type i_ename is table of emp.ename%type index by pls_integer;
  eno i_empno;
  ena i_ename;
begin
  select empno,ename bulk collect into eno,ena from emp where deptno=30;
  for i in eno.first..eno.last loop
    dbms_output.put_line(eno(i) || ' ' || ena(i));
  end loop;
end;

语法2

execute immediate 'select语句' bulk collect into 集合变量

--案例
declare
  --声明一个变量存放select查询结果
  v_sql varchar2(255);
  --定义索引表集合存储emp表中empno和ename
  type i_empno is table of emp.empno%type index by pls_integer;
  type i_ename is table of emp.ename%type index by pls_integer;
  --定义索引变量
  eno i_empno;
  eme i_ename;
begin
  --将sql语句赋值给v_sql
  v_sql:='select empno,ename from emp where deptno=30';
  --bulk collect语句,将v_sql查询到的结果放到eno和eme中
  execute immediate v_sql bulk collect into eno,eme;
  --循环打印eno和eme中所有的数据
  for i in eno.first..eme.last loop
    dbms_output.put_line(eno(i) || ' ' || eme(i));
  end loop;
end;

语法3

fetch 游标 bulk collect into 集合变量

--案例
declare
  --声明一个游标
  cursor cursor1 is select empno,ename from emp where deptno=30;
  --定义索引表集合存储emp表中empno和ename
  type i_empno is table of emp.empno%type index by pls_integer;
  type i_ename is table of emp.ename%type index by pls_integer;
  --定义索引变量
  eno i_empno;
  eme i_ename;
begin
  --打开游标
  open cursor1;
  --bulk collect 语句
  fetch cursor1 bulk collect into eno,eme;
  --关闭游标
  close cursor1;
  --循环打印eno和eme中所有的数据
  for i in eno.first..eme.last loop
    dbms_output.put_line(eno(i) || ' ' || eme(i));
  end loop;
end;

3.2 嵌套表

嵌套表和索引表一样都是存放一组数据类型相同的数据。
它的下标只能是整数类型(下标是连续的整数)。
在使用前需要初始化嵌套表变量。

3.2.1 在PL/SQL中使用

3.2.1.1 语法

--语法：
type 类型名 is table of 存储的数据的数据类型(长度);
--声明变量：
变量名 嵌套表类型名;
--初始化赋值：
变量名:=嵌套表类型名();  
--初始化一个带元素的嵌套表
变量名:=嵌套表类型名(值,值,值,..,值);
--扩展嵌套表
变量名.extend(n);
--赋值：
变量名(下标):=值;

3.2.1.2 使用案例
案例1：初始化一个空的嵌套表

declare
  type mytype is table of varchar2(20);
  c1 mytype;
begin
  --初始化
  c1 := mytype();
  dbms_output.put_line(c1.count);
  --将嵌套表c1扩展两个元素
  c1.extend(2);
  dbms_output.put_line(c1.count);
  c1(1) := 'A';
  c1.extend(5);
  for i in 2..6 loop
    c1(i) := chr(64 + i);
  end loop;
  for i in c1.first..c1.last loop
    dbms_output.put_line(c1(i));
  end loop;
end;

案例2：初始化一个带元素的嵌套表

declare
  --定义一个嵌套表
  type mytype is table of varchar2(20);
  c2 mytype;
begin
  --初始化一个带元素的嵌套表
  c2 := mytype('张三','李四','王五','赵六');
  --嵌套表c2扩展2个元素
  c2.extend(2);
  c2(5):='小米';
  c2(6):='小曹同学';
  --遍历嵌套表
  for i in c2.first..c2.last loop
    dbms_output.put_line(c2(i));
  end loop;
end;

3.2.2 在数据库使用

如果在pl/sql中使用嵌套表,那么其功能与索引表比较相似。
嵌套表的一个重要的特色是支持作为数据表列 存储，因此可以将嵌套表存储在数据表中或者从数据表中取出嵌套表，这是索引表不具有的功能。
为了让嵌套表类型能在数据表中使用,要求嵌套表类型必须保存到数据字典中因此需要使用 create type 语句创建一个持久的嵌套表类型。

3.2.2.1 语法

create type 类型名 is table of 元素数据类型;

3.2.2.2 案例

create type ctype is table of varchar2(30);
declare
 c ctype;
begin
  select ename bulk collect into c from emp where deptno=10;
  for i in c.first..c.last loop
    dbms_output.put_line(c(i));
  end loop;
end;

3.2.3 在建表时使用

3.2.3.1 语法

--创建语法：
create table 表名(
  列名 类型,
  嵌套表列名 嵌套表类型
)nested table 嵌套表列名 store as 表名;
--插入数据
insert into 表名(字段名1,字段名2)values(嵌套表类型名称(值1),嵌套表类型名称(值2));

3.2.3.2 案例

create table x(
  id number(10),
  namelist ctype
)nested table namelist store as y;

--插入数据
insert into x(id,namelist)values(1,ctype('小红','小米','土豆'));
--查询
select * from x;
select * from table(select namelist from x where id=1);

3.3 变长数组

下标同样只能是数字类型,使用前需要初始化,和扩展。
变长数组类型在定义时,需要指定它的最大长度。
变长数组在扩展时,不能超过最大长度。
有最大长度限制的嵌套表, 编码上和嵌套表完全相同。

3.3.1 在PL/SQL中使用

3.3.1.1 语法

--创建语句：
type 类型名 is varray(最大长度) of 元素的数据类型;
--初始化：
变量名:=类型名();--初始化一个空的变长数组
变量名:=类型名(值1,值2);--初始化一个带元素的变长数组
--元素扩展：
变量名.extend(n);
--赋值：
变量名(n):=值;

3.3.1.2 案例

declare
  type atype is varray(5) of varchar2(30);
  a atype;
begin
  --初始化
  a:=atype();
  --扩展
  a.extend(3);
  --添加数据
  a(1):='A';
  a(2):='B';
  a(3):='C';
  for i in a.first..a.last loop
    dbms_output.put_line(a(i));
  end loop;
end;

3.3.2 在数据库使用

3.3.2.1 创建语法

create type 类型名 is varray(长度) of 类型;

3.3.2.2 案例：查询emp表中工资排名第5位到第14位的姓名

create type atype1 is varray(10) of varchar2(30);
declare
  a atype1;
begin
  select ename bulk collect into a from (
  select ename,row_number() over(order by sal desc nulls last)ranks from emp)
  where ranks between 5 and 14;
  for i in a.first..a.last loop
    dbms_output.put_line(a(i));
  end loop;
end; 

3.3.3 在建表时使用

3.3.3.1 创建语法
create table 表名(
变量 类型,
变长数组列 变长数组类型
);
3.3.3.2 案例

create table z(
  id number(10),
  namelist atype1
)
----插入数据
insert into z(id,namelist) values(1,atype1('a','b','c','d'));
select * from table(select namelist from z where id=1);