统一SQL介绍
https://www.light-pg.com/docs/LTSQL/current/index.html
源和目标
源数据库：Oracle
目标数据库：Postgresql，TDSQL-MySQL，达梦8，LightDB-Oracle
操作目标
在Oracle中，cast函数允许将一种内建数据类型转换成另一种内建类型。当前统一SQL支持红框语法分支到目标数据库的转换。
在Oracle中，cast内建类型转换列表，本文针对cast(expr as number)说明经过统一SQL转换到目标库中与之适配的数据类型。
统一SQL转换
通过统一SQL到目标库Postgresql，TDSQL-MySQL，达梦8，LightDB-Oracle转换结果如下：
(关于数据类型number/decimal/dec/numeric还可参考文章：https://blog.csdn.net/Richar1/article/details/137876283 )
| cast(x as target_type) | 测试Oracle-SQL | 2pg-expect | 2tdmysql-expect | 2dm-expect | 2ltora-expect |
| number | select CAST(col_float_binary AS NUMBER) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal | decimal | number | number |
| number(*) | select CAST(col_float_binary AS NUMBER(*)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal | decimal | number | number |
| number(*,0) | select CAST(col_float_binary AS NUMBER(*,0)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38,0) | decimal(38,0) | number(38,0) | number(38,0) |
| number(*,s) | select CAST(col_float_binary AS NUMBER(*,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38,2) | decimal(38,2) | number(38,2) | number(38,2) |
| number(4)/number(4,0) | select CAST(col_float_binary AS NUMBER(4)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(4) | decimal(4) | number(4) | number(4) |
| number(9)/number(9,0) | select CAST(col_float_binary AS NUMBER(9)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(9) | decimal(9) | number(9) | number(9) |
| number(18)/number(18,0) | select CAST(col_float_binary AS NUMBER(18)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(18) | decimal(18) | number(18) | number(18) |
| number(38)/number(38,0) | select CAST(col_float_binary AS NUMBER(38)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38) | decimal(38) | number(38) | number(38) |
| number(p,s) | select CAST(col_float_binary AS NUMBER(10,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(10,2) | decimal(10,2) | number(10,2) | number(10,2) |
| decimal | select CAST(col_float_binary AS decimal) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38,0) | decimal(38,0) | decimal(38,0) | decimal(38,0) |
| decimal(*) | select CAST(col_float_binary AS decimal(*)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38,0) | decimal(38,0) | decimal(38,0) | decimal(38,0) |
| decimal(*,0) | select CAST(col_float_binary AS decimal(*,0)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38,0) | decimal(38,0) | decimal(38,0) | decimal(38,0) |
| decimal(*,s) | select CAST(col_float_binary AS decimal(*,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38,2) | decimal(38,2) | decimal(38,2) | decimal(38,2) |
| decimal(4)/decimal(4,0) | select CAST(col_float_binary AS decimal(4)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(4) | decimal(4) | decimal(4) | decimal(4) |
| decimal(9)/decimal(9,0) | select CAST(col_float_binary AS decimal(9)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(9) | decimal(9) | decimal(9) | decimal(9) |
| decimal(18)/decimal(18,0) | select CAST(col_float_binary AS decimal(18)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(18) | decimal(18) | decimal(18) | decimal(18) |
| decimal(38)/decimal(38,0) | select CAST(col_float_binary AS decimal(38)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(38) | decimal(38) | decimal(38) | decimal(38) |
| decimal(p,s) | select CAST(col_float_binary AS decimal(10,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | decimal(10,2) | decimal(10,2) | decimal(10,2) | decimal(10,2) |
| dec | select CAST(col_float_binary AS dec) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(38,0) | dec(38,0) | dec(38,0) | dec(38,0) |
| dec(*) | select CAST(col_float_binary AS dec(*)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(38,0) | dec(38,0) | dec(38,0) | dec(38,0) |
| dec(*,0) | select CAST(col_float_binary AS dec(*,0)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(38,0) | dec(38,0) | dec(38,0) | dec(38,0) |
| dec(*,s) | select CAST(col_float_binary AS dec(*,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(38,2) | dec(38,2) | dec(38,2) | dec(38,2) |
| dec(4)/dec(4,0) | select CAST(col_float_binary AS dec(4)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(4) | dec(4) | dec(4) | dec(4) |
| dec(9)/dec(9,0) | select CAST(col_float_binary AS dec(9)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(9) | dec(9) | dec(9) | dec(9) |
| dec(18)/dec(18,0) | select CAST(col_float_binary AS dec(18)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(18) | dec(18) | dec(18) | dec(18) |
| dec(38)/dec(38,0) | select CAST(col_float_binary AS dec(38)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(38) | dec(38) | dec(38) | dec(38) |
| dec(p,s) | select CAST(col_float_binary AS dec(10,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | dec(10,2) | dec(10,2) | dec(10,2) | dec(10,2) |
| numeric | select CAST(col_float_binary AS numeric) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(38,0) | decimal(38,0) | numeric(38,0) | numeric(38,0) |
| numeric(*) | select CAST(col_float_binary AS numeric(*)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(38,0) | decimal(38,0) | numeric(38,0) | numeric(38,0) |
| numeric(*,0) | select CAST(col_float_binary AS numeric(*,0)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(38,0) | decimal(38,0) | numeric(38,0) | numeric(38,0) |
| numeric(*,s) | select CAST(col_float_binary AS numeric(*,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(38,2) | decimal(38,2) | numeric(38,2) | numeric(38,2) |
| numeric(4)/numeric(4,0) | select CAST(col_float_binary AS numeric(4)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(4) | decimal(4) | numeric(4) | numeric(4) |
| numeric(9)/numeric(9,0) | select CAST(col_float_binary AS numeric(9)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(9) | decimal(9) | numeric(9) | numeric(9) |
| numeric(18)/numeric(18,0) | select CAST(col_float_binary AS numeric(18)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(18) | decimal(18) | numeric(18) | numeric(18) |
| numeric(38)/numeric(38,0) | select CAST(col_float_binary AS numeric(38)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(38) | decimal(38) | numeric(38) | numeric(38) |
| numeric(p,s) | select CAST(col_float_binary AS numeric(10,2)) AS to_nu FROM topg_function_cast_table WHERE id = 1; | numeric(10,2) | decimal(10,2) | numeric(10,2) | numeric(10,2) |
使用限制
可参考统一SQL官方手册中各个目标库下关于cast函数的章节
https://www.light-pg.com/docs/LTSQL/current/index.html
