lightdb新特性--兼容oracle存储过程--包package支持嵌套表

  • Hanson69

    2022-10-13 13:28:14
  • 国内数据库
  • 原创

相关文章：

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

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



在前面的文章中介绍了lightdb新特性package和嵌套表，但是之前的版本并不支持在package中定义和使用嵌套表。

为了满足业务需求，在当前版本中支持了该特性，下面对该特性作简单的介绍。


package有包规范和包体两部分，使用包体之前必须已经定义好包规范，所以首先要可以在包规范中进行定义嵌套表。

嵌套表在使用之前首先要定义嵌套表类型，有了类型后才可以定义嵌套表变量。我们可以在包规范中定义嵌套表类型，

然后在包外引用嵌套表类型来定义变量；也可以在包规范中同时定义嵌套表变量，然后 包外引用嵌套表变量。

下面举例说明：


包中定义嵌套表类型和变量

CREATE OR REPLACE PACKAGE pkg_names IS
  TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

  names2 Roster;   -- nested table variable

  function getInfo return Roster;
  function getSum return int;
  function getFirst return varchar;
  function getPrivate return varchar;
  function execDelete return int;
END;

/


CREATE OR REPLACE PACKAGE BODY pkg_names IS
  TYPE Roster_p IS TABLE OF VARCHAR( 15);   -- nested table type

  names4 Roster_p;   -- nested table variable

  function getInfo return Roster as
  begin
    names2( 1) := 'P Perez';     -- add one element
    names2( 2) := 'D Caruso';   -- add one element
    names2( 3) := 'R Singh';     -- add one element

    raise info 'Current Values: %', names2;
    raise info 'first Values: %', names2.first;
    raise info 'last Values: %', names2.last;
    raise info 'the count: %', names2.count;

    return names2;
  end;

  -- get the sum of associative arrays
  function getSum return int as
  begin
    names2( 1) := 'P Perez';     -- add one element
    names2( 2) := 'D Caruso';   -- add one element
    names2( 3) := 'R Singh';     -- add one element
    return names2.count;
  end;

  -- get the first element of nested table
  function getFirst return varchar as
  begin
    names2( 1) := 'P Perez';     -- add one element
    names2( 2) := 'D Caruso';   -- add one element
    names2( 3) := 'R Singh';     -- add one element
    return names2.first;
  end;

  -- get the private nested table
  function getPrivate return varchar as
  begin
    names4( 1) := 'P Perez';     -- add one element
    names4( 2) := 'D Caruso';   -- add one element
    names4( 3) := 'R Singh';     -- add one element

    raise info 'Current Values: %', names4;
    raise info 'first Values: %', names4.first;
    raise info 'last Values: %', names4.last;
    raise info 'the count: %', names4.count;
    return names4.first;
  end;

  function execDelete return int as
  begin
    -- delete the public associative arrays
    names2( 1) := 'P Perez';     -- add one element
    names2( 2) := 'D Caruso';   -- add one element
    names2( 3) := 'R Singh';     -- add one element

    raise info 'before delete, the names2 is %', names2;
    names2.delete();
    raise info 'after delete, the names2 is %', names2;

    -- delete the private associative arrays
    names4( 1) := 'P Perez';     -- add one element
    names4( 2) := 'D Caruso';   -- add one element
    names4( 3) := 'R Singh';     -- add one element

    raise info 'before delete, the names4 is %', names4;
    names4.delete();
    raise info 'after delete, the names4 is %', names4;

    return names2.count;
  end;
end;

/


匿名块引用包中嵌套表类型

-- Anonymous block reference the nested table type in the package
DECLARE
  -- Reference nested table type in package
  names2 pkg_names.Roster;
begin
  -- init
  names2( 1) := 'P Perez';     -- add one element
  names2( 2) := 'D Caruso';   -- add one element
  names2( 3) := 'R Singh';     -- add one element

  -- first() , last(), count()
  raise info 'Current Values: %', names2;
  raise info 'first Values: %', names2.first;
  raise info 'last Values: %', names2.last;
  raise info 'the count: %', names2.count;

  names2.extend;
  raise info 'Current Values: %', names2;

  names2.EXTEND( 2);
  raise info 'Current Values: %', names2;
 
  --names2 := pkg_names.Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  --raise info 'Current Values: %', pkg_names.names2;
 
  names2( 3) := 'J Hamil';   -- Change value of one element
  raise info 'Current Values: %', names2;
 
  -- add one element, the elements between the valid element and the new element is NULL
  names2( 8) := 'A Jansen';
  raise info 'Current Values: %', names2;
 
  names2.delete;
  raise info 'Current Values: %', names2;
  raise info 'the count: %', names2.count;

end;
/
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 3
INFO:  the count: 3
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh", NULL}
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh", NULL, NULL, NULL}
INFO:  Current Values: { "P Perez", "D Caruso", "J Hamil", NULL, NULL, NULL}
INFO:  Current Values: { "P Perez", "D Caruso", "J Hamil", NULL, NULL, NULL, NULL, "A Jansen"}
INFO:  Current Values: {}

INFO:  the count: < NULL>


使用嵌套表方法

-- FOR with FIRST(), LAST()
DECLARE
  -- Reference nested table type in package
  names2 pkg_names.Roster;
  total integer;
begin
  -- init
  names2( 1) := 'P Perez';     -- add one element
  names2( 2) := 'D Caruso';   -- add one element
  names2( 3) := 'R Singh';     -- add one element

  total := names2.count;
  raise info 'Total % people', total;

  FOR i IN names2.first .. names2.last LOOP
      raise info 'ID: %, people: %', i, names2(i);
  END LOOP;

end;
/
INFO:  Total 3 people
INFO:  ID: 1, people: P Perez
INFO:  ID: 2, people: D Caruso

INFO:  ID: 3, people: R Singh


匿名块引用包中嵌套表变量

-- Anonymous block reference the nested table variable in the package
begin
  -- init
  pkg_names.names2( 1) := 'P Perez';     -- add one element
  pkg_names.names2( 2) := 'D Caruso';   -- add one element
  pkg_names.names2( 3) := 'R Singh';     -- add one element

  -- first() , last(), count()
  raise info 'Current Values: %', pkg_names.names2;
  raise info 'first Values: %', pkg_names.names2. first;
  raise info 'last Values: %', pkg_names.names2. last;
  raise info 'the count: %', pkg_names.names2. count;

  pkg_names.names2.extend;
  raise info 'Current Values: %', pkg_names.names2;

  pkg_names.names2.EXTEND( 2);
  raise info 'Current Values: %', pkg_names.names2;
 
  --names2 := pkg_names.Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  --raise info 'Current Values: %', pkg_names.names2;
 
  pkg_names.names2( 3) := 'J Hamil';   -- Change value of one element
  raise info 'Current Values: %', pkg_names.names2;
 
  -- add one element, the elements between the valid element and the new element is NULL
  pkg_names.names2( 8) := 'A Jansen';
  raise info 'Current Values: %', pkg_names.names2;
 
  pkg_names.names2. delete;
  raise info 'Current Values: %', pkg_names.names2;
  raise info 'the count: %', pkg_names.names2. count;

end;
/
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 3
INFO:  the count: 3
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh", NULL}
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh", NULL, NULL, NULL}
INFO:  Current Values: { "P Perez", "D Caruso", "J Hamil", NULL, NULL, NULL}
INFO:  Current Values: { "P Perez", "D Caruso", "J Hamil", NULL, NULL, NULL, NULL, "A Jansen"}
INFO:  Current Values: {}

INFO:  the count: < NULL>


和其它语法的结合使用

-- FOR with FIRST(), LAST()
DECLARE
  total int;
begin
  -- init
  pkg_names.names2( 1) := 'P Perez';     -- add one element
  pkg_names.names2( 2) := 'D Caruso';   -- add one element
  pkg_names.names2( 3) := 'R Singh';     -- add one element

  total := pkg_names.names2. count;
  raise info 'Total % people', total;

  FOR i IN pkg_names.names2. first .. pkg_names.names2. last LOOP
      raise info 'ID: %, people: %', i, pkg_names.names2(i);
  END LOOP;

end;
/
INFO:  Total 3 people
INFO:  ID: 1, people: P Perez
INFO:  ID: 2, people: D Caruso
INFO:  ID: 3, people: R Singh
-- function returns a TABLEOF and operates on the return value
DECLARE
  names_l pkg_names.Roster;
begin
  names_l = pkg_names.getInfo();

  names_l.extend();
  raise info 'Current Values: %', names_l;
  raise info 'first Values: %', names_l.first;
  raise info 'last Values: %', names_l.last;
  raise info 'the count: %', names_l.count;
end;
/
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 3
INFO:  the count: 3
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh", NULL}
INFO:   first Values: 1
INFO:   last Values: 4

INFO:  the count: 4


包中方法调用嵌套表

-- function reference the nested table in the package
select pkg_names.getInfo();
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 3
INFO:  the count: 3
             getinfo              
----------------------------------
 { "P Perez", "D Caruso", "R Singh"}
( 1 row)

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

select pkg_names.getFirst();
 getfirst
----------
  1
( 1 row)

select pkg_names.getPrivate();
INFO:  Current Values: { "P Perez", "D Caruso", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 3
INFO:  the count: 3
 getprivate
------------
  1
( 1 row)

select pkg_names.execDelete();
INFO:   before delete, the names2 is { "P Perez", "D Caruso", "R Singh"}
INFO:   after delete, the names2 is {}
INFO:   before delete, the names4 is { "P Perez", "D Caruso", "R Singh"}
INFO:   after delete, the names4 is {}
 execdelete
------------
           
( 1 row)


rowid作为嵌套表元素类型

-- test the 'table of rowid' in package
CREATE TABLE people_source (
  person_id   INTEGER NOT NULL PRIMARY KEY,
  first_name VARCHAR( 20) NOT NULL,
  last_name   VARCHAR( 20) NOT NULL,
  title       VARCHAR( 10) NOT NULL
);
INSERT INTO people_source VALUES ( 1, 'John', 'Smith', 'Mr');
INSERT INTO people_source VALUES ( 2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES ( 3, 'Jane', 'Doe', 'Miss');

INSERT INTO people_source VALUES ( 4, 'Dave', 'Brown', 'Mr');


CREATE OR REPLACE PACKAGE name_pkg IS
  type type_rowid is table of rowid INDEX BY int4;

END;

/


和游标的结合使用

DECLARE
  type ref_cursor is ref cursor;
  cur ref_cursor;
  v_rowid name_pkg.type_rowid;
begin
  open cur for
      SELECT a.rowid from people_source a ;
      loop
        fetch cur bulk collect into v_rowid limit 10;

        for i in 1..v_rowid.count
        LOOP
          RAISE notice '% is %', i, v_rowid(i);
          delete from people_source where rowid=v_rowid(i);
        end loop;

        exit when cur%notfound;
      end loop;

  v_rowid.delete();
  CLOSE cur;
end;
/
NOTICE:   1 is ( 0, 1)
NOTICE:   2 is ( 0, 2)
NOTICE:   3 is ( 0, 3)
NOTICE:   4 is ( 0, 4)


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