3.2.2.7. 转换为 OceanBase-Oracle

3.2.2.7.1. 转换函数

3.2.2.7.1.1. CAST

语法
CAST (expr AS type_name )
描述
该函数用于将一种内置数据类型转换为另一种内置数据类型

参数解释

参数

说明

expr

列名或者表达式。

AS

用于分隔两个参数,在 AS 之前的是要处理的数据,在 AS 之后是要转换的数据类型。

type_name

数据库的内建数据类型名称。

下表显示了哪些数据类型可以转换为其他内置数据类型

from BINARY_FLOAT / BINARY_DOUBLE

from CHAR / VARCHAR2

from NUMBER

from DATETIME/INTERVAL

from RAW

from NCHAR/ NVARCHAR2

to BINARY_FLOAT/ BINARY_DOUBLE

yes

yes

yes

no

no

yes

to CHAR/ VARCHAR2

yes

yes

yes

yes

yes

no

to NUMBER

yes

yes

yes

no

no

yes

to DATETIME/INTERVAL

no

yes

no

yes

no

no

to RAW

yes

yes

yes

no

yes

no

to NCHAR/NVARCHAR2

yes

no

yes

yes

yes

yes

警告

使用 CAST 函数转换数据类型时,需要注意以下内容:

  • 对于以上列表中的转换,如果目标库对转换后的目标数据类型不支持,则会在目标库中执行时报错。

  • 注意在转换成RAW类型时,Oracle和oceanbase-oracle-Oracle的实现有一定的差异,会出现执行结果不一致的情况。

  • 对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字,转换后的SQL在目标库执行会存在报错或者精度错误的情况。

示例

-- 转换前Oracle SQL:
select
cast(123 as BINARY_FLOAT),
cast(123 as BINARY_DOUBLE),
cast(1 as CHAR),
cast(123 as VARCHAR2(10)),
cast('123' as NUMBER),
cast('2023-12-20 10:07:55.222' as TIMESTAMP),
cast('2-6' AS INTERVAL YEAR TO MONTH),
cast('1 10:22:22' AS INTERVAL DAY TO SECOND),
cast('313233' AS RAW(10)),
cast(123 as NCHAR(3)),
cast(123 as NVARCHAR2(3))
from dual;
CAST(123ASBINARY_FLOAT)|CAST(123ASBINARY_DOUBLE)|CAST(1ASCHAR)|CAST(123ASVARCHAR2(10))|CAST('123'ASNUMBER)|CAST('2023-12-2010:07:55.222'ASTIMESTAMP)|CAST('2-6'ASINTERVALYEARTOMONTH)|CAST('110:22:22'ASINTERVALDAYTOSECOND)|CAST('313233'ASRAW(10))|CAST(123ASNCHAR(3))|CAST(123ASNVARCHAR2(3))|
-----------------------+------------------------+-------------+-----------------------+-------------------+-----------------------------------------+--------------------------------+--------------------------------------+-----------------------+-------------------+-----------------------+
                  123.0|                   123.0|1            |123                    |                123|                  2023-12-20 10:07:55.222|2-6                             |1 10:22:22.0                          |123                    |123                |123                    |


-- 转换后oceanbase-oracle-Oracle SQL:
SELECT CAST(123 AS binary_float),CAST(123 AS binary_double),CAST(1 AS char),CAST(123 AS varchar2(10)),CAST('123' AS number),CAST('2023-12-20 10:07:55.222' AS timestamp),CAST('2-6' AS interval year to month),CAST('1 10:22:22' AS interval day to second(6)),CAST('313233' AS raw(10)),CAST(123 AS nchar(3)),CAST(123 AS nvarchar2(3)) FROM dual
binary_float|binary_double|bpchar|varchar2|numeric|timestamp              |interval                                     |interval                                        |raw   |bpchar|nvarchar2|
------------+-------------+------+--------+-------+-----------------------+---------------------------------------------+------------------------------------------------+------+------+---------+
       123.0|        123.0|1     |123     |    123|2023-12-20 10:07:55.222|2 years 6 mons 0 days 0 hours 0 mins 0.0 secs|0 years 0 mons 1 days 10 hours 22 mins 22.0 secs|313233|123   |123      |


-- 转换前Oracle SQL:
SELECT
     CAST('100.2345' AS NUMBER) AS to_nu,
     CAST('100.2345' AS NUMBER(*)) AS to_nu,
     CAST('100.2345' AS NUMBER(*,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(*,2)) AS to_nu,
     CAST('100.2345' AS NUMBER(4)) AS to_nu,
     CAST('100.2345' AS NUMBER(4,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(9)) AS to_nu,
     CAST('100.2345' AS NUMBER(9,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(18)) AS to_nu,
     CAST('100.2345' AS NUMBER(18,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(38)) AS to_nu,
     CAST('100.2345' AS NUMBER(38,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(10,2)) AS to_nu,
     cast('100.2345' AS decimal) AS to_dec,
     cast('100.2345' AS decimal(*)) AS to_dec,
     cast('100.2345' AS decimal(*,0)) AS to_dec,
     cast('100.2345' AS decimal(*,2)) AS to_dec,
     cast('100.2345' AS decimal(4)) AS to_dec,
     cast('100.2345' AS decimal(4,0)) AS to_dec,
     cast('100.2345' AS decimal(9)) AS to_dec,
     cast('100.2345' AS decimal(9,0)) AS to_dec,
     CAST('100.2345' AS decimal(18)) AS to_dec,
     CAST('100.2345' AS decimal(18,0)) AS to_dec,
     CAST('100.2345' AS decimal(38)) AS to_dec,
     CAST('100.2345' AS decimal(38,0)) AS to_dec,
     CAST('100.2345' AS decimal(10,2)) AS to_dec,
     cast('100.2345' AS dec) AS to_dec,
     cast('100.2345' AS dec(*)) AS to_dec,
     cast('100.2345' AS dec(*,0)) AS to_dec,
     cast('100.2345' AS dec(*,2)) AS to_dec,
     cast('100.2345' AS dec(4)) AS to_dec,
     cast('100.2345' AS dec(4,0)) AS to_dec,
     cast('100.2345' AS dec(9)) AS to_dec,
     cast('100.2345' AS dec(9,0)) AS to_dec,
     CAST('100.2345' AS dec(18)) AS to_dec,
     CAST('100.2345' AS dec(18,0)) AS to_dec,
     CAST('100.2345' AS dec(38)) AS to_dec,
     CAST('100.2345' AS dec(38,0)) AS to_dec,
     CAST('100.2345' AS dec(10,2)) AS to_dec,
     cast('100.2345' AS numeric) AS to_numr,
     cast('100.2345' AS numeric(*)) AS to_numr,
     cast('100.2345' AS numeric(*,0)) AS to_numr,
     cast('100.2345' AS numeric(*,2)) AS to_numr,
     cast('100.2345' AS numeric(4)) AS to_numr,
     cast('100.2345' AS numeric(4,0)) AS to_numr,
     cast('100.2345' AS numeric(9)) AS to_numr,
     cast('100.2345' AS numeric(9,0)) AS to_numr,
     CAST('100.2345' AS numeric(18)) AS to_numr,
     CAST('100.2345' AS numeric(18,0)) AS to_numr,
     CAST('100.2345' AS numeric(38)) AS to_numr,
     CAST('100.2345' AS numeric(38,0)) AS to_numr,
     CAST('100.2345' AS numeric(10,2)) AS to_numr
FROM dual
TO_NU   |TO_NU   |TO_NU|TO_NU |TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU |TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345|  100|100.23|  100|  100|  100|  100|  100|  100|  100|  100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|    100|    100|    100| 100.23|    100|    100|    100|    100|    100|    100|    100|    100| 100.23|

-- 转换后oceanbase-oracle-Oracle SQL:
SELECT CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number(38,0)) AS to_nu,CAST('100.2345' AS number(38,2)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(10,2)) AS to_nu,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,2)) AS to_dec,CAST('100.2345' AS decimal(4)) AS to_dec,CAST('100.2345' AS decimal(4)) AS to_dec,CAST('100.2345' AS decimal(9)) AS to_dec,CAST('100.2345' AS decimal(9)) AS to_dec,CAST('100.2345' AS decimal(18)) AS to_dec,CAST('100.2345' AS decimal(18)) AS to_dec,CAST('100.2345' AS decimal(38)) AS to_dec,CAST('100.2345' AS decimal(38)) AS to_dec,CAST('100.2345' AS decimal(10,2)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,2)) AS to_dec,CAST('100.2345' AS dec(4)) AS to_dec,CAST('100.2345' AS dec(4)) AS to_dec,CAST('100.2345' AS dec(9)) AS to_dec,CAST('100.2345' AS dec(9)) AS to_dec,CAST('100.2345' AS dec(18)) AS to_dec,CAST('100.2345' AS dec(18)) AS to_dec,CAST('100.2345' AS dec(38)) AS to_dec,CAST('100.2345' AS dec(38)) AS to_dec,CAST('100.2345' AS dec(10,2)) AS to_dec,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,2)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(10,2)) AS to_numr FROM dual
to_nu   |to_nu   |to_nu|to_nu |to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu |to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345|  100|100.23|  100|  100|  100|  100|  100|  100|  100|  100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|    100|    100|    100| 100.23|    100|    100|    100|    100|    100|    100|    100|    100| 100.23|

3.2.2.7.1.2. TO_CLOB

语法
TO_CLOB(lob_column | char)
描述
该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。

参数解释

参数

说明

lob_column

属于 LOB 列或其他字符串中的 NCLOB 值。

char

属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。

示例

CREATE TABLE unisql_test_clob (c1 clob,c2 varchar2(10));
INSERT INTO unisql_test_clob VALUES (TO_CLOB('1'),'orale');

-- 转换前Oracle SQL:
SELECT TO_CLOB(c1),TO_CLOB(c2),TO_CLOB(10)  FROM unisql_test_clob;
TO_CLOB(C1)|TO_CLOB(C2)|TO_CLOB(10)|
-----------+-----------+-----------+
1          |orale      |10         |


-- 转换后oceanbase-oracle-Oracle SQL:
SELECT CAST(c1 AS text),CAST(c2 AS text),CAST(10 AS text) FROM unisql_test_clob
c1|c2   |text|
--+-----+----+
1 |orale|10  |

3.2.2.7.1.3. TO_NCHAR (character)

语法
TO_NCHAR(character)
描述
该函数将 CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据转换为国家字符集,返回 NVARCHAR2 数据类型。

参数解释

参数

说明

character

CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据

示例

CREATE TABLE unisql_test_nchar(col1 INT,col2 VARCHAR2(20));
INSERT INTO unisql_test_nchar VALUES(1,'unisql tool');

-- 转换前Oracle SQL:
SELECT TO_NCHAR(col1),TO_NCHAR(col2) FROM unisql_test_nchar;
TO_NCHAR(COL1)|TO_NCHAR(COL2)|
--------------+--------------+
1             |unisql tool   |

-- 转换后oceanbase-oracle-Oracle SQL:
SELECT CAST(col1 AS text),CAST(col2 AS text) FROM unisql_test_nchar
col1|col2       |
----+-----------+
1   |unisql tool|

3.2.2.7.1.4. TO_NCHAR (number)

语法
TO_NCHAR(n)
描述
该函数将 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型的数值转换为国家字符集中的字符串。

参数解释

参数

说明

n

属于 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。

示例

-- 转换前Oracle SQL:
SELECT TO_NCHAR(123456789.123) FROM DUAL;
TO_NCHAR(123456789.123)|
-----------------------+
123456789.123          |

-- 转换后oceanbase-oracle-Oracle SQL:
SELECT CAST(123456789.123 AS text) FROM DUAL
text         |
-------------+
123456789.123|

3.2.2.7.1.5. RAWTOHEX

该函数将一个字节数组转换为十六进制字符串。

-- 转换前Oracle SQL:
SELECT RAWTOHEX(SYS_GUID()) FROM DUAL;
RAWTOHEX(SYS_GUID())            |
--------------------------------+
0D003A97D52E7EB7E0632989140A93CC|

-- 转换后oceanbase-oracle-Oracle SQL:
SELECT replace(SYS_GUID(), '-', '') FROM DUAL
replace                         |
--------------------------------+
874cb2d1c2f24023a5f16d1525043d6b|

3.2.2.7.1.6. ROWIDTOCHAR

该函数将 rowid 值转换为 VARCHAR2 数据类型的值 参考示例如下:

CREATE TABLE unisql_rowidtochar_test (
 id NUMBER,
 name VARCHAR(50)
);

INSERT INTO unisql_rowidtochar_test (id, name) VALUES (1, 'Alice');
INSERT INTO unisql_rowidtochar_test (id, name) VALUES (2, 'Bob');

-- 转换前Oracle SQL:
SELECT ROWIDTOCHAR(ROWID) AS char_rowid FROM unisql_rowidtochar_test;
CHAR_ROWID        |
------------------+
AAA2i1AAMAAIbEjAAA|
AAA2i1AAMAAIbEjAAB|

-- 转换后oceanbase-oracle SQL:
SELECT ROWIDTOCHAR(ROWID) AS char_rowid FROM unisql_rowidtochar_test
char_rowid|
----------+
(0,1)     |
(0,2)     |

3.2.2.7.2. 环境和标识符函数

3.2.2.7.2.1. SYS_GUID

语法
SYS_GUID()
描述
该函数生成并返回由 16 个字节组成的全局唯一标识符,即生成一个全局唯一序列号

参数解释

示例

-- 转换前Oracle SQL:
SELECT SYS_GUID() FROM DUAL;
SYS_GUID()      |
----------------+
íx OH xàc)   çt|

-- 转换后oceanbase-oracle-Oracle SQL:
SELECT SYS_GUID() FROM DUAL;
------------------------------------+
SYS_GUID                            |
------------------------------------+
c0f0d0f9-4ca1-11ee-b63c-78ac443a561e|