PL/SQL是否有通用数据类型？自定义场景化类型转换咨询

一、关于你设想的函数实现 #

Oracle 中没有 variant 这种通用可变类型，但可以通过两种方式实现类似的场景化转换逻辑：

1. 使用 ANYDATA 类型（通用多类型兼容） #

ANYDATA 是 Oracle 内置的通用数据类型，可存储任意类型数据，结合类型判断实现自定义转换：

CREATE OR REPLACE FUNCTION my_type(p_type IN ANYDATA) RETURN VARCHAR2 AS
    v_type    NUMBER;
    v_date    DATE;
    v_number  NUMBER;
    v_varchar VARCHAR2(4000);
BEGIN
    v_type := p_type.GetType();
    
    -- 处理日期类型：仅当为当天0点时用指定格式转换
    IF v_type = SYS_TYPEID(SYS.STANDARD.DATE) THEN
        IF p_type.GetDate(v_date) = DBMS_TYPES.SUCCESS THEN
            IF v_date = TRUNC(v_date, 'DD') THEN
                RETURN TO_CHAR(v_date, 'YYYY-MM-DD'); -- 替换为你的目标格式
            ELSE
                RETURN TO_CHAR(v_date, 'YYYY-MM-DD HH24:MI:SS');
            END IF;
        END IF;
    -- 处理数值类型：区分整数和小数场景
    ELSIF v_type = SYS_TYPEID(SYS.STANDARD.NUMBER) THEN
        IF p_type.GetNumber(v_number) = DBMS_TYPES.SUCCESS THEN
            IF MOD(v_number, 1) = 0 THEN
                RETURN TO_CHAR(v_number, 'FM999999999'); -- 整数无格式冗余
            ELSE
                RETURN TO_CHAR(v_number, 'FM999999999.00'); -- 小数保留两位
            END IF;
        END IF;
    -- 处理字符串类型：直接返回或自定义处理
    ELSIF v_type = SYS_TYPEID(SYS.STANDARD.VARCHAR2) THEN
        IF p_type.GetVarchar2(v_varchar) = DBMS_TYPES.SUCCESS THEN
            RETURN v_varchar;
        END IF;
    END IF;
    
    -- 未匹配类型的默认返回
    RETURN TO_CHAR(p_type);
EXCEPTION
    WHEN OTHERS THEN
        RETURN '转换失败';
END;
/

2. 使用重载函数（类型匹配更直观） #

如果能明确输入的可能类型，可创建多个同名函数，Oracle 会自动根据输入类型匹配执行：

-- 日期类型处理函数
CREATE OR REPLACE FUNCTION my_type(p_type IN DATE) RETURN VARCHAR2 AS
BEGIN
    IF p_type = TRUNC(p_type, 'DD') THEN
        RETURN TO_CHAR(p_type, 'YYYY-MM-DD');
    ELSE
        RETURN TO_CHAR(p_type, 'YYYY-MM-DD HH24:MI:SS');
    END IF;
END;
/

-- 数值类型处理函数
CREATE OR REPLACE FUNCTION my_type(p_type IN NUMBER) RETURN VARCHAR2 AS
BEGIN
    IF MOD(p_type, 1) = 0 THEN
        RETURN TO_CHAR(p_type, 'FM999999999');
    ELSE
        RETURN TO_CHAR(p_type, 'FM999999999.00');
    END IF;
END;
/

-- 字符串类型处理函数
CREATE OR REPLACE FUNCTION my_type(p_type IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
    RETURN p_type; -- 可添加字符串校验/转换逻辑
END;
/

二、灵活的转换格式配置方案 #

针对 Oracle 默认转换逻辑不符合需求的问题，推荐两种可动态调整的方案：

1. 数据库表存储格式配置 #

创建配置表存储不同类型、场景的转换格式，函数动态读取配置，修改格式无需改动代码：

-- 创建格式配置表
CREATE TABLE conversion_formats (
    data_type    VARCHAR2(20) NOT NULL,
    scene_code   VARCHAR2(50) NOT NULL,
    format_str   VARCHAR2(100) NOT NULL,
    PRIMARY KEY (data_type, scene_code)
);

-- 插入示例配置
INSERT INTO conversion_formats VALUES ('DATE', 'WHOLE_DAY', 'YYYY-MM-DD');
INSERT INTO conversion_formats VALUES ('DATE', 'FULL_TIME', 'YYYY-MM-DD HH24:MI:SS');
INSERT INTO conversion_formats VALUES ('NUMBER', 'INTEGER', 'FM999999999');
INSERT INTO conversion_formats VALUES ('NUMBER', 'DECIMAL', 'FM999999999.00');

搭配动态读取配置的函数：

CREATE OR REPLACE FUNCTION my_type_with_config(p_type IN ANYDATA, p_scene IN VARCHAR2) RETURN VARCHAR2 AS
    v_type       NUMBER;
    v_date       DATE;
    v_number     NUMBER;
    v_format     VARCHAR2(100);
BEGIN
    v_type := p_type.GetType();
    
    IF v_type = SYS_TYPEID(SYS.STANDARD.DATE) THEN
        SELECT format_str INTO v_format
        FROM conversion_formats
        WHERE data_type = 'DATE' AND scene_code = p_scene;
        
        IF p_type.GetDate(v_date) = DBMS_TYPES.SUCCESS THEN
            RETURN TO_CHAR(v_date, v_format);
        END IF;
    ELSIF v_type = SYS_TYPEID(SYS.STANDARD.NUMBER) THEN
        SELECT format_str INTO v_format
        FROM conversion_formats
        WHERE data_type = 'NUMBER' AND scene_code = p_scene;
        
        IF p_type.GetNumber(v_number) = DBMS_TYPES.SUCCESS THEN
            RETURN TO_CHAR(v_number, v_format);
        END IF;
    END IF;
    
    RETURN TO_CHAR(p_type);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '未找到对应场景的转换配置';
END;
/

2. PL/SQL 包封装转换逻辑 #

将转换逻辑和格式参数封装到包中，可通过修改包的全局变量快速调整格式：

CREATE OR REPLACE PACKAGE conversion_pkg IS
    -- 可直接修改这些变量调整格式
    g_date_whole_day_format VARCHAR2(20) := 'YYYY-MM-DD';
    g_date_full_time_format VARCHAR2(20) := 'YYYY-MM-DD HH24:MI:SS';
    g_number_integer_format VARCHAR2(20) := 'FM999999999';
    g_number_decimal_format VARCHAR2(20) := 'FM999999999.00';
    
    FUNCTION convert_data(p_type IN ANYDATA) RETURN VARCHAR2;
END conversion_pkg;
/

CREATE OR REPLACE PACKAGE BODY conversion_pkg IS
    -- 私有函数：判断是否为当天0点日期
    FUNCTION is_whole_date(p_date IN DATE) RETURN BOOLEAN IS
    BEGIN
        RETURN p_date = TRUNC(p_date, 'DD');
    END;
    
    FUNCTION convert_data(p_type IN ANYDATA) RETURN VARCHAR2 AS
        v_type    NUMBER;
        v_date    DATE;
        v_number  NUMBER;
    BEGIN
        v_type := p_type.GetType();
        
        IF v_type = SYS_TYPEID(SYS.STANDARD.DATE) THEN
            IF p_type.GetDate(v_date) = DBMS_TYPES.SUCCESS THEN
                RETURN TO_CHAR(v_date, CASE WHEN is_whole_date(v_date) THEN g_date_whole_day_format ELSE g_date_full_time_format END);
            END IF;
        ELSIF v_type = SYS_TYPEID(SYS.STANDARD.NUMBER) THEN
            IF p_type.GetNumber(v_number) = DBMS_TYPES.SUCCESS THEN
                RETURN TO_CHAR(v_number, CASE WHEN MOD(v_number,1)=0 THEN g_number_integer_format ELSE g_number_decimal_format END);
            END IF;
        END IF;
        
        RETURN TO_CHAR(p_type);
    END;
END conversion_pkg;
/

内容的提问来源于stack exchange，提问作者Peter Frey