###
  • インターネット
  • プログラム
  • ゲーム
  • 問題点
  • ウィンドウズ
  • ソーシャルメディア
  • アンドロイド
  • iOS

    • Ms SQL ストアド プロシージャ メッセージの説明。 特別なクエリとストアド プロシージャのどちらが優れていますか? ストアド プロシージャの作成、変更、削除

    30.10.2019 問題点

    ストアド プロシージャの概念が定義されています。 パラメーターを使用したスト​​アド プロシージャの作成、変更、および使用の例を示します。 入力パラメータと出力パラメータの定義が示されています。 ストアド プロシージャの作成と呼び出しの例が示されています。

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

    ストアドプロシージャ相互接続された SQL ステートメントのグループであり、これを使用するとプログラマの作業がより簡単かつ柔軟になります。 ストアドプロシージャ多くの場合、一連の個別の SQL ステートメントよりもはるかに単純です。 ストアド プロシージャは、1 つ以上の SQL ステートメントまたは関数で構成され、コンパイルされた形式でデータベースに保存される一連のコマンドです。 データベース内での実行 ストアドプロシージャ個々の SQL ステートメントの代わりに、ユーザーには次の利点があります。

    • 必要な演算子はすでにデータベースに含まれています。
    • 彼らは全員ステージを通過しました 解析する実行可能形式です。 前に ストアド プロシージャの実行 SQL Server は実行プランを生成し、最適化とコンパイルを実行します。
    • ストアドプロシージャサポート モジュール式プログラミング大きなタスクを、独立した、より小さく、管理しやすい部分に分割できるためです。
    • ストアドプロシージャ他の人に影響を与える可能性があります ストアドプロシージャおよび機能。
    • ストアドプロシージャ他の種類のアプリケーション プログラムから呼び出すことができます。
    • いつもの、 ストアドプロシージャ個々のステートメントのシーケンスよりも高速に実行されます。
    • ストアドプロシージャ使いやすい: コマンドは数十または数百のコマンドで構成できますが、実行するには目的のコマンドの名前を指定するだけです。 ストアドプロシージャ。 これにより、クライアントからサーバーに送信されるリクエストのサイズが軽減され、ネットワークの負荷が軽減されます。

    プロシージャを実行する場所と同じ場所に保存すると、ネットワーク上で転送されるデータ量が削減され、システム全体のパフォーマンスが向上します。 応用 ストアドプロシージャソフトウェア システムのメンテナンスと変更が簡素化されます。 通常、ルールとデータ処理アルゴリズムの形式のすべての整合性制約はデータベース サーバーに実装され、セットとしてエンド アプリケーションで利用できます。 ストアドプロシージャ、データ処理インターフェイスを表します。 データの整合性を確保するため、またセキュリティ目的のため、アプリケーションは通常、データへの直接アクセスを受け取りません。データを扱うすべての作業は、特定のメソッドを呼び出すことによって実行されます。 ストアドプロシージャ.

    このアプローチにより、データ処理アルゴリズムの変更が非常に簡単になり、すべてのネットワーク ユーザーがすぐに利用できるようになり、アプリケーション自体に変更を加えずにシステムを拡張できるようになります。 ストアドプロシージャデータベースサーバー上で。 開発者は、アプリケーションを再コンパイルしたり、そのコピーを作成したり、新しいバージョンで作業するようにユーザーに指示したりする必要はありません。 ユーザーは、システムに変更が加えられたことにさえ気づいていない可能性があります。

    ストアドプロシージャテーブルや他のデータベース オブジェクトとは独立して存在します。 これらはクライアント プログラムによって呼び出されます。 ストアドプロシージャまたはトリガー。 開発者はアクセス権を管理できます。 ストアドプロシージャ、その実行を許可または禁止します。 コードを変更する ストアドプロシージャ所有者または固定データベース ロールのメンバーのみが許可されます。 必要に応じて、その所有権をあるユーザーから別のユーザーに譲渡できます。

    MS SQL Server環境のストアドプロシージャ

    SQL Server を使用する場合、ユーザーは特定のアクションを実装する独自のプロシージャを作成できます。 ストアドプロシージャは本格的なデータベース オブジェクトであるため、それぞれが特定のデータベースに保存されます。 直接電話 ストアドプロシージャプロシージャが配置されているデータベースのコンテキストで実行される場合にのみ可能です。

    ストアド プロシージャの種類

    SQL Serverにはいくつかの種類があります ストアドプロシージャ.

    • システム ストアドプロシージャさまざまな管理アクションを実行するように設計されています。 ほとんどすべてのサーバー管理活動は、彼らの助けを借りて実行されます。 システム的に言えることは、 ストアドプロシージャはシステム テーブルの操作を提供するインターフェイスであり、最終的にはユーザー データベースとシステム データベースの両方のシステム テーブルからのデータの変更、追加、削除、取得を行います。 システム ストアドプロシージャ sp_ プレフィックスが付いており、システム データベースに保存され、他のデータベースのコンテキストで呼び出すことができます。
    • カスタム ストアドプロシージャ特定のアクションを実行します。 ストアドプロシージャ– 本格的なデータベース オブジェクト。 その結果、それぞれの ストアドプロシージャ実行される特定のデータベースに配置されます。
    • 一時的 ストアドプロシージャ一時的にのみ存在し、その後サーバーによって自動的に破棄されます。 それらはローカルとグローバルに分けられます。 ローカル一時的 ストアドプロシージャ作成された接続からのみ呼び出すことができます。 このようなプロシージャを作成するときは、単一​​の # 文字で始まる名前を付ける必要があります。 すべての一時的なオブジェクトと同様に、 ストアドプロシージャこのタイプのファイルは、ユーザーが切断されるか、サーバーが再起動または停止されると自動的に削除されます。 グローバルテンポラリ ストアドプロシージャ同じ手順を持つサーバーからのすべての接続で利用できます。 これを定義するには、文字 ## で始まる名前を付けるだけです。 これらのプロシージャは、サーバーが再起動または停止されるとき、またはプロシージャが作成されたコンテキスト内の接続が閉じられるときに削除されます。

    ストアド プロシージャの作成、変更、削除

    創造 ストアドプロシージャ次の問題を解決する必要があります。

    • 作成されたタイプの決定 ストアドプロシージャ: 一時的またはカスタム。 さらに、独自のシステムを作成することもできます ストアドプロシージャ、sp_ という接頭辞を付けた名前を付けて、システム データベースに配置します。 この手順は、任意のローカル サーバー データベースのコンテキストで使用できます。
    • アクセス権の計画。 作成中 ストアドプロシージャデータベース オブジェクトに対して、それを作成したユーザーと同じアクセス権を持つことを考慮する必要があります。
    • 意味 ストアド プロシージャのパラメータ。 ほとんどのプログラミング言語に含まれるプロシージャと同様に、 ストアドプロシージャ入力パラメータと出力パラメータがある場合があります。
    • コード開発 ストアドプロシージャ。 プロシージャ コードには、他の SQL コマンドの呼び出しを含む、一連の SQL コマンドを含めることができます。 ストアドプロシージャ.

    新しいものを作成し、既存のものを変更する ストアドプロシージャ次のコマンドを使用して実行します。

    <определение_процедуры>::= (CREATE | ALTER ) PROC プロシージャ名 [;番号] [(@パラメータ名 データ型 ) [=デフォルト] ][,...n] AS sql_operator [...n]

    このコマンドのパラメータを見てみましょう。

    接頭辞 sp_ 、 # 、 ## を使用すると、作成されたプロシージャをシステムまたは一時的なプロシージャとして定義できます。 コマンド構文からわかるように、作成されたプロシージャを所有する所有者の名前や、プロシージャを配置するデータベースの名前を指定することはできません。 したがって、作成したものを配置するには、 ストアドプロシージャ特定のデータベースでは、そのデータベースのコンテキストで CREATE PROCEDURE コマンドを発行する必要があります。 本体から回転する場合 ストアドプロシージャ短縮名は、同じデータベースのオブジェクトに対して、つまりデータベース名を指定せずに使用できます。 他のデータベースにあるオブジェクトにアクセスする必要がある場合は、データベース名の指定が必須です。

    名前の数字は識別番号です ストアドプロシージャ、これによりプロシージャのグループ内で一意に識別されます。 管理を容易にするために、プロシージャは論理的に同じタイプになります。 ストアドプロシージャ同じ名前で異なる識別番号を与えることでグループ化できます。

    作成した入出力データを転送するには ストアドプロシージャパラメータを使用できますが、その名前はローカル変数の名前と同様に @ 記号で始まる必要があります。 1つ ストアドプロシージャ複数のパラメータをカンマで区切って指定できます。 プロシージャの本体では、このプロシージャのパラメータの名前と名前が一致するローカル変数を使用しないでください。

    対応するデータ型を判断するには、 ストアド プロシージャのパラメータ、ユーザー定義のものを含むあらゆる SQL データ型が適しています。 ただし、CURSOR データ型は次のようにのみ使用できます。 出力パラメータ ストアドプロシージャ、つまり OUTPUT キーワードを指定します。

    OUTPUT キーワードの存在は、対応するパラメータがデータを返すことを意図していることを意味します。 ストアドプロシージャ。 ただし、これは、パラメーターが値を渡すのに適していないという意味ではありません。 ストアドプロシージャ。 OUTPUT キーワードを指定すると、サーバーに終了が指示されます。 ストアドプロシージャパラメータの現在の値を、プロシージャの呼び出し時にパラメータの値として指定したローカル変数に割り当てます。 OUTPUT キーワードを指定する場合、プロシージャを呼び出すときの対応するパラメーターの値は、ローカル変数を使用してのみ設定できることに注意してください。 通常のパラメータに許可されている式や定数は許可されません。

    VARYING キーワードは、CURSOR 型の OUTPUT パラメータと組み合わせて使用​​されます。 それは次のことを決定します 出力パラメータ結果セットが存在します。

    DEFAULT キーワードは、対応する値を表します。 デフォルトパラメータ。 したがって、プロシージャを呼び出すときに、対応するパラメータの値を明示的に指定する必要はありません。

    サーバーはクエリ実行プランとコンパイルされたコードをキャッシュするため、次回プロシージャが呼び出されるときは、既製の値が使用されます。 ただし、場合によっては、プロシージャ コードを再コンパイルする必要がある場合もあります。 RECOMPILE キーワードを指定すると、システムに実行計画を作成するように指示されます。 ストアドプロシージャ彼女が電話するたびに。

    FOR REPLICATION パラメータは、データをレプリケートし、作成されたデータを有効にする場合に必要です。 ストアドプロシージャ出版用の記事として。

    ENCRYPTION キーワードは、サーバーにコードを暗号化するように指示します。 ストアドプロシージャ、作業を実装する独自のアルゴリズムの使用に対する保護を提供できます。 ストアドプロシージャ.

    AS キーワードは本文自体の先頭に配置されます ストアドプロシージャ、つまり これまたはそのアクションを実装するための一連の SQL コマンド。 プロシージャの本体では、ほぼすべての SQL コマンドを使用でき、トランザクションを宣言したり、ロックを設定したり、その他のコマンドを呼び出すことができます。 ストアドプロシージャ。 から出る ストアドプロシージャ RETURNコマンドを使用して実行できます。

    ストアド プロシージャの削除次のコマンドによって実行されます。

    DROP PROCEDURE (プロシージャ名) [,...n]

    ストアド プロシージャの実行

    のために ストアド プロシージャを実行する使用されるコマンドは次のとおりです。

    [[ EXEC [ UTE] プロシージャ名 [;番号] [[@パラメータ名=](値 | @変数名) |][,...n]

    電話の場合 ストアドプロシージャパッケージ内の唯一のコマンドではないため、EXECUTE コマンドが存在する必要があります。 さらに、このコマンドは、別のプロシージャまたはトリガーの本体からプロシージャを呼び出すために必要です。

    プロシージャを呼び出すときの OUTPUT キーワードの使用は、次のときに宣言されたパラメータに対してのみ許可されます。 プロシージャの作成 OUTPUT キーワードを使用します。

    プロシージャを呼び出すときにパラメータにキーワード DEFAULT が指定されている場合、それが使用されます。 デフォルト値。 当然のことながら、指定された単語 DEFAULT は、それが定義されているパラメーターに対してのみ許可されます。 デフォルト値.

    EXECUTE コマンドの構文は、プロシージャを呼び出すときにパラメータ名を省略できることを示しています。 ただし、この場合、ユーザーはパラメータの値を、パラメータの値を指定するときにリストされたのと同じ順序で指定する必要があります。 プロシージャの作成。 パラメータへの割り当て デフォルト値、出品時にスキップすることはできません。 定義されているパラメータを省略したい場合 デフォルト値を呼び出すときにパラメータ名を明示的に指定するだけで十分です。 ストアドプロシージャ。 さらに、この方法では、パラメータとその値を任意の順序でリストできます。

    プロシージャを呼び出すときは、値を含むパラメータ名、またはパラメータ名のない値のみを指定することに注意してください。 これらを組み合わせることはできません。

    例12.1。 パラメータのないプロシージャ。 イワノフが購入した商品の名前と価格を取得する手順を作成します。

    CREATE PROC my_proc1 AS SELECT Product.Name、Product.Price*Transaction.Quantity AS Cost、Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE顧客 .Last name='イワノフ' 例12.1。 イワノフが購入した商品の名前と価値を取得する手順。

    のために 手続きへのアクセス次のコマンドを使用できます。

    EXEC my_proc1 または my_proc1

    このプロシージャはデータセットを返します。

    例12.2。 パラメータのないプロシージャ。 第一級品の価格を10％引き下げる手順を作成します。

    のために 手続きへのアクセス次のコマンドを使用できます。

    EXEC my_proc2 または my_proc2

    このプロシージャはデータを返しません。

    例12.3。 入力パラメータを使用したプロシージャ。 特定の顧客が購入した商品の名前と価格を取得するプロシージャを作成します。

    CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Product.Name、Product.Price*Transaction.Quantity AS Cost、Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode) ON Customer。 CustomerCode =Transaction.ClientCode WHERE Client.LastName=@k 例12.3。 特定の顧客が購入した商品の名前と価格を取得する手順。

    のために 手続きへのアクセス次のコマンドを使用できます。

    EXEC my_proc3 "イワノフ" または my_proc3 @k="イワノフ"

    例12.4。。 指定されたタイプの製品の価格を、指定された%に従って値下げするプロシージャを作成します。

    のために 手続きへのアクセス次のコマンドを使用できます。

    EXEC my_proc4 "ワッフル",0.05 または EXEC my_proc4 @t="ワッフル", @p=0.05

    例12.5。 入力パラメータを使用したプロシージャおよびデフォルト値。 指定されたタイプの製品の価格を、指定された%に従って値下げするプロシージャを作成します。

    CREATE PROC my_proc5 @t VARCHAR(20)=’Candy`, @p FLOAT=0.1 AS UPDATE Product SET Price=Price*(1-@p) WHERE Type=@t 例12.5。 入力パラメータとデフォルト値を使用したプロシージャ。 指定されたタイプの製品の価格を、指定された%に従って値下げするプロシージャを作成します。

    のために 手続きへのアクセス次のコマンドを使用できます。

    EXEC my_proc5 "ワッフル",0.05 または EXEC my_proc5 @t="ワッフル", @p=0.05 または EXEC my_proc5 @p=0.05

    この場合、キャンディーの価格が減額されます (プロシージャを呼び出すときに型の値は指定されず、デフォルトで使用されます)。

    後者の場合、プロシージャを呼び出すときに両方のパラメータ (タイプとパーセンテージの両方) が指定されず、それらの値がデフォルトで取得されます。

    例12.6。 入力パラメータと出力パラメータを使用したプロシージャ。 特定の月に販売された商品の総原価を決定するプロシージャを作成します。

    CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Product.Price*Transaction.Quantity) FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Month(Transaction.Date) HAVING Month(トランザクション.日付)=@m 例12.6。 入力パラメータと出力パラメータを使用するプロシージャ。 特定の月に販売された商品の総原価を決定するプロシージャを作成します。

    のために 手続きへのアクセス次のコマンドを使用できます。

    DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

    このコマンド ブロックを使用すると、1 月に販売された商品の原価を決定できます ( 入力パラメータ月は 1) として指定されます。

    特定の従業員が勤務する会社が購入した商品の合計数量を決定する手順を作成します。

    まず、従業員が勤務する会社を決定する手順を作成します。

    例12.7。使用法 ネストされたプロシージャ。 特定の従業員が勤務する会社が購入した商品の合計数量を決定する手順を作成します。

    次に、関心のある会社が購入した商品の合計数量を計算するプロシージャを作成します。

    CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Transaction.Quantity) FROM Client INNER JOIN Transaction ON Client.ClientCode= Transaction.ClientCode GROUP BY Client.Firm HAVING Client.Company=@firm 例12.7。 特定の従業員が勤務する会社が購入した商品の合計数量を決定する手順を作成します。

    このプロシージャは、次のコマンドを使用して呼び出されます。

    DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k OUTPUT SELECT @k

    22 件の回答

    主に WinForms クライアント/サーバー アプリケーションを作成した私の経験から、次のような簡単な結論に達しました。

    ストアド プロシージャを使用します。

    • データを扱うあらゆる複雑な作業に。 実際にカーソル テーブルや一時テーブルが必要な操作を実行する場合、通常、これが SQL Server で最も速い方法です。
    • データへのアクセスをブロックする必要がある場合。 ユーザー (またはロールなど) にテーブルへのアクセスを許可しない場合、データを操作する唯一の方法は、作成した SP を介することになります。

    特別なクエリを使用します。

    • データ アクセスを制限する必要がない (または別の方法で制限する) 場合の CRUD の場合。
    • 簡単な検索用。 複数の検索条件に対して SP を作成するのは面倒であり、保守も困難です。 検索クエリを十分に高速に作成できる場合は、それを使用してください。

    私はほとんどのアプリケーションで SP とアドホック SQL の両方を使用してきましたが、SP は最終的にはコード (C# など) であり、制御、テスト、保守が難しいため、SP を使用することはますます少なくなってきています。 特別な理由が見つからない場合は、アドホック SQL を使用することをお勧めします。

    ストアド プロシージャは、データベースに対して実行されるアクションをカプセル化するソフトウェア コントラクトです。 プロシージャ内のコード、さらにはデータベース スキーマ自体も、コンパイルおよびデプロイされたコードに影響を与えることなく変更できるため、プロシージャの入力と出力は変更されません。

    アプリケーションにクエリを組み込むことで、データ モデルと緊密に結び付きます。

    同じ理由で、データベース内のすべてのテーブルに対する CRUD クエリであるストアド プロシージャを単純に作成することも、依然として密結合であるため、推奨されません。 その代わりに、手順は煩雑で粒度が粗くなければなりません。

    これが、データベースを保守する必要がある人々とユーザー インターフェイスを開発する人々の間の主な対立だと思います。

    データ担当者として、アドホック クエリを介してアクセスされるデータベースを扱うことは考えません。効果的な構成や管理が難しいからです。 スキーマの変更に何が影響するかをどうやって知ることができますか? また、セキュリティ上の理由から、ユーザーにデータベース テーブルへの直接アクセスを与えるべきではないと思います (SQL インジェクション攻撃だけを意味するわけではなく、直接権限を許可せず、すべてのユーザーにアクセスを要求する基本的な内部制御であるためでもあります)不正行為の可能性を防ぐために、アプリケーション用に設計されたプロシージャのみを使用すること。テーブルに対するアクセス許可を直接挿入、更新、または削除できる金融システムには、不正行為が発生する大きなリスクがあります。

    データベースはオブジェクト指向ではないため、オブジェクト指向の観点からは優れているように見えるコードも、データベースの観点からは非常に悪い場合があります。

    当社の開発者は、すべてのデータベース アクセスが proc を通じて行われることをうれしく思っています。これにより、コードの新しいブランチを作成して再コンパイルして再起動するよりも、データ依存のエラーを修正し、実稼働環境で proc を実行する方がはるかに速くなるためです。生産に入ります。 私たちはすべてのプロセスが破壊的であることを要求しているため、ソース管理はまったく問題になりません。 Subversion にない場合は、dbas によって定期的に削除されるため、ソース管理を使用することに抵抗はありません。

    ストアド プロシージャは確かに適しています。ストアド プロシージャはコンパイルされ、実行前に実行計画があり、権限管理を行うことができます。

    ストアド プロシージャのソース コードに関するこの問題がわかりません。 少し規律を持ってさえいれば、間違いなくそれらを制御することができます。

    常にストアド プロシージャのソースである .sql ファイルから始めてください。 コードを書いた後は、バージョン管理に入れてください。 次回ストアド プロシージャを編集するときは、データベースではなく元のコントロールからストアド プロシージャを取得します。 これに従えば、コードと同じくらい優れた制御ソースが得られます。

    ここで Oracle の Tom Kyte の言葉を引用したいと思います...コードをどこに書くかに関する彼のルールは次のとおりです...少し関係ありませんが、よく知られていると思います。

    私たちのアプリケーションには、リクエストのコンテンツ (場合によってはストアド プロシージャ呼び出し) を提供するコード層があります。 これにより、次のことが可能になります。

    • バージョン管理時にすべてのリクエストを簡単に取得できる
    • 異なるデータベースサーバーのクエリごとにすべての変更を加える
    • コードを通じて同じリクエストコードの繰り返しを排除します

    アクセス制御はデータベースではなく中間層に実装されるため、ストアド プロシージャは必要ありません。 これは、アドホック クエリとストアド プロシージャの中間のようなものです。

    どちらのストアド プロシージャも中央リポジトリにあるものの、移植が (潜在的に) 難しいという説得力のある議論があります。また、アドホック クエリはコード内にあるためデバッグが簡単ですが、コード内で見つけるのが難しい場合もあります。

    ストアド プロシージャの方が効率的であるという議論は、もはや骨抜きになりません。 リンクテキスト

    ストアド プロシージャと動的クエリについて Google で検索すると、どちらの方法でも適切な引数が表示されるため、おそらく自分で決定する方が良いでしょう...

    考慮すべき点: とにかく、ストアド プロシージャが必要なのは誰ですか?

    これは明らかにユーザー自身のニーズと好みの問題ですが、公開環境でアドホック リクエストを使用する場合は安全性があることを考慮することが重要です。 常にパラメータ化して、SQL インジェクションなどの一般的な脆弱性に注意してください。

    ストアド プロシージャは、再コンパイルせずに変更できるため、優れています。 できるだけ頻繁に使用するように努めます。

    ユーザー入力に基づいて動的に生成されるクエリにアドホックを使用しています。

    他の人が述べた理由で Proc を使用することもできますが、プロファイラーまたは proc パーツを使用して proc を構成するのも簡単です。 こうすることで、SQL サーバーに何が送信されているかを知るためにアプリケーションの実行について誰かに話す必要がなくなります。

    アドホックリクエストを使用している場合は、パラメータ化されていることを確認してください

    パラメータ化された SQL または SPROC... パフォーマンスの点では重要ではありません... どちらかの最適化を要求できます。

    私にとって、SPROC に残っている最後の利点は、sproc を実行するためのログイン権限を付与するだけで、多くの SQL 権限管理を省略できることです。パラメータ化された SQL を使用する場合、接続文字列に関連付けられたログインにはさらに多くの権限が与えられます。 (たとえば、アクセス権のあるテーブルの 1 つに任意の種類の選択ステートメントを書き込む)。

    私は依然としてパラメータ化された SQL を好みますが...

    sproc のパフォーマンスに関する議論には議論の余地があります。上位 3 つの RDBM はしばらくの間、クエリ プラン キャッシュを使用します。 それは文書化されています... それとも 1995 年でしょうか?

    ただし、SQL をアプリケーションに埋め込むこともひどい設計です。コードのメンテナンスは、多くの人にとって欠落している概念のようです。

    ORM を使用してアプリケーションを最初から開始できる場合 (グリーン ボックス アプリケーションは決して少数ではありません!)、クラス モデルが DB モデルを駆動し、時間を節約できるため、これは優れた選択です。

    ORM フレームワークが使用できない場合は、必要に応じて SQL 文字列を検索するために SQL リソース XML ファイルを作成するハイブリッド アプローチを使用しました (これらはリソース フレームワークによってキャッシュされます)。 SQL でコード内で行われる小規模な操作が必要な場合、つまり主要な SQL 文字列操作が必要な場合は、このアプローチを再考します。

    このハイブリッド アプローチにより、開発者は管理が容易になり (私のチームはクエリ プランを読めるほど聡明なため、少数派かもしれません)、デプロイは SVN から簡単にチェックアウトできます。 さらに、SQL リソース ファイルを置き換えるだけで、RDBM の切り替えが簡単になります (もちろん、ORM ツールほど簡単ではありませんが、レガシー システムやサポートされていないデータベースでも動作します)。

    私の経験では、クエリやストアド プロシージャの 90% はまったく作成すべきではありません (少なくとも手動では)。

    データ アクセスは何らかの方法で自動的に生成される必要があります。 プロシージャをコンパイル時に静的に生成するか、実行時に動的に生成するかを決定できますが、テーブル (オブジェクト プロパティ) に列を追加する場合は、1 つのファイルを変更するだけで済みます。

    すべてのデータを保存したい アクセスデータ アクセス層が直接 SQL クエリを実行するプログラム コード内。 一方、ロジックは、 管理、トリガー、ストアドプロシージャ、ユーザー定義関数などの形式でデータベースに配置しました。 私がデータベースに価値があると考えるものの例は、データ生成です。クライアントに FirstName と LastName があるとします。 UI には、重要なロジックから派生した DisplayName が必要になりました。 この世代では、行 (または他のソース データ) が更新されるたびにトリガーによって実行されるストアド プロシージャを作成します。

    データ アクセス層はデータベースであり、データとデータ アクセスに関連するすべての処理がそこで行われるという誤解がややありがちです。 それは間違っていますが、この考えから生まれたプロジェクトはたくさんあります。 おそらくこれは局所的な現象です。

    SQL Server でストアド プロシージャを使用する必要があるのはどのような場合であり、ビューを使用する必要があるのはどのような場合ですか?

    権限を使用すると、パラメータを渡すことができる動的クエリを作成できます。

    どちらが最も速いですか?また、他のものより速いのはどのような根拠に基づいていますか?

    ビューまたはストアド プロシージャはメモリを永続的に保持しますか?

    ビューが仮想テーブルを作成し、プロシージャが材料テーブルを作成すると誰かが言ったら、それは何を意味しますか?

    また何かポイントがあれば教えてください。

    「ストアドプロシージャとビューの違いは何ですか？」の解決策をWebから集めました。

    景色は バーチャルテーブル。 ビュー内の複数のテーブルを結合し、そのビューを使用して、データが単一のテーブルからのものであるかのようにデータを表示できます。

    ストアド プロシージャはパラメータを使用して、データの更新や挿入、個々の値やデータ セットの返しなどの関数を実行します。

    ビューとストアド プロシージャの作成 - ビューとストアド プロシージャを使用する時期と理由に関する Microsoft からの情報が含まれています。

    2 つのテーブルがあるとします。

    tbl_user 列: .user_id、.user_name、.user_pw

    tbl_profile 列: .profile_id、.user_id .profile_description

    したがって、これらの大量のテーブルからクエリを実行する場合は、すべての SQL ピースで結合を行う代わりに、次のようなビューを定義します。

    CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO

    したがって、将来的にユーザーIDでprofile_descriptionをクエリしたい場合は...私がしなければならないことは次のとおりです

    SELECT プロファイルの説明 FROM vw_user_profile WHERE user_id = @ID

    このコードはストアド プロシージャで使用できます。例:

    プロシージャの作成 dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO

    後で電話できるように

    Dbo.getDesc 25

    ユーザー ID 25 の説明を取得します。25 はパラメータです。

    もちろん他にもたくさんありますが、それは基本的な考え方にすぎません。

    まず、両方が異なるものであることを理解する必要があります。 ストアド プロシージャは、INSERT-UPDATE-DELETE ステートメントに最適に使用されます。 ビューは SELECT ステートメントに使用されます。 そして両方を使用する必要があります。

    ビューではデータを変更できません。

    ビュー: これは、異なる実際のデータベース テーブルの 1 つ以上の行と列で構成される仮想テーブルです。 これは、複数のテーブルの行と列のパターンです。 ここではパラメータを渡すことはできません。

    ストアド プロシージャ: ストアド プロシージャは、パラメータを入力として送信し、出力を受信できる、事前に実行された SQL ステートメントのセットです。

    ビューはストアド プロシージャ内で使用できますが、ストアド プロシージャをビュー内で使用することはできません...!

    ストア プロシージャは、単純な SQL だけでは不十分な場合に使用されます。 ストア プロシージャには、変数、ループ、および他のストアド プロシージャへの呼び出しが含まれます。 これはプログラミング言語であり、クエリ言語ではありません。

      ビューは静的です。 これらは特定のレイアウトを持つ新しいテーブルと考えてください。テーブル内のデータは、作成したクエリを使用してオンザフライで作成されます。 他の SQL テーブルと同様に、 WHERE 、 GROUP BY 、および ORDER BY を使用して並べ替えやフィルター処理を行うことができます。

      それはあなたが何をしているかによって異なります。

      それはデータベースによって異なります。 単純なビューは、クエリを実行して結果をフィルターするだけです。 ただし、Oracle などのデータベースでは、「マテリアライズド」ビューを作成できます。これは基本的に、ビューの基礎となるデータが変更されると自動的に更新されるテーブルです。

      マテリアライズド ビューを使用すると、ビュー列 (特にデータベースのどこにも存在しない計算列) にインデックスを作成できます。

      何を言っているのか分かりません。

    主な違いは、ビューをリクエストすると、その定義がリクエストに挿入されることです。 このプロシージャはクエリ結果を生成することもできますが、コンパイルはすぐに行われます。 もう 1 つのオプションはインデックス付きビューです。

    SQL ビューは、SQL SELECT クエリに基づく仮想テーブルです。 ビューは、1 つ以上の既存のデータベース テーブルまたは他のビューを参照します。 これはデータベースのスナップショットですが、ストアド プロシージャは単一の実行プランにコンパイルされた Transact-SQL ステートメントのグループです。

    ビューはデータベース テーブルに格納されているデータの単純な表示ですが、ストアド プロシージャは実行可能なステートメントのグループです。

    ストア プロシージャが SQL ステートメントを実行するのに対し、ビューは参照するテーブルのデータを表示するため、ビューが高速になります。

    この記事を参照してください: ビューとストアド プロシージャ。 まさにあなたが探しているもの

    @Patrickの言ったことは正しいですが、他の質問に答えると、ビューはメモリ内に自体を作成し、結合の種類、データ、および集計が行われるかどうかに応じて、かなりビューを消費するものになる可能性があります。

    ストアド プロシージャは、#tmpTable1 のような一時ハッシュ テーブルを使用するか、@tmpTable1 を使用してメモリ内ですべての処理を実行します。 何を言いたいかによる。

    ストアド プロシージャは関数に似ていますが、直接名で呼び出されます。 リクエスト自体内で実際に使用される関数の代わりに。

    明らかに、大量のデータを取得しない限り、ほとんどの場合、メモリ テーブルの方が高速です。

    ビュー内のデータは変更できないという Mahesh の意見は完全に正しいわけではありません。 パトリックの視点から見ると

    CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id

    データを更新できます...例として、次のいずれかを実行できます...

    vw_user_profile を更新します。 user_id=4 の profile_description="Manager" を設定します。

    tbl_profile を更新します。 user_id=4 の profile_description="Manager" を設定します。

    テーブル全体のすべてのフィールドが存在するわけではなく、PROFILE_ID が主キーであり NULL にすることはできないと想定しているため、このビューに INSERT することはできません。 ただし、ビューに INSERT したい場合もあります...

    ...を使用して既存のテーブルのビューを作成しました。

    ジャンクの表示を SELECT * from として作成

    ジャンク (コード、名前) 値 ("glyn"、"Glyn Roberts")、("Mary"、"Maryann Roberts") に挿入します。

    ID>4のジャンクから削除

    この場合、INSERT と DELETE の両方が機能しました

    集計または計算されたフィールドは更新できないことは明らかですが、単なるストレート ビューであるビューは更新可能でなければなりません。

    ビューに複数のテーブルが含まれている場合、挿入や削除はできませんが、ビューが 1 つのテーブルのサブセットである場合は、通常は挿入または削除できます。

    上記のコメントに加えて、View についていくつか注意事項を追加したいと思います。

    1. 表現を使用すると、複雑さを隠すことができます。 5 人がプロジェクトに取り組んでいるが、そのうちの 1 人だけが複雑な結合などのデータベースに長けているシナリオを想像してください。 このようなシナリオでは、他のチーム メンバーが任意の 1 つのテーブルをクエリするときに簡単にクエリできるビューを作成できます。
    2. セキュリティは View によって簡単に実装できます。 仮に私たちが 従業員次のような機密性の高い列を含むテーブル 給料 , SSN番号。 これらの列は、表示を許可されていないユーザーには表示されません。 この場合、テーブル内の承認を必要としない列を選択するビューを作成できます。 名前 , 年齢と脆弱な列 (前述した給与など) を公開することなく、など。 これで、テーブルを直接クエリする権限を削除できるようになりました。 従業員そしてビューに読み取り権限を保存するだけです。 したがって、ビューを使用してセキュリティを実装できます。

    Microsoft SQL Server で独自のアルゴリズムを実装および自動化する ( 計算) ストアド プロシージャを使用できるので、今日はストアド プロシージャがどのように作成、変更、削除されるかについて説明します。

    ただし、その前に、ストアド プロシージャとは何か、そして T-SQL でストアド プロシージャが必要な理由を理解するために、少し理論を説明します。

    注記！ 初心者のプログラマには、T-SQL に関する次の役立つ資料をお勧めします。

    • T-SQL 言語のより詳細な学習については、『The T-SQL Programmer's Path』という書籍を読むことをお勧めします。 Transact-SQL 言語のチュートリアル。

    T-SQL のストアド プロシージャとは何ですか?

    ストアドプロシージャ– これらは、一連の SQL 命令の形式でアルゴリズムを含むデータベース オブジェクトです。 つまり、ストアド プロシージャはデータベース内のプログラムであると言えます。 ストアド プロシージャは、再利用可能なコードをサーバー上に保存するために使用されます。たとえば、特定のアルゴリズム、逐次計算、または複数ステップの SQL ステートメントを作成した場合、このアルゴリズムに含まれるすべての命令を毎回実行しないように、フォーマットすることができます。ストアド プロシージャとして。 同時に、SQL プロシージャを作成すると、サーバーはコードをコンパイルします。その後、この SQL プロシージャを実行するたびに、サーバーはコードを再コンパイルしません。

    SQL Server でストアド プロシージャを実行するには、その名前の前に EXECUTE コマンドを記述する必要があります。このコマンドを EXEC と省略することもできます。 たとえば、SELECT ステートメント内のストアド プロシージャを関数として呼び出すことは機能しなくなります。 手続きは別途開始されます。

    ストアド プロシージャでは、関数とは異なり、UNSERT、UPDATE、DELETE などのデータ変更操作を実行することがすでに可能です。 プロシージャでは、ほぼすべてのタイプの SQL ステートメントを使用することもできます。たとえば、CREATE TABLE でテーブルを作成したり、EXECUTE でテーブルを作成したりできます。 他のプロシージャを呼び出します。 例外は、関数、ビュー、トリガーの作成または変更、スキーマの作成、およびその他の同様の命令など、いくつかの種類の命令です。たとえば、ストアド プロシージャでデータベース接続コンテキスト (USE) を切り替えることもできません。

    ストアド プロシージャは入力パラメータと出力パラメータを持つことができ、表形式のデータを返すことも、何も返さず、そこに含まれる命令のみを実行することもできます。

    ストアド プロシージャは非常に便利で、多くの操作を自動化または簡素化するのに役立ちます。たとえば、ピボット テーブルを使用してさまざまな複雑な分析レポートを常に生成する必要があります。 ピボット演算子。 この演算子を使用してクエリを作成しやすくするため ( ご存知のとおり、PIVOT の構文は非常に複雑です)、概要レポートを動的に生成するプロシージャを作成できます。たとえば、資料「T-SQL の動的 PIVOT」では、この機能をストアド プロシージャの形式で実装する例が示されています。

    Microsoft SQL Server でのストアド プロシージャの使用例

    ソースデータの例

    以下のすべての例は、Microsoft SQL Server 2016 Express で実行されます。 ストアド プロシージャが実際のデータでどのように動作するかを示すには、このデータが必要です。作成しましょう。 たとえば、テスト テーブルを作成し、それにいくつかのレコードを追加してみましょう。それが製品とその価格のリストを含むテーブルであるとします。

    テーブル作成命令 CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- データ追加命令 INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "マウス", 100), (1, "キーボード", 200), (2, "電話", 400) GO --クエリを選択 SELECT * FROM TestTable

    データが揃ったので、ストアド プロシージャの作成に進みましょう。

    T-SQL でのストアド プロシージャの作成 - CREATE PROCEDURE ステートメント

    ストアド プロシージャはステートメントを使用して作成されます プロシージャの作成, この指示の後に、プロシージャの名前を記述し、必要に応じて括弧内に入力パラメータと出力パラメータを定義する必要があります。 この後、キーワード AS を書き込み、キーワード BEGIN で命令のブロックを開き、このブロックを単語 END で閉じます。 このブロック内に、アルゴリズムまたはある種の逐次計算を実装するすべての命令を記述します。つまり、T-SQL でプログラムします。

    たとえば、新しいレコードを追加するストアド プロシージャを作成してみましょう。 新製品がテストテーブルに加わりました。 これを行うには、3 つの入力パラメータを定義します。@CategoryId – 製品カテゴリ識別子、@ProductName – 製品名、@Price – このパラメータはオプションです。 それをプロシージャに渡す必要はありません ( たとえば、価格はまだわかりません)、この目的のために、その定義にデフォルト値を設定します。 これらのパラメータはプロシージャの本体内にあります。 通常の変数と同じように、BEGIN...END ブロック内で使用できます ( ご存知のとおり、変数は @ 記号で表されます。）。 出力パラメータを指定する必要がある場合は、パラメータ名の後にキーワード OUTPUT ( または略してOUT).

    BEGIN...END ブロックには、データを追加する命令と、プロシージャの最後に SELECT 命令を記述します。これにより、ストアド プロシージャは、指定されたカテゴリの製品に関する表形式のデータを返します。追加されたばかりの新しい製品を考慮します。 また、このストアド プロシージャでは、複数のスペースが誤って入力される状況を排除するために、入力パラメータの処理、つまり文字列の先頭と末尾の余分なスペースを削除する処理を追加しました。

    この手順のコードは次のとおりです ( 私もそれについてコメントしました).

    プロシージャの作成 CREATE PROCEDURE TestProcedure (--入力パラメータ @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --アルゴリズムを実装する命令 --受信パラメータの処理 --先頭の余分なスペースを削除そしてテキスト行の最後に SET @ProductName = LTRIM(RTRIM(@ProductName)); -- 新しいレコードを追加します INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) -- データを返します SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

    T-SQL でのストアド プロシージャの実行 - EXECUTE コマンド

    すでに述べたように、EXECUTE または EXEC コマンドを使用してストアド プロシージャを実行できます。 受信パラメータは、それらをリストし、プロシージャ名の後に適切な値を指定するだけでプロシージャに渡されます ( 出力パラメータの場合は、OUTPUT コマンドも指定する必要があります。）。 ただし、パラメータの名前を指定しないこともできますが、この場合は、値を指定する順序に従う必要があります。 入力パラメータが定義されている順序で値を指定します( これは出力パラメータにも当てはまります).

    デフォルト値を持つパラメータは指定する必要はありません。これらはいわゆるオプションのパラメータです。

    ここでは、ストアド プロシージャ、特にテスト プロシージャを実行する、異なるが同等の方法をいくつか紹介します。

    1. 価格を指定せずにプロシージャを呼び出します。 EXECUTE T​​estProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. 価格を示すプロシージャ EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3 を呼び出します。 パラメータ名を指定せずにプロシージャを呼び出します。 EXEC TestProcedure 1, "Test product 3", 400

    ストアド プロシージャを T-SQL に変更する - ALTER PROCEDURE ステートメント

    手順を使用して手順のアルゴリズムを変更できます。 手順の変更。 つまり、既存のプロシージャを変更するには、CREATE PROCEDURE の代わりに ALTER PROCEDURE を記述し、必要に応じて他のすべてを変更するだけです。

    テスト手順、たとえば @Price パラメータを変更する必要があるとします。 価格を必須にします。このためにデフォルト値を削除し、結果のデータセットを取得する必要がなくなったと想定します。このために、単純にストアドプロシージャから SELECT ステートメントを削除します。

    プロシージャ ALTER PROCEDURE TestProcedure (--受信パラメータ @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --アルゴリズムを実装する命令 --受信パラメータの処理 --先頭の余分なスペースを削除し、テキスト行の終わり SET @ProductName = LTRIM(RTRIM(@ProductName)); -- 新しいレコードを追加します INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

    T-SQL でのストアド プロシージャの削除 - DROP PROCEDURE ステートメント

    必要に応じて、手順に従ってストアド プロシージャを削除できます。 ドロップ手順.

    たとえば、作成したテスト プロシージャを削除してみましょう。

    DROP PROCEDURE テスト手順

    ストアド プロシージャを削除する場合、そのプロシージャが他のプロシージャまたは SQL ステートメントによって参照されている場合、それらが参照するプロシージャはもう存在しないため、削除後にエラーが発生して失敗することに注意してください。

    私が持っているのはこれだけです。この資料が興味深く、お役に立てば幸いです、さようなら!

    ストアドプロシージャストアド プロシージャ) は、名前付きデータベース プログラム オブジェクトです。 SQL Server にはいくつかの種類のストアド プロシージャがあります。

    システムストアドプロシージャシステム ストアド プロシージャ) は DBMS 開発者によって提供され、システム ディレクトリでアクションを実行したり、システム情報を取得したりするために使用されます。 通常、それらの名前は接頭辞「sp_」で始まります。 すべての種類のストアド プロシージャを実行するには、EXECUTE コマンド (短縮して EXEC と呼ぶこともできます) を使用します。 たとえば、sp_helplogins ストアド プロシージャをパラメータなしで実行すると、アカウント名に関する 2 つのレポートが生成されます。 （英語）ログイン) と各データベース内の対応するユーザー （英語）ユーザー）。

    EXEC sp_helplogins;

    システム ストアド プロシージャを使用して実行されるアクションを理解するには、次の表を参照してください。 10.6 にいくつかの例を示します。 SQL Server には、合計で 1,000 を超えるシステム ストアド プロシージャが存在します。

    表10.6

    SQL Server システム ストアド プロシージャの例

    ユーザーは、ユーザー データベースおよび一時オブジェクトのデータベースにストアド プロシージャを作成できます。 後者の場合、ストアド プロシージャは次のようになります。 一時的な。一時テーブルの場合と同様、一時ストアド プロシージャの名前は、ローカルの一時ストアド プロシージャの場合は接頭辞 "#" で始まり、グローバルの場合は "##" で始まる必要があります。 ローカル一時プロシージャは、それが作成された接続内でのみ使用できます。グローバル一時プロシージャは、他の接続内でも使用できます。

    SQL Server のプログラム可能なオブジェクトは、Transact-SQL ツールまたはアセンブリを使用して作成できます。 （英語） Microsoft.Net Framework の CRL (Common Language Runtime) 環境のアセンブリ)。 このチュートリアルでは、最初の方法のみを説明します。

    ストアド プロシージャを作成するには、CREATE PROCEDURE (PROC と短縮することもできます) ステートメントを使用します。その形式は次のとおりです。

    CREATE (PROC I PROCEDURE) proc_name [ ; 番号 ]

    [(gパラメータのデータ型)

    [「デフォルト] |

    [と [ ,...n ] ]

    【複製用】

    AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

    ストアド プロシージャ (またはトリガー、関数、ビュー) が ENCRYPTION オプションを使用して作成された場合、そのコードはテキストが判読できなくなるような方法で変換されます。 同時に、で述べたように、使用されているアルゴリズムは SQL Server の以前のバージョンから転送されたものであり、信頼できる保護アルゴリズムとは見なされません。逆変換を迅速に実行できるユーティリティがあります。

    RECOMPILE オプションは、プロシージャが呼び出されるたびにシステムがテキストを再コンパイルすることを指定します。 通常の場合、最初の実行時にコンパイルされたプロシージャはキャッシュに保存されるため、パフォーマンスが向上します。

    EXECUTE AS は、プロシージャが実行されるセキュリティ コンテキストを指定します。 次に、値の 1 つ f CALLER | 自己 | オーナー | 「ユーザー名」)。 CALLER がデフォルトであり、このモジュールを呼び出すユーザーのセキュリティ コンテキストでコードが実行されることを意味します。 したがって、ユーザーはプログラム可能なオブジェクト自体に対する権限だけでなく、そのオブジェクトによって影響を受ける他のデータベース オブジェクトに対する権限も持っている必要があります。 EXECUTE AS SELF は、プログラム可能なオブジェクトを作成または変更するユーザーのコンテキストを使用することを意味します。 OWNER は、プロシージャの現在の所有者のコンテキストでコードが実行されることを指定します。 所有者が指定されていない場合は、そのスキーマが属するスキーマの所有者とみなされます。 EXECUTE AS "user_name" を使用すると、ユーザー名を (一重引用符で囲んで) 明示的に指定できます。

    プロシージャにはパラメータを指定できます。 これらは、プロシージャに値を渡すために使用されるローカル変数です。 パラメータがキーワード OUTPUT (または略して OUT) を使用して宣言されている場合、それは出力値です。完了後にプロシージャ内で指定された値は、プロシージャを呼び出したプログラムで使用できます。 READONLY キーワードは、ストアド プロシージャ内でパラメータの値を変更できないことを意味します。

    パラメータにはデフォルト値を割り当てることができ、プロシージャの呼び出し時にパラメータ値が明示的に指定されなかった場合に使用されます。 例を見てみましょう:

    CREATE PROC surma (@a int, @b int=0,

    ©結果 int 出力) AS

    SET @結果=0a+0b

    3 つのパラメーターを持つプロシージャを作成しました。@b パラメーターのデフォルト値は =0 で、@result パラメーターは出力パラメーターであり、呼び出し側プログラムに値を返します。 実行されるアクションは非常に単純です。出力パラメーターは 2 つの入力パラメーターの合計値を受け取ります。

    SQL Server Management Studio で作業している場合、作成されたストアド プロシージャはプログラマブル データベース オブジェクト セクションにあります。 （英語）プログラマビリティ) はストアド プロシージャのサブセクションにあります (図 10.2)。

    プロシージャを呼び出すときは、変数と定数の両方を入力パラメータとして使用できます。 2 つの例を見てみましょう。 最初の方法では、プロシージャの入力パラメータが定数として明示的に指定され、呼び出しの出力パラメータに OUTPUT キーワードが指定されます。 2 番目のオプションは、最初の入力パラメータとして変数の値を使用し、DEFAULT キーワードを使用して 2 番目のパラメータにデフォルト値を使用することを指定します。

    米。 10.2.

    @с int を宣言します。

    EXEC summa 10.5,@c OUTPUT;

    0c を印刷します。 – 15 個が表示されます

    Gi int = 5 を宣言します。

    – 呼び出すときはデフォルト値を使用します

    EXEC 合計 Gi,DEFAULT , 0c OUTPUT;

    0c を印刷します。 – 5が表示されます

    次に、プロシージャが終了する戻りコードの分析の例を考えてみましょう。 Bookl テーブル内の書籍が指定された範囲の年に出版された数を計算する必要があるとします。 さらに、最初の年が最終年より大きい場合、プロシージャは「1」を返し、カウントされません。それ以外の場合は、本の数をカウントして 0 を返します。

    CREATE PROC dbo.rownum (0Firs Year int, GLast Year int, 0result int OUTPUT) AS

    IF 0最初の年>0最後の年 RETURN 1

    SET @result= (SELECT COUNT(*) FROM dbo.Bookl

    0Firs Year と 0Last Year の間の場所);

    このプロシージャを呼び出す変形例を考えてみましょう。このプロシージャでは、リターン コードが整数変数 0ret に格納され、その後その値が分析されます (この場合は 1 になります)。 PRINT ステートメントで使用される CAST 関数は、整数変数 Gres の値を文字列型に変換するために使用されます。

    DECLARE 0ret int、Gres int

    EXEC Gret = rownum 2004、2002、Gres OUT;

    IF 0ret=l PRINT "開始年が終了年より大きいです"

    PRINT "本の数" + CAST(Gres as varchar(20))

    ストアド プロシージャは、テーブルからデータを読み取るだけでなく、データを変更したり、テーブルやその他の多数のデータベース オブジェクトを作成したりすることもできます。

    ただし、ストアド プロシージャからスキーマ、関数、トリガー、プロシージャ、ビューを作成することはできません。

    次の例は、これらの機能と、一時オブジェクトのスコープに関連する問題の両方を示しています。 次のストアド プロシージャは、一時テーブル #TaL2 の存在を確認します。 このテーブルが存在しない場合は作成されます。 この後、2 つの列の値がテーブル #TaL2 に入力され、SELECT ステートメントを使用してテーブルの内容が表示されます。

    CREATE PROC My_Procl (@id int, @name varchar(30))

    OBJECT_ID("tempdb.dbo.#Tab21) が NULL の場合

    INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

    dbo から * を選択します。 #タブ2 –№1

    ストアド プロシージャを初めて呼び出す前に、そこで使用される一時テーブル #TaL2 を作成します。 EXEC 演算子に注意してください。 前の例では、パラメータは「位置によって」プロシージャに渡されましたが、この場合、パラメータを渡すための別の形式が使用されます - 「名前によって」、パラメータの名前とその値が明示的に示されています。

    CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

    EXEC My_Procl 0name="lvan", 0id=2;

    SELECT * FROM dbo.#Tab2; –№2

    上の例では、SELECT ステートメントは 2 回処理されます。1 回目はプロシージャ内で、2 回目は呼び出し元のコード フラグメント (コメント「No. 2」でマーク) から処理されます。

    プロシージャの 2 回目の呼び出しの前に、一時テーブル #TaL2 を削除します。 次に、同じ名前の一時テーブルがストアド プロシージャから作成されます。

    DROP TABLE dbo.#Tab2;

    EXEC My_Procl 0name="イワン", 0id=2;

    SELECT * FROM dbo.#Tab2; –№2

    この場合、プロシージャ内にある SELECT ステートメント (コメント「Xa 1」付き) のみがデータを表示します。 ストアド プロシージャで作成された一時テーブルは、プロシージャが返された時点ですでに tempdb データベースから削除されているため、SELECT "No. 2" を実行するとエラーが発生します。

    DROP PROCEDURE ステートメントを使用してストアド プロシージャを削除できます。 その形式を以下に示します。 1 つのステートメントで複数のストアド プロシージャを削除し、それらをカンマで区切ってリストできます。

    DROP (PROC I PROCEDURE) (プロシージャ) [

    たとえば、以前に作成した Summa プロシージャを削除してみましょう。

    DROP PROC 合計。

    ALTER PROCEDURE ステートメントを使用して、既存のプロシージャを変更 (実際には再定義) できます (許可されています)。

    略称PROC）。 ALTER キーワードを除き、ステートメントの形式は CREATE PROCEDURE の形式と基本的に同じです。 たとえば、dbo プロシージャを変更してみましょう。 rownum を、所有者のセキュリティ コンテキストで実行するように設定します。

    ALTER PROC dbo.rownum(SFirs Year int,

    SLast Year int、Sresult int OUTPUT)

    所有者として実行を使用 – インストール可能なオプション

    IF 0Firs Year>0Last Year RETURN 1 ELSE BEGIN

    SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

    SFirs Year と SLast Year の間);

    場合によっては、コマンドを動的に生成し、データベース サーバー上で実行することが必要になる場合があります。 この問題は、EXEC 演算子を使用して解決することもできます。 以下の例では、年属性が変数で指定された値と等しい場合に Bookl テーブルからレコードを取得します。

    DECLARE 0у int = 2000;

    EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

    動的に生成された命令の実行により、「SQL インジェクション」などのコンピューター攻撃を実行するための前提条件が作成されます。 （英語） SQL インジェクション)。 攻撃の本質は、攻撃者が動的に生成されたクエリに独自の SQL コードを挿入することです。 これは通常、置換されるパラメータがユーザー入力の結果から取得される場合に発生します。

    前の例を少し変更してみましょう。

    DECLARE 0у varchar(100);

    SET 0у="2ООО"; – ユーザーからこれを受け取りました

    SET ステートメントで割り当てられた文字列値をユーザーから (Web アプリケーション経由などの方法を問わず) 受け取ったと仮定すると、この例はコードの「通常の」動作を示しています。

    DECLARE 0у varchar(100);

    SET 0у="2000; dbo.Book2 から削除"; - 注射

    EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

    このような場合、可能であれば、SQL インジェクションの障壁の 1 つであるパラメーターのタイプを制御できるシステム ストアド プロシージャ sp_executcsql を使用することをお勧めします。 形式を詳しく考慮せずに、前に示したものと同様の例を見てみましょう。

    実行 sp_executesql

    N"SELECT * FROM dbo.Bookl WHERE =0y",

    これにより、クエリで使用されるパラメーターの種類が明示的に指定され、SQL Server が実行中にそれを制御します。 引用符の前の文字「N」は、プロシージャで要求されているように、これが Unicode リテラル定数であることを示します。 パラメータには定数値だけでなく、別の変数の値も割り当てることができます。