lightdb新特性--兼容oracle存储过程--包package支持联合数组

  • Hanson69

    2022-08-24 19:31:25
  • 国内数据库
  • 原创

http://blog.itpub.net/69982913/viewspace-2908972/

http://blog.itpub.net/69982913/viewspace-2901312/

在前面的文章中介绍了lightdb新特性package和联合数组，但是之前的版本并不支持在package中定义和使用联合数组。为了满足业务需求，在当前版本中支持了该特性，下面对该特性作简单的介绍。


package有包规范和包体两部分，使用包体之前必须已经定义好包规范，所以首先要可以在包规范中进行定义联合数组。联合数组在使用之前首先要定义联合数组类型，有了类型后才可以定义联合数组变量。我们可以在包规范中定义联合数组类型，然后在包外引用联合数组类型来定义变量；也可以在包规范中同时定义联合数组变量，然后 包外引用联合数组变量。下面举例说明：



定义包规范

--

-- test associative array in package
--
create or replace package pkg_population is
  -- Associative array indexed by string:
  TYPE population IS TABLE OF NUMERIC   -- Associative array type
    INDEX BY VARCHAR( 64);             --  indexed by string

  city_population population;

  function getInfo return int;
  function getSum return int;
  function getFirst return varchar;
  function getPrivate return varchar;
  function execDelete return int;
end;

/


定义包体

-- package body reference the associative array type in the package
create or replace package body pkg_population is

  -- Associative array indexed by string:
  TYPE population_p IS TABLE OF NUMERIC   -- Associative array type
    INDEX BY VARCHAR( 64);             --  indexed by string

  city_population_p population_p;

  function getInfo return int as
  begin
    city_population( 'Smallville')  := 2000;
    city_population( 'Midland')     := 750000;
    city_population( 'Megalopolis') := 1000000;

    -- first(), last(), count()
    raise info 'the city_population is %', city_population;
    raise info 'the first of city_population is %', city_population.first;
    raise info 'the last of city_population is %', city_population.last;
    raise info 'the count of city_population is %', city_population.count;
    return 0;
  end;

  -- get the sum of associative arrays
  function getSum return int as
  begin
    city_population( 'Smallville')  := 2000;
    city_population( 'Midland')     := 750000;
    city_population( 'Megalopolis') := 1000000;
    return city_population.count;
  end;

  -- get the first element of associative arrays
  function getFirst return varchar as
  begin
    city_population( 'Smallville')  := 2000;
    city_population( 'Midland')     := 750000;
    city_population( 'Megalopolis') := 1000000;
    return city_population.first;
  end;

  -- delete the associative arrays
  function execDelete return int as
  begin
    -- delete the public associative arrays
    city_population( 'Smallville')  := 2000;
    city_population( 'Midland')     := 750000;
    city_population( 'Megalopolis') := 1000000;
    raise info 'before delete, the city_population is %', city_population;
    city_population.delete();
    raise info 'after delete, the city_population is %', city_population;

    -- delete the private associative arrays
    city_population_p( 'Smallville')  := 2000;
    city_population_p( 'Midland')     := 750000;
    city_population_p( 'Megalopolis') := 1000000;
    raise info 'before delete, the city_population_p is %', city_population_p;
    city_population_p.delete();
    raise info 'after delete, the city_population_p is %', city_population_p;

    return city_population.count;
  end;

  -- get the private associative arrays
  function getPrivate return varchar as
  begin
    city_population_p( 'Smallville')  := 2000;
    city_population_p( 'Midland')     := 750000;
    city_population_p( 'Megalopolis') := 1000000;

    raise info 'the city_population_p is %', city_population_p;
    raise info 'the first of city_population_p is %', city_population_p.first;
    raise info 'the last of city_population_p is %', city_population_p.last;
    raise info 'the count of city_population_p is %', city_population_p.count;
    return city_population_p.first;
  end;

end;

/


引用包中的联合数组类型

-- Anonymous block reference the associative array type in the package
DECLARE
  city_population pkg_population.population;
begin
  -- init
  city_population( 'Smallville')  := 2000;
  city_population( 'Midland')     := 750000;
  city_population( 'Megalopolis') := 1000000;

  -- first() , last(), count()
  raise info 'the city_population is %', city_population;
  raise info 'the first of city_population is %', city_population.first;
  raise info 'the last of city_population is %', city_population.last;
  raise info 'the count of city_population is %', city_population.count;

  -- add element
  city_population( 'Anmi')     := 3000;
  raise info 'the first of city_population is %', city_population.first;
  raise info 'the last of city_population is %', city_population.last;
  raise info 'Anmi is %', city_population( 'Anmi');

  -- Change value
  city_population( 'Smallville') := 2001;
  raise info 'the city_population is %', city_population;

  -- delete()
  city_population.delete;       -- delete Associative array
  raise info 'the city_population is %', city_population;
  raise info 'the count of city_population is %', city_population.count;

end;
/
INFO:  the city_population is { 2000, 750000, 1000000}
INFO:  the first of city_population is Megalopolis
INFO:  the last of city_population is Smallville
INFO:  the count of city_population is 3
INFO:  the first of city_population is Anmi
INFO:  the last of city_population is Smallville
INFO:  Anmi is 3000
INFO:  the city_population is { 2001, 750000, 1000000, 3000}
INFO:  the city_population is {}

INFO:  the count of city_population is 0


引用联合数组中的变量


-- Anonymous block reference the associative array variable in the package
begin
  -- init
  pkg_population.city_population( 'Smallville')  := 2000;
  pkg_population.city_population( 'Midland')     := 750000;
  pkg_population.city_population( 'Megalopolis') := 1000000;

  -- first() , last(), count()
  raise info 'the city_population is %', pkg_population.city_population;
  raise info 'the first of city_population is %', pkg_population.city_population. first;
  raise info 'the last of city_population is %', pkg_population.city_population. last;
  raise info 'the count of city_population is %', pkg_population.city_population. count;

  -- add element
  pkg_population.city_population( 'Anmi')     := 3000;
  raise info 'the first of city_population is %', pkg_population.city_population. first;
  raise info 'the last of city_population is %', pkg_population.city_population. last;
  raise info 'Anmi is %', pkg_population.city_population( 'Anmi');

  -- Change value
  pkg_population.city_population( 'Smallville') := 2001;
  raise info 'the city_population is %', pkg_population.city_population;

  -- delete()
  pkg_population.city_population. delete;       -- delete Associative array
  raise info 'the city_population is %', pkg_population.city_population;
  raise info 'the count of city_population is %', pkg_population.city_population. count;

end;
/
INFO:  the city_population is { 2000, 750000, 1000000}
INFO:  the first of city_population is Megalopolis
INFO:  the last of city_population is Smallville
INFO:  the count of city_population is 3
INFO:  the first of city_population is Anmi
INFO:  the last of city_population is Smallville
INFO:  Anmi is 3000
INFO:  the city_population is { 2001, 750000, 1000000, 3000}
INFO:  the city_population is {}

INFO:  the count of city_population is 0


调用包中函数，对联合数组进行操作

-- function reference the associative array type in the package
select pkg_population.getInfo();
INFO:  the city_population is { 2000, 750000, 1000000}
INFO:  the first of city_population is Megalopolis
INFO:  the last of city_population is Smallville
INFO:  the count of city_population is 3
 getinfo
---------
        0
( 1 row)

select pkg_population.getSum();
 getsum
--------
      3
( 1 row)

select pkg_population.getFirst();
  getfirst  
-------------
 Megalopolis
( 1 row)

select pkg_population.getPrivate();
INFO:  the city_population_p is { 2000, 750000, 1000000}
INFO:  the first of city_population_p is Megalopolis
INFO:  the last of city_population_p is Smallville
INFO:  the count of city_population_p is 3
 getprivate  
-------------
 Megalopolis
( 1 row)

select pkg_population.execDelete();
INFO:   before delete, the city_population is { 2000, 750000, 1000000}
INFO:   after delete, the city_population is {}
INFO:   before delete, the city_population_p is { 2000, 750000, 1000000}
INFO:   after delete, the city_population_p is {}
 execdelete
------------
          0
( 1 row)


请使用浏览器的分享功能分享到微信等