ストアドプロシージャとユーザー定義関数のどちらを記述するかの選択¶
このトピックでは、ストアドプロシージャと UDFs の主な違いについて説明します。これには、それぞれの呼び出し方法と実行できる内容の違いも含まれます。
大まかに言えば、ストアドプロシージャと UDFs は、以下で説明するように、通常の使用方法が異なります。
ストアドプロシージャの目的 |
ユーザー定義関数の目的 |
---|---|
通常、 SQL ステートメントを実行して管理操作を実行します。ストアドプロシージャの本文では、値(エラーインジケーターなど)を明示的に返すことが許可されていますが、必須ではありません。 |
値を計算して返します。関数は、式を指定することによって常に明示的に値を返します。たとえば、 JavaScript UDF の本文には、値を返す |
このトピックの内容:
いつストアドプロシージャまたは UDF を作成するのか¶
一般的に、ストアドプロシージャまたは UDF を作成するかどうかを決定する場合には、次の推奨事項を考慮します。
次の場合にはストアドプロシージャを作成する |
次の場合には UDF を作成する |
---|---|
|
|
サポートされているハンドラー言語¶
プロシージャまたは UDF を記述するときは、サポートされている言語のいずれかでそのロジックをハンドラーとして記述します。次のテーブルに、サポートされている言語をリストしています。
ストアドプロシージャ |
ユーザー定義関数 |
---|---|
使用法と動作の違い¶
次のセクションでは、プロシージャと UDFs でサポートされる動作の具体的な違いについて説明します。
UDFs は値を返すが、ストアドプロシージャは値を返すとは限らない¶
ストアドプロシージャと関数はどちらも値を返すことができます。ただし、
ストアドプロシージャの本文では、値(エラーインジケーターなど)を明示的に返すことが許可されていますが、必須ではありません。これは、ストアドプロシージャの一般的な目的が、 SQL ステートメントを実行して管理操作を実行することだからです。
プロシージャが明示的に何も返さない場合でも、すべての CREATE PROCEDURE ステートメントに戻り型を指定する RETURNS 句を含める必要があることに注意してください。プロシージャが明示的に値を返さない場合は、暗黙的に NULL を返します。
次の例のコードでは、 RETURNS 句を使用してプロシージャの戻り型を宣言していますが、エラーが発生した場合にのみ値が返されます。つまり、すべてのコードパスが値を返すわけではありません。
create or replace procedure do_stuff(input number) returns varchar language sql as $$ declare error varchar default 'Bad input. Number must be less than 10.'; begin if (input > 10) then return error; end if; -- Perform an operation that doesn't return a value. end; $$ ;
プロシージャが明示的に値を返さない場合は、暗黙的に NULL を返します。
UDF は、式を指定することによって常に明示的に値を返します。これは、 UDF の目的が値を計算して返すことだからです。たとえば、 JavaScript UDF の本文には、値を返す
return
ステートメントが必要です。
UDF は SQL で直接使用できる値を返すが、ストアドプロシージャの値は直接使用できるとは限らない¶
Snowflakeスクリプトブロック 内でストアドプロシージャを呼び出すと、 Snowflakeスクリプト変数 に ストアドプロシージャが返す値をキャプチャ することができます。
Snowflakeスクリプトブロックからストアドプロシージャを呼び出していない場合は、ストアドプロシージャが返す値を SQL で直接使用することはできません(関数が返す値の場合とは異なる)。CALL コマンドの構文は、戻り値を格納する場所や、その値を操作したり別の操作に渡したりする方法を提供しません。つまり、以下のステートメントは、有効な SQL ステートメントではありません。
y = stored_procedure1(x); -- Not allowed.
ストアドプロシージャの戻り値を使用する間接的な方法はあります(Snowflakeスクリプトブロックの外部)。
別のストアドプロシージャ内でストアドプロシージャを呼び出すことができます。たとえば、ストアドプロシージャハンドラーが JavaScript で記述されている場合、外側のストアドプロシージャの JavaScript は、内側のストアドプロシージャの出力を取得および格納できます。ただし、外部ストアドプロシージャ(および各内部ストアドプロシージャ)は、呼び出し元に複数の値を返すことができません。
ストアドプロシージャを呼び出してから RESULT_SCAN 関数を呼び出し、ストアドプロシージャ用に生成されたステートメント ID を渡すことができます。
結果セットを仮テーブルまたは永続テーブルに保存し、ストアドプロシージャコールから戻った後に、そのテーブルを使用できます。
データ量が大きすぎない場合は、複数の行と列を VARIANT に( JSON 値などで)保存し、その VARIANT を返すことができます。
UDFs は別のステートメントのコンテキストで呼び出し可、ストアドプロシージャは独立して呼び出し¶
ストアドプロシージャは値に評価されず、一般式を使用できるすべてのコンテキストで使用できるわけではありません。たとえば、 SELECT my_stored_procedure()...
は実行できません。
UDF は値に評価され、一般式を使用できるコンテキスト(SELECT my_function() ...
など)で使用できます。
ストアドプロシージャは、独立したステートメントとして呼び出されます。以下のコードは、ストアドプロシージャの呼び出しと関数の呼び出しの違いを示しています。
CALL MyStoredProcedure_1(argument_1);
SELECT MyFunction_1(column_1) FROM table1;
関数とプロシージャの呼び出しの詳細については、次をご参照ください。
1つのステートメントで複数の UDFs を呼び出すことができるが、1つのステートメントで呼び出すことができるストアドプロシージャは1つ¶
単一の実行可能ステートメントは、1つのストアドプロシージャのみを呼び出すことができます。対照的に、単一の SQL ステートメントは複数の関数を呼び出すことができます。
同様に、ストアドプロシージャは、関数とは異なり、式の一部として呼び出すことはできません。
ただし、ストアドプロシージャ内では、ストアドプロシージャは別のストアドプロシージャを呼び出すことも、再帰的に呼び出すこともできます。この例は、コード例セクション 例 に示されています。
関数とプロシージャの呼び出しの詳細については、次をご参照ください。
UDFs はデータベースにアクセス不可、ストアドプロシージャはアクセス可¶
ストアドプロシージャ内で、 SELECT、 UPDATE、 CREATE などのデータベース操作を実行できます。
たとえば、 JavaScript ストアドプロシージャでは、 JavaScript API を使用してこれらの操作を実行できます。
以下の例は、ストアドプロシージャが別のストアドプロシージャを呼び出す 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. $$;
Snowflakeスクリプト のストアドプロシージャで、 SQL ステートメントを実行できます。
以下の例は、ストアドプロシージャが別のストアドプロシージャを呼び出す SQL ステートメントを作成および実行する方法を示しています。
$$
は、ストアドプロシージャにあるSnowflakeスクリプトコードの開始と終了を示します。CREATE PROCEDURE ... -- Call a stored procedure named my_procedure(). CALL my_procedure(22); -- Execute a SQL statement that includes a call to a UDF. SELECT my_udf(column1) FROM table1;
ストアドプロシージャとは異なり、 UDFs はデータベース操作を実行できる API にアクセスできません。