ストアドプロシージャの概要¶
ストアドプロシージャは、関数におおよそ似ています。関数と同様に、ストアドプロシージャは一度作成され、何度も実行できます。ストアドプロシージャは CREATE PROCEDURE コマンドで作成され、 CALL コマンドで実行されます。
ストアドプロシージャは単一の値を返します。ストアドプロシージャ内で SELECT
ステートメントを実行できますが、結果はストアドプロシージャ内で使用するか、返される単一の値に絞り込む必要があります。
Snowflakeのストアドプロシージャは JavaScript と、ほとんどの場合 SQL を使用します。
JavaScript は、制御構造(分岐およびループ)を提供します。
SQL は、JavaScript APIの関数を呼び出すことにより実行されます。
このトピックの内容:
ストアドプロシージャの利点¶
ストアドプロシージャによる許可。
ストレート SQL がサポートしない、プロシージャロジック(分岐とループ)。
エラー処理。
SQL ステートメントの動的作成および実行。
プロシージャを実行するロールの権限ではなく、プロシージャを所有するロールの権限で実行するコードを記述します。これによりストアドプロシージャの所有者は、別の方法では指定された操作を実行できなかったユーザーに、実行する権限を委任できます。ただし、これらの所有者の権利のストアドプロシージャには制限があります。
ストアドプロシージャの一般的な使用法の1つは、複数の SQL ステートメントが必要な、頻繁に実行されるタスクを自動化することです。たとえば、指定した日付より古いデータを削除して、データベースを「クリーンアップ」するとします。複数の DELETE ステートメントを記述できます。各ステートメントは1つの特定のテーブルからデータを削除します。これらすべてのステートメントを単一のストアドプロシージャに入れて、カットオフ日を指定するパラメーターを渡すことができます。その後、プロシージャを呼び出すだけでデータベースをクリーンアップできます。データベースが変更されたら、プロシージャを更新して追加のテーブルをクリーンアップできます。クリーンアップコマンドを使用するユーザーが複数いる場合、すべてのテーブル名を覚えて各テーブルを個別にクリーンアップするのではなく、プロシージャ1つを呼び出すことができます。
ストアドプロシージャと UDFs の違い¶
ストアドプロシージャと UDFs (ユーザー定義関数)の両方により、モジュラーコードの記述が容易になります。ただし、 UDFs とストアドプロシージャには重要な違いがあります。
ストアドプロシージャは独立したステートメントとして呼び出されます¶
ストアドプロシージャは、ステートメントの一部としてではなく、独立したステートメントとして呼び出されます。以下のコードは、ストアドプロシージャの呼び出しと関数の呼び出しの違いを示しています。
CALL MyStoredProcedure1(argument_1); -- Stored Procedure call SELECT MyFunction1(column_1) FROM table1; -- Function call
ストアドプロシージャの戻り値は、直接 SQL に使用できません¶
ストアドプロシージャからの戻り値は、関数からの戻り値とは異なり、 SQL で直接使用することはできません。
ストアドプロシージャは値を返すことができますが、 CALL コマンドの構文は、戻り値を保存する場所や、その値を操作したり別の操作に渡したりする方法を提供しません。 SQL には、次のようなステートメントを作成する方法がありません。
y = stored_procedure1(x); -- Not allowed.
ただし、ストアドプロシージャの戻り値を使用する間接的な方法があります。
別のストアドプロシージャ内でストアドプロシージャを呼び出すことができます。外部ストアドプロシージャの JavaScript は、内部ストアドプロシージャの出力を取得して保存できます。ただし、外部ストアドプロシージャ(および各内部ストアドプロシージャ)は、呼び出し元に複数の値を返すことができません。
ストアドプロシージャを呼び出してから RESULT_SCAN 関数を呼び出し、ストアドプロシージャ用に生成されたステートメント ID を渡すことができます。
結果セットを仮テーブルまたは永続テーブルに保存し、ストアドプロシージャコールから戻った後に、そのテーブルを使用できます。
データ量が大きすぎない場合は、複数の行と列を
VARIANT
に( JSON 値などで)保存し、そのVARIANT
を返すことができます。
CALL ステートメントごとの単一のストアドプロシージャ¶
単一の実行可能ステートメントは、1つのストアドプロシージャのみを呼び出すことができます。対照的に、単一の SQL ステートメントは複数の関数を呼び出すことができます。
同様に、ストアドプロシージャは、関数とは異なり、式の一部として呼び出すことはできません。
ただし、ストアドプロシージャ内では、ストアドプロシージャは別のストアドプロシージャを呼び出すことも、再帰的に呼び出すこともできます。この例は、コード例セクション 例 に示されています。
ストアドプロシージャの呼び出しの詳細については、 ストアドプロシージャの呼び出し をご参照ください。
ストアドプロシージャはデータベースにアクセスし、 APIを介してネストされたクエリを発行できます¶
Snowflakeは JavaScript API を( JavaScript オブジェクトとメソッドの形式で)提供します。 API により、ストアドプロシージャは SELECT、 UPDATE、 CREATE などのデータベース操作を実行できます。
以下の例は、ストアドプロシージャが別のストアドプロシージャを呼び出す SQL ステートメントを作成および実行する方法を示しています。 $$
は、ストアドプロシージャの JavaScript コードの開始と終了を示します。
create procedure ... $$ // Create a Statement object that can call a stored procedure named // MY_PROCEDURE(). var stmt1 = snowflake.createStatement( { sqlText: "call MY_PROCEDURE(22)" } ); // Execute the SQL command; in other words, call MY_PROCEDURE(22). stmt1.execute(); // Create a Statement object that executes a SQL command that includes // a call to a UDF. var stmt2 = snowflake.createStatement( { sqlText: "select MY_UDF(column1) from table1" } ); // Execute the SQL statement and store the output (the "result set") in // a variable named "rs", which we can access later. var rs = stmt2.execute(); // etc. $$;
ストアドプロシージャとは異なり UDFs ( JavaScript UDFs を含む)は、データベース操作を実行できる API にアクセスできません。
ストアドプロシージャまたは UDF の作成を選択します¶
一般的に、ストアドプロシージャを作成するか UDF を作成するかを決定する場合:
- ストアドプロシージャの作成
別のアプリケーション/システムから既存のストアドプロシージャを移行する場合。
データベース操作を実行する必要がある場合:
SELECT、 UPDATEなどの典型的なクエリと DML。
一時テーブルの削除、
N
日より古いデータの削除、ユーザーの追加など、DDLを含む管理タスク。
- UDF の作成
既存の UDF を別のアプリケーション/システムから移行する場合。
SQL ステートメントの一部として呼び出すことができ、ステートメントで使用される値を返す必要がある関数が必要な場合。
出力にすべての入力行またはすべてのグループの値を含める必要がある場合。例:
select MyFunction(col1) from table1; select MyAggregateFunction(col1) from table1 group by col2;
移行に関する考慮事項¶
多くのデータベースシステムでは、 SQL のみにストアドプロシージャを実装しています。 SQL ストアドプロシージャをSnowflakeに移行するには、 ストアドプロシージャの使用 の例に示すように、 JavaScript に SQL を埋め込みます。