HANA SQL Script学习(2): Data Type Extension
2 Data Type Extension
/*
2.Data Type Extension
--创建table数据类型
CREATE TYPE <type_name> AS TABLE (<column_list_definition>)
<column_list_definition> ::= <column_elem> [{, <column_elem>}...]
<column_elem> ::= <column_name> <data_type> [<column_store_data_type>] [<ddic_data_type>]
<column_name> ::= <identifier>
<data_type> ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL
| REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT | VARBINARY | BLOB | CLOB | NCLOB | TEXT
<column_store_data_type> ::= CS_ALPHANUM | CS_INT | CS_FIXED | CS_FLOAT | CS_DOUBLE | CS_DECIMAL_FLOAT | CS_FIXED(p-s, s)
| CS_SDFLOAT | CS_STRING | CS_UNITEDECFLOAT | CS_DATE | CS_TIME | CS_FIXEDSTRING | CS_RAW
| CS_DAYDATE | CS_SECONDTIME | CS_LONGDATE | CS_SECONDDATE
<ddic_data_type> ::= DDIC_ACCP | DDIC_ALNM | DDIC_CHAR | DDIC_CDAY | DDIC_CLNT | DDIC_CUKY | DDIC_CURR | DDIC_D16D
| DDIC_D34D | DDIC_D16R | DDIC_D34R | DDIC_D16S | DDIC_D34S | DDIC_DATS | DDIC_DAY | DDIC_DEC
| DDIC_FLTP | DDIC_GUID | DDIC_INT1 | DDIC_INT2 | DDIC_INT4 | DDIC_INT8 | DDIC_LANG | DDIC_LCHR
| DDIC_MIN | DDIC_MON | DDIC_LRAW | DDIC_NUMC | DDIC_PREC | DDIC_QUAN | DDIC_RAW | DDIC_RSTR
| DDIC_SEC | DDIC_SRST | DDIC_SSTR | DDIC_STRG | DDIC_STXT | DDIC_TIMS | DDIC_UNIT | DDIC_UTCM
| DDIC_UTCL | DDIC_UTCS | DDIC_TEXT | DDIC_VARC | DDIC_WEEK
*/
--示例 CREATE TYPE tt_publishers AS TABLE ( publisher INTEGER, name VARCHAR(50), price DECIMAL, cnt INTEGER); --删除table type类型
/* DROP TYPE <type_name> [<drop_option>] <type_name> ::= [<schema_name>.]<identifier> <drop_option> ::= CASCADE | RESTRICT */ --示例 DROP TYPE tt_publishers; --创建Row Type Variable DECLARE a ROW (a INT, b VARCHAR(16), c TIMESTAMP); DECLARE b ROW LIKE <persistent table name>; DECLARE c ROW LIKE :<other table/row/cursor variable name>; --注意: --Row type variables are not supported in scalar user-defined functions. --EXEC INTO is not supported. --You cannot pass row type variables as parameters of procedures or functions --示例,语法报错?? DO BEGIN DECLARE x, y ROW (a INT, b VARCHAR(16), c TIMESTAMP); x = ROW(1, 'a', '2000-01-01'); x.a = 2; y = :x; SELECT :y.a, :y.b, :y.c FROM DUMMY; -- Returns [2, 'a', '2000-01-01'] END DO BEGIN DECLARE CURSOR cur FOR SELECT 1 as a, 'a' as b, to_timestamp('2000-01-01') as c FROM DUMMY; DECLARE x ROW LIKE :cur; OPEN cur; FETCH cur INTO x; SELECT :x.a, :x.b, :x.c FROM DUMMY; -- Returns [1, 'a', '2000-01-01'] SELECT 2, 'b', '2000-02-02' INTO x FROM DUMMY; SELECT :x.a, :x.b, :x.c FROM DUMMY; -- Returns [2, 'b', '2000-02-02'] END;
本文来自博客园,作者:渔歌晚唱,转载请注明原文链接:https://www.cnblogs.com/tangToms/p/14110664.html