plsql数组和嵌套

浏览数：15 / 时间：2015年06月12日

6 集合类型

6.1 数组

定义：由其元素的最大数目限定的单维有限集合，存放2GB(2*1024*1024*1024)个元素，排列是紧密的

(1)数组的定义、声明、初始化

A 数字类型的数组类型

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40); --数组的声明+初始化
begin
for idx in 1..4 loop
    dbms_output.put_line(to_char(v_numvarray(idx)));
end loop;
end;

也可以在begin后面对数组进行初始化

declare
type num_varray is varray(5) of number;
v_numvarray num_varray;         --只是进行声明
begin
v_numvarray:=num_varray(10,20,30,40);     --在begin后面对数组进行初始化，注意，不能写在declare部分
for idx in 1..4 loop
    dbms_output.put_line(to_char(v_numvarray(idx)));
end loop;
end;

B 字符串类型的数组类型

declare
G type char_varray is varray(5) of varchar2(10);
v_charvarray char_varray:=char_varray(‘10‘,‘20‘,‘30‘,‘40‘);
begin
for idx in 1..4 loop
    dbms_output.put_line(v_charvarray(idx));
end loop;
end;

C 存储记录

--数组的扩展式赋值
declare
type hrc_org_rec is record(hrc_org_id number,hrc_descr varchar2(20),org_short_name varchar2(30));
type num_varray is varray(50) of hrc_org_rec;
cursor csr_hrc_org is select o.org_id,h.hrc_descr,o.org_short_name
                          from org_tab o,hrc_tab h
                         where o.hrc_code=h.hrc_code;
v_numvarray num_varray:=num_varray(null);
i integer:=1;
begin
for idx in csr_hrc_org loop
    v_numvarray(i):=idx;
    v_numvarray.extend;        --数组往后扩展一个位置
    i:=i+1;
end loop;
for idx in 1..v_numvarray.count loop
    dbms_output.put_line(to_char(v_numvarray(idx).hrc_org_id));
    dbms_output.put_line(v_numvarray(idx).hrc_descr);
    dbms_output.put_line(v_numvarray(idx).org_short_name);
end loop;
end;

--统一初始化数组
declare
type hrc_org_rec is record(hrc_org_id number,hrc_descr varchar2(20),org_short_name varchar2(30));
type num_varray is varray(50) of hrc_org_rec;
cursor csr_hrc_org is select o.org_id,h.hrc_descr,o.org_short_name
                          from org_tab o,hrc_tab h
                         where o.hrc_code=h.hrc_code;
v_numvarray num_varray:=num_varray(null,null,null,null,null,null);
i integer:=1;
begin
for idx in csr_hrc_org loop
    v_numvarray(i):=idx;
    i:=i+1;
end loop;
for idx in 1..v_numvarray.count loop
    dbms_output.put_line(to_char(v_numvarray(idx).hrc_org_id));
    dbms_output.put_line(v_numvarray(idx).hrc_descr);
    dbms_output.put_line(v_numvarray(idx).org_short_name);
end loop;
end;

(2) NULL数组和NULL元素

NULL数组 -- 数组还没有被初始化
NULL元素 -- 数组已经被初始化，但是里面的元素的值是NULL

A NULL数组，数组可以用 is null 来判断是否为空的

declare
type num_varray is varray(5) of number;
v_numvarray num_varray;
begin
if v_numvarray is null then
    dbms_output.put_line(‘NULL‘);
end if;
end;

declare
type num_varray is varray(5) of number;
v_numvarray num_varray;
begin
v_numvarray:=null;            --记录是不能这样赋值的，而数组可以
if v_numvarray is null then
    dbms_output.put_line(‘NULL‘);
end if;
end;

B NULL元素

declare
type num_varray is varray(5) of number;
v_numvarray num_varray;
begin
v_numvarray:=num_varray(null);
if v_numvarray is null then
    dbms_output.put_line(‘NULL‘);
else
    dbms_output.put_line(‘NOT NULL‘);
end if;
end;

declare
type num_varray is varray(5) of number;
v_numvarray num_varray;
begin
v_numvarray:=num_varray(null);
if v_numvarray is null then
    dbms_output.put_line(‘NULL‘);
else
    dbms_output.put_line(‘NOT NULL‘);
end if;
if v_numvarray(1) is null then
    dbms_output.put_line(‘element is null!‘);
end if;
end;

总结：
   v_numvarray:=num_varray(null);     --初始化数组，第一个元素为null
   v_numvarray:=null;                 --将整个数组置为NULL，相当于未初始化

(3) 访问数组的元素，通过数组的索引下标，数组的下标是从1开始的

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40); --数组的声明+初始化
begin
for idx in 1..4 loop
    dbms_output.put_line(to_char(v_numvarray(idx)));
end loop;
end;

A 没有定义的元素是否能访问？

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40); --数组的声明+初始化
begin
for idx in 1..4 loop
    dbms_output.put_line(to_char(v_numvarray(idx)));
end loop;
dbms_output.put_line(v_numvarray(5)); --报错，没有的定义的元素是不能访问的，ORA-06533:Subscript beyond count
end;

B 长度越界

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40,50,60); --报错，长度不能够越界，
                                                         --ORA-06532:Subscript outside of limit
begin
for idx in 1..4 loop
    dbms_output.put_line(to_char(v_numvarray(idx)));
end loop;
dbms_output.put_line(v_numvarray(5));
dbms_output.put_line(v_numvarray(6));
end;

(4)和index-by表的区别

declare
type num_table is table of number index by binary_integer;
v_example_tab num_table:=num_table(1,2,3,4,5,6);      --这种赋值是错误
v_num number:=13;
begin
for idx in 1..6 loop
    dbms_output.put_line(to_char(v_example_tab(idx)));
end loop;
end;

declare
type num_table is table of number index by binary_integer;
v_example_tab num_table:=null;        --不能够赋予NULL值，但是可以给元素赋null值
v_num number:=13;
begin
for idx in 1..6 loop
    dbms_output.put_line(to_char(v_example_tab(idx)));
end loop;
end;
declare
type num_table is table of number;
v_numtable num_table:=num_table(10,20,30,40);
begin
for idx in 1..v_numtable.count loop
    dbms_output.put_line(to_char(v_numtable(idx)));
end loop;
end;

可以给index-by表的元素赋NULL值
declare
type num_table is table of number index by binary_integer;
v_example_tab num_table;
v_num number:=13;
begin
for idx in 1..6 loop
    v_example_tab(idx):=null;
end loop;
for idx in 1..6 loop
    if v_example_tab(idx) is null then
      dbms_output.put_line(‘NULL‘);
    end if;
end loop;
end;

declare
type num_table is table of number index by binary_integer;
v_example_tab num_table;
begin
if v_example_tab is null then      --不能判断是否为NULL，这种判断没意义，尽管没有报错
    dbms_output.put_line(‘NULL‘);
else
    dbms_output.put_line(‘NOT NULL‘);
end if;
end;

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40,50,60);   --数组不能长度越界，但是index-by不存在界限的问题
begin
for idx in 1..4 loop
    dbms_output.put_line(to_char(v_numvarray(idx)));
end loop;
dbms_output.put_line(v_numvarray(5));
dbms_output.put_line(v_numvarray(6));
end;

###########################################################################################

6.2 嵌套表

定义：由其元素的没有最大数目限制的单维集合，无上限，排列是紧密的

(1) 定义、声明、初始化
declare
type num_table is table of number;            --定义嵌套表的方式，与数组类似
v_numtable num_table:=num_table(10,20,30,40);
begin
for idx in 1..v_numtable.count loop
    dbms_output.put_line(to_char(v_numtable(idx))); --下标也是从1开始的，访问跟数组是一样的
end loop;
end;

(2) NULL嵌套表和NULL嵌套表元素

A NULL嵌套表

declare
type num_table is table of number;
v_numtable num_table;
begin
if v_numtable is null then
    dbms_output.put_line(‘NULL‘);
end if;
end;

B NULL嵌套表元素

declare
type num_table is table of number;
v_numtable num_table;
begin
v_numtable:=num_table(null);
if v_numtable is null then
    dbms_output.put_line(‘NULL‘);
else
    dbms_output.put_line(‘NOT NULL‘);
end if;
if v_numtable(1) is null then
    dbms_output.put_line(‘element is null!‘);
end if;
end;

(3)访问嵌套表的元素 -- 跟数组一样

declare
type num_table is table of number;
v_numtable num_table:=num_table(10,20,30,40);
begin
for idx in 1..v_numtable.count loop
    dbms_output.put_line(to_char(v_numtable(idx)));
end loop;
end;

###########################################################################################

6.3 集合的方法

集合的方法：数组跟嵌套表都是可以用的，除非特别指出

(1) exists方法 -- 返回集合中索引对应位置是否有元素的存在，存在就返回true，不存在返回false

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40);
begin
if v_numvarray.exists(5) then
    dbms_output.put_line(‘YES‘);
else
    dbms_output.put_line(‘NO‘);
end if;
end;

(2) count方法 -- 返回集合元素的数目，包括NULL值

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40,null);
begin
dbms_output.put_line(to_char(v_numvarray.count));         --5个
end;

(3) limit方法 -- 只有数组有这个方法，嵌套表是没有的，输出数组的上限

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,20,30,40);
begin
dbms_output.put_line(to_char(v_numvarray.limit));        --输出上限
end;

(4) first、last 方法，返回的是索引号，而不是第一个值

first -- 返回集合中第一个有值的元素（包含null值）的索引号，如果集合为空返回null
last -- 返回集合中最后一个有值的元素（包含null值）的索引号，如果集合为空返回null

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,null,30,null);
begin
dbms_output.put_line(to_char(v_numvarray.first));     --输出的是1
dbms_output.put_line(to_char(v_numvarray.last));      --输出的是4
end;

(5) next，prior方法

next -- 返回集合中指定元素下一个有值的元素（包含null值）的索引号，如果集合为空返回null
prior -- 返回集合中指定元素上一个有值的元素（包含null值）的索引号，如果集合为空返回null

declare
type num_varray is varray(5) of number;
v_numvarray num_varray:=num_varray(10,null,30,null);
begin
dbms_output.put_line(to_char(v_numvarray.next(1)));   --输出的是2
dbms_output.put_line(to_char(v_numvarray.next(4)));   --输出的是空
end;

(6) extend方法 -- 在集合的末尾处添加新元素或者在末尾处初始化元素

数组：
declare
type num_varray is varray(200) of number;
v_numvarray num_varray:=num_varray(null,null);
begin
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray(1):=1001;
v_numvarray(2):=1002;
v_numvarray.extend;   --在集合的末尾处添加一个null元素
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray(3):=1003;
v_numvarray.extend(5); --在集合的末尾处添加5个null元素
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray(8):=1008;
v_numvarray.extend(5,8);   --在集合的末尾处添加5个在索引8处的元素
dbms_output.put_line(to_char(v_numvarray.count));
for i in 1..v_numvarray.count loop
    if v_numvarray(i) is not null then
      dbms_output.put_line(to_char(v_numvarray(i)));
    else
      dbms_output.put_line(‘NULL!‘);
    end if;
end loop;
end;

A 直接extend    --在末尾处添加一个null元素
B extend(5)     --在末尾处添加5个null元素
C extend(5,8)   --在集合的末尾处添加5个在索引8处的元素

嵌套表：
跟数组一模一样

(7) trim方法 -- 在集合的末尾处删除元素，后面带数字表示在末尾处删除多少个元素

declare
type num_varray is varray(200) of number;
v_numvarray num_varray:=num_varray(null,null);
begin
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray(1):=1001;
v_numvarray(2):=1002;
v_numvarray.trim;      --在集合的末尾处删除一个元素
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray.extend;
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray(2):=10022;
v_numvarray.trim(2);
dbms_output.put_line(to_char(v_numvarray.count));
v_numvarray.extend(2);
v_numvarray(1):=10011;
v_numvarray(2):=10021;
dbms_output.put_line(to_char(v_numvarray.count));
for i in 1..v_numvarray.count loop
    if v_numvarray(i) is not null then
      dbms_output.put_line(to_char(v_numvarray(i)));
    else
      dbms_output.put_line(‘NULL!‘);
    end if;
end loop;
end;

(8) delete方法 -- 删除元素

只有嵌套表后面能带数字，数字表示删除数字所在位置的元素，如果是数组，只能用delete方法，后面不能带数字

declare
type num_table is table of number;
v_numtable num_table:=num_table(null,null,null,null,null);
begin
dbms_output.put_line(to_char(v_numtable.count));
v_numtable(1):=1001;
v_numtable(2):=1002;
v_numtable.delete;         --直接将嵌套表的元素全部删除
dbms_output.put_line(to_char(v_numtable.count));
v_numtable.extend(2);
dbms_output.put_line(to_char(v_numtable.count));
v_numtable(1):=1001;
v_numtable(2):=1002;
dbms_output.put_line(to_char(v_numtable.count));
v_numtable.delete(2);     --删除索引位置2的元素
dbms_output.put_line(to_char(v_numtable.count));
v_numtable.extend(2);
dbms_output.put_line(to_char(v_numtable.count));
v_numtable(2):=10022;
dbms_output.put_line(to_char(v_numtable.count));    --为什么会变成4了
for idx in 1..v_numtable.count loop
    dbms_output.put_line(v_numtable(idx));
end loop;
end;

###########################################################################################

6.4 集合在数据库中的使用

6.4.1 数组在数据库中的使用

(1) 数组在数据库中存储
A 创建一个数组对象

SQL> create or replace type add_list as varray(10) of number(10); /

Type created.

B 查询数据字典
select * from user_objects where object_name=‘ADD_LIST‘;        --数组以type形式存在

select * from user_types;

(2) 数组的DML操作

A insert 操作

方法1：直接插入
insert into direct_address_list values(‘OFF101‘,add_list(1001,1002,1003,1004));
commit;

查询：
SQL> select * from direct_address_list;

LIST_I DIRECT_ADDRESS
------ --------------------------------------------------
OFF101 ADD_LIST(1001, 1002, 1003, 1004)

人          车              人和车的关系表
R001        C001            R001     C001
R002        C002            R001     C002
R003        C003            R002     C003
                   R002     C003
把车的编号作为数组
R001 ADD_LIST(C001,C002)
R002 ADD_LIST(C003)
R003 ADD_LIST(C003)

方法2：用匿名块的方式插入
declare
v_add_varray add_list:=add_list(1001,1002,1003,1004);
begin
insert into direct_address_list values(‘OFF102‘,v_add_varray);
commit;
end;

SQL> select * from direct_address_list;

LIST_I DIRECT_ADDRESS
------ --------------------------------------------------
OFF101 ADD_LIST(1001, 1002, 1003, 1004)
OFF102 ADD_LIST(1001, 1002, 1003, 1004)

B update操作

方法1：直接update

update direct_address_list set DIRECT_ADDRESS=add_list(1011,1012,1013) where list_id=‘OFF102‘;
commit;

SQL> select * from direct_address_list;

LIST_ID          DIRECT_ADDRESS
-------------------- --------------------------------------------------
OFF101             ADD_LIST(1001, 1002, 1003, 1004)
OFF102             ADD_LIST(1011, 1012, 1013)

方法2：在匿名块中update

declare
v_add_varray add_list:=add_list(1011,1012,1013,1014);
begin
update direct_address_list set direct_address=v_add_varray where list_id=‘OFF101‘;
commit;
end;

SQL> select * from direct_address_list;

LIST_ID          DIRECT_ADDRESS
-------------------- --------------------------------------------------
OFF101             ADD_LIST(1011, 1012, 1013, 1014)
OFF102             ADD_LIST(1011, 1012, 1013)

C delete操作，不能用数组作为where条件过滤

delete from direct_address_list where list_id=‘OFF102‘;
commit;

SQL> select * from direct_address_list;

LIST_ID          DIRECT_ADDRESS
-------------------- --------------------------------------------------
OFF101             ADD_LIST(1011, 1012, 1013, 1014)

D select 操作

方法1：直接查询

SQL> select * from direct_address_list;

LIST_ID          DIRECT_ADDRESS
-------------------- --------------------------------------------------
OFF101             ADD_LIST(1011, 1012, 1013, 1014)

方法2：用匿名块查询并依次输出使用select into方法
declare
v_add_varray direct_address_list.direct_address%type;   --%type也是行变量，但是只能存储一列
begin
select direct_address into v_add_varray from direct_address_list where list_id=‘OFF101‘;
for idx in 1..v_add_varray.count loop
    dbms_output.put_line(to_char(v_add_varray(idx)));
end loop;
end;

declare
v_add_varray direct_address_list.direct_address%type;   --%type也是行变量，但是只能存储一列
begin
for i in (select list_id from direct_address_list) loop
      select direct_address into v_add_varray from direct_address_list where list_id=i.list_id;
      for idx in 1..v_add_varray.count loop
          dbms_output.put_line(to_char(v_add_varray(idx)));
      end loop;
      dbms_output.put_line(‘--------------‘);
end loop;
end;

多行输出

declare
v_add_varray direct_address_list.direct_address%type;   --%type也是行变量，但是只能存储一列
begin
for i in (select list_id from direct_address_list) loop
      select direct_address into v_add_varray from direct_address_list where list_id=i.list_id;
      for idx in 1..v_add_varray.count loop
          dbms_output.put_line(to_char(v_add_varray(idx)));
      end loop;
      dbms_output.put_line(‘--------------‘);
end loop;
end;

##########################################################################################

6.4.2 嵌套表在数据库中的使用

(1) 嵌套表在数据库中存储

A 创建一个嵌套表对象
create or replace type home_add_list as table of number(10);

B 查询数据字典
select * from user_objects where object_name=‘HOME_ADD_LIST‘;

TYPE就是集合类型，无法从视图上识别是哪种集合类型，需要查看创建的代码才能够看到

SQL> set long 1000;     --调整select结果的长度

怎样查看一个对象的创建的代码
select dbms_metadata.get_ddl(‘TYPE‘,‘TYPE_NAME‘) from dual;

C 基于嵌套表创建表

create table home_addresses_list(list_id varchar2(6) primary key,home_addresses home_add_list)
nested table home_addresses store as home_addresses_tab;
嵌套表                      作为..存储

select * from user_objects where object_name like ‘HOME_ADDRESS%‘;      --发现上面的操作创建了两个表类型

select * from user_tables where table_name like ‘HOME_ADDRESS%‘;
--通过这个视图，会发现HOME_ADDRESSES_TAB这个表它的nested这个字段的值为YES，说明它是一个嵌套表

(2) 嵌套表的DML操作

A insert 操作
方法1：直接insert
insert into home_addresses_list values(‘OFF101‘,home_add_list(1001,1002,1003,1004));
commit;

SQL> select * from home_addresses_list;

LIST_ID          HOME_ADDRESSES
-------------------- --------------------------------------------------
OFF101             HOME_ADD_LIST(1001, 1002, 1003, 1004)

方法2：通过匿名块
declare
v_add_varray home_add_list:=home_add_list(1001,1002,1003,1004);
begin
insert into home_addresses_list values(‘OFF102‘,v_add_varray);
commit;
end;

SQL> select * from home_addresses_list;

LIST_ID          HOME_ADDRESSES
-------------------- --------------------------------------------------
OFF101             HOME_ADD_LIST(1001, 1002, 1003, 1004)
OFF102             HOME_ADD_LIST(1001, 1002, 1003, 1004)

B update操作:跟数组一样

C delete操作:跟数组一样

D select操作

方法1：直接查询

SQL> select * from home_addresses_list;

LIST_ID          HOME_ADDRESSES
-------------------- --------------------------------------------------
OFF101             HOME_ADD_LIST(1001, 1002, 1003, 1004)
OFF102             HOME_ADD_LIST(1001, 1002, 1003, 1004)

方法2：通过匿名块查询(和数组一样)

方法3：子查询的方法，只查询嵌套表行(直接查询嵌套表是查不到)

SQL> select * from table(select home_addresses from home_addresses_list where list_id=‘OFF101‘);

COLUMN_VALUE
------------
   1001
   1002
   1003
   1004

方法4：将嵌套表展开的方式来展现

SQL> select list_id,column_value from home_addresses_list,table(home_addresses);

LIST_ID          COLUMN_VALUE
-------------------- ------------
OFF101                 1001
OFF101                 1002
OFF101                 1003
OFF101                 1004
OFF102                 1001
OFF102                 1002
OFF102                 1003
OFF102                 1004

8 rows selected.

方法5：通过创建临时表的方式对局部变量操作

A 创建一个实体表作为临时表存储嵌套表的数据

create table num_table(col number(10));

B 通过对局部变量的访问插入嵌套表数据到上面所创建的表

declare
v_add_list home_add_list:=home_add_list(1001,1002,1003);
begin
insert into num_table select column_value from table(cast(v_add_list as home_add_list));
commit;
end;

select column_value from table(cast(v_add_list as home_add_list)) --将一个嵌套表散开显示

SQL> select * from num_table;

       COL
----------
      1001
      1002
      1003

用在home_addresses_list表
declare
cursor csr_add_list is select * from home_addresses_list;
v_add_list varchar2(30);
v_home_list home_add_list;
begin
open csr_add_list;
loop
    fetch csr_add_list into v_add_list,v_home_list;
    exit when(csr_add_list%notfound);
    insert into num_table select column_value from table(cast(v_home_list as home_add_list));
end loop;
close csr_add_list;
end;

select * from num_table;

练习6:
写一段程序，在SCOTT下
1.克隆表dept_t,从dept克隆来的，不要数据
2.给dept_t加一列，数据类型为数组，存储员工号
3.程序需要独处所有的员工号，存储在数组中，然后写入到dept_t的数组列中

create table dept_t as select * from dept where 1=2;
create or replace type emp_list as varray(100) of number;
alter table dept_t add varray_empno emp_list;

declare
cursor csr_emp is select b.deptno,b.dname,b.loc
                      from dept b;
v_deptno number;
v_dname varchar2(30);
v_loc varchar2(30);
v_varray emp_list:=emp_list(null);
i integer;
begin
open csr_emp;
loop
    v_varray.delete;
    fetch csr_emp into v_deptno,v_dname,v_loc;
    exit when(csr_emp%notfound);
    i:=1;
    for idx in (select a.empno from emp a where a.deptno=v_deptno) loop
      v_varray.extend;
      v_varray(i):=idx.empno;
      i:=i+1;
    end loop;
    insert into dept_t values(v_deptno,v_dname,v_loc,v_varray);
    commit;
end loop;
close csr_emp;
exception when others then
null;
end;

SQL> select * from dept_t;

    DEPTNO DNAME          LOC       VARRAY_EMPNO
---------- -------------- ------------- --------------------------------------------------
   10     ACCOUNTING      NEW YORK   EMP_LIST(7782, 7839, 7934)
   20     RESEARCH          DALLAS   EMP_LIST(7369, 7566, 7788, 7876, 7902)
   30     SALES          CHICAGO   EMP_LIST(7499, 7521, 7654, 7698, 7844, 7900)
   40     OPERATIONS      BOSTON   EMP_LIST()

###########################################################################################