Snowflake存储过程中编写多条UPDATE语句的方法咨询

代码问题分析 #

• 错误地将UPDATE语句放在DECLARE块的resultset定义中，resultset的作用是定义结果集变量，并非直接承载执行语句。
• 声明了returns table()但未返回有效结果集，同时多语句的嵌套写法违反了Snowflake的API调用规则。

方案1：仅执行更新（无返回结果） #

如果只需要完成更新操作，不需要返回数据，可以使用以下写法：

create or replace procedure sp_multipleupdate()
  returns varchar
  language sql
  as
  $$
  begin
    update TEST.PUBLIC.DEPT set Dname='PM' where deptid=10;
    update TEST.PUBLIC.emp set name='veavi' where deptno=20;
    update TEST.PUBLIC.loc set locname='del' where id=5;
    
    return '更新全部执行完毕';
  end;
  $$;

调用方式：

call sp_multipleupdate();

方案2：返回每条更新的影响行数 #

如果需要跟踪每条更新操作影响的行数，可以通过@@ROWCOUNT变量获取数据，再返回结果表：

create or replace procedure sp_multipleupdate_with_rowcount()
  returns table(update_table varchar, affected_rows integer)
  language sql
  as
  $$
  declare
    dept_rows integer;
    emp_rows integer;
    loc_rows integer;
    res resultset;
  begin
    update TEST.PUBLIC.DEPT set Dname='PM' where deptid=10;
    dept_rows := @@ROWCOUNT;
    
    update TEST.PUBLIC.emp set name='veavi' where deptno=20;
    emp_rows := @@ROWCOUNT;
    
    update TEST.PUBLIC.loc set locname='del' where id=5;
    loc_rows := @@ROWCOUNT;
    
    res := select 'DEPT' as update_table, dept_rows as affected_rows
           union all
           select 'EMP' as update_table, emp_rows as affected_rows
           union all
           select 'LOC' as update_table, loc_rows as affected_rows;
    
    return table(res);
  end;
  $$;

调用方式：

call sp_multipleupdate_with_rowcount();

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