ストアドプロシージャの使用

.

関連トピック

• JavaScript ストアドプロシージャ API

• 呼び出し元権限と所有者権限のストアドプロシージャの理解

• JavaScript UDFs

ストアドプロシージャを使用すると、ユーザーは複数の SQL ステートメントを手続き型ロジックと組み合わせることにより、複雑なビジネスロジックを含むことができるモジュラーコードを作成できます。

このトピックの内容：

ストアドプロシージャの作成

ストアドプロシージャ DDL

ストアドプロシージャは、ファーストクラスのデータベースオブジェクトです。次の DDL コマンドは、ストアドプロシージャに適用されます。

• CREATE PROCEDURE

• ALTER PROCEDURE

• DROP PROCEDURE

• DESCRIBE PROCEDURE

• SHOW PROCEDURES

さらに、Snowflakeは、ストアドプロシージャを実行するための次のコマンドを提供します。

• CALL

ストアドプロシージャの命名規則

ストアドプロシージャはデータベースオブジェクトです。つまり、指定されたデータベースとスキーマで作成されます。これらは、 データベース.スキーマ.プロシージャ名 の形式で、名前空間によって定義された完全修飾名を持っています。例：

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();

完全修飾名なしで呼び出された場合、ストアドプロシージャは、セッションで使用されているデータベースとスキーマに従って解決されます。

名前のオーバーロード

Snowflakeは、ストアドプロシージャ名のオーバーロードをサポートしています。同じスキーマ内の複数のストアドプロシージャは、引数の数または引数の種類によって署名が異なる限り、同じ名前を持つことができます。オーバーロードされたストアドプロシージャが呼び出されると、Snowflakeは引数をチェックし、正しいストアドプロシージャを呼び出します。

例については、 ストアドプロシージャ名のオーバーロード をご参照ください。

オーバーロードを使用する場合は注意してください。自動型変換とオーバーロードを組み合わせると、軽微なユーザーエラーによって予期しない結果が引き起こされやすくなります。例については、 ストアドプロシージャ名のオーバーロード をご参照ください。

システム定義関数およびユーザー定義関数との潜在的な競合

ストアドプロシージャとユーザー定義関数は、引数の数が異なるか、引数に対するデータ型が異なる場合、同じ名前を持つことができます。

ただし、Snowflakeでは、システム定義関数と同じ名前のストアドプロシージャは作成できません。

トランザクション管理

ストアドプロシージャはアトミックではありません。ストアドプロシージャ内の1つのステートメントが失敗した場合、ストアドプロシージャ内の他のステートメントは必ずしもロールバックされるとは限りません。

トランザクションでストアドプロシージャを使用して、ステートメントのグループをアトミックにすることができます。詳細については、 ストアドプロシージャとトランザクション をご参照ください。

一般的なヒント

対称コード

アセンブリ言語でのプログラミングに精通している場合は、次の類推をご参照ください。アセンブリ言語では、関数は多くの場合、環境を対称的に作成および取り消します。例：

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

ストアドプロシージャでこのアプローチを使用できます。

• ストアドプロシージャがセッションに一時的な変更を加えた場合、そのプロシージャは戻る前にそれらの変更を元に戻す必要があります。

• ストアドプロシージャが例外処理や分岐、またはどのステートメントが実行されるかに影響を与える可能性のある他のロジックを利用する場合、特定の呼び出し中にどのブランチを取るかに関係なく、作成したものをクリーンアップする必要があります。

たとえば、コードは以下に示す擬似コードのようになります。

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

ストアドプロシージャの呼び出し

ストアドプロシージャを実行するには、 CALL ステートメントを使用します。例：

call stproc1(5.14::FLOAT);

ストアドプロシージャの各引数は、一般的な式にすることができます。

CALL stproc1(2 * 5.14::FLOAT);

引数はサブクエリにすることができます。

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

CALL ステートメントごとに1つのストアドプロシージャのみを呼び出すことができます。例えば、次のステートメントは失敗します。

call proc1(1), proc2(2);                          -- Not allowed

また、ストアドプロシージャ CALL を式の一部として使用することはできません。例えば、次のステートメントはすべて失敗します。

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

ただし、ストアドプロシージャ内では、ストアドプロシージャは別のストアドプロシージャを呼び出すことも、再帰的に呼び出すこともできます。

ご用心

ネストされた呼び出しは、許可される最大スタック深度を超える可能性があるため、呼び出しをネストするとき、特に再帰を使用するときは注意してください。

権限

ストアドプロシージャは、2種類の権限を利用します。

• ストアドプロシージャ自体に対する直接の権限。

• ストアドプロシージャがアクセスするデータベースオブジェクト（テーブルなど）の権限。

ストアドプロシージャの権限

他のデータベースオブジェクト（テーブル、ビュー、 UDFsなど）と同様に、ストアドプロシージャはロールによって所有され、他のロールに付与できる1つ以上の権限を持っています。

現在、次の権限がストアドプロシージャに適用されます。

• USAGE

• OWNERSHIP

ロールがストアドプロシージャを使用するには、ロールが所有者であるか、ストアドプロシージャに対する USAGE 権限が付与されている必要があります。

ストアドプロシージャがアクセスするデータベースオブジェクトの権限

この件は、 呼び出し元権限と所有者権限のストアドプロシージャの理解 で説明されています。

ストアドプロシージャの考慮事項

• ストアドプロシージャはネストと再帰を許可しますが、現在、ユーザー定義のストアドプロシージャに対するネストされた呼び出しの最大スタック深度は5（最上位のストアドプロシージャを含む）であり、コールチェーン内の個別のストアドプロシージャが大量のリソースを消費する場合は、それよりも小さくなる可能性があります。

• 同時に多くのストアドプロシージャを呼び出すと、まれに、デッドロックが発生する場合があります。

SQL インジェクション

ストアドプロシージャは、 SQL ステートメントを動的に作成して実行できます。ただし、これにより、特にパブリックまたは信頼できないソースからの入力を使用して SQL ステートメントを作成する場合、 SQL インジェクション攻撃が可能になります。

テキストを連結するのではなく、パラメーターをバインドすることにより、 SQL インジェクション攻撃のリスクを最小限に抑えることができます。変数のバインドの例については、 変数のバインド をご参照ください。

連結を使用することを選択した場合は、パブリックソースからの入力を使用して SQL を動的に構築するときに入力を慎重に確認する必要があります。また、権限が制限されているロール（読み取り専用アクセス、特定のテーブルまたはビューへのアクセスなど）を使用したクエリなど、他の予防措置を講じることもできます。

SQL インジェクション攻撃の詳細については、 SQL インジェクション （Wikipedia）をご参照ください。

ストアドプロシージャの設計のヒント

ストアドプロシージャを設計するためのヒントを次に示します。

• このストアドプロシージャには、どのようなリソース（例：テーブル）が必要ですか。

• どの権限が必要ですか。

  アクセスするデータベースオブジェクト、ストアドプロシージャを実行するロール、およびそれらのロールに必要な権限を検討します。

  プロシージャを呼び出し元権限ストアドプロシージャにする必要がある場合は、その特定のプロシージャ、または関連するプロシージャのグループのいずれかを実行するロールを作成できます。その後、必要な権限をそのロールに付与し、適切なユーザーにそのロールを付与できます。

• ストアドプロシージャは、呼び出し元権限または所有者権限で実行する必要がありますか。このトピックの詳細については、 呼び出し元権限と所有者権限のストアドプロシージャの理解 をご参照ください。

• プロシージャはどのようにエラーを処理する必要がありますか。たとえば、必要なテーブルが欠落している場合、または引数が無効な場合、プロシージャは何をする必要がありますか。

• ストアドプロシージャは、例えばログテーブルに書き込むことによって、アクティビティまたはエラーをログに記録する必要がありますか？

• ストアドプロシージャを使用する場合と UDF を使用する場合についての説明もご参照ください： ストアドプロシージャまたは UDF の作成を選択します。

ストアドプロシージャの文書化

ストアドプロシージャは通常、再利用されるように記述されており、多くの場合、共有されます。ストアドプロシージャを文書化すると、ストアドプロシージャの使用と保守が容易になります。

以下は、ストアドプロシージャを文書化するための一般的な推奨事項です。

通常、ストアドプロシージャについて知りたいユーザーが少なくとも2人います。

• ユーザー/呼び出し元。

• プログラマー/作成者。

ユーザー（およびプログラマー）の場合、次のそれぞれを文書化します。

• ストアドプロシージャの名前。

• ストアドプロシージャ（データベースとスキーマ）の「場所」。

• ストアドプロシージャの目的。

• 各入力パラメーターの名前、データ型、および意味。

• 戻り値の名前、データ型、および意味。戻り値がサブフィールドを含む VARIANT などの複合型の場合、それらのサブフィールドを文書化します。

• ストアドプロシージャがその環境の情報（セッション変数やセッションパラメーターなど）に依存している場合は、それらの名前、目的、および有効な値を文書化します。

• 返されたエラー、スローされた例外など。

• プロシージャを実行するために必要なロールまたは権限。（このトピックの詳細については、 ストアドプロシージャの設計のヒント のロールの説明をご参照ください。）

• プロシージャが呼び出し元権限プロシージャであるか、所有者権限プロシージャであるか。

• 前提条件。例えば、プロシージャが呼び出される前に存在する必要のあるテーブル。

• 作成される新しいテーブルなど、いずれかの出力（戻り値以外）。

• 権限の変更、古いデータの削除などの「副作用」。ほとんどのストアドプロシージャ（関数とは異なります）は、戻り値ではなく、副作用のために特別に呼び出されます。したがって、それらの効果を文書化していることを確認してください。

• ストアドプロシージャの実行後にクリーンアップが必要な場合は、そのクリーンアップを文書化します。

• プロシージャを複数ステートメントのトランザクションの一部として（ AUTOCOMMIT=FALSE を使用して）呼び出すことができるかどうか、またはトランザクションの外部で（ AUTOCOMMIT=TRUE を使用して）実行する必要があるかどうか。

• 呼び出しの例および戻り値の例。

• 制限（該当する場合）。例えば、プロシージャがテーブルを読み取り、テーブルの各行の情報を含む VARIANT を返すとします。 VARIANT が VARIANT の最大有効サイズよりも大きくなる可能性があるため、呼び出し元に、プロシージャがアクセスするテーブル内の最大行数を知らせる必要がある場合があります。

• 警告（該当する場合）。

• トラブルシューティングのヒント。

プログラマーの場合：

• 作成者。

• プロシージャが呼び出し元権限プロシージャまたは所有者権限プロシージャとして作成された理由を説明してください。理由は明らかではない可能性があります。

• ストアドプロシージャはネストできますが、ネストの深さに制限があります。ストアドプロシージャが他のストアドプロシージャを呼び出し、それ自体が他のストアドプロシージャによって呼び出される可能性がある場合、ストアドプロシージャの呼び出しスタックの既知の最大深度を指定できます。そのため、呼び出し元は、ストアドプロシージャの呼び出しが最大呼び出しスタック深度を超える可能性があるかどうかをある程度把握できます。

• デバッグのヒント。

この情報の場所と形式はユーザー次第です。たとえば、内部ウェブサイトに HTML 形式で情報を保存できます。保存先を決定する前に、組織が他の製品の類似情報、またはビュー、ユーザー定義関数などの他のSnowflake機能の類似情報をどこに保存するかを考えます。

その他のヒント：

• ほとんどすべてのソースコードに必要なコメントをソースコードに含めます。

  • コードからのリバースエンジニアリングの意味は難しいことを忘れないでください。アルゴリズムの仕組みだけでなく、そのアルゴリズムの目的も説明してください。

• ストアドプロシージャでは、 CREATE PROCEDURE または ALTER PROCEDURE ステートメントで指定できるオプションの COMMENT を使用できます。他の人は SHOW PROCEDURES コマンドを実行してこのコメントを読むことができます。

• 実用的な場合は、各ストアドプロシージャの CREATE PROCEDURE コマンドのマスターコピーをソースコード管理システムに保存することを検討してください。SnowflakeのTime Travel機能はストアドプロシージャには適用されないため、Snowflakeの外部で古いバージョンのストアドプロシージャを検索する必要があります。ソースコード管理システムが利用できない場合は、テーブルの VARCHAR フィールドに CREATE PROCEDURE コマンドを保存し、新しいバージョンを（古いバージョンを置き換えずに）追加することにより、一部をシミュレートできます。

• ストアドプロシージャに関する情報の提供に役立つ命名規則の使用を検討します。たとえば、名前のプレフィックスまたはサフィックスは、プロシージャが呼び出し元権限ストアドプロシージャであるか、所有者権限ストアドプロシージャであるかを示す場合があります。（たとえば、呼び出し元権限のプレフィックスとして cr_ を使用できます。）

• コメントだけでなく、入力引数のデータ型と順序を表示するには、 SHOW PROCEDURES コマンドを使用できます。ただし、これは引数の名前とデータ型のみを表示することに注意してください。引数については説明しません。

• 適切な権限がある場合は、 DESCRIBE PROCEDURE コマンドを使用して以下を確認できます。

  • 引数の名前とデータ型。

  • プロシージャの本体、およびプロシージャが所有者または呼び出し元として実行されるかどうか。

  • 戻り値のデータ型。

  • その他の有用な情報。