ストアドプロシージャ

ストアド プロシージャ( prc、proc、storp、sproc、StoPro、StoredProc、StoreProc、sp、SP とも呼ばれる)は、リレーショナルデータベース管理システム( RDBMS )にアクセスするアプリケーションで使用できるサブルーチンです。このようなプロシージャは、データベースデータ ディクショナリに格納されます。

ストアド プロシージャの用途には、データ検証(データベースに統合) やアクセス制御メカニズムなどがあります。さらに、ストアド プロシージャを使用すると、元々アプリケーションに実装されていたロジックを統合して一元化できます。時間とメモリを節約するために、複数のSQLステートメントの実行を必要とする大規模または複雑な処理をストアド プロシージャに保存し、すべてのアプリケーションでそのプロシージャを呼び出すことができます。1 つのストアド プロシージャを別のストアド プロシージャ内から実行することで、ネストされたストアド プロシージャを使用できます。

ストアド プロシージャは、結果セット、つまりステートメントの結果を返す場合がありますSELECT。このような結果セットは、カーソルを使用して、他のストアド プロシージャによって、結果セット ロケーターを関連付けることによって、またはアプリケーションによって処理できます。ストアド プロシージャには、データを処理するための宣言された変数や、テーブル内の複数の行をループできるようにするカーソルも含まれる場合があります。ストアド プロシージャのフロー制御ステートメントにはIF、通常WHILE、、、、、ステートメントなどが含まれます。ストアド プロシージャは、変数の宣言方法と場所に応じて、変数を受け取り、結果を返したり、変数を変更して返したりできます LOOP。REPEATCASE

ストアドプロシージャはユーザー定義関数(UDF)に似ています。主な違いは、UDF は SQL ステートメント内の他の式と同じように使用できるのに対し、ストアドプロシージャはCALLステートメントを使用して呼び出す必要があることです。^[1]

CALL プロシージャ(...)

または

EXECUTE プロシージャ(...)

ストアド プロシージャの正確で正しい実装は、データベース システムによって異なります。ほとんどの主要データベース ベンダーは、何らかの形でストアド プロシージャをサポートしています。データベース システムに応じて、ストアド プロシージャは、SQL、Java、C、C++など、さまざまなプログラミング言語で実装できます。非 SQL 言語で記述されたストアド プロシージャは、SQL ステートメント自体を実行する場合と実行しない場合があります。

ストアド プロシージャの採用が進むにつれて、 SQL:1999およびSQL:2003標準のSQL/PSM の部分にSQL 言語の手続き型要素が導入されました。これにより、SQL は命令型プログラミング言語になりました。ほとんどのデータベース システムは、SQL/PSM を超える独自のベンダー固有の拡張機能を提供しています。SQL /JRTだけでなく、Java ストアド プロシージャの標準仕様も存在します。

オーバーヘッド

ストアド プロシージャ ステートメントはデータベースに直接格納されるため、ソフトウェア アプリケーションがインライン (動的) SQL クエリをデータベースに送信するときに通常必要となるコンパイル オーバーヘッドの全部または一部が削除される可能性があります(ただし、ほとんどのデータベース システムでは、ステートメントキャッシュやその他のメソッドを実装して、動的 SQL ステートメントを繰り返しコンパイルしないようにしています)。また、一部のプリコンパイル済み SQL は回避されますが、SQL ステートメントのすべての引数がコンパイル時に提供されるわけではないため、ステートメントによって最適な実行プランの作成が複雑になります。特定のデータベース実装と構成によっては、ストアド プロシージャと汎用クエリまたはユーザー定義関数とでパフォーマンス結果が異なる場合があります。

ネットワークトラフィックの回避

ストアド プロシージャの主な利点は、データベース エンジン内で直接実行できることです。実稼働システムでは、これは通常、プロシージャがデータに直接アクセスする専用のデータベース サーバー上で完全に実行されることを意味します。利点は、ネットワーク コストを節約できることです。これは、一連の SQL ステートメントが関係する場合に顕著です。

ビジネスロジックのカプセル化

ストアド プロシージャを使用すると、プログラマーはビジネス ロジックをAPI としてデータベースに埋め込むことができるため、データ管理が簡素化され、クライアント プログラムの他の場所でロジックをエンコードする必要性が減ります。これにより、クライアント プログラムの障害によるデータ破損の可能性が低くなります。データベース システムは、ストアド プロシージャを利用してデータの整合性と一貫性を確保できます。

アクセス権の委任

多くのシステムでは、ストアド プロシージャに、そのプロシージャを実行するユーザーが直接持っていないデータベースへのアクセス権を付与できます。

SQLインジェクション攻撃からの保護

ストアド プロシージャを使用すると、インジェクション攻撃から保護できます。ストアド プロシージャのパラメータは、攻撃者が SQL コマンドを挿入した場合でもデータとして扱われます。また、一部の DBMS ではパラメータの型がチェックされます。ただし、入力を使用して動的 SQL を生成するストアド プロシージャは、適切な予防策を講じない限り、依然として SQL インジェクションに対して脆弱です。

一部のシステムでは、ストアド プロシージャを使用してトランザクション管理を制御できます。また、他のシステムでは、ストアド プロシージャはトランザクション内で実行されるため、トランザクションはストアド プロシージャに対して実質的に透過的です。ストアド プロシージャは、データベース トリガーまたは条件ハンドラーから呼び出すこともできます。たとえば、ストアド プロシージャは、特定のテーブルへの挿入またはテーブル内の特定のフィールドの更新によってトリガーされ、ストアド プロシージャ内のコードが実行されます。ストアド プロシージャを条件ハンドラーとして記述すると、データベース管理者は、ストアド プロシージャを使用してエラーをキャッチし、データベースまたはファイルなどの外部リソースに監査情報を記録することで、システム内のエラーをより詳細に追跡できます。

• 関数は、特定の計算を実行するために作成されたサブプログラムです。
• スカラー関数は 1 つの値 (または NULL) のみを返しますが、テーブル関数は 0 個以上の行 (各行には 1 つ以上の列が含まれます) で構成される (リレーショナル) テーブルを返します。
• 関数は値を返す必要があります (RETURNキーワードを使用) が、ストアド プロシージャの場合、これは必須ではありません。
• ストアド プロシージャではRETURNキーワードを使用できますが、値は渡されません。
• SELECT関数は、データ操作を行わない限り、ステートメント内で使用できます。ただし、プロシージャをSELECTステートメントに含めることはできません。
• ストアド プロシージャは、OUTパラメーターを使用して複数の値を返したり、値を返さなかったりすることができます。
• ストアド プロシージャを使用すると、クエリのコンパイル時間が節約されます。
• ストアド プロシージャはデータベース オブジェクトです。
• ストアド プロシージャはマテリアル オブジェクトです。

準備済みステートメントは、通常のステートメントまたはクエリを受け取り、それをパラメータ化して、後で異なるリテラル値を使用できるようにします。ストアド プロシージャと同様に、準備済みステートメントは効率性のためにサーバー上に保存され、SQL インジェクション攻撃からある程度保護されます。準備済みステートメントはより単純で宣言的ですが、通常は手続き型ロジックを使用するようには記述されず、変数を操作することはできません。準備済みステートメントはインターフェイスが単純でクライアント側の実装が簡単なため、DBMS 間でより広く再利用できます。

スマート コントラクトは、RDBMS ではなくブロックチェーンに保存される実行可能コードに適用される用語です。パブリック ブロックチェーン ネットワークの実行結果のコンセンサス メカニズムは、従来のプライベート データベースやフェデレーション データベースとは原理的に異なりますが、通常は価値取引の感覚を持ちながらも、表面上はストアド プロシージャと同じ機能を実行します。

• ストアド プロシージャ言語は、多くの場合、ベンダー固有です。データベース ベンダーを変更するには、通常、既存のストアド プロシージャを書き直す必要があります。
• ストアド プロシージャへの変更は、他のコードよりもバージョン管理システム内で追跡するのが困難です。変更をプロジェクト履歴に保存するには、スクリプトとして再現する必要があり、プロシージャの違いを正しくマージして追跡するのが困難になることがあります。
• ストアド プロシージャのエラーは、アプリケーション IDE のコンパイルまたはビルド ステップの一部として検出できません。ストアド プロシージャが失われたり、誤って削除された場合も同様です。
• 異なるベンダーのストアド プロシージャ言語は、洗練度のレベルが異なります。
• ストアド プロシージャの作成とデバッグのためのツール サポートは、他のプログラミング言語ほど充実していないことがよくありますが、これはベンダーや言語によって異なります。
  • たとえば、PL/SQL と T-SQL にはそれぞれ専用の IDE とデバッガーがあります。PL/PgSQL はさまざまな IDE からデバッグできます。

• MySQL のストアド プロシージャに関する FAQ
• PostgreSQL手続き型言語サポートの概要
• Sybase ASE でのストアド プロシージャの使用
• PL/SQL プロシージャ
• Oracle データベース PL/SQL 言語リファレンス
• SAP HANA SQLScript リファレンス