文章目录
一、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);