Snowflake创建函数遇语法错误：unexpected 'curDay' 求解决方案

在Snowflake中创建以下函数时出现语法错误：

CREATE OR REPLACE FUNCTION DB_BI_DEV.RAW_CPMS_AAR.cfn_GetShiftIDFromDateTime (dateTime TIMESTAMP_NTZ(9), shiftCalendarID int)

RETURNS int
LANGUAGE SQL 
AS
$$

DECLARE
        time time:=TIME(:dateTime);
        curDay  int;
        prvDay  int;
        shiftID int;
BEGIN
    SELECT TOP 1
        :shiftCalendarID = ID,
         curDay := DATEDIFF( day, BeginDate, :dateTime ) % PeriodInDays + 1,
         prvDay := ( :curDay + PeriodInDays - 2 ) % PeriodInDays + 1
    FROM DB_BI_DEV.RAW_CPMS_AAR.ShiftCalendar
    WHERE ID = :shiftCalendarID
        OR (    :shiftCalendarID IS NULL
            AND Name = 'Factory'
            AND BeginDate <= :dateTime )
    ORDER BY BeginDate DESC;
    
    RETURN shiftID;
    END;
$$

错误提示：syntax error line 7 at position 5 unexpected 'curDay'. (line 7)

尝试过用LET声明变量、移除time变量，但仍出现相同错误。以下是在SQL Server中可正常运行的同名函数作为参考：

CREATE FUNCTION [dbo].[cfn_GetShiftIDFromDateTime] (
       @dateTime datetime,
       @shiftCalendarID int
)
RETURNS int
AS
BEGIN
    DECLARE
        @time    time = CONVERT( time, @dateTime ),
        @curDay  int,
        @prvDay  int,
        @shiftID int;

    SELECT TOP 1
        @shiftCalendarID = ID,
        @curDay = DATEDIFF( dd, BeginDate, @dateTime ) % PeriodInDays + 1,
        @prvDay = ( @curDay + PeriodInDays - 2 )       % PeriodInDays + 1
    FROM ShiftCalendar
    WHERE ID = @shiftCalendarID
        OR (    @shiftCalendarID IS NULL
            AND Name = 'Factory'
            AND BeginDate <= @dateTime )
    ORDER BY BeginDate DESC;

    SELECT @shiftID = ID
    FROM Shift
    WHERE ShiftCalendarID = @shiftCalendarID
        AND (  ( FromDay = @curDay AND FromTimeOfDay <= @time         AND TillTimeOfDay >  @time )
            OR ( FromDay = @curDay AND FromTimeOfDay >= TillTimeOfDay AND FromTimeOfDay <= @time )
            OR ( FromDay = @prvDay AND FromTimeOfDay >= TillTimeOfDay AND TillTimeOfDay >  @time )
        );

    RETURN @shiftID;
END 
GO

• 变量声明规则不兼容：Snowflake SQL UDF不支持DECLARE关键字，必须用LET声明变量，且每个变量需单独定义，不能用逗号分隔多个变量。
• SELECT赋值语法差异：Snowflake不支持SQL Server中SELECT @var = column的赋值方式，需改用SELECT ... INTO将查询结果直接批量赋值给变量。
• 变量引用方式错误：Snowflake中引用函数内部变量无需加冒号:，直接使用变量名即可。
• 关键字冲突：time是Snowflake的内置数据类型，用作变量名会引发语法歧义，需重命名。

CREATE OR REPLACE FUNCTION DB_BI_DEV.RAW_CPMS_AAR.cfn_GetShiftIDFromDateTime (dateTime TIMESTAMP_NTZ(9), shiftCalendarID int)
RETURNS int
LANGUAGE SQL 
AS
$$
    LET time_var TIME := TIME(dateTime);
    LET curDay INT;
    LET prvDay INT;
    LET shiftID INT;
    LET cal_id INT;
    LET cal_period INT;

    -- 获取ShiftCalendar的核心数据
    SELECT TOP 1
        ID,
        DATEDIFF(DAY, BeginDate, dateTime) % PeriodInDays + 1,
        PeriodInDays
    INTO cal_id, curDay, cal_period
    FROM DB_BI_DEV.RAW_CPMS_AAR.ShiftCalendar
    WHERE ID = shiftCalendarID
        OR (shiftCalendarID IS NULL AND Name = 'Factory' AND BeginDate <= dateTime)
    ORDER BY BeginDate DESC;

    -- 计算前一天的偏移值
    prvDay := (curDay + cal_period - 2) % cal_period + 1;

    -- 查询匹配的ShiftID
    SELECT TOP 1 ID
    INTO shiftID
    FROM DB_BI_DEV.RAW_CPMS_AAR.Shift
    WHERE ShiftCalendarID = cal_id
        AND (
            (FromDay = curDay AND FromTimeOfDay <= time_var AND TillTimeOfDay > time_var)
            OR (FromDay = curDay AND FromTimeOfDay >= TillTimeOfDay AND FromTimeOfDay <= time_var)
            OR (FromDay = prvDay AND FromTimeOfDay >= TillTimeOfDay AND TillTimeOfDay > time_var)
        );

    RETURN shiftID;
$$;

内容的提问来源于stack exchange，提问作者Mohamed Sharif