Oracle数据库 Oracle集合

本文详细介绍了Oracle数据库中的集合类型,包括索引表、嵌套表和变长数组。内容涵盖它们的定义、组成、使用场景及示例,特别强调了在PL/SQL和数据库中的应用,并给出了创建和操作集合的语法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、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 集合变量 fromwhere条件
--案例:
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);
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值