【LightDB】oracle存储过程兼容之联合数组

联合数组

---

前言

联合数组是存储过程中常用的结构，又称索引表，是键值对的集合。LightDB 22.2支持支持在存储过程中使用联合数组。

一、联合数组申明和变量定义

联合数组元素类型目前支持基本类型（integer,varchar,numberic),表明%rowtype,列名%type。索引类型支持varchar和binary_integer,PLS_INTEGER。

代码如下（示例）：

  type type_a is table of char(1) index by binary_integer;
  type type_b is table of varchar2 index by binary_integer;
  type type_c is table of varchar2(30) index by varchar(20);
  v_a type_a;
  v_b type_b;
  v_c type_c;

二、操作联合数组

1.联合数组元素的引用与赋值

代码如下（示例）：

DECLARE
  TYPE population IS TABLE OF NUMERIC INDEX BY VARCHAR(64);
  city_population  population;
  v_a numeric;
  v_b numeric;
  v_c numeric;
BEGIN
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
  v_a := city_population('Smallville');
  v_b := city_population('Midland');
  v_c := city_population('Megalopolis');
  dbms_output.put_line('Midland is ' || v_a);
  dbms_output.put_line('Smallville is ' || v_b);
  dbms_output.put_line('Megalopolis is ' || v_c);
END;
/

2.联合数组元素的删除

这个版本只支持清空联合数组内容，不支持删除指定key
代码如下（示例）：

 DECLARE
  TYPE population IS TABLE OF NUMERIC INDEX BY VARCHAR(64);
  city_population  population;
  v_a numeric;
BEGIN
  -- Add elements (key-value pairs) to associative array:
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
  city_population.delete();
  v_a := city_population.count();
  dbms_output.put_line('v_a is ' ||  v_a);
END;
/

3.联合数组的属性

目前支持first，last,count属性。比较遗憾的直接调用函数或存储过程时，不支持联合数组属性传参(select和update等其它场景无此问题)。
代码如下（示例）：

DECLARE
  -- Associative array indexed by string:
  TYPE population IS TABLE OF NUMERIC  -- Associative array type
    INDEX BY VARCHAR(64);            --  indexed by string
  city_population  population;        -- Associative array variable
   outstr varchar(1024);
BEGIN
  -- Add elements (key-value pairs) to associative array:
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
  outstr  := 'Midland is ' ||  city_population.count;
  dbms_output.put_line(outstr);
  outstr  := 'Smallville is '|| city_population.first;
  dbms_output.put_line(outstr );
  outstr  := 'Megalopolis is ' ||  city_population.last;
  dbms_output.put_line(outstr);
END;
/

三、高级特性

1. bulk collect短句

利用BULK COLLECT短语可以将数据批量地从SQL引擎
传送给PL/SQL引擎，而不是每次传送一行数据。可以在下列
语句中使用BULK COLLECT短语。利用BULK COLLECT语句，每个处理语句返回一个完整
的结果集，一次性存储到一个或多个集合变量中，相比于利用
LOOP循环每次返回一条记录的操作方式，效率明显提高。

select into语句

代码如下（示例）：

create table test(a int);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);

declare
  type type_a is table of char(1) index by binary_integer;
  v_a type_a;
  outstr varchar(1024);
begin
    select  a bulk collect into v_a  from test;
    outstr  =  'v_a.count=' || v_a.count || ';' || 'v_a(1)=' || v_a(1) || ';' ;
    dbms_output.put_line(outstr);
end;
/

fetch into语句

代码如下（示例）：

create table test(a int);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);

declare
type c_cursor  is ref cursor;
type type_a is table of test%rowtype index by binary_integer;
c_test c_cursor;
v_a type_a;
 outstr varchar(1024);
begin
    open c_test for  select *  from test order by a;
    fetch c_test bulk collect into v_a limit 3;
    outstr  =  'v_a.count=' || v_a.count || ';' || 'v_a(1)=' || v_a(1) || ';' ;
    dbms_output.put_line(outstr);
end;
/

总结

整体而言，LightDB已支持了一些常见用法。