SQL Server ストアド プロシージャ 基本

SQL Server ストアド プロシージャ 基本

SQL ServerSQL Server 2005SQL Server ストアド プロシージャTransact-SQL

• http://msdn.microsoft.com/ja-jp/library/ms190782(v=SQL.90).aspx

基本

可能なこと

• 入力パラメータを受け取り、呼び出し元に出力パラメータの形式で複数の値を返す
• データベース内での操作を実行するステートメントを含む
• 呼び出し元にステータス値を返し、成功、失敗、および失敗の原因を示す

実行

• EXECUTE ステートメントを使用

<blockquote>関数と異なる点は、名前の代わりに値を返さないことと、式の中で直接使用できないことの 2 点</blockquote>

利点

サーバーに登録

• セキュリティ属性 (権限など) と所有権の継承を利用でき、証明書を添付することもできる
• 参照するオブジェクトに対する直接的な権限がユーザーになくても、ストアド プロシージャを実行する権限をユーザーに許可することができる
• セキュリティの向上
  • パラメータ化されたストアド プロシージャは、SQL Injection 攻撃からアプリケーションを保護するうえで有効
• モジュール プログラミングが可能です。
  • 一度プロシージャを作成すれば、プログラム中で何度でも呼び出せます。
• 遅延バインドが可能です。
  • 間接指定が可能になり、コードの拡張が容易
• ネットワーク トラフィックが減少します。
  • 数百行の Transact-SQL コードをネットワークに送信するのではなく、プロシージャ内のコードを実行する 1 つのステートメントを使用することで、数百行のコードが必要な処理を実行できます。

種類

ユーザー定義ストアド プロシージャ

• ストアド プロシージャは、コードを再利用するためにカプセル化したモジュールやルーチン

可能なこと

• 入力パラメータを受け取ること、表形式かスカラ値の結果とメッセージをクライアントに返す
• DDL (データ定義言語) ステートメントおよび DML (データ操作言語) ステートメントを呼び出す
• 出力パラメータを返す

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

Transact-SQL

• ransact-SQL ステートメントをまとめて、ユーザーが指定したパラメータを受け取ったり返したりできるようにして保存したもの

CLR

• ユーザーが入力したパラメータを取得したり返したりできる Microsoft .NET Framework の CLR (共通言語ランタイム) メソッドへの参照
• .NET Framework アセンブリのクラスの静的パブリック メソッドとして実装

拡張ストアド プロシージャ

• 拡張ストアド プロシージャを使用すると、C などのプログラミング言語で独自の外部ルーチンを作成できる
• DLL なので、Microsoft SQL Server インスタンスで動的に読み込んで実行

<blockquote>この機能は、将来のバージョンの Microsoft SQL Server では削除される予定。代わりに、「CLR (共通言語ランタイム) 統合の概要」を使用すること。</blockquote>

システム ストアド プロシージャ

• 管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行
• システム ストアド プロシージャは Resource データベースが物理的な保存場所で、sp_ というプレフィックスが付きます

設計

<blockquote>バッチとして記述できる Transact-SQL コードはほぼすべて、ストアド プロシージャの作成に使用できます</blockquote>

規則

• 他のデータベース オブジェクトを作成できます。
• 一時テーブルを参照できます。
• ローカルな一時テーブルを作成した場合は、そのストアド プロシージャのためだけに存在するため、終了すると使用できなくなります。
• ストアド プロシージャから、呼び出されたストアド プロシージャは、呼び出し元が作成した、すべてのオブジェクトにアクセスできます。
• リモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド プロシージャはトランザクションにはかかわりません。
• パラメータの最大数は 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 (データ定義言語) ステートメントで使用するオブジェクト名を、スキーマの名前で修飾することにより、名前が確実に同じオブジェクトに解決されます。

<blockquote>スキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします</blockquote>