Kingbase Create Type 语句使用
Kingbase create type类型创建语句
说明:
KingbaseES 数据库中,可以通过CREATE TYPE语句定义一种新的数据类型。
有八种形式的CREATE TYPE。它们分别创建组合类型、枚举类型、 范围类型、基础类型、shell 类型、嵌套表类型、可变数组类型和对象类型。下文将依次讨论这些形式。
1.组合类型:
组合类型本质上和表的行类型相同,但是如果只想定义一种类型,使用 CREATE TYPE避免了创建一个实际的表。
语法:
CREATE TYPE name AS
( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
示例:
create type comb_type as (c1 int , c2 varchar2(20) );
create table combtable (c1 int , c2 varchar2(20) , c3 varchar2(20));
insert into 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 ;
end
create or replace FUNCTION getcomb() RETURNS SETOF comb_type AS $$
select c1 , c2 FROM combtable
$$ LANGUAGE SQL;
select * from getcomb();
2.枚举类型:
枚举类型需要一个带引号的标签构成的列表,每一个标签长度必须不超过 NAMEDATALEN字节(在标准的 KingbaseES编译中是 64 字节)。
语法:
CREATE TYPE name AS ENUM
( [ 'label' [, ... ] ] )
示例:
CREATE TYPE enumtype AS ENUM ('first', 'second', 'third');
CREATE TABLE enumtable (
id serial,
status enumtype
);
insert into enumtable(status) values('first') ,('second');
select * from enumtable;
3.范围类型:
语法:
CREATE TYPE name AS RANGE (
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 AS RANGE (subtype = float8, subtype_diff = float8mi);
create table rangetable (id serial , num float8range);
insert into rangetable(num) values('(1.12,3.45)') , ('(1.12,3.45)');
select * from rangetable;
4.基础类型:
语法:
CREATE TYPE name (
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 TYPE name
6.嵌套表类型:
语法:
CREATE TYPE name AS TABLE OF datatype [NOT NULL]
示例:
CREATE TYPE int_table AS TABLE OF INT NOT NULL;
declare
inttab int_table;
i INT;
begin
inttab := int_table(1,2,3,4,5);
i := inttab.first();
WHILE i IS NOT NULL LOOP
RAISE NOTICE 'inttab(%) is %', i, inttab(i);
i := inttab.next(i);
END LOOP;
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 ] TYPE name [ FORCE ] [ AUTHID { CURRENT_USER | DEFINER } ]
{ AS | IS } OBJECT (
attribute_name data_type [, ... ]
[, subprogram_spec [, ... ] ]
[, constructor_spec [, ... ] ]
)
这里 subprogram_spec 是以下之一:
{ STATIC | MEMBER } FUNCTION function_name
[ ( [ argname [ argmode ] argtype [ { DEFAULT | := } default_expr ] [, ...] ] ) ]
{ RETURNS | RETURN } rettype ]
{ STATIC | MEMBER } PROCEDURE procedure_name
[ ( [ argname [ argmode ] argtype [ { DEFAULT | := } default_expr ] [, ...] ] ) ]
constructor_spec 是:
CONSTRUCTOR FUNCTION name
[ ( [ SELF IN OUT name ] [, argname [ argmode ] argtype [ { DEFAULT | := } default_expr ] [, ...] ] ) ]
{ RETURNS | RETURN } SELF AS RESULT
示例:
CREATE OR REPLACE TYPE objtype FORCE AUTHID CURRENT_USER AS OBJECT(
f1 int,
f2 varchar2(10),
MEMBER FUNCTION memfunc(i int) RETURN INT,
STATIC PROCEDURE staproc,
CONSTRUCTOR FUNCTION objtype(self in out objtype) RETURN SELF AS RESULT
);
CREATE OR REPLACE TYPE BODY objtype AS
MEMBER FUNCTION memfunc(i int) RETURN INT AS
BEGIN
RAISE NOTICE 'self.f1 is %', self.f1;
RAISE NOTICE 'self.f2 is %', f2;
RETURN i;
END;
STATIC PROCEDURE staproc AS
BEGIN
RAISE NOTICE 'this is a static procedure in object type';
END;
CONSTRUCTOR FUNCTION objtype(self in out objtype) RETURN SELF AS RESULT as
begin
f1 := 1;
self.f2 := 'a';
RETURN ;
END;
END;
CREATE TABLE tb1(i objtype);
INSERT INTO tb1 select * from objtype(2, 'b');
INSERT INTO tb1 select * from objtype();
SELECT * FROM tb1;
CALL objtype.staproc();
KINGBASE研究院