将SQL Server表值函数DateTable转换为Oracle数据库SQL查询

下面提供两种适配Oracle的实现方案，根据你的Oracle版本和使用场景选择：

方案1：返回游标类型（简单易用，适合多数场景） #

这种方式无需预先定义自定义类型，直接返回游标，上手门槛低：

CREATE OR REPLACE FUNCTION DateTable (
    p_StartDate DATE,
    p_EndDate DATE
) RETURN SYS_REFCURSOR
IS
    v_result SYS_REFCURSOR;
BEGIN
    OPEN v_result FOR
        SELECT FullDateAlternateKey, CalendarYear, MonthNumberOfYear
        FROM AdventureWorksDW.dbo.DimDate
        WHERE FullDateAlternateKey BETWEEN p_StartDate AND p_EndDate;
    
    RETURN v_result;
END;
/

调用示例：

-- 用游标变量获取并输出结果
DECLARE
    v_cur SYS_REFCURSOR;
    v_date DATE;
    v_year NUMBER;
    v_month NUMBER;
BEGIN
    v_cur := DateTable(DATE '2020-01-01', DATE '2020-12-31');
    FETCH v_cur INTO v_date, v_year, v_month;
    WHILE v_cur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(v_date || ' | ' || v_year || ' | ' || v_month);
        FETCH v_cur INTO v_date, v_year, v_month;
    END LOOP;
    CLOSE v_cur;
END;
/

方案2：返回表类型（可直接用于SELECT查询，贴近SQL Server用法） #

如果需要像SQL Server那样直接在SELECT语句中调用函数（比如SELECT * FROM TABLE(DateTable(...))），需要先定义自定义类型再创建函数：

1. 定义行对象类型

CREATE OR REPLACE TYPE DateTableRow AS OBJECT (
    FullDateAlternateKey DATE,
    CalendarYear NUMBER,
    MonthNumberOfYear NUMBER
);
/

2. 定义表类型

CREATE OR REPLACE TYPE DateTableResult AS TABLE OF DateTableRow;
/

3. 创建表值函数

CREATE OR REPLACE FUNCTION DateTable (
    p_StartDate DATE,
    p_EndDate DATE
) RETURN DateTableResult
IS
    v_result DateTableResult;
BEGIN
    SELECT DateTableRow(FullDateAlternateKey, CalendarYear, MonthNumberOfYear)
    BULK COLLECT INTO v_result
    FROM AdventureWorksDW.dbo.DimDate
    WHERE FullDateAlternateKey BETWEEN p_StartDate AND p_EndDate;
    
    RETURN v_result;
END;
/

调用示例：

SELECT * FROM TABLE(DateTable(DATE '2020-01-01', DATE '2020-12-31'));

关键差异说明 #

• Oracle参数不能用@前缀，改用p_这类前缀区分参数
• 原SQL Server中的方括号[]在Oracle中无需使用，直接写字段/表名即可
• 注意Oracle表名默认大写，需确保原表AdventureWorksDW.dbo.DimDate在Oracle中的实际命名正确（比如可能是ADVENTUREWORKS.DIMDATE）

内容的提问来源于stack exchange，提问作者Sql-power_bi