确定受 DML 命令影响的行数

执行每个 DML 命令 后，Snowflake Scripting 会设置以下全局变量。您可以使用这些变量来确定最后一个 DML 语句是否影响了任何行。

变量

描述

SQLROWCOUNT

受最后一个 DML 语句影响的行数。

这相当于 JavaScript 存储过程中的 getNumRowsAffected()。

SQLFOUND

如果最后一个 DML 语句影响一行或多行，则为 true。

SQLNOTFOUND

如果最后一个 DML 语句影响零行，则为 true。

本节中的示例使用下表：

CREATE OR REPLACE TABLE my_values (value NUMBER);
Copy

以下示例使用 SQLROWCOUNT 变量返回受最后一个 DML 语句（在本示例中为 INSERT 语句）影响的行数。SELECT 语句不是 DML 语句，对 SQLROWCOUNT 变量无影响。

BEGIN

  -- Insert 3 rows into a table.
  INSERT INTO my_values VALUES (1), (2), (3);
  -- SQLROWCOUNT is not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  SELECT * from my_values;
  -- Returns the number of rows affected by
  -- the last DML statement (the INSERT statement).
  RETURN SQLROWCOUNT;

END;
Copy

注意：如果您在 Python Connector 代码中使用 SnowSQL、Classic Console 或者 execute_stream 或 execute_string 方法，请改用本示例（请参阅 在 SnowSQL、Classic Console 和 Python Connector 中使用 Snowflake Scripting）：

EXECUTE IMMEDIATE $$
BEGIN

  -- Insert 3 rows into a table.
  INSERT INTO my_values VALUES (1), (2), (3);
  -- SQLROWCOUNT is not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  SELECT * from my_values;
  -- Returns the number of rows affected by
  -- the last DML statement (the INSERT statement).
  RETURN SQLROWCOUNT;

END;
$$;
Copy
+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

以下示例使用 SQLFOUND 和 SQLNOTFOUND 变量返回受最后一个 DML 语句（UPDATE 语句）影响的行数。与前面的示例一样， SELECT 语句不会影响 SQLFOUND 和 SQLNOTFOUND 变量。

BEGIN

  -- Update the rows in a table that have values less than 3.
  UPDATE my_values SET value = 4 WHERE value < 3;
  -- SQLFOUND and SQLNOTFOUND are not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  SELECT * from my_values;
  -- SQLFOUND returns 'true' if the last DML statement
  -- (the UPDATE statement) affected one or more rows.
  IF (SQLFOUND = true) THEN
    RETURN 'Updated ' || SQLROWCOUNT || ' rows.';
  -- SQLNOTFOUND returns 'true' if the last DML statement
  -- (the UPDATE statement) affected zero rows.
  ELSEIF (SQLNOTFOUND = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'SQLFOUND and SQLNOTFOUND are not true.';
  END IF;

END;
Copy

注意：如果您在 Python Connector 代码中使用 SnowSQL、Classic Console 或者 execute_stream 或 execute_string 方法，请改用本示例（请参阅 在 SnowSQL、Classic Console 和 Python Connector 中使用 Snowflake Scripting）：

EXECUTE IMMEDIATE $$
BEGIN

  -- Update the rows in a table that have values less than 3.
  UPDATE my_values SET value = 4 WHERE value < 3;
  -- SQLFOUND and SQLNOTFOUND are not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  SELECT * from my_values;
  -- SQLFOUND returns 'true' if the last DML statement
  -- (the UPDATE statement) affected one or more rows.
  IF (SQLFOUND = true) THEN
    RETURN 'Updated ' || SQLROWCOUNT || ' rows.';
  -- SQLNOTFOUND returns 'true' if the last DML statement
  -- (the UPDATE statement) affected zero rows.
  ELSEIF (SQLNOTFOUND = true) THEN
    RETURN 'No rows updated.';
  ELSE
    RETURN 'SQLFOUND and SQLNOTFOUND are not true.';
  END IF;

END;
$$;
Copy
+-----------------+
| anonymous block |
|-----------------|
| Updated 2 rows. |
+-----------------+
语言: 中文