- 追加された行はこのように表示されます。
- 削除された行は
このように表示されます。
!!!SQL Server ストアド プロシージャ 基本
[SQL Server][SQL Server 2005][Transact-SQL]
[SQL Server][SQL Server 2005][SQL Server ストアド プロシージャ][Transact-SQL]
*http://msdn.microsoft.com/ja-jp/library/ms190782(v=SQL.90).aspx
!!!基本
!!可能なこと
*入力パラメータを受け取り、呼び出し元に出力パラメータの形式で複数の値を返す
*データベース内での操作を実行するステートメントを含む
*呼び出し元にステータス値を返し、成功、失敗、および失敗の原因を示す
!!実行
*EXECUTE ステートメントを使用
""関数と異なる点は、名前の代わりに値を返さないことと、式の中で直接使用できないことの 2 点
!!利点
サーバーに登録
*セキュリティ属性 (権限など) と所有権の継承を利用でき、証明書を添付することもできる
*参照するオブジェクトに対する直接的な権限がユーザーになくても、ストアド プロシージャを実行する権限をユーザーに許可することができる
*セキュリティの向上
**パラメータ化されたストアド プロシージャは、SQL Injection 攻撃からアプリケーションを保護するうえで有効
*モジュール プログラミングが可能です。
**一度プロシージャを作成すれば、プログラム中で何度でも呼び出せます。
*遅延バインドが可能です。
**間接指定が可能になり、コードの拡張が容易
*ネットワーク トラフィックが減少します。
**数百行の Transact-SQL コードをネットワークに送信するのではなく、プロシージャ内のコードを実行する 1 つのステートメントを使用することで、数百行のコードが必要な処理を実行できます。
!!!種類
!!ユーザー定義ストアド プロシージャ
*ストアド プロシージャは、コードを再利用するためにカプセル化したモジュールやルーチン
!可能なこと
*入力パラメータを受け取ること、表形式かスカラ値の結果とメッセージをクライアントに返す
*DDL (データ定義言語) ステートメントおよび DML (データ操作言語) ステートメントを呼び出す
*出力パラメータを返す
!2種類のストアドプロシージャ
::Transact-SQL
*ransact-SQL ステートメントをまとめて、ユーザーが指定したパラメータを受け取ったり返したりできるようにして保存したもの
::[CLR|http://msdn.microsoft.com/ja-jp/library/ms131094(v=SQL.90).aspx]
*ユーザーが入力したパラメータを取得したり返したりできる Microsoft .NET Framework の CLR (共通言語ランタイム) メソッドへの参照
*.NET Framework アセンブリのクラスの静的パブリック メソッドとして実装
!!拡張ストアド プロシージャ
*拡張ストアド プロシージャを使用すると、C などのプログラミング言語で独自の外部ルーチンを作成できる
*DLL なので、Microsoft SQL Server インスタンスで動的に読み込んで実行
""この機能は、将来のバージョンの Microsoft SQL Server では削除される予定。代わりに、「CLR (共通言語ランタイム) 統合の概要」を使用すること。
!![システム ストアド プロシージャ|http://msdn.microsoft.com/ja-jp/library/ms187961(v=SQL.90).aspx]
*管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行
*システム ストアド プロシージャは Resource データベースが物理的な保存場所で、sp_ というプレフィックスが付きます
!!!設計
""バッチとして記述できる Transact-SQL コードはほぼすべて、ストアド プロシージャの作成に使用できます
!!規則
*他のデータベース オブジェクトを作成できます。
*一時テーブルを参照できます。
*ローカルな一時テーブルを作成した場合は、そのストアド プロシージャのためだけに存在するため、終了すると使用できなくなります。
*ストアド プロシージャから、呼び出されたストアド プロシージャは、呼び出し元が作成した、すべてのオブジェクトにアクセスできます。
*リモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド プロシージャはトランザクションにはかかわりません。
*パラメータの最大数は 2100 です。
*ローカル変数の最大数は、使用可能なメモリ量によってのみ制限されます。
*使用可能なメモリによって異なりますが、ストアド プロシージャの最大サイズは 128 MB
!使用出来ないステートメント
,_,_,_,_
,CREATE AGGREGATE,CREATE RULE,CREATE DEFAULT,CREATE SCHEMA
,CREATE FUNCTION または ALTER FUNCTION,CREATE TRIGGER または ALTER TRIGGER,CREATE PROCEDURE または ALTER PROCEDURE,CREATE VIEW または ALTER VIEW
,SET PARSEONLY,SET SHOWPLAN_ALL,SET SHOWPLAN_TEXT,SET SHOWPLAN_XML
,USE database_name
!!名前の修飾
*スキーマにより修飾されていないオブジェクト名使用される場合、既定ではこの名前はストアド プロシージャのスキーマになります。
*他のユーザーが使用するには、すべての DDL (データ定義言語) ステートメントで使用するオブジェクト名を、スキーマの名前で修飾することにより、名前が確実に同じオブジェクトに解決されます。
""スキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします
