3.2.1. 数据类型
统一SQL支持将oracle数据类型转换为其他数据库类型，转换后映射关系如下， 部分数据类型转换后存在差异，可以点击相应类型链接查看详情 。具体支持数据类型边界可参考 统一SQL使用边界规范
3.2.1.1. 数据类型转换映射表
序号 |
数据类型分类 |
Oracle 数据类型 |
LightDB-Oracle 数据类型 |
PostgreSQL 数据类型 |
TDSQL-Mysql 数据类型 |
TDSQL-Oracle 数据类型 |
OceanBase-MySQL 数据类型 |
OceanBase-Oracle 数据类型 |
GaussDB-Oracle 数据类型 |
DM 数据类型 |
|---|---|---|---|---|---|---|---|---|---|---|
1 |
数字 |
BINARY_FLOAT |
BINARY_FLOAT |
real |
FLOAT |
BINARY_FLOAT |
FLOAT |
BINARY_FLOAT |
real |
BINARY_FLOAT |
2 |
数字 |
BINARY_DOUBLE |
double precision |
double precision |
DOUBLE |
BINARY_DOUBLE |
DOUBLE |
BINARY_DOUBLE |
double precision |
BINARY_DOUBLE |
3 |
大对象 |
BFILE |
不支持 |
不支持 |
不支持 |
不支持 |
不支持 |
BFILE |
不支持 |
BFILE |
4 |
大对象 |
BLOB |
bytea |
bytea |
LONGBLOB |
BLOB |
LONGBLOB |
BLOB |
bytea |
BLOB |
5 |
字符 |
CHAR(n),CHARACTER(n) |
char(n),char(n) |
|||||||
6 |
大对象 |
CLOB |
CLOB |
text |
LONGTEXT |
LONGTEXT |
CLOB |
text |
CLOB |
|
7 |
时间&日期 |
DATE |
DATE |
timestamp(0) |
DATETIME |
DATE |
DATETIME |
DATE |
timestamp(0) |
DATE |
8 |
数字 |
DECIMAL(p,s),DEC(p,s) |
DECIMAL(p,s) |
DECIMAL(p,s),DEC(p,s) |
||||||
9 |
数字 |
DOUBLE PRECISION |
DOUBLE PRECISION |
double precision |
DOUBLE |
DOUBLE |
DOUBLE |
DOUBLE PRECISION |
double precision |
DOUBLE PRECISION |
10 |
数字 |
FLOAT(p) |
FLOAT(p) |
double precision |
FLOAT(p) |
FLOAT(p) |
FLOAT(p) |
FLOAT(p) |
double precision |
FLOAT(p) |
11 |
数字 |
INTEGER,INT |
BIGINT |
bigint,bigint |
BIGINT |
BIGINT |
BIGINT |
bigint,bigint |
bigint,bigint |
INTEGER,INT |
12 |
时间&日期 |
INTERVAL YEAR(p) TO MONTH |
INTERVAL YEAR TO MONTH |
interval year to month |
不支持 |
不支持 |
不支持 |
INTERVAL YEAR(p) TO MONTH |
interval year to month |
INTERVAL YEAR(p) TO MONTH |
13 |
时间&日期 |
INTERVAL DAY(p) TO SECOND(s) |
INTERVAL DAY TO SECOND(s) |
interval day to second(s) |
不支持 |
不支持 |
不支持 |
INTERVAL DAY(p) TO SECOND(s) |
interval day to second(s) |
INTERVAL DAY(p) TO SECOND(s) |
14 |
其他类型 |
LONG |
LONG |
text |
MEDIUMTEXT |
MEDIUMTEXT |
MEDIUMTEXT |
LONG |
text |
LONG |
15 |
其他类型 |
LONG RAW |
BYTEA |
bytea |
LONGBLOB |
LONGBLOB |
LONGBLOB |
LONG RAW |
bytea |
LONG RAW |
16 |
字符 |
NCHAR(n) |
NCHAR(n) |
char(n) |
NAHCR(n) |
NCHAR(n) |
NCHAR(n) |
NCHAR(n) |
char(n) |
NCHAR(n) |
17 |
字符 |
NCHAR VARYING(n) |
NCHAR VARYING(n) |
varchar(n) |
NVARCHAR(n) |
NVARCHAR(n) |
NVARCHAR(n) |
NCHAR VARYING(n) |
varchar(n) |
NCHAR VARYING(n) |
18 |
大对象 |
NCLOB |
CLOB |
text |
LONGTEXT |
LONGTEXT |
LONGTEXT |
NCLOB |
text |
NCLOB |
19 |
数字 |
NUMBER(p,s),NUMERIC(p,s) |
DECIMAL(p,s) |
NUMBER(p,s),NUMERIC(p,s) |
NUMBER(p,s),NUMERIC(p,s) |
|||||
20 |
字符 |
NVARCHAR2(n) |
NVARCHAR2(n) |
varchar(n) |
NVARCHAR(n) |
NVARCHAR(n) |
NVARCHAR2(n) |
NVARCHAR2(n) |
varchar(n) |
NVARCHAR(n) |
21 |
其他类型 |
RAW(n) |
RAW(n) |
bytea |
VARBINARY(n) |
VARBINARY(n) |
NVARCHAR(n) |
RAW(n) |
bytea |
RAW(n) |
22 |
其他类型 |
REAL |
REAL |
double precision |
REAL |
REAL |
REAL |
REAL |
double precision |
REAL |
23 |
其他类型 |
ROWID |
ROWID |
char(10) |
不支持 |
不支持 |
不支持 |
ROWID |
char(10) |
ROWID |
24 |
数字 |
SMALLINT |
BIGINT |
bigint |
BIGINT |
BIGINT |
BIGINT |
SMALLINT |
bigint |
SMALLINT |
25 |
时间&日期 |
TIMESTAMP(p) |
TIMESTAMP(p) |
timestamp(p) |
TIMESTAMP(p) |
TIMESTAMP(p) |
TIMESTAMP(p) |
TIMESTAMP(p) |
timestamp(p) |
TIMESTAMP(p) |
26 |
时间&日期 |
TIMESTAMP(p) WITH TIME ZONE |
TIMESTAMP(p) WITH TIME ZONE |
timestamp(p) with time zone |
不支持 |
TIMESTAMP(p) |
TIMESTAMP(p) |
TIMESTAMP(p) WITH TIME ZONE |
timestamp(p) with time zone |
TIMESTAMP(p) WITH TIME ZONE |
28 |
时间&日期 |
TIMESTAMP(p) WITH LOCAL TIME ZONE |
不支持 |
不支持 |
不支持 |
TIMESTAMP(p) WITH LOCAL TIME ZONE |
不支持 |
TIMESTAMP(p) WITH LOCAL TIME ZONE |
不支持 |
TIMESTAMP(p) WITH LOCAL TIME ZONE |
29 |
其他类型 |
UROWID(n) |
VARCHAR(n) |
varchar(n) |
不支持 |
不支持 |
UROWID(n) |
varchar(n) |
UROWID(n) |
|
30 |
字符 |
VARCHAR(n) |
VARCHAR(n) |
varchar(n) |
VARCHAR(n) |
VARCHAR(n) |
VARCHAR(n) |
varchar(n) |
VARCHAR(n) |
|
31 |
字符 |
VARCHAR2(n) |
VARCHAR2(n) |
varchar(n) |
VARCHAR(n) |
VARCHAR(n) |
varchar(n) |
|||
32 |
XML类型 |
XMLTYPE |
XMLTYPE |
xml |
不支持 |
不支持 |
不支持 |
XMLType |
xml |
XMLType |
3.2.1.2. 转换为 PostgreSQL
3.2.1.2.1. 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))
3.2.1.2.2. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后PostgreSQL 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))
3.2.1.2.3. DEC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后PostgreSQL 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))
3.2.1.2.4. NUMBER
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
number |
decimal |
number —– decimal |
||
number(*) |
decimal |
number(*) —– decimal |
||
number(*,0) |
decimal(38,0) |
number(*,0)—-decimal(38,0) |
||
number(*,s) |
s > 0 |
decimal(38,s) |
number(*,2)—-decimal(38,2) |
|
number(p) / number(p,0) |
p>0 |
空 或 0 |
decimal(p) |
number(4,0)—–decimal(4) |
number(p,s) |
p>0 |
s>0 |
decimal(p,s) |
number(10,2)—–decimal(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));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_number_t (c1 decimal,c2 decimal,c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))
注意
- 当
p、s是以下情况时，不支持：
p >= 39
p == 0
s > 127
p==0 && s == 0
s < 0
例如：
create table t(col NUMBER(39));create table t(col NUMBER(0));create table t(col NUMBER(1,128));create table t(col NUMBER(0,0));
3.2.1.2.5. NUMERIC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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_number_t(c1 NUMBER,c2 NUMBER(*),c3 NUMBER(*,0),c4 NUMBER(*,2),c5 NUMBER(4),c6 NUMBER(4,0),c7 NUMBER(10,2));
-- 转换后PostgreSQL SQL：
CREATE TABLE unisql_number_t (c1 decimal,c2 decimal,c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))
警告
- 在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|
-- PostgreSQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c decimal,cx decimal);
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|
-- PostgreSQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 decimal(38,0),c2 decimal(2),c3 decimal(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|
3.2.1.3. 转换为 LightDB-Oracle
3.2.1.3.1. 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))
3.2.1.3.2. 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))
3.2.1.3.3. 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))
注意
- 当
p、s是以下情况时，不支持： p >= 39
p == 0
s > 127
p==0 && s == 0
s < 0
例如：
create table t(col NUMBER(39));
create table t(col NUMBER(0));
create table t(col NUMBER(1,128));
create table t(col NUMBER(0,0));
3.2.1.3.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))
3.2.1.3.5. 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))
3.2.1.4. 转换为 TDSQL-Mysql
3.2.1.4.1. CHAR(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示指定的固定长度。 |
BYTE |
表示以字节为单位提供列的长度。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE));
CREATE TABLE unisql_char_char_test (col char(10 CHAR));
-- 转换后TDSQL-MySQL：
CREATE TABLE `unisql_char_byte_test` (`col` char(10))
CREATE TABLE `unisql_char_char_test` (`col` char(10))
3.2.1.4.2. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 TDSQL-MySQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-MySQL：
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))
3.2.1.4.3. DEC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 TDSQL-MySQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-MySQL：
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))
3.2.1.4.4. NUMBER
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 TDSQL-MySQL 数据类型 |
示例 |
|---|---|---|---|---|
number |
decimal |
number —– decimal |
||
number(*) |
decimal |
number(*) —– decimal |
||
number(*,0) |
decimal(38,0) |
number(*,0)—-decimal(38,0) |
||
number(*,s) |
s > 0 |
decimal(38,s) |
number(*,2)—-decimal(38,2) |
|
number(p) / number(p,0) |
p>0 |
空 或 0 |
decimal(p) |
number(4,0)—–decimal(4) |
number(p,s) |
p>0 |
s>0 |
decimal(p,s) |
number(10,2)—–decimal(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));
-- 转换后TDSQL-MySQL：
CREATE TABLE unisql_number_t (c1 decimal,c2 decimal,c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))
3.2.1.4.5. NUMERIC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 TDSQL-MySQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-MySQL：
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))
警告
- 在Oracle中，FLOAT数据类型支持参数的范围：
FLOAT(n): [1,126]
- TDSQL-MySQL中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
在使用上述数据类型时，请确保数据类型精度标度范围在支持范围内。
对于目标库数据类型无法处理的数据长度，在运行时会报错。
在处理数字类型数据时，源库和目标库会存在截取或四舍五入的情况，可能会导致精度的损失(参考备注示例内容)。
在TDSQL-MySQL中decimal默认可以处理的整数位为10位，对转换后是decimal且整数位超过10位时，在运行时会报错。推荐使用时根据需要指定精度和标度。
对于decimal/dec/numeric,decimal/dec/numeric(*),number/decimal/dec/numeric(,0),number/decimal/dec/numeric(,s)转换到目标库后默认精度(p=38)，对于超过38位的数据，在目标库执行将报错，此种情况下请谨慎使用。
备注
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|
-- TDSQL-MySQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c decimal,cx decimal);
-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,9999999995);
INSERT INTO unisql_number_t(id,c) values(1,9999999999);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,c) values(2,10000000000);
INSERT INTO unisql_number_t(id,cx) values(10,9999999995);
INSERT INTO unisql_number_t(id,cx) values(11,9999999999);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,cx) values(12,10000000000);
SELECT * FROM unisql_number_t;
id|c |cx |
--+----------+----------+
0|9999999995| |
1|9999999999| |
10| |9999999995|
11| |9999999999|
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|
-- TDSQL-MySQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 decimal(38,0),c2 decimal(2),c3 decimal(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|
3.2.1.4.6. VARCHAR2(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示存储的字节数或字符数的长度大小。 |
BYTE |
表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE));
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR));
-- 转换后TDSQL-MySQL：
CREATE TABLE `unisql_varchar2_byte_test` (`col` varchar(10))
CREATE TABLE `unisql_varchar2_char_test` (`col` varchar(10))
3.2.1.5. 转换为 TDSQL-Oracle
3.2.1.5.1. CHAR(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示指定的固定长度。0<size<=2000 |
BYTE |
表示以字节为单位提供列的长度。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数,当前BYTE、CHAR仅作为语法糖支持转换,非语义一致。
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_char_byte_test (col char(10 BYTE));
CREATE TABLE unisql_char_char_test (col char(10 CHAR));
-- 转换后TDSQL-Oracle模式 SQL:
CREATE TABLE unisql_char_byte_test (col char(10))
CREATE TABLE unisql_char_char_test (col char(10))
3.2.1.5.2. CHARACTER(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示指定的固定长度。0<size<=2000 |
BYTE |
表示以字节为单位提供列的长度。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数,当前BYTE、CHAR仅作为语法糖支持转换,非语义一致。
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_character_byte_test (col character(10 BYTE));
CREATE TABLE unisql_character_char_test (col character(10 CHAR));
-- 转换后TDSQL-Oracle模式 SQL:
CREATE TABLE unisql_character_byte_test (col character(10))
CREATE TABLE unisql_character_char_test (col character(10))
3.2.1.5.3. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-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))
3.2.1.5.4. DEC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-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))
3.2.1.5.5. NUMBER
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-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))
注意
- 当
p、s是以下情况时，不支持：
p >= 39
p == 0
s > 127
p==0 && s == 0
s < 0
例如：
create table t(col NUMBER(39));create table t(col NUMBER(0));create table t(col NUMBER(1,128));create table t(col NUMBER(0,0));
3.2.1.5.6. NUMERIC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 PostgreSQL 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后TDSQL-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))
3.2.1.5.7. VARCHAR2(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示存储的字节数或字符数的长度大小。32767 bytes if MAX_STRING_SIZE = EXTENDED;4000 bytes if MAX_STRING_SIZE = STANDARD,请指定大于0的整数。 |
BYTE |
表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数,当前BYTE、CHAR仅作为语法糖支持转换,非语义一致。
此外size的最大值和Oracle的配置相关,而目标库TDSQL-PostgreSQL(Oracle模式)可以兼容Oracle的最大值,此类型统一SQL转换时未做最大值的校验。
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE));
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR));
-- 转换后TDSQL-Oracle模式 SQL:
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10))
CREATE TABLE unisql_varchar2_char_test (col varchar2(10))
3.2.1.5.8. VARCHAR(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示存储的字节数或字符数的长度大小。 |
BYTE |
表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数,当前BYTE、CHAR仅作为语法糖支持转换,非语义一致。
此外size的最大值和Oracle的配置相关,而目标库TDSQL-PostgreSQL(Oracle模式)可以兼容Oracle的最大值,此类型统一SQL转换时未做最大值的校验。
示例
-- 转换前Oracle SQL:
CREATE TABLE unisql_varchar_byte_test (col varchar(10 BYTE));
CREATE TABLE unisql_varchar_char_test (col varchar(10 CHAR));
-- 转换后TDSQL-Oracle模式 SQL:
CREATE TABLE unisql_varchar_byte_test (col varchar(10))
CREATE TABLE unisql_varchar_char_test (col varchar(10))
3.2.1.6. 转换为 OceanBase-MySQL
3.2.1.6.1. 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));
-- 转换后OceanBase-MySQL：
CREATE TABLE `unisql_char_byte_test` (`col` char(10))
CREATE TABLE `unisql_char_char_test` (`col` char(10))
3.2.1.6.2. 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));
-- 转换后OceanBase-MySQL：
CREATE TABLE `unisql_varchar2_byte_test` (`col` varchar(10))
CREATE TABLE `unisql_varchar2_char_test` (`col` varchar(10))
3.2.1.7. 转换为 OceanBase-Oracle
3.2.1.7.1. 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));
-- 转换后OceanBase-Oracle SQL:
CREATE TABLE unisql_char_byte_test (col char(10 BYTE))
CREATE TABLE unisql_char_char_test (col char(10 CHAR))
3.2.1.7.2. 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));
-- 转换后OceanBase-Oracle SQL:
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE))
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR))
3.2.1.8. 转换为 GaussDB-Oracle
3.2.1.8.1. CHAR(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示指定的固定长度。 |
BYTE |
表示以字节为单位提供列的长度。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE));
CREATE TABLE unisql_char_char_test (col char(10 CHAR));
-- 转换后GaussDB-Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10))
CREATE TABLE unisql_char_char_test (col char(10))
3.2.1.8.2. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 GaussDB-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));
-- 转换后GaussDB-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))
3.2.1.8.3. DEC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 GaussDB-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));
-- 转换后GaussDB-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))
3.2.1.8.4. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 GaussDB-Oracle 数据类型 |
示例 |
|---|---|---|---|---|
number |
decimal |
number —– decimal |
||
number(*) |
decimal |
number(*) —– decimal |
||
number(*,0) |
decimal(38,0) |
number(*,0)—-decimal(38,0) |
||
number(*,s) |
s > 0 |
decimal(38,s) |
number(*,2)—-decimal(38,2) |
|
number(p) / number(p,0) |
p>0 |
空 或 0 |
decimal(p) |
number(4,0)—–decimal(4) |
number(p,s) |
p>0 |
s>0 |
decimal(p,s) |
number(10,2)—–decimal(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));
-- 转换后GaussDB-Oracle SQL：
CREATE TABLE unisql_number_t (c1 decimal,c2 decimal,c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))
注意
- 当
p、s是以下情况时，不支持： p >= 39
p == 0
s > 127
p==0 && s == 0
s < 0
例如：
create table t(col NUMBER(39));
create table t(col NUMBER(0));
create table t(col NUMBER(1,128));
create table t(col NUMBER(0,0));
3.2.1.8.5. NUMERIC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 GaussDB-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));
-- 转换后GaussDB-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))
3.2.1.8.6. VARCHAR2(size [BYTE | CHAR])
参数说明
参数 |
说明 |
|---|---|
size |
表示存储的字节数或字符数的长度大小。 |
BYTE |
表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。 |
CHAR |
表示以字符为单位提供列的长度。 |
警告
因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。
示例
-- 转换前Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE));
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR));
-- 转换后GaussDB-Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar(10))
CREATE TABLE unisql_varchar2_char_test (col varchar(10))
警告
- 在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的特性，转换到GaussDB-Oracle时都会被转换成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|
-- GaussDB-Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c decimal,cx decimal);
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|
-- GaussDB-Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 decimal(38,0),c2 decimal(2),c3 decimal(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|
3.2.1.9. 转换为 DM
3.2.1.9.1. 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));
-- 转换后达梦 SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE))
CREATE TABLE unisql_char_char_test (col char(10 CHAR))
3.2.1.9.2. DECIMAL
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 达梦 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后达梦 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))
3.2.1.9.3. DEC
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 达梦 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后达梦 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))
3.2.1.9.4. NUMBER
Oracle 数据类型 |
精度(p) |
标度(s) |
转换后 达梦 数据类型 |
示例 |
|---|---|---|---|---|
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));
-- 转换后达梦 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))
警告
- 在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位的数据，在目标库执行将报错，此种情况下请谨慎使用。
备注
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|
-- 达梦SQL
在达梦中，对于number，整数位超过40位将会报错。
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,9999999999999999999999999999999999999995);
INSERT INTO unisql_number_t(id,c) values(1,9999999999999999999999999999999999999999);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,c) values(2,10000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(10,9999999999999999999999999999999999999995);
INSERT INTO unisql_number_t(id,cx) values(11,9999999999999999999999999999999999999999);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,cx) values(12,10000000000000000000000000000000000000000);
-- 查询数据
SELECT * FROM unisql_number_t;
id|c |cx |
--+----------------------------------------+----------------------------------------+
0|9999999999999999999999999999999999999995| |
1|9999999999999999999999999999999999999999| |
10| |9999999999999999999999999999999999999995|
11| |9999999999999999999999999999999999999999|
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|
-- 达梦 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|
3.2.1.9.5. 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));
-- 转换后达梦 SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE))
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR))