■ はじめに

Snowflake でデータがなかったら
エラーを発生させる必要が出てきたので
ストアドの例外について調べてみた

【１】例外の宣言

https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/exceptions.html#declaring-an-exception
https://docs.snowflake.com/ja/sql-reference/snowflake-scripting/declare.html#exception-declaration-syntax

-- ブロックの DECLARE セクションで独自の例外を宣言
-- <exception_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;
declare
  my_exception exception (-20002, 'Raised MY_EXCEPTION.');

【２】例外発生

-- 例外を発生させるには、 RAISE コマンドを実行
raise my_exception;

【３】EXCEPTION ブロック

https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/exceptions.html#handling-an-exception

EXCEPTION ブロックにより、
例外をキャッチすることでできる
 => プログラムで言うとtry-catchみたいなもの

例

declare
  my_exception exception (-20002, 'Raised MY_EXCEPTION.');
begin
  let counter := 0;
  let should_raise_exception := true;
  if (should_raise_exception) then
    raise my_exception;
  end if;
  counter := counter + 1;
  return counter;
exception
  when statement_error then
    return object_construct('Error type', 'STATEMENT_ERROR',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  when my_exception then
    return object_construct('Error type', 'MY_EXCEPTION',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
  when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
end;

使用上の注意

* EXCEPTION ブロックでキャッチすると、
　クエリとしてはそのまま成功された形になる
 => エラーとして扱いたい場合は、キャッチしなくていい

【４】サンプル

* 以下の関連記事は、例１の応用。

【Snowflake】ストアド～ステージ内でデータ0件の場合エラーにする～
https://dk521123.hatenablog.com/entry/2022/12/29/175848

例１：０件だったら独自の例外を発生させる

execute immediate $$
declare
  -- ★ここの値を変更して動作確認してください★
  id varchar default 'x001';
  row_count integer default 0;
  -- Exception code は -20,999 ~ -20,000 で指定
  zero_count_exception exception (-20100, 'No data');
begin
  let result_set resultset := (select count(*) as row_count from demo_users where id = :id);
  let cursor_for_result_set cursor for result_set;
  for row_variable in cursor_for_result_set do
    let row_count number := row_variable.row_count;
    if (row_count <= 0) then
      raise zero_count_exception;
    else
      return row_count;
    end if;
  end for;
exception
  -- STATEMENT_ERROR
  -- この例外は、ステートメントの実行中にエラーが発生したことを示します。
  -- たとえば、存在しないテーブルを削除しようとすると、この例外が発生します。
  when statement_error then
    return object_construct(
      'Error type', 'statement_error',
      'SQLCODE', sqlcode,
      'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate
    );
  when zero_count_exception then
    return object_construct(
      'Error type', 'zero_count_exception',
      'SQLCODE', sqlcode,
      'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate
    );
  when other then
    return object_construct(
      'Error type', 'other_error',
      'SQLCODE', sqlcode,
      'SQLERRM', sqlerrm,
      'SQLSTATE', sqlstate
    );
end;
$$
;

テスト用データ

create table demo_users (id varchar, name varchar);

insert into demo_users (id, name) values
    ('x001', 'Mike'),
    ('x002', 'Tom');

-- drop table demo_users;

【５】補足：SQLROWCOUNT

SQLROWCOUNT っていうSQLの行数を返すものが
用意されているらしい

https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/dml-status.html

-- SQLROWCOUNT
-- 最後の DML ステートメントの影響を受けた行の数。

execute immediate $$
begin
  -- Insert +3 rows into a table.
  insert into demo_users (id, name) values
      ('x003', 'Sam'),
      ('x004', 'Smith'),
      ('x005', 'Ken');

  -- SQLROWCOUNT is not affected by statements
  -- that are not DML statements (e.g. SELECT statements).
  select * from demo_users;
  -- Returns the number of rows affected by
  -- the last DML statement (the INSERT statement).
  return sqlrowcount;

end;
$$;
+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

プログラムの超個人的なメモ

Memo for Programming.

【Snowflake】ストアド～例外 / EXCEPTION ～

■ はじめに

目次

【１】例外の宣言

【２】例外発生

【３】EXCEPTION ブロック

使用上の注意

【４】サンプル

例１：０件だったら独自の例外を発生させる

【５】補足：SQLROWCOUNT

関連記事