Oracle函数转Snowflake函数求助：转换后调用报错

我不熟悉Snowflake，尝试把下面的Oracle函数转换成Snowflake存储过程时遇到报错：Error: invalid identifier 'RESULT_SET' (line 72)，麻烦帮忙解决转换问题和报错。

原Oracle代码 #

CREATE OR REPLACE function schema_1.getCName(CCodes VARCHAR2)
  return varchar2
as
  TYPE results_type IS REF CURSOR;
  results results_type;
  CValue VARCHAR2(1023 CHAR);
  CCodesForInClause VARCHAR2(511 CHAR) DEFAULT '';
  CCodesValue VARCHAR2(510 CHAR);
  cursorQuery VARCHAR2(1023 CHAR) DEFAULT '';
  CNames VARCHAR2(1023 CHAR);
begin
  CNames := '';
  IF(instr(CCodes,',') > 0) THEN
    CCodesValue := CCodes;
    WHILE (instr(CCodesValue,',') > 0)
    LOOP
      CCodesForInClause := CCodesForInClause || '''' || 
        trim(substr(CCodesValue, 0, instr(CCodesValue,',')-1)) || 
        ''',';
      CCodesValue := substr(CCodesValue, instr(CCodesValue,',') + 1, 
        LENGTH(CCodesValue) - instr(CCodesValue,','));
    END LOOP;

    CCodesForInClause := CCodesForInClause || '''' || trim(CCodesValue) || '''';
    cursorQuery := 'SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV IN ' || '(' ||  CCodesForInClause || ') order by C_NAME';

    OPEN results FOR cursorQuery;
    LOOP
      IF (length(trim(CValue)) > 0) THEN
        CNames := CNames || CValue || ';';
      END IF;
      FETCH results INTO CValue;
      EXIT WHEN results%NOTFOUND;
    END LOOP;

    IF (length(trim(CNames)) > 0) THEN
      CNames := substr(CNames, 1, length(CNames)-1);
    END IF;
    CLOSE results;
  ELSE
    OPEN results FOR 'SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV = :cc' USING CCodes;
    FETCH results into CValue;

      IF (results%FOUND) THEN
        CNames := CValue;
      END IF;
      CLOSE results;
    END IF;

    DBMS_Output.Put_Line(CNames);
    return CNames;
end;

我编写的Snowflake代码 #

CREATE OR REPLACE PROCEDURE Schema1.CName(cCodes STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    CValue STRING;
    cCodesForInClause STRING DEFAULT '';
    cCodesValue STRING;
    cursorQuery STRING;
    cNames STRING DEFAULT '';
    result_set resultset  ;
BEGIN
   IF(regexp_instr(cCodes,',') > 0) THEN
        cCodesValue := cCodes;

    WHILE (instr(cCodesValue,',') > 0)
    LOOP
      cCodesForInClause := cCodesForInClause || '''' || trim(substr(cCodesValue, 0, regexp_instr(cCodesValue,',')-1)) || ''',';
      cCodesValue := substr(cCodesValue, instr(cCodesValue,',') + 1, LENGTH(cCodesValue) - instr(cCodesValue,','));
    END LOOP;

    cCodesForInClause := cCodesForInClause || '''' || trim(cCodesValue) || '''';
    cursorQuery := 'SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV IN ' || '(' ||  cCodesForInClause || ') order by C_NAME'
    ;
OPEN result_set FOR cursorQuery;
LOOP
    IF (length(trim(CValue)) > 0) THEN
       cNames := cNames || CValue || ';';
      END IF;
    FETCH result_set INTO CValue;
    --EXIT WHEN results%NOTFOUND;
END LOOP;  
IF (length(trim(cNames)) > 0) THEN
      cNames := substr(cNames, 1, length(cNames)-1);
    END IF;
    ELSE
    cursorQuery := 'SELECT DISTINCT C_NAME FROM ONESRC_DEV.ONESRC_OWNER.EmergingMarketsRegions where WHERE C_ABBREV = :cc';
    result_set := (EXECUTE IMMEDIATE :cursorQuery);
        -- Fetch the result
        IF (RESULTSET_HAS_ROWS(result_set)) THEN
            FETCH result_set INTO CValue;
            IF (LENGTH(TRIM(CValue)) > 0) THEN
                cNames := CValue;
            END IF;
        END IF;
        END IF;
        return cNames;
        END;$$;

call Schema1.CName('USA');

问题分析与修复方案 #

报错核心原因 #

1. 函数使用错误：Snowflake SQL存储过程中不存在RESULTSET_HAS_ROWS内置函数，无法用它判断结果集是否有行。
2. 结果集获取方式错误：ELSE分支中用result_set := (EXECUTE IMMEDIATE :cursorQuery)无法正确绑定结果集，Snowflake需要用OPEN ... FOR语法打开游标。
3. SQL语法错误：ELSE分支的查询语句里有重复的WHERE关键字（where WHERE），直接导致SQL执行失败。
4. 循环无退出条件：IF分支的LOOP没有终止逻辑，会触发无限循环。
5. 变量未初始化：CValue初始值为NULL，第一次执行trim(CValue)会触发空值错误。

修正后的完整代码 #

CREATE OR REPLACE PROCEDURE Schema1.CName(cCodes STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    CValue STRING DEFAULT ''; -- 初始化变量避免空值问题
    cCodesForInClause STRING DEFAULT '';
    cCodesValue STRING;
    cursorQuery STRING;
    cNames STRING DEFAULT '';
    result_set resultset;
BEGIN
    IF(instr(cCodes,',') > 0) THEN -- 统一用instr，和Oracle逻辑保持一致
        cCodesValue := cCodes;

        WHILE (instr(cCodesValue,',') > 0)
        LOOP
            cCodesForInClause := cCodesForInClause || '''' || trim(substr(cCodesValue, 0, instr(cCodesValue,',')-1)) || ''',';
            cCodesValue := substr(cCodesValue, instr(cCodesValue,',') + 1); -- 省略长度参数，Snowflake默认取到字符串末尾
        END LOOP;

        cCodesForInClause := cCodesForInClause || '''' || trim(cCodesValue) || '''';
        cursorQuery := 'SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV IN (' || cCodesForInClause || ') ORDER BY C_NAME';
        
        OPEN result_set FOR cursorQuery;
        FETCH result_set INTO CValue; -- 先获取第一行数据
        WHILE (CValue IS NOT NULL) -- 用CValue是否为空判断循环终止
        LOOP
            cNames := cNames || CValue || ';';
            FETCH result_set INTO CValue;
        END LOOP;  
        
        IF (length(trim(cNames)) > 0) THEN
            cNames := substr(cNames, 1, length(cNames)-1);
        END IF;
        CLOSE result_set; -- 释放结果集资源
    ELSE
        -- 修正重复WHERE，用OPEN ... FOR绑定参数
        cursorQuery := 'SELECT DISTINCT C_NAME FROM ONESRC_DEV.ONESRC_OWNER.EmergingMarketsRegions WHERE C_ABBREV = :cc';
        OPEN result_set FOR cursorQuery USING cCodes;
        FETCH result_set INTO CValue;
        
        IF (CValue IS NOT NULL AND length(trim(CValue)) > 0) THEN
            cNames := CValue;
        END IF;
        CLOSE result_set;
    END IF;
    
    return cNames;
END;
$$;

-- 测试调用
call Schema1.CName('USA');

额外优化说明 #

1. 统一使用instr替代regexp_instr，和原Oracle逻辑对齐，减少正则匹配的不必要开销。
2. 简化substr参数，Snowflake中省略第三个长度参数时，默认截取到字符串末尾，代码更简洁。
3. 调整循环逻辑，先FETCH再处理数据，避免原逻辑中第一次循环CValue为空的无效判断。
4. 增加CLOSE result_set语句，确保结果集资源被正确释放。

内容的提问来源于stack exchange，提问作者Jeet Chatterjee