背景

在信創適配中，從Oracle遷移過來的存儲過程使用到可變數組。因此在LightDB-X 23.4版本中對現有的集合類型進行了增強，添加了可變數組類型。

集合類型

在LightDB-X 23.4版本開始plorasql支持的集合類型同Oracle一致，覆蓋了關聯數組，可變數組和嵌套表三種類型，支持的方法如下

• DELETE 從集合中刪除元素。
• EXTEND 將元素添加到集合的末尾。
• EXISTS 如果集合的指定元素存在，則返回TRUE。
• FIRST 返回集合中的第一個索引。
• LAST 返回集合中的最后一個索引。
• COUNT 返回集合中的元素數量。

可變數組

可變大小數組是一個數組，其元素數量可以從零（空）到聲明的最大大小不等，在這里超過也支持。

要訪問可變數組的元素，使用語法variable_name(index)。在數據庫中存儲和檢索可變數組時，其索引和元素順序將保持穩定。

隨著您添加或刪除元素，可變數組變量占用的內存量可以動態增加或減少。

目前只支持本地類型的可變數組，不支持全局可變數組。

可變數組示例

準備數據

select dbms_output.serveroutput(true);
create table user_info(id int, name varchar(30));
insert into user_info values( 11, 'zhangsan');
insert into user_info values( 21, 'lisi');
insert into user_info values( 31, 'wangwu');

匿名塊

declaretype id_array is varray(10) of INTEGER;ids id_array := id_array(11, 21);v_name varchar2(30);
beginif ids.exists(3) thendbms_output.put_line('ids(3) exists.');elsedbms_output.put_line('ids(3) not exists.');end if;ids.extend;ids(ids.count) := 31;if ids.exists(3) thendbms_output.put_line('ids(3) exists.');elsedbms_output.put_line('ids(3) not exists.');end if;dbms_output.put_line('count=' || ids.count);for i in ids.first .. ids.last loopselect name into v_name from user_info where id = ids(i);dbms_output.put_line('ids(' || i || ') name is ' || v_name);end loop;ids.delete;dbms_output.put_line('after is.delete, count=' || ids.count);
end;
/ids(3) not exists.
ids(3) exists.
count=3
ids(1) name is zhangsan
ids(2) name is lisi
ids(3) name is wangwu
after is.delete, count=0
DO

函數

create or replace function f_varray() return int istype id_array is varray(10) of INTEGER;ids id_array := id_array(11, 21);v_name varchar2(30);
beginif ids.exists(3) thendbms_output.put_line('ids(3) exists.');elsedbms_output.put_line('ids(3) not exists.');end if;ids.extend;ids(ids.count) := 31;if ids.exists(3) thendbms_output.put_line('ids(3) exists.');elsedbms_output.put_line('ids(3) not exists.');end if;dbms_output.put_line('count=' || ids.count);for i in ids.first .. ids.last loopselect name into v_name from user_info where id = ids(i);dbms_output.put_line('ids(' || i || ') name is ' || v_name);end loop;ids.delete;dbms_output.put_line('after is.delete, count=' || ids.count);return ids.count;
end;
/select f_varray();ids(3) not exists.
ids(3) exists.
count=3
ids(1) name is zhangsan
ids(2) name is lisi
ids(3) name is wangwu
after is.delete, count=0f_varray 
----------0
(1 row)

存儲過程

create or replace procedure p_varray() istype id_array is varray(10) of INTEGER;ids id_array := id_array(11, 21);v_name varchar2(30);
beginif ids.exists(3) thendbms_output.put_line('ids(3) exists.');elsedbms_output.put_line('ids(3) not exists.');end if;ids.extend;ids(ids.count) := 31;if ids.exists(3) thendbms_output.put_line('ids(3) exists.');elsedbms_output.put_line('ids(3) not exists.');end if;dbms_output.put_line('count=' || ids.count);for i in ids.first .. ids.last loopselect name into v_name from user_info where id = ids(i);dbms_output.put_line('ids(' || i || ') name is ' || v_name);end loop;ids.delete;dbms_output.put_line('after is.delete, count=' || ids.count);
end;
/call p_varray();ids(3) not exists.
ids(3) exists.
count=3
ids(1) name is zhangsan
ids(2) name is lisi
ids(3) name is wangwu
after is.delete, count=0
CALL