相关文章:
http://blog.itpub.net/69982913/viewspace-2918133/
http://blog.itpub.net/69982913/viewspace-2918161/
前面文章介绍了嵌套表可以定义在存储过程和package中,lightdb新版本也支持创建独立的嵌套表类型,即可以通过create type来创建用户自定义的嵌套表类型,然后作为一个普通的数据类型来使用。
关于嵌套表的特性请见前面的文章,这里介绍嵌套表作为独立的类型如何使用。
创建独立的嵌套表类型
CREATE TYPE Roster AS TABLE OF VARCHAR( 15); -- nested table type
-- Change value of nested table
DECLARE
-- nested table variable initialized :
names2 Roster;
BEGIN
names2(
1) :=
'
A Jansen';
names2(
2
) :=
'
B Gupta'
;
raise info
'Current Values: %', names2;
END;
/
INFO: Current
Values: {
"A Jansen",
"B Gupta"}
函数返回值为嵌套表类型
create
type class_type
as (
id
integer,
name
varchar,
addr
text
);
create
type
class
as
table of
class_type;
create
or
replace
function tableof_assignment()
return
class
as
a
class;
b
class;
begin
a(
1) = (
1,
'lisi',
'beijing');
b = a;
b(
2) = (
2,
'zahngwu',
'chengdu');
RAISE INFO
'a:%' , a;
return b;
end;
/
select tableof_assignment();
INFO: a:{
"(1,lisi,beijing)"}
tableof_assignment
--------------------------------------------
{
"(1,lisi,beijing)",
"(2,zahngwu,chengdu)"}
( 1 row)
支持%type
create
type class_type
as (
id
integer,
name varchar,
addr
text
);
create type class as table of class_type.name%type;
create or replace function tableof_cast()
return class as
a class;
begin
a(
1) = (
1,
'lisi',
'beijing');
return a;
end;
/
select tableof_cast();
tableof_cast
--------------------------------------------
{
"(1,lisi,beijing)"}
(
1
row)