• 统一SQL 支持Oracle cast函数转换

    统一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-SQL2pg-expect2tdmysql-expect2dm-expect2ltora-expect
    numberselect CAST(col_float_binary AS NUMBER) AS to_nu FROM topg_function_cast_table WHERE id = 1;decimaldecimalnumbernumber
    number(*)select CAST(col_float_binary AS NUMBER(*)) AS to_nu FROM topg_function_cast_table WHERE id = 1;decimaldecimalnumbernumber
    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)
    decimalselect 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)
    decselect 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)
    numericselect 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

  • 相关阅读:
    这10 个很“哇塞”的Web资源，前端必备的神仙级网站
    【译】C# 11 特性的早期预览
    前端面经整理
    随想录一刷Day45——动态规划
    Docker上部署mysql（超简单！！！）
    LeetCode297：hard级别中最简单的存在，java版，用时击败98%，内存击败百分之九十九
    游戏盾如何有效防护DDoS
    kubernetes 实战学习
    idea E9 OA环境搭建
    lunatic亚毫秒 Web 框架的LiveView实时视图
  • 原文地址：https://blog.csdn.net/Richar1/article/details/137905044