知识准备
-定长数组
--定长数字型数组,长度为10
type identifer is varray(10) of number;
如:
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;
/
-变长数组
--可变长数字型数组,数组中元素长度为10,角标索引为integer自动增长
type identifer is table of number(10) index by binary_integer;
如:
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 为用户的在线状态,因用户的数量无法确定,此处采用可变长数组的定义方式:
--为了在存储过程中可以使用自定义的数组类型,此处使用程序包的方式
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;
/
这里是我的处理方法,如有更好的方法,欢迎指教。