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

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

このトピックの内容:

注釈

匿名プロシージャの作成と呼び出しの両方を実行するには、 CALL （匿名プロシージャの場合） を使用します。匿名プロシージャの作成と呼び出しには、 CREATE PROCEDURE スキーマ権限を持つロールは必要ありません。

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

Snowflakeによって適用される規則に従って、プロシージャに名前を付ける必要があります。

詳細については、 プロシージャおよび UDFs の命名とオーバーロード をご参照ください。

トランザクション管理

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

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

一般的なヒント

対称コード

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

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

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

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

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

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

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;
    $$
    ;
Copy

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

SQL コマンドを使用してストアドプロシージャを呼び出します。ストアドプロシージャの呼び出しの詳細については、 ストアドプロシージャの呼び出し をご参照ください。

権限

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

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

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

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

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

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

  • USAGE

  • OWNERSHIP

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

ストアドプロシージャによってアクセスされるデータベースオブジェクトの権限

この件は、 Understanding Caller's Rights and Owner's Rights Stored Procedures で説明されています。

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

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

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

SQL インジェクション

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

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

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

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

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

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

  • このストアドプロシージャには、テーブルなどのどのリソースが必要ですか？

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

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

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

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

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

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

  • ストアドプロシージャを使用する場合と 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 コマンドを使用して以下を確認できます。

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

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

    • 戻り値のデータ型。

    • その他の有用な情報。

言語: 日本語