在PL/SQL中自定义数组类型

知识准备

定长数组
1
2
--定长数字型数组,长度为10
type identifer is varray(10) of number;

如:

1
2
3
4
5
6
7
8
9
10
11
12
13
set serveroutput on;
declare
type v_array is varray(10) of number;
v_index number;
v_arr v_array;
v_arr := v_array(1,23,567,233,66);
begin
for index in 1..v_arr.count
loop
dbms_output.put_line(v_arr(v_index));
end loop;
end;
/

变长数组
1
2
--可变长数字型数组,数组中元素长度为10,角标索引为integer自动增长
type identifer is table of number(10) index by binary_integer;

如:

1
2
3
4
5
6
7
8
9
10
11
12
13
set serveroutput on;
declare
type v_array is is table of number(10) index by binary_integer;
v_index number;
v_arr v_array;
v_arr := v_array(1,23,567,233,66);
begin
for index in 1..v_arr.count
loop
dbms_output.put_line(v_arr(v_index));
end loop;
end;
/

实战

批量更新用户的在线状态,userid 为用户id,useronline 为用户的在线状态,因用户的数量无法确定,此处采用可变长数组的定义方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--为了在存储过程中可以使用自定义的数组类型,此处使用程序包的方式
create or replace package useronline_pkg
as
type int_array is table of number(10)
index by binary_integer;
procedure batch_updateuseronline
(v_userid in int_array, v_onlines in int_array);
end useronline_pkg;
/
--声明程序包体
create or replace package body useronline_pkg
as
procedure batch_updateuseronline(
v_userid in int_array,
v_onlines in int_array
)
as
user_id number(10);
user_online number(1);
v_index number(8);
begin
for v_index in 1..v_userid.count
loop
user_id := v_userid(v_index);
user_online := v_onlines(v_index);
--若用户的在线状态的记录已经存在,则只更新其在线状态;否则,则插入一条记录
merge into useronline tb
using (select count(userid) as cnt
from useronline where userid=user_id) tb_tmp
on(tb_tmp.cnt <> 0)
when matched then
update set
tb.onlines=user_online,
tb.updatetime=sysdate
where userid=user_id
when not matched then
insert values(user_id, user_online, sysdate);
commit;
end loop;
end batch_updateuseronline;
end useronline_pkg;
/