异构关系数据库(Sqlserver与Oracle)之间的数据类型转换参考

时间:2021-12-21 22:00:25

一、Oracle到SqlServer的数据类型的转变

编号

Oracle

ToSqlServer

SqlServer

1

BINARY_DOUBLE

VARCHAR(100)

real

2

BINARY_FLOAT

VARCHAR(100)

float

3

BLOB

UNKNOWN

image

4

CHAR

VARCHAR(2)

nchar

5

DATE

UNKNOWN

date

6

INTERVAL_DAY_TO_SECOND

VARCHAR(100)

nvarchar

7

INTERVAL_YEAR_TO_MONTH

VARCHAR(100)

nvarchar

8

LONG

VARCHAR(100)

ntext

9

LONG RAW

VARCHAR(100)

image

10

NCLOB

TEXT

ntext

11

NUMBER

FLOAT(53)

Numeric

12

NVARCHAR2

VARCHAR(20)

nvarchar

13

RAW

VARCHAR(20)

varbinary

14

TIMESTAMP

UNKNOWN

timestamp

15

TIMESTAMP(6) WITH LOCAL TIME ZONE

VARCHAR(100)

datetime

16

TIMESTAMP(6) WITH TIME ZONE

VARCHAR(100)

datetime

17

CLOB

TEXT

TEXT

 二、SqlServer到Oracle的数据类型的转变

编号

SqlServer

ToOracle

Oracle

1

bigint

INTEGER

NUMBER

2

bit

CHAR(1)

NUMBER

3

char

VARCHAR2(10)

CHAR

4

date

VARCHAR2(10)

DATE

5

datetime2(7)

VARCHAR2(27)

TIMESTAMP

6

datetimeoffset(7)

VARCHAR2(34)

TIMESTAMP (7) WITH TIME ZONE

7

decimal(18, 0)

INTEGER

NUMBER

8

float

NUMBER

float

9

Int

INTEGER

NUMBER

10

nchar(10)

VARCHAR2(10)

NCHAR

11

ntext

CLOB

NCLOB

12

numeric(18, 0)

INTEGER

NUMBER

13

nvarchar(MAX)

CLOB

NCLOB

14

real

NUMBER

real

15

smallint

INTEGER

NUMBER

16

sql_variant

CLOB

NCLOB

17

xml

CLOB

NCLOB

18

uniqueidentifier

VARCHAR2(36)

CHAR

19

tinyint

INTEGER

NUMBER

20

smalldatetime

TIMESTAMP

DATE

21

smallint

INTEGER

NUMBER

22

Xml

CLOB

NCLOB

23

varchar(MAX)

CLOB

VARCHAR

24

geography

BLOB

BLOB

25

geometry

BLOB

BLOB

26

hierarchyid

BLOB

BLOB

27

binary(50)

BLOB

BLOB

28

Image

BLOB

BLOB

29

Int

INTEGER

INTEGER

30

money

NUMBER(19,4)

NUMBER(19,4)

31

nvarchar(50)

VARCHAR2(50)

VARCHAR2(50)

32

smallmoney

NUMBER(10, 4)

NUMBER(10, 4)

33

text

CLOB

CLOB

34

varbinary(50)

BLOB

BLOB

35

varbinary(MAX)

BLOB

BLOB

36

varchar(50)

VARCHAR

VARCHAR