Snowflake SQL存储过程创建报错：意外':='和','语法错误求助

我编写了一个Snowflake SQL存储过程，用于在数据库克隆完成后移除其中的行访问策略（Row Access Policy）和数据屏蔽策略（Masking Policy），但创建该存储过程时出现语法错误，此前类似版本的脚本可正常运行，无法自行定位问题。

错误信息 #

Syntax error: unexpected ':='. (line 25)
Syntax error line 13 at position 46 unexpected ','. (line 25)

原始错误代码 #

CREATE or REPLACE PROCEDURE DBMGT.DBADMIN.Q_DROP_MASKING_and_ROP_ON_BKP_DB("sourceDB" varchar, "clonedDB" varchar)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER 
AS 
$$
DECLARE
    ref_sql VARCHAR;
    rs RESULTSET;
    cmd VARCHAR DEFAULT '''';
    cmd2 VARCHAR DEFAULT '''';
    PATH VARCHAR DEFAULT '''';
    qry VARCHAR default '''';
    s string default '''';
    ts := CONVERT_TIMEZONE('UTC', current_timestamp())::timestamp_ntz;

BEGIN
ref_sql := ''SELECT DISTINCT REF_ENTITY_DOMAIN, REF_SCHEMA_NAME, REF_ENTITY_NAME, REF_COLUMN_NAME
              FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
              WHERE REF_DATABASE_NAME = ?
              AND REF_COLUMN_NAME IS NOT NULL;'';

  --Execute above query passing in bind variables
  rs := (EXECUTE IMMEDIATE :ref_sql USING(sourceDB));

  --Create a CURSOR on the result set above to be able to loop for col masking pols
  LET policy_cur CURSOR FOR rs;

  create or replace temporary table drop_masking_rop_log(sql_stmt varchar);

  --Loop through each row
  FOR ref IN policy_cur DO
    PATH := clonedDB  || ''.'' || ref.REF_SCHEMA_NAME || ''.'' || ref.REF_ENTITY_NAME;
    cmd := ''ALTER '' || ref.REF_ENTITY_DOMAIN || '' '' || PATH || '' MODIFY COLUMN '' || ref.REF_COLUMN_NAME || '' UNSET MASKING POLICY;''; --unset column masking pols

cmd2 :=  ''ALTER '' || ref.REF_ENTITY_DOMAIN || '' '' || PATH || '' DROP ALL ROW ACCESS POLICIES;''; --unset row masking pols

INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES (:cmd, :ts); 
INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES(:cmd2, :ts); 
INSERT INTO drop_masking_rop_log VALUES (:cmd);
INSERT INTO drop_masking_rop_log VALUES (:cmd2);



BEGIN
            execute immediate cmd;
        EXCEPTION WHEN OTHER THEN
                LET LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                insert into DBMGT.DBADMIN.POLICYLOG values (:s , :ts);
                insert into drop_masking_rop_log values (:s);
        END;   


BEGIN
            execute immediate cmd2;
        EXCEPTION WHEN OTHER THEN
                LET LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                insert into DBMGT.DBADMIN.POLICYLOG values (:s , :ts);
                insert into drop_masking_rop_log values (:s);
        END;   

end for;

rs := (select * from drop_masking_rop_log);
return table(rs);



END;
$$
;

修正后的可运行代码 #

CREATE or REPLACE PROCEDURE DBMGT.DBADMIN.Q_DROP_MASKING_and_ROP_ON_BKP_DB("sourceDB" varchar, "clonedDB" varchar)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER 
AS 
$$
DECLARE
    ref_sql VARCHAR;
    rs RESULTSET;
    cmd VARCHAR DEFAULT '''';
    cmd2 VARCHAR DEFAULT '''';
    PATH VARCHAR DEFAULT '''';
    qry VARCHAR DEFAULT '''';
    s STRING DEFAULT '''';
    LINE VARCHAR; -- 新增：声明异常处理中使用的LINE变量
    ts TIMESTAMP_NTZ DEFAULT CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP())::TIMESTAMP_NTZ; -- 修正：用DEFAULT替代:=初始化变量

BEGIN
ref_sql := ''SELECT DISTINCT REF_ENTITY_DOMAIN, REF_SCHEMA_NAME, REF_ENTITY_NAME, REF_COLUMN_NAME
              FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
              WHERE REF_DATABASE_NAME = ?
              AND REF_COLUMN_NAME IS NOT NULL;'';

  --Execute above query passing in bind variables
  rs := (EXECUTE IMMEDIATE :ref_sql USING(sourceDB));

  --Create a CURSOR on the result set above to be able to loop for col masking pols
  LET policy_cur CURSOR FOR rs;

  CREATE OR REPLACE TEMPORARY TABLE drop_masking_rop_log(sql_stmt varchar);

  --Loop through each row
  FOR ref IN policy_cur DO
    PATH := clonedDB  || ''.'' || ref.REF_SCHEMA_NAME || ''.'' || ref.REF_ENTITY_NAME;
    cmd := ''ALTER '' || ref.REF_ENTITY_DOMAIN || '' '' || PATH || '' MODIFY COLUMN '' || ref.REF_COLUMN_NAME || '' UNSET MASKING POLICY;''; --unset column masking pols

cmd2 :=  ''ALTER '' || ref.REF_ENTITY_DOMAIN || '' '' || PATH || '' DROP ALL ROW ACCESS POLICIES;''; --unset row masking pols

INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES (:cmd, :ts); 
INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES(:cmd2, :ts); 
INSERT INTO drop_masking_rop_log VALUES (:cmd);
INSERT INTO drop_masking_rop_log VALUES (:cmd2);

BEGIN
            EXECUTE IMMEDIATE cmd;
        EXCEPTION WHEN OTHER THEN
                LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES (:s , :ts);
                INSERT INTO drop_masking_rop_log VALUES (:s);
        END;   

BEGIN
            EXECUTE IMMEDIATE cmd2;
        EXCEPTION WHEN OTHER THEN
                LINE := SQLCODE || ': ' || SQLERRM;
                s := 'exception for prev qry ' || :LINE;
                INSERT INTO DBMGT.DBADMIN.POLICYLOG VALUES (:s , :ts);
                INSERT INTO drop_masking_rop_log VALUES (:s);
        END;   

END FOR;

rs := (SELECT * FROM drop_masking_rop_log);
RETURN TABLE(rs);

END;
$$
;

错误原因说明 #

• DECLARE块变量初始化语法错误：Snowflake SQL存储过程中，DECLARE块内的变量必须用DEFAULT关键字初始化，不能直接使用:=赋值，原始代码中ts := CONVERT_TIMEZONE(...)违反了该规则。
• 未声明局部变量：异常处理块中使用的LINE变量未在DECLARE块中提前声明，导致隐含语法错误。
• 额外优化：统一使用大写关键字（如CURRENT_TIMESTAMP()、EXECUTE IMMEDIATE），符合Snowflake最佳实践。

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