概要
Snowflakeのストアドプロシージャについて触れる機会があったので、備忘録として本記事を作成いたしました。
言語はSQL(Snowflakeスクリプト)です。
ストアドプロシージャの構成
ptest_fromテーブルからptestテーブルへレコードを全件insert処理を行うプロシージャを通じて、CREATEから各ブロックについて解説していきます。
①CREATE・・・プロシージャ定義
作成するプロシージャの名称、使用言語、戻り値の型などを設定します。
CREATE OR REPLACE PROCEDURE test_insert_allrecords()
RETURNS VARCHAR --プロシージャ完了後の戻り値、ここではプロシージャ実行の成功or失敗を通知する
LANGUAGE SQL --言語選択、Java, Javascript, Python, Scala, SQLから選択可能
as
② DECLARE・・・宣言ブロック(省略可能)
プロシージャ内で使用する変数や例外を宣言します。
変数を使用しない場合は記述不要です。
DECLARE
maximum_count integer; --「変数名 データ型;」で１変数を定義します。
v_c1 varchar(10);
v_c2 number;
records CURSOR FOR SELECT c1, c2 FROM ptest_from; --カーソル変数、クエリ結果から繰り返し行を取得する際に使用する変数。詳しくはページ末尾の公式ドキュメント参照
test_exception EXCEPTION (-20001, 'テーブルが存在しません'); --例外の宣言。ここで例外の名前、例外コード、例外時の表示メッセージを設定しています。
③ BEGIN...ENDブロック・・・スクリプトブロック
宣言した変数やSQL文を用いて行いたい操作を記述します。
ここでは例外の発生とカーソル変数を使用したinsertの繰り返し処理を記述しております。
BEGIN
if (not exists(
select 1 from information_schema.tables
where table_name = 'PTEST_FROM')) then
raise test_exception;
end if; --IF文がTRUEの場合、RAISEで例外を発生させる。
open records; --カーソルをopen
select count(*) into maximum_count from ptest_from; --クエリ結果を変数に代入する際には、 select 列名 into 変数名 from テーブル //処理としてはレコード数を繰り返し回数とするため取得
for i in 1 to maximum_count do --ループ処理、1回から上記行で定義したmaximum_count回まで繰り返すよう指示している
fetch records into v_c1, v_c2;
INSERT INTO ptest values (:v_c1, :v_c2, current_timestamp()); --SQL文内で変数を使用する際には':'を先頭につける
end for;
close records; --カーソルをclose
return maximum_count || '行を挿入しました。'; --プロシージャ実行後に出力されるreturn値を設定(正常系)
④ EXCEPTION・・・例外ブロック
例外をキャッチした際の処理を記述します。
ここではfromテーブル(=PTEST_FROM)が存在しない場合は、宣言したtest_exceptionのエラーメッセージを、それ以外のエラー時に'不明なエラー'を出すようにします。
EXCEPTION
when test_exception then
RETURN SQLERRM; --RAISEで例外処理となった場合、宣言ブロックで宣言したtest_exceptionのエラーメッセージを返す
when other then
RETURN '不明なエラー'; --それ以外の例外処理時は'不明なエラー'を返す
END; --最後にBEGINに対応してENDを記述
テーブル準備
プロシージャを動かすためにテーブルの作成とデータ投入を行っていきます。
--insert先のテーブル
create table ptest(
c1 varchar(10),
c2 number,
c3 datetime
)
;
--insert元のテーブル
create table ptest_from(
c1 varchar(10),
c2 number
)
;
--テストデータをptest_fromテーブルに挿入
insert into ptest_from values
('test', 5),
('test2', 8)
;
プロシージャの実施(正常系)
プロシージャの呼び出しは、callで行います。
call test_insert_allrecords();
--出力結果
| TEST_INSERT_ALLRECORDS |
|---|
| 2行を挿入しました。 |
プロシージャは動き、正常系のメッセージを出してくれました。次にテーブルを確認します。
select * from ptest;
--出力結果
| C1 | C2 | C3 |
|---|---|---|
| test | 5 | 2023-05-18 06:52:46.649 |
| test2 | 8 | 2023-05-18 06:52:47.153 |
テーブル準備時にinsertしたデータと処理時のタイムスタンプが格納されています。
プロシージャの実施(例外処理)
最後に例外処理時の動きを確認します。
①ptest_fromテーブルが存在しなかった場合
まずは、例外として処理を設定しているテーブルが存在しなかった場合の動きを確認します。
insert元のptest_fromテーブルを削除し、その後プロシージャを実行します。
drop table ptest_from;
call test_insert_allrecords();
--出力結果
| TEST_INSERT_ALLRECORDS |
|---|
| テーブルが存在しません |
設定したエラーメッセージが出てくれました。
②それ以外のエラーの場合
列名を変更して、ptest_fromテーブルを再作成します。
create table ptest_from(
c1 varchar(10),
c3 number --列名c2をc3に変更
)
;
call test_insert_allrecords();
--出力結果
| TEST_INSERT_ALLRECORDS |
|---|
| 不明なエラー |
無事'不明なエラー'が表示されました。
まとめ
プロシージャはかなり自由度が高く、他にも様々なことが実行できるので、ドキュメントを読みながら少しずつ理解を深めていきたいなと思いました。
ここまでお読みくださりありがとうございました！
参照
[DECLARE （Snowflakeスクリプト）]
https://docs.snowflake.com/ja/sql-reference/snowflake-scripting/declare
[カーソル]
https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/cursors
[変数]
https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/variables
[例外]
https://docs.snowflake.com/ja/developer-guide/snowflake-scripting/exceptions