4.1. 数据类型

本节主要介绍统一SQL支持原生Oracle数据库中的SQL数据类型的对比信息

序号	数据类型分类	Oracle 数据类型	统一SQL	LightDB-Oracle 数据类型
1	数字	BINARY_FLOAT	支持	BINARY_FLOAT
2	数字	BINARY_DOUBLE	支持	BINARY_DOUBLE
3	大对象	BFILE	不支持
4	大对象	BLOB	支持	BLOB
5	字符	CHAR(n),CHARACTER(n)	支持	CHAR(n),CHARACTER(n)
6	大对象	CLOB	支持	CLOB
7	时间&日期	DATE	支持	DATE
8	数字	DECIMAL(p,s),DEC(p,s)	支持	参见下文
9	数字	DOUBLE PRECISION	支持	DOUBLE PRECISION
10	数字	FLOAT(p)	支持	FLOAT(p)
11	数字	INTEGER,INT	支持	BIGINT
12	时间&日期	INTERVAL YEAR(p) TO MONTH	支持	INTERVAL YEAR TO MONTH
13	时间&日期	INTERVAL DAY(p) TO SECOND(s)	支持	INTERVAL DAY TO SECOND(s)
14	其他类型	LONG	支持	LONG
15	其他类型	LONG RAW	支持	BYTEA
16	字符	NCHAR(n)	支持	NCHAR(n)
17	字符	NCHAR VARYING(n)	支持	NCHAR VARYING(n)
18	大对象	NCLOB	支持	CLOB
19	数字	NUMBER(p,s),NUMERIC(p,s)	支持	参见下文
20	字符	NVARCHAR2(n)	支持	NVARCHAR2(n)
21	其他类型	RAW(n)	支持	RAW(n)
22	其他类型	REAL	支持	REAL
23	其他类型	ROWID	支持	ROWID
24	数字	SMALLINT	支持	BIGINT
25	时间&日期	TIMESTAMP(p)	支持	TIMESTAMP(p)
26	时间&日期	TIMESTAMP(p) WITH TIME ZONE	支持	TIMESTAMP(p) WITH TIME ZONE
27	其他类型	UROWID(n)	支持	VARCHAR(n)
28	字符	VARCHAR(n)	支持	VARCHAR(n)
29	字符	VARCHAR2(n)	支持	VARCHAR2(n)
30	XML类型	XMLTYPE	支持	XMLTYPE

示例

-- 备注：下面例子中LONG类型的字段创建了两个，直接在oracle中执行会提示：SQL 错误 [1754] [42000]: ORA-01754: 表只能包含一个 LONG 类型的列
-- 所以如果是要在oracle中运行测试下面的测试语句，可以自行去掉一个LONG类型
-- 转换前Oracle SQL：
create table unisql_type_test(
  col0        BINARY_FLOAT,
  col1        BINARY_DOUBLE,
  col2        BLOB,
  col3        CHAR,
  col4        CHAR(10),
  col5        CHARACTER,
  col6        character(10),
  col7        CLOB,
  col8        DATE,
  col9        DECIMAL,
  col10 DECIMAL(10,2),
  col11       DEC,
  col12       DEC(10,2),
  col13 DOUBLE PRECISION,
  col14       FLOAT,
  col15       FLOAT(10),
  col16       INTEGER,
  col17       INT,
  col18       INTERVAL YEAR TO MONTH,
  col19       INTERVAL DAY TO SECOND,
  col20       LONG,
  col21       LONG RAW,
  col22       NCHAR(10),
  col23       NCHAR VARYING(10),
  col24       NCLOB,
  col25       NUMBER,
  col26       NUMBER(10,2),
  col27       NUMERIC,
  col28       NUMERIC(10,2),
  col29       NVARCHAR2(10),
  col30       RAW(10),
  col31       REAL,
  col32       ROWID,
  col33       SMALLINT,
  col34       TIMESTAMP,
  col35       TIMESTAMP(6),
  col36       TIMESTAMP(6) with TIME zone,
  col37       UROWID(100),
  col38       VARCHAR(10),
  col39       VARCHAR2(10),
  col40       XMLTYPE
)

-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_type_test (col0 binary_float,
  col1 binary_double,
  col2 blob,
  col3 char,
  col4 char(10),
  col5 CHARACTER,
  col6 CHARACTER(10),
  col7 clob,
  col8 DATE,
  col9 decimal,
  col10 decimal(10,
  2),
  col11 DEC,
  col12 DEC(10,
  2),
  col13 double PRECISION,
  col14 float,
  col15 float(10),
  col16 bigint,
  col17 bigint,
  col18 INTERVAL YEAR TO MONTH,
  col19 INTERVAL DAY TO SECOND,
  col20 LONG,
  col21 bytea,
  col22 nchar(10),
  col23 nchar VARYING(10),
  col24 clob,
  col25 NUMBER,
  col26 NUMBER(10,
  2),
  col27 NUMERIC,
  col28 NUMERIC(10,
  2),
  col29 nvarchar2(10),
  col30 raw(10),
  col31 REAL,
  col32 rowid,
  col33 bigint,
  col34 timestamp,
  col35 timestamp(6),
  col36 timestamp(6) WITH time ZONE,
  col37 varchar(100),
  col38 varchar(10),
  col39 varchar2(10),
  col40 xmltype)

警告

在Oracle中，FLOAT数据类型支持参数的范围：

FLOAT(n): [1,126]

LightDB-Oracle中FLOAT数据类型支持范围：

FLOAT(n): [1,53]

所以统一SQL在转换时进行了参数校验，限定了FLOAT参数的范围应该在[1,53]之间。

在Oracle中，对于数据类型NUMBER，DECIMAL，DEC，NUMERIC的精度标度范围：

精度范围(p)：1~38
标度范围(s)：-84~127

统一SQL转换时支持的精度标度范围如下(且p>=s)：

精度范围(p)：1~38
标度范围(s)：0~38

在使用上述数据类型时，请确保数据类型精度标度范围在支持范围内。
对于目标库数据类型无法处理的数据长度，在运行时会报错。
在处理数字类型数据时，源库和目标库会存在截取或四舍五入的情况，可能会导致精度的损失(参考备注示例内容)
对于decimal/dec/numeric,decimal/dec/numeric(*),number/decimal/dec/numeric(,0),number/decimal/dec/numeric(,s)转换到目标库后默认精度(p=38)，对于超过38位的数据，在目标库执行将报错，此种情况下请谨慎使用。
在CREATE建表时，如果使用了GENERATED ALWAYS AS IDENTITY的特性，转换到PostgreSQL时都会被转换成bigint类型。

备注

1.在oracle中，对于以下类型语法，在处理数据时存在数字截取的情况,举例比较：
  1.1. number/decimal/dec/numeric
  1.2. number(*)/decimal(*)/dec(*)/numeric(*)
  1.3. number(*,0)/decimal(*,0)/dec(*,0)/numeric(*,0)
-- Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c NUMBER,cx NUMBER(*));
-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(1,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(2,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(3,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(4,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,c) values(5,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);

INSERT INTO unisql_number_t(id,cx) values(10,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(11,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(12,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(13,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(14,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,cx) values(15,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
--查询数据
SELECT * FROM unisql_number_t;
ID|C                                                                                                                             |CX                                                                                                                            |
--+------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
 0|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 1|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 2|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 3|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 4|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
10|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
11|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
12|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
13|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
14|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|

-- LightDB-Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c number,cx number);
SELECT * FROM unisql_number_t;

-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(1,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(2,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(3,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(4,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行不会报错
INSERT INTO unisql_number_t(id,c) values(5,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);


INSERT INTO unisql_number_t(id,cx) values(10,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(11,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(12,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(13,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(14,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行不会报错
INSERT INTO unisql_number_t(id,cx) values(15,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);



-- 查询数据
SELECT * FROM unisql_number_t;
id|c                                                                                                                             |cx                                                                                                                            |
--+------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
 0|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 1|999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 2|999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 3|999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 4|999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 5|999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
10|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
11|                                                                                                                              |999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
12|                                                                                                                              |999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
13|                                                                                                                              |999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
14|                                                                                                                              |999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
15|                                                                                                                              |999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000|


1. 插入数据时四舍五入的场景,举例说明：
-- Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 NUMBER(*,0),c2 NUMBER(2,0),c3 NUMBER(3,2));

INSERT INTO unisql_number_t(c1) values(1.11);
INSERT INTO unisql_number_t(c1) values(1.51);
INSERT INTO unisql_number_t(c2) values(1.11);
INSERT INTO unisql_number_t(c2) values(1.51);
INSERT INTO unisql_number_t(c3) values(1.111);
INSERT INTO unisql_number_t(c3) values(1.115);

SELECT * FROM unisql_number_t;
C1|C2|C3  |
--+--+----+
1|  |    |
2|  |    |
 | 1|    |
 | 2|    |
 |  |1.11|
 |  |1.12|

-- LightDB-Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 NUMBER(38,0),c2 NUMBER(2),c3 NUMBER(3,2));

INSERT INTO unisql_number_t(c1) values(1.11);
INSERT INTO unisql_number_t(c1) values(1.51);
INSERT INTO unisql_number_t(c2) values(1.11);
INSERT INTO unisql_number_t(c2) values(1.51);
INSERT INTO unisql_number_t(c3) values(1.111);
INSERT INTO unisql_number_t(c3) values(1.115);

SELECT * FROM unisql_number_t;
c1|c2|c3  |
--+--+----+
1|  |    |
2|  |    |
 | 1|    |
 | 2|    |
 |  |1.11|
 |  |1.12|

4.1.1. NUMBER

Oracle 数据类型	精度(p)	标度(s)	转换后 LightDB-Oracle 数据类型	示例
number			number	number —– number
number(*)			number	number(*) —– number
number(*,0)			number(38,0)	number(*,0)—-number(38,0)
number(*,s)		s > 0	number(38,s)	number(*,2)—-number(38,2)
number(p) / number(p,0)	p>0	空或 0	number(p)	number(4,0)—–number(4)
number(p,s)	p>0	s>0	number(p,s)	number(10,2)—–number(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_number_t(c1 NUMBER,c2 NUMBER(*),c3 NUMBER(*,0),c4 NUMBER(*,2),c5 NUMBER(4),c6 NUMBER(4,0),c7 NUMBER(10,2));

-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_number_t (c1 number,c2 number,c3 number(38,0),c4 number(38,2),c5 number(4),c6 number(4),c7 number(10,2))

4.1.2. DECIMAL

Oracle 数据类型	精度(p)	标度(s)	转换后 LightDB-Oracle 数据类型	示例
decimal			decimal(38,0)	decimal —– decimal(38,0)
decimal(*)			decimal(38,0)	decimal(*) —– decimal(38,0)
decimal(*,0)			decimal(38,0)	decimal(*,0)—-decimal(38,0)
decimal(*,s)		s > 0	decimal(38,s)	decimal(*,2)—-decimal(38,2)
decimal(p) / decimal(p,0)	p>0	空或 0	decimal(p)	decimal(4,0)—–decimal(4)
decimal(p,s)	p>0	s>0	decimal(p,s)	decimal(10,2)—–decimal(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_decimal_t(c1 decimal,c2 decimal(*),c3 decimal(*,0),c4 decimal(*,2),c5 decimal(4),c6 decimal(4,0),c7 decimal(10,2));

-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_decimal_t (c1 decimal(38,0),c2 decimal(38,0),c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))

4.1.3. DEC

Oracle 数据类型	精度(p)	标度(s)	转换后 LightDB-Oracle 数据类型	示例
dec			dec(38,0)	dec —– dec(38,0)
dec(*)			dec(38,0)	dec(*) —– dec(38,0)
dec(*,0)			dec(38,0)	dec(*,0)—-dec(38,0)
dec(*,s)		s > 0	dec(38,s)	dec(*,2)—-dec(38,2)
dec(p) / dec(p,0)	p>0	空或 0	dec(p)	dec(4,0)—–dec(4)
dec(p,s)	p>0	s>0	dec(p,s)	dec(10,2)—–dec(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_dec_t(c1 dec,c2 dec(*),c3 dec(*,0),c4 dec(*,2),c5 dec(4),c6 dec(4,0),c7 dec(10,2));

-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_dec_t (c1 dec(38,0),c2 dec(38,0),c3 dec(38,0),c4 dec(38,2),c5 dec(4),c6 dec(4),c7 dec(10,2))

4.1.4. NUMERIC

Oracle 数据类型	精度(p)	标度(s)	转换后 LightDB-Oracle 数据类型	示例
numeric			numeric(38,0)	numeric —– numeric(38,0)
numeric(*)			numeric(38,0)	numeric(*) —– numeric(38,0)
numeric(*,0)			numeric(38,0)	numeric(*,0)—-numeric(38,0)
numeric(*,s)		s > 0	numeric(38,s)	numeric(*,2)—-numeric(38,2)
numeric(p) / numeric(p,0)	p>0	空或 0	numeric(p)	numeric(4,0)—–numeric(4)
numeric(p,s)	p>0	s>0	numeric(p,s)	numeric(10,2)—–numeric(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_numeric_t(c1 numeric,c2 numeric(*),c3 numeric(*,0),c4 numeric(*,2),c5 numeric(4),c6 numeric(4,0),c7 numeric(10,2));

-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_numeric_t (c1 numeric(38,0),c2 numeric(38,0),c3 numeric(38,0),c4 numeric(38,2),c5 numeric(4),c6 numeric(4),c7 numeric(10,2))

4.1.5. CHAR(size [BYTE | CHAR])

参数说明

参数	说明
size	表示指定的固定长度。
BYTE	表示以字节为单位提供列的长度。
CHAR	表示以字符为单位提供列的长度。

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE));
CREATE TABLE unisql_char_char_test (col char(10 CHAR));

-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE))
CREATE TABLE unisql_char_char_test (col char(10 CHAR))

4.1.6. VARCHAR2(size [BYTE | CHAR])

参数说明

参数	说明
size	表示存储的字节数或字符数的长度大小。
BYTE	表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。
CHAR	表示以字符为单位提供列的长度。

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE));
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR));


-- 转换后LightDB-Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE))
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR))