4.2.2.7. 转换为 OceanBase-Oracle
4.2.2.7.1. 转换函数
4.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|
4.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 |
4.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|
4.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|
4.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|
4.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) |
4.2.2.7.2. 环境和标识符函数
4.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|