求助：将含游标与循环的Oracle函数转换为Snowflake函数

需要将Oracle的GET_SN_CONCAT函数转换为Snowflake版本，该函数根据传入参数通过游标循环拼接所有序列号并返回结果。改写后出现语法错误，附上相关代码及报错信息，寻求解决方法。

原Oracle函数代码 #

CREATE OR REPLACE FUNCTION C3BL_HADOOP.GET_SN_CONCAT (
   P_ID1_I          NUMBER,
   P_ID2_I          NUMBER,
   P_SN_TYPE_I      VARCHAR2,
   P_DELIMITER_I    VARCHAR2)
   RETURN VARCHAR2
AS
   CURSOR C_SHIP_SN
   IS
      SELECT DISTINCT NVL ( (SELECT ACTUAL_SERIAL_NO
                               FROM XXCCS_BOP_SN_CROSS_REF
                              WHERE DUM_ID = SERIAL.FM_SERIAL_NUMBER),
                           TRIM (SERIAL.FM_SERIAL_NUMBER))
                         FM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_SHIP_LINES_SN  SERIAL,
             XXCCS_BOP_OE_SHIPMENT_LINES SHIP
       WHERE     SHIP.DELIVERY_DETAIL_ID = SERIAL.DELIVERY_DETAIL_ID
             AND SHIP.LINE_ID = P_ID1_I
      UNION
      SELECT DISTINCT NVL ( (SELECT ACTUAL_SERIAL_NO
                               FROM XXCCS_BOP_SN_CROSS_REF
                              WHERE DUM_ID = XOSL.SERIAL_NUMBER),
                           TRIM (XOSL.SERIAL_NUMBER))
                         FM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_SHIPMENT_LINES XOSL
       WHERE XOSL.LINE_ID = P_ID1_I;

   CURSOR C_RECEIPT_SN
   IS
      SELECT DISTINCT NVL ( (SELECT ACTUAL_SERIAL_NO
                               FROM XXCCS_BOP_SN_CROSS_REF
                              WHERE DUM_ID = SERIAL_NUM),
                           TRIM (SERIAL_NUM))
                         SERIAL_NUM
        FROM XXCCS_BOP_OE_RCPT_LINES_SN
       WHERE SHIPMENT_LINE_ID = P_ID1_I AND TRANSACTION_ID = P_ID2_I;

   CURSOR C_CUST_SN
   IS
      SELECT DISTINCT
             NVL (TRIM (ATTRIBUTE9), TRIM (FROM_SERIAL_NUMBER))
                FROM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_LOT_SERIAL_NUM 
       WHERE LINE_ID = P_ID1_I;

   CURSOR C_SPARE_SN
   IS
      SELECT NVL (TRIM (ATTRIBUTE9), TRIM (FROM_SERIAL_NUMBER))
                FROM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_LOT_SERIAL_NUM
       WHERE LINE_ID = P_ID1_I;

   L_START       NUMBER := 0;
   L_SN_CONCAT   VARCHAR2 (4000);
BEGIN
   IF P_SN_TYPE_I = 'SHIP'
   THEN
      FOR C_SN_CUR IN C_SHIP_SN
      LOOP
         IF L_START = 0
         THEN
            IF C_SN_CUR.FM_SERIAL_NUMBER IS NOT NULL
            THEN
               L_SN_CONCAT := C_SN_CUR.FM_SERIAL_NUMBER;
            END IF;
         ELSE
            IF C_SN_CUR.FM_SERIAL_NUMBER IS NOT NULL
            THEN
               L_SN_CONCAT :=
                  L_SN_CONCAT || P_DELIMITER_I || C_SN_CUR.FM_SERIAL_NUMBER;
            END IF;
         END IF;

         L_START := L_START + 1;
      END LOOP;
   ELSIF P_SN_TYPE_I = 'RECEIPT'
   THEN
      FOR C_SN_CUR IN C_RECEIPT_SN
      LOOP
         IF L_START = 0
         THEN
            IF C_SN_CUR.SERIAL_NUM IS NOT NULL
            THEN
               L_SN_CONCAT := C_SN_CUR.SERIAL_NUM;
            END IF;
         ELSE
            IF C_SN_CUR.SERIAL_NUM IS NOT NULL
            THEN
               L_SN_CONCAT :=
                  L_SN_CONCAT || P_DELIMITER_I || C_SN_CUR.SERIAL_NUM;
            END IF;
         END IF;

         L_START := L_START + 1;
      END LOOP;
   ELSIF P_SN_TYPE_I = 'CUST'
   THEN
      FOR C_SN_CUR IN C_CUST_SN
      LOOP
         IF L_START = 0
         THEN
            IF C_SN_CUR.FROM_SERIAL_NUMBER IS NOT NULL
            THEN
               L_SN_CONCAT := C_SN_CUR.FROM_SERIAL_NUMBER;
            END IF;
         ELSE
            IF C_SN_CUR.FROM_SERIAL_NUMBER IS NOT NULL
            THEN
               L_SN_CONCAT :=
                  L_SN_CONCAT || P_DELIMITER_I || C_SN_CUR.FROM_SERIAL_NUMBER;
            END IF;
         END IF;

         L_START := L_START + 1;
      END LOOP;
END IF;

   RETURN (L_SN_CONCAT);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN (L_SN_CONCAT);
END GET_SN_CONCAT;
/

尝试改写的Snowflake代码 #

CREATE OR REPLACE FUNCTION CX_DB.CX_GSLOBAC_STG.TEMP_DEVA_GET_SN_CONCAT 
(  "P_ID1_I"          NUMBER,
   "P_ID2_I"          NUMBER,
   --P_SN_TYPE_I      VARCHAR,
   "P_DELIMITER_I"    VARCHAR)
   RETURNS VARCHAR
   LANGUAGE SQL
as
$$
DECLARE

C_RECEIPT_SN CURSOR FOR 
                SELECT DISTINCT NVL (a.ACTUAL_SERIAL_NO,
                TRIM (b.SERIAL_NUM))
                SERIAL_NUM
                FROM EDW_SERVICE_ETL_DB.SS.CSF_RCV_SERIAL_TRANSACTIONS b,
                left join (SELECT DUM_ID,ACTUAL_SERIAL_NO
                FROM EDW_SERVICE_ETL_DB.SS.CSF_XXCTS_INV_SNO_CROSS_REF) a
                a.DUM_ID = b.SERIAL_NUM
                WHERE SHIPMENT_LINE_ID = P_ID1_I AND TRANSACTION_ID = P_ID2_I;

L_SN_CONCAT VARCHAR;

BEGIN
LET L_START       NUMBER := 0;
FOR C_SN_CUR in C_RECEIPT_SN DO
    IF L_START = 0
     THEN
        IF C_SN_CUR.SERIAL_NUM IS NOT NULL
        THEN
           L_SN_CONCAT := C_SN_CUR.SERIAL_NUM;
        END IF;
     ELSE
        IF C_SN_CUR.SERIAL_NUM IS NULL
        THEN
           L_SN_CONCAT :=
              L_SN_CONCAT || P_DELIMITER_I || C_SN_CUR.SERIAL_NUM;
        END IF;
     END IF;
    L_START := L_START + 1;
END FOR;
RETURN L_SN_CONCAT;
END;
$$
;

报错信息 #

syntax error line 4 at position 0 unexpected 'C_RECEIPT_SN'. syntax
error line 5 at position 37 unexpected 'a'. syntax error line 12 at
position 16 unexpected 'WHERE'. (line 4)

错误原因及修复方案 #

1. SQL语言UDF不支持游标和循环 #

Snowflake的LANGUAGE SQL类型UDF仅支持单条SQL语句逻辑，无法使用游标、DECLARE变量、FOR循环等PL/SQL风格语法。要实现循环逻辑，需改用LANGUAGE JAVASCRIPT类型UDF，或者用Snowflake原生的LISTAGG聚合函数直接拼接，无需循环。

2. 游标SQL语句语法错误 #

原游标中的JOIN语法存在两处错误：

• FROM后多余的逗号需删除，LEFT JOIN前不需要逗号分隔表
• 表关联条件前必须添加ON关键字

修正后的游标SQL：

SELECT DISTINCT NVL(a.ACTUAL_SERIAL_NO, TRIM(b.SERIAL_NUM)) AS SERIAL_NUM
FROM EDW_SERVICE_ETL_DB.SS.CSF_RCV_SERIAL_TRANSACTIONS b
LEFT JOIN (SELECT DUM_ID, ACTUAL_SERIAL_NO FROM EDW_SERVICE_ETL_DB.SS.CSF_XXCTS_INV_SNO_CROSS_REF) a
ON a.DUM_ID = b.SERIAL_NUM
WHERE SHIPMENT_LINE_ID = P_ID1_I AND TRANSACTION_ID = P_ID2_I

3. 循环条件逻辑错误 #

原代码中ELSE分支判断C_SN_CUR.SERIAL_NUM IS NULL，会导致空值被错误拼接，应改为IS NOT NULL，与第一个分支逻辑保持一致。

4. 完整修复后的JavaScript版本UDF（还原所有分支逻辑） #

以下是匹配原Oracle函数所有分支逻辑的Snowflake JavaScript UDF：

CREATE OR REPLACE FUNCTION CX_DB.CX_GSLOBAC_STG.GET_SN_CONCAT(
    P_ID1_I NUMBER,
    P_ID2_I NUMBER,
    P_SN_TYPE_I VARCHAR,
    P_DELIMITER_I VARCHAR
) RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sn_concat = "";
var start = 0;

// 处理SHIP类型
if (P_SN_TYPE_I === 'SHIP') {
    var ship_sql = `
        SELECT DISTINCT NVL((SELECT ACTUAL_SERIAL_NO FROM XXCCS_BOP_SN_CROSS_REF WHERE DUM_ID = SERIAL.FM_SERIAL_NUMBER), TRIM(SERIAL.FM_SERIAL_NUMBER)) AS FM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_SHIP_LINES_SN SERIAL
        JOIN XXCCS_BOP_OE_SHIPMENT_LINES SHIP ON SHIP.DELIVERY_DETAIL_ID = SERIAL.DELIVERY_DETAIL_ID
        WHERE SHIP.LINE_ID = ?
        UNION
        SELECT DISTINCT NVL((SELECT ACTUAL_SERIAL_NO FROM XXCCS_BOP_SN_CROSS_REF WHERE DUM_ID = XOSL.SERIAL_NUMBER), TRIM(XOSL.SERIAL_NUMBER)) AS FM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_SHIPMENT_LINES XOSL
        WHERE XOSL.LINE_ID = ?
    `;
    var stmt = snowflake.createStatement({sqlText: ship_sql, binds: [P_ID1_I, P_ID1_I]});
    var rs = stmt.execute();
    while (rs.next()) {
        var sn = rs.getColumnValue(1);
        if (sn !== null) {
            if (start === 0) {
                sn_concat = sn;
            } else {
                sn_concat += P_DELIMITER_I + sn;
            }
            start++;
        }
    }
}
// 处理RECEIPT类型
else if (P_SN_TYPE_I === 'RECEIPT') {
    var receipt_sql = `
        SELECT DISTINCT NVL(a.ACTUAL_SERIAL_NO, TRIM(b.SERIAL_NUM)) AS SERIAL_NUM
        FROM EDW_SERVICE_ETL_DB.SS.CSF_RCV_SERIAL_TRANSACTIONS b
        LEFT JOIN (SELECT DUM_ID, ACTUAL_SERIAL_NO FROM EDW_SERVICE_ETL_DB.SS.CSF_XXCTS_INV_SNO_CROSS_REF) a
        ON a.DUM_ID = b.SERIAL_NUM
        WHERE SHIPMENT_LINE_ID = ? AND TRANSACTION_ID = ?
    `;
    var stmt = snowflake.createStatement({sqlText: receipt_sql, binds: [P_ID1_I, P_ID2_I]});
    var rs = stmt.execute();
    while (rs.next()) {
        var sn = rs.getColumnValue(1);
        if (sn !== null) {
            if (start === 0) {
                sn_concat = sn;
            } else {
                sn_concat += P_DELIMITER_I + sn;
            }
            start++;
        }
    }
}
// 处理CUST类型
else if (P_SN_TYPE_I === 'CUST') {
    var cust_sql = `
        SELECT DISTINCT NVL(TRIM(ATTRIBUTE9), TRIM(FROM_SERIAL_NUMBER)) AS FROM_SERIAL_NUMBER
        FROM XXCCS_BOP_OE_LOT_SERIAL_NUM
        WHERE LINE_ID = ?
    `;
    var stmt = snowflake.createStatement({sqlText: cust_sql, binds: [P_ID1_I]});
    var rs = stmt.execute();
    while (rs.next()) {
        var sn = rs.getColumnValue(1);
        if (sn !== null) {
            if (start === 0) {
                sn_concat = sn;
            } else {
                sn_concat += P_DELIMITER_I + sn;
            }
            start++;
        }
    }
}

return sn_concat;
$$;

5. 简化版：使用LISTAGG聚合函数（无需循环） #

若无需复杂的空值判断逻辑，可直接用Snowflake的LISTAGG函数实现拼接，性能更优：

CREATE OR REPLACE FUNCTION CX_DB.CX_GSLOBAC_STG.GET_SN_CONCAT(
    P_ID1_I NUMBER,
    P_ID2_I NUMBER,
    P_SN_TYPE_I VARCHAR,
    P_DELIMITER_I VARCHAR
) RETURNS VARCHAR
LANGUAGE SQL
AS
$$
CASE P_SN_TYPE_I
    WHEN 'SHIP' THEN (
        SELECT LISTAGG(DISTINCT NVL((SELECT ACTUAL_SERIAL_NO FROM XXCCS_BOP_SN_CROSS_REF WHERE DUM_ID = SERIAL.FM_SERIAL_NUMBER), TRIM(SERIAL.FM_SERIAL_NUMBER)), P_DELIMITER_I)
        FROM XXCCS_BOP_OE_SHIP_LINES_SN SERIAL
        JOIN XXCCS_BOP_OE_SHIPMENT_LINES SHIP ON SHIP.DELIVERY_DETAIL_ID = SERIAL.DELIVERY_DETAIL_ID
        WHERE SHIP.LINE_ID = P_ID1_I
        UNION ALL
        SELECT LISTAGG(DISTINCT NVL((SELECT ACTUAL_SERIAL_NO FROM XXCCS_BOP_SN_CROSS_REF WHERE DUM_ID = XOSL.SERIAL_NUMBER), TRIM(XOSL.SERIAL_NUMBER)), P_DELIMITER_I)
        FROM XXCCS_BOP_OE_SHIPMENT_LINES XOSL
        WHERE XOSL.LINE_ID = P_ID1_I
    )
    WHEN 'RECEIPT' THEN (
        SELECT LISTAGG(DISTINCT NVL(a.ACTUAL_SERIAL_NO, TRIM(b.SERIAL_NUM)), P_DELIMITER_I)
        FROM EDW_SERVICE_ETL_DB.SS.CSF_RCV_SERIAL_TRANSACTIONS b
        LEFT JOIN (SELECT DUM_ID, ACTUAL_SERIAL_NO FROM EDW_SERVICE_ETL_DB.SS.CSF_XXCTS_INV_SNO_CROSS_REF) a
        ON a.DUM_ID = b.SERIAL_NUM
        WHERE SHIPMENT_LINE_ID = P_ID1_I AND TRANSACTION_ID = P_ID2_I
    )
    WHEN 'CUST' THEN (
        SELECT LISTAGG(DISTINCT NVL(TRIM(ATTRIBUTE9), TRIM(FROM_SERIAL_NUMBER)), P_DELIMITER_I)
        FROM XXCCS_BOP_OE_LOT_SERIAL_NUM
        WHERE LINE_ID = P_ID1_I
    )
END
$$;

内容的提问来源于stack exchange，提问作者Shiva Shylaja