lightdb新特性--兼容oracle存储过程--嵌套表

  • Hanson69

    2022-10-13 10:05:16
  • 国内数据库
  • 原创

相关文章

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


从概念上讲，嵌套表就像一个具有任意数量元素的一维数组。但是，嵌套表与数组在以下几个重要方面有所不同：

l   数组需要声明元素个数，但嵌套表不需要。嵌套表的大小可以动态增加。

l   数组总是稠密的。嵌套数组最初是密集的，但可能变得稀疏，因为可以从中删除元素。

 

嵌套表与联合数组类似（联合数组详细信息看之前的文章），不同之处在于：

l   嵌套表的索引值类型必须为数字类型，索引值为从1 开始的有序关键字，没有固定上限。

l   嵌套表除了可以在PL/SQL 匿名块、 包规范中定义外，还可以作为独立的数据库对象存储在数据库服务器中，是全局数据类型。

l   嵌套表必须使用构造器进行初始化后才可以使用。没有初始化的嵌套表被自动赋值为NULL 。

l   初始化时嵌套表中元素是连续存储的，元素索引值是连续的（密集集合）。 在后续操作过程中，随着部分元素被删除，元素可以不连续存储，元素之间可以存在间隙（稀疏集合）。

以下是联合数组和嵌套表的区别总结：

Collection Type

Number of Elements

Index Type

Dense or Sparse

Uninitialized    Status

Where Defined

Can Be Attribute    Data Type

Associative array (or index-by table)

Unspecified

String or  PLS_INTEGER

Either

Empty

In PL/SQL block or package

No

Nested table

Unspecified

Integer

Starts dense, can become sparse

Null

In PL/SQL block or package or at schema   level

Only if defined at schema level

 

 嵌套表类型定义和使用

-- Change value of nested table
DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;
    names2( 3) := 'P Perez';   -- Change value of one element

    raise info 'Current Values: %', names2;

    names2 := Roster( 'A Jansen', 'B Gupta');   -- Change entire table
    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:  Current Values: { "D Caruso", "J Hamil", "P Perez", "R Singh"}
INFO:  Current Values: { "A Jansen", "B Gupta"}


嵌套表FIRST(), LAST()方法

-- add elements and test FIRST(), LAST()

DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;
    raise info 'first Values: %', names2.first;
    raise info 'last Values: %', names2.last;

    names2( 5) := 'P Perez';   -- add one element

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

    -- add one element, the elements between the valid element and the new element is NULL
    names2( 7) := 'A Jansen';

    raise info 'Current Values: %', names2;
    raise info 'last Values: %', names2.last;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:   first Values: 1
INFO:   last Values: 4
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", "P Perez"}
INFO:   last Values: 5
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", "P Perez", NULL, "A Jansen"}

INFO:   last Values: 7


嵌套表EXTEND()方法

-- test EXTEND()
DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;

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

    names2.EXTEND();
    raise info 'Current Values: %', names2;

    names2.EXTEND( 2);
    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", NULL}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", NULL, NULL}
INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh", NULL, NULL, NULL, NULL}


嵌套表DELETE()方法

-- test DELETE()

DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    raise info 'Initial Values %', names2;

    names2.DELETE;

    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { "D Caruso", "J Hamil", "D Piro", "R Singh"}

INFO:  Current Values: {}


嵌套表默认初始化

-- test DEFAULT initialized
DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster default Roster( 'none'); -- default

BEGIN
    raise info 'Initial Values %', names2;

    names2 := Roster();
    raise info 'Current Values: %', names2;

    names2 := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    raise info 'Current Values: %', names2;
END;
/
INFO:  Initial Values { none}
INFO:  Current Values: {}

INFO:  Current Values: { "D Caruso", "J Hamil", "D Piro", "R Singh"}



存储过程其它语法结合使用

DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    total integer;
BEGIN
    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 4 people
INFO:  ID: 1, people: D Caruso
INFO:  ID: 2, people: J Hamil
INFO:  ID: 3, people: D Piro
INFO:  ID: 4, people: R Singh
DECLARE
    TYPE Roster IS TABLE OF VARCHAR( 15);   -- nested table type

    -- nested table variable initialized with constructor:
    names2 Roster := Roster( 'D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    i integer;
BEGIN
    FOR i IN 1.. 6 LOOP
        IF names2.EXISTS(i) THEN
            raise info 'names2(%) = %', i, names2(i);
        ELSE
            raise info 'names2(%) does not exist', i;
        END IF;
    END LOOP;

END;
/
INFO:  names2( 1) = D Caruso
INFO:  names2( 2) = J Hamil
INFO:  names2( 3) = D Piro
INFO:  names2( 4) = R Singh
INFO:  names2( 5) does not exist
INFO:  names2( 6) does not exist


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