说明
KingbaseES 数据库中,可以通过CREATE TYPE语句定义一种新的数据类型。
有八种形式的CREATE TYPE。它们分别创建组合类型、枚举类型、 范围类型、基础类型、shell 类型、嵌套表类型、可变数组类型和对象类型。下文将依次讨论这些形式。
1.组合类型
组合类型本质上和表的行类型相同,但是如果只想定义一种类型,使用 CREATE TYPE避免了创建一个实际的表。
语法:
CREATE TYPE name AS
( [ attribute_name data_type [ COLLATEcollation ] [, ... ] ] )
示例:
create type comb_type as (c1 int , c2 varchar2(20) );
createtable combtable (c1 int , c2 varchar2(20) , c3 varchar2(20));
insertinto combtable values(1,'zhangsan','beijing'),(2,'lisi','shanghai');
declare
comb comb_type;
begin
for i in (select c1 , c2 from combtable) loop
select i.c1 , i.c2 into comb from dual;
raise notice 'comb_type = %',comb;
end loop ;
endcreateor replace FUNCTION getcomb() RETURNS SETOF comb_type AS $$
select c1 , c2 FROM combtable
$$ LANGUAGESQL;
select*from getcomb();
2.枚举类型
枚举类型需要一个带引号的标签构成的列表,每一个标签长度必须不超过 NAMEDATALEN字节(在标准的 KingbaseES编译中是 64 字节)。
语法:
CREATE TYPEnameAS ENUM
( [ 'label' [, ... ] ] )
示例:
CREATE TYPE enumtype AS ENUM ('first', 'second', 'third');
CREATETABLE enumtable (
id serial,
status enumtype
);
insertinto enumtable(status) values('first') ,('second');
select*from enumtable;
3.范围类型
语法:
CREATE TYPE name ASRANGE (
SUBTYPE = subtype
[ , SUBTYPE_OPCLASS = subtype_operator_class ]
[ , COLLATION=collation ]
[ , CANONICAL = canonical_function ]
[ , SUBTYPE_DIFF = subtype_diff_function ]
)
Range类型的输入格式:
'(lower-bound,upper-bound)''(lower-bound,upper-bound]''[lower-bound,upper-bound)''[lower-bound,upper-bound]''empty'
其中,"(" 和 ")" 表示定义的范围不包括此元素,"[" 和 "]" 表示定义的范围包括此元素,'empty' 表示空,空表示范围内不包含任何东西。
示例:
CREATE TYPE float8range ASRANGE (subtype = float8, subtype_diff = float8mi);
createtable rangetable (id serial , num float8range);
insertinto rangetable(num) values('(1.12,3.45)') , ('(1.12,3.45)');
select*from rangetable;
4.基础类型
语法:
CREATETYPEname (
INPUT = input_function,
OUTPUT = output_function
[ , RECEIVE = receive_function ]
[ , SEND = send_function ]
[ , TYPMOD_IN = type_modifier_input_function ]
[ , TYPMOD_OUT = type_modifier_output_function ]
[ , ANALYZE = analyze_function ]
[ , INTERNALLENGTH = { internallength | VARIABLE } ]
[ , PASSEDBYVALUE ][ , ALIGNMENT = alignment ][ , STORAGE = storage ][ , LIKE = like_type ][ , CATEGORY = category ][ , PREFERRED = preferred ][ , DEFAULT = default ][ , ELEMENT = element ][ , DELIMITER = delimiter ][ , COLLATABLE = collatable ]
)
5.shell 类型
shell 类型仅仅是一种用于后面要定义的类型的占位符,通过发出一个不带除类型名之外其他参数的CREATE TYPE命令可以创建这种类型。
语法:
CREATE TYPEname
6.嵌套表类型
语法:
CREATE TYPE name ASTABLEOF datatype [NOTNULL]
示例:
CREATE TYPE int_table AS TABLE OF INT NOTNULL;
declare
inttab int_table;
i INT;
begin
inttab := int_table(1,2,3,4,5);
i := inttab.first();
WHILE i ISNOTNULLLOOP
RAISE NOTICE 'inttab(%) is %', i, inttab(i);
i := inttab.next(i);
ENDLOOP;
end
7.可变数组类型
可变数组的下标索引类型为 INT 并且从 1 开始。与嵌套表不同,可变数组在定义的时候需要指定最大元素个数,使用中不能超过该限制。
语法:
CREATE TYPE name AS VARRAY(size_limit) OF datatype [NOT NULL]
示例:
create type varray100 is varray (20) of int;
declare
var varray100 = varray100();
begin
var.extend();
RAISE NOTICE 'var lenght %', var.count();
var.extend(10);
RAISE NOTICE 'var lenght %', var.count();
var.delete;
var.extend();
var(1) := 7;
var.extend(19, 1);
RAISE NOTICE 'var lenght %', var.count();
RAISE NOTICE 'var first() = %', var.first();
RAISE NOTICE 'var last() = %', var.last();
end
8.对象类型
语法:
CREATE [ OR REPLACE ] TYPEname [ FORCE ] [ AUTHID { CURRENT_USER | DEFINER } ]
{ AS | IS }OBJECT (
attribute_name data_type [, ... ]
[, subprogram_spec [, ... ] ]
[, constructor_spec [, ... ] ]
)
这里 subprogram_spec 是以下之一:
{ STATIC | MEMBER }FUNCTIONfunction_name
[ ( [ argname [ argmode ] argtype [ { DEFAULT | := } default_expr ] [, ...] ] ) ]
{ RETURNS | RETURN }rettype ]
{ STATIC | MEMBER }PROCEDUREprocedure_name
[ ( [ argname [ argmode ] argtype [ { DEFAULT | := } default_expr ] [, ...] ] ) ]
constructor_spec 是:CONSTRUCTORFUNCTIONname
[ ( [ SELF INOUTname ] [, argname [ argmode ] argtype [ { DEFAULT | := } default_expr ] [, ...] ] ) ]
{ RETURNS | RETURN }SELFASRESULT
示例:
CREATEOR REPLACE TYPE objtype FORCE AUTHID CURRENT_USERAS OBJECT(
f1 int,
f2 varchar2(10),
MEMBERFUNCTION memfunc(i int) RETURNINT,
STATICPROCEDURE staproc,
CONSTRUCTOR FUNCTION objtype(self inout objtype) RETURN SELF ASRESULT
);
CREATEOR REPLACE TYPE BODY objtype ASMEMBERFUNCTION memfunc(i int) RETURNINTASBEGIN
RAISE NOTICE 'self.f1 is %', self.f1;
RAISE NOTICE 'self.f2 is %', f2;
RETURN i;
END;
STATICPROCEDURE staproc ASBEGIN
RAISE NOTICE 'this is a static procedure in object type';
END;
CONSTRUCTOR FUNCTION objtype(self inout objtype) RETURN SELF ASRESULTasbegin
f1 :=1;
self.f2 :='a';
RETURN ;
END;
END;
CREATETABLE tb1(i objtype);
INSERTINTO tb1 select*from objtype(2, 'b');
INSERTINTO tb1 select*from objtype();
SELECT*FROM tb1;
CALL objtype.staproc();