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;