ストアドプロシージャの概要

.

関連トピック

関数と同様に、ストアドプロシージャは一度作成され、何度も実行できます。ストアドプロシージャは CREATE PROCEDURE コマンドで作成され、 CALL コマンドで実行されます。

ストアドプロシージャは、次のいずれかの言語で記述できます。

ストアドプロシージャから、単一の値または表形式のデータを返すことができます。

このトピックの内容:

ストアドプロシージャの利点

ストアドプロシージャによる許可。

  • ストレート SQL がサポートしない、プロシージャロジック（分岐とループ）。

  • エラー処理。

  • SQL ステートメントの動的作成および実行。

  • プロシージャを実行するロールの権限ではなく、プロシージャを所有するロールの権限で実行するコードを記述します。これによりストアドプロシージャの所有者は、別の方法では指定された操作を実行できなかったユーザーに、実行する権限を委任できます。ただし、これらの所有者の権利のストアドプロシージャには制限があります。

ストアドプロシージャの一般的な使用法の1つは、複数の SQL ステートメントが必要な、頻繁に実行されるタスクを自動化することです。たとえば、指定した日付より古いデータを削除して、データベースを「クリーンアップ」するとします。複数の DELETE ステートメントを記述できます。各ステートメントは1つの特定のテーブルからデータを削除します。これらすべてのステートメントを単一のストアドプロシージャに入れて、カットオフ日を指定するパラメーターを渡すことができます。その後、プロシージャを呼び出すだけでデータベースをクリーンアップできます。データベースが変更されたら、プロシージャを更新して追加のテーブルをクリーンアップできます。クリーンアップコマンドを使用するユーザーが複数いる場合、すべてのテーブル名を覚えて各テーブルを個別にクリーンアップするのではなく、プロシージャ1つを呼び出すことができます。

ストアドプロシージャと UDFs の違い

ストアドプロシージャと UDFs （ユーザー定義関数）の両方により、モジュラーコードの記述が容易になります。ただし、 UDFs とストアドプロシージャには重要な違いがあります。

ストアドプロシージャから値を返すことはオプションです

関数とストアドプロシージャの両方が値を返すことができます。ただし、

  • 関数の 目的 は、値を計算して返すことです。関数は、式を指定することによって常に値を明示的に返します（言語により、 return などのキーワードが必要な場合と必要ない場合あり）。たとえば、 JavaScript UDF の本文には、値を返す return ステートメントが必要です。

  • ストアドプロシージャの 目的 は、通常、 SQL ステートメントを実行することです（例: 管理操作を実行するため）。ストアドプロシージャの本文は、値（例: エラーインジケーター）を明示的に返すことができますが、必須ではありません。

    プロシージャが明示的に何も返さない場合でも、すべての CREATE PROCEDURE ステートメントに戻り型を定義する RETURNS 句を含める必要があることに注意してください。

    たとえば、次の疑似コードでは、 RETURNS 句は必須ですが、 RETURN ステートメントはオプションです。

    CREATE PROCEDURE f()
RETURNS INTEGER          -- required
LANGUAGE SQL
AS
$$
    ...
    RETURN error_code;   -- optional
$$
;

    プロシージャが明示的に値を返さない場合は、暗黙的に NULL を返します。

ストアドプロシージャは独立したステートメントとして呼び出されます

関数は値に評価され、一般式を使用できるコンテキスト（例: SELECT my_function() ...）で使用できます。

ストアドプロシージャは値に評価されず、一般式を使用できるすべてのコンテキストで使用できるわけではありません。たとえば、 SELECT my_stored_procedure()... は実行できません。

ストアドプロシージャは、独立したステートメントとして呼び出されます。以下のコードは、ストアドプロシージャの呼び出しと関数の呼び出しの違いを示しています。

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;

ストアドプロシージャの戻り値は、直接 SQL に使用できません

ストアドプロシージャからの戻り値は、関数からの戻り値とは異なり、 SQL で直接使用することはできません。

ストアドプロシージャは値を返すことができますが、 CALL コマンドの構文は、戻り値を保存する場所や、その値を操作したり別の操作に渡したりする方法を提供しません。 SQL には、次のようなステートメントを作成する方法がありません。

y = stored_procedure1(x);                         -- Not allowed.

ただし、ストアドプロシージャの戻り値を使用する間接的な方法があります。

  • 別のストアドプロシージャ内でストアドプロシージャを呼び出すことができます。外部ストアドプロシージャの JavaScript は、内部ストアドプロシージャの出力を取得して保存できます。ただし、外部ストアドプロシージャ（および各内部ストアドプロシージャ）は、呼び出し元に複数の値を返すことができません。

  • ストアドプロシージャを呼び出してから RESULT_SCAN 関数を呼び出し、ストアドプロシージャ用に生成されたステートメント ID を渡すことができます。

  • 結果セットを仮テーブルまたは永続テーブルに保存し、ストアドプロシージャコールから戻った後に、そのテーブルを使用できます。

  • データ量が大きすぎない場合は、複数の行と列を VARIANT に（ JSON 値などで）保存し、その VARIANT を返すことができます。

CALL ステートメントごとの単一のストアドプロシージャ

単一の実行可能ステートメントは、1つのストアドプロシージャのみを呼び出すことができます。対照的に、単一の SQL ステートメントは複数の関数を呼び出すことができます。

同様に、ストアドプロシージャは、関数とは異なり、式の一部として呼び出すことはできません。

ただし、ストアドプロシージャ内では、ストアドプロシージャは別のストアドプロシージャを呼び出すことも、再帰的に呼び出すこともできます。この例は、コード例セクション 例 に示されています。

ストアドプロシージャの呼び出しの詳細については、 ストアドプロシージャの呼び出し をご参照ください。

ストアドプロシージャにより、データベースへのアクセス、およびネストされたクエリの発行が可能

ストアドプロシージャ内で、 SELECT、 UPDATE、 CREATE などのデータベース操作を実行できます。

  • JavaScript ストアドプロシージャでは、 JavaScript API （JavaScript オブジェクトおよびメソッドの形式）を使用してこれらの操作を実行できます。

    以下の例は、ストアドプロシージャが別のストアドプロシージャを呼び出す 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 （ 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;
⇧
最上部に戻る