Snowflakeのストアドプロシージャには、戻り値にテーブル情報を指定できる機能があることをご存じでしたか？
この戻り値にテーブル情報を指定できる機能はとても便利です。
これまではストアドプロシージャで処理を実行し、結果についてはSelect文を実行して確認という２ステップ必要だった手順が、ストアドプロシージャを実行するだけの１ステップで実行結果が表示できるようになり、作業効率も上がります。
分析屋としては、データクレンジングの際に何度も使うストアドプロシージャには、必須の機能だと考えます。
すぐに使えるサンプルコードもありますので、是非試してください。
処理手順の概要とサンプルコード
ストアドプロシージャで戻り値にSelect文の実行結果を返すためには、いくつかの手順が必要です。
処理の流れは以下の通りです。
①ストアドプロシージャの戻り値の型を指定
②Select文の結果を保持するResultSet変数を定義
③Select文を実行し②のResultSet変数に設定
④return文でテーブル情報を返す
以下サンプルSQLを示します。
CREATE OR REPLACE PROCEDURE TEST_P (in_t varchar(100))
// ① ストアドプロシージャの戻り値の型を指定
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
// ② Select文の結果を保持するResultSet変数を定義
res RESULTSET;
BEGIN
create or replace temporary table _test as select 1 as no;
// ③ Select文を実行し②のResultSet変数に設定
res := (select * from _test);
// ④ return文でテーブル情報を返す
return TABLE(res);
end;
$$;以下、各処理の説明を記載します。
①ストアドプロシージャの戻り値の型を指定
CREATE OR REPLACE PROCEDURE 構文の Returnsパラメータは、戻り値の型を指定するパラメータです。
文字列を戻り値としたい場合は「String」を指定しますが、テーブル情報を戻り値としたい場合は「TABLE()」を指定します。
②Select文の結果を保持するResultSet変数を定義
ResultSet変数の定義は、必ずDECLARE と BEGIN で囲まれた部分に記載する必要があります。
Snowflakeのお作法だと理解してください。変数名は何をつけても問題ありません。
変数名を変えた場合、サンプルコードの「res」も置き換えるようにしてください。
③Select文を実行しResultSet変数に設定
戻り値を制御しているのが、この部分の記述です。Select文を変えることで、戻り値の内容を変えることができます。
なお、Snowflakeのお作法として、ResultSet変数にSelect文の結果を設定する場合、Select文はカッコで囲む必要があります。
また、ResultSet変数への値の設定には「:=」（コロンイコール）を指定する必要があります。
Snowflake初心者だと、「=」と「:=」の使い分けが難しく感じてしまいますよね。
DECLARE ～BEGINで囲まれた領域で定義した変数に値を設定するには「:=」（コロンイコール）を使うと覚えてしまうのが簡単です。
④return文でテーブル情報を返す
return 文に指定した内容が、ストアドプロシージャの戻り値として返されます。
テーブル情報を返す場合は、「TABLE(ResultSet変数名)」を指定します。
このReturn文は便利で、ストアドプロシージャのデバッグにも使えます。
例えば①のReturnsパラメータでStringを指定した場合、Returnで任意の文字列を返し処理結果として表示することが可能です。
その他
サンプルコードの「$$」は、Webベースのアプリでストアドプロシージャを実行するために必要なお作法です。
プロシージャの関数の始まりと終わりを表しているとのことですので、深い意味は考えずに AS と DECLAREの間と END;の後に「$$」を指定しましょう。
ストアドプロシージャーの実行手順
ストアドプロシージャの実行には、call 文を利用し、作成した関数を呼び出します。
下記のサンプルコードを実行し、ストアドプロシージャの戻り値がWebUI上に表示されることを確認ください。
// 作成したストアドプロシージャの実行
call TEST_P(IN_T => 'TEST');実行結果が以下のよう表示されれば成功です。
なお、今回の関数では引数の値を利用しませんでしたが、ストアドプロシージャの引数に値を設定する場合は「=>」を指定します。
上記サンプルでは、変数「IN_T」に文字列’test’を設定した上で、ストアドプロシージャを実行しています。
コメント