如何在Snowflake存储过程中将删除的行数赋值给变量

问题描述 #

我需要获取从表中删除的行数并在Snowflake存储过程中使用该数值，请问如何将删除的行数赋值给变量？以下是我尝试执行的代码块，不确定如何将RESULTSET转换为数值赋值给变量：

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  cnt INTEGER;
  query VARCHAR DEFAULT 'DELETE FROM leased_bicycles where bicycle_ID in ( 105, 106)';
BEGIN
  rs := (EXECUTE IMMEDIATE :query);
  //cnt := TABLE(rs);
  RETURN TABLE(rs);
END;
$$;

解决方案 #

有两种简洁的方式可以实现需求：

方式一：从DELETE返回的结果集提取行数

DELETE语句执行后会返回一个单行单列的结果集，存储删除的行数。可以通过将结果集转为表，再把值赋值给变量：

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  cnt INTEGER;
  query VARCHAR DEFAULT 'DELETE FROM leased_bicycles where bicycle_ID in (105, 106)';
BEGIN
  rs := (EXECUTE IMMEDIATE :query);
  -- 从结果集中提取行数到变量
  SELECT $1 INTO cnt FROM TABLE(rs);
  -- 此处可根据业务逻辑使用cnt变量，比如打印、参与后续计算等
  RETURN cnt;
END;
$$;

方式二：使用ROW_COUNT()函数（推荐）

Snowflake提供的ROW_COUNT()函数可以直接返回最近一次DML操作（INSERT/UPDATE/DELETE/MERGE）影响的行数，无需处理结果集，代码更简洁：

EXECUTE IMMEDIATE $$
DECLARE
  cnt INTEGER;
  query VARCHAR DEFAULT 'DELETE FROM leased_bicycles where bicycle_ID in (105, 106)';
BEGIN
  EXECUTE IMMEDIATE :query;
  cnt := ROW_COUNT();
  -- 按需使用cnt变量
  RETURN cnt;
END;
$$;

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