「SQL Server ストアド プロシージャ 基本」の版間の差分
ナビゲーションに移動
検索に移動
| 1行目: | 1行目: | ||
| − | ==SQL Server ストアド プロシージャ 基本== | + | ==[[SQL Server ストアド プロシージャ 基本]]== |
[[SQL Server]] | [[SQL Server 2005]] | [[SQL Server ストアド プロシージャ]] | [[Transact-SQL]] | | [[SQL Server]] | [[SQL Server 2005]] | [[SQL Server ストアド プロシージャ]] | [[Transact-SQL]] | | ||
| − | *http://msdn.microsoft.com/ja-jp/library/ms190782(v=SQL.90).aspx | + | *http://msdn.microsoft.com/ja-jp/library/ms190782(v=[[SQL]].90).aspx |
==基本== | ==基本== | ||
===可能なこと=== | ===可能なこと=== | ||
| 15行目: | 15行目: | ||
*参照するオブジェクトに対する直接的な権限がユーザーになくても、ストアド プロシージャを実行する権限をユーザーに許可することができる | *参照するオブジェクトに対する直接的な権限がユーザーになくても、ストアド プロシージャを実行する権限をユーザーに許可することができる | ||
*セキュリティの向上 | *セキュリティの向上 | ||
| − | **パラメータ化されたストアド | + | **パラメータ化されたストアド プロシージャは、[[SQL]] Injection 攻撃からアプリケーションを保護するうえで有効 |
*モジュール プログラミングが可能です。 | *モジュール プログラミングが可能です。 | ||
**一度プロシージャを作成すれば、プログラム中で何度でも呼び出せます。 | **一度プロシージャを作成すれば、プログラム中で何度でも呼び出せます。 | ||
| 21行目: | 21行目: | ||
**間接指定が可能になり、コードの拡張が容易 | **間接指定が可能になり、コードの拡張が容易 | ||
*ネットワーク トラフィックが減少します。 | *ネットワーク トラフィックが減少します。 | ||
| − | **数百行の Transact-SQL コードをネットワークに送信するのではなく、プロシージャ内のコードを実行する 1 つのステートメントを使用することで、数百行のコードが必要な処理を実行できます。 | + | **数百行の [[Transact-SQL]] コードをネットワークに送信するのではなく、プロシージャ内のコードを実行する 1 つのステートメントを使用することで、数百行のコードが必要な処理を実行できます。 |
==種類== | ==種類== | ||
===ユーザー定義ストアド プロシージャ=== | ===ユーザー定義ストアド プロシージャ=== | ||
| 27行目: | 27行目: | ||
====可能なこと==== | ====可能なこと==== | ||
*入力パラメータを受け取ること、表形式かスカラ値の結果とメッセージをクライアントに返す | *入力パラメータを受け取ること、表形式かスカラ値の結果とメッセージをクライアントに返す | ||
| − | *DDL ( | + | *DDL (データ定義[[言語]]) ステートメントおよび DML (データ操作[[言語]]) ステートメントを呼び出す |
*出力パラメータを返す | *出力パラメータを返す | ||
====2種類のストアドプロシージャ==== | ====2種類のストアドプロシージャ==== | ||
| − | =====Transact-SQL===== | + | =====[[Transact-SQL]]===== |
| − | *ransact-SQL ステートメントをまとめて、ユーザーが指定したパラメータを受け取ったり返したりできるようにして保存したもの | + | *ransact-[[SQL]] ステートメントをまとめて、ユーザーが指定したパラメータを受け取ったり返したりできるようにして保存したもの |
=====[http://msdn.microsoft.com/ja-jp/library/ms131094(v=SQL.90).aspx CLR]===== | =====[http://msdn.microsoft.com/ja-jp/library/ms131094(v=SQL.90).aspx CLR]===== | ||
| − | *ユーザーが入力したパラメータを取得したり返したりできる Microsoft .NET Framework の CLR (共通言語ランタイム) メソッドへの参照 | + | *ユーザーが入力したパラメータを取得したり返したりできる Microsoft [[.NET]] Framework の CLR (共通言語ランタイム) メソッドへの参照 |
| − | *.NET Framework アセンブリのクラスの静的パブリック メソッドとして実装 | + | *[[.NET]] Framework アセンブリのクラスの静的パブリック メソッドとして実装 |
===拡張ストアド プロシージャ=== | ===拡張ストアド プロシージャ=== | ||
| − | *拡張ストアド プロシージャを使用すると、C | + | *拡張ストアド プロシージャを使用すると、C などのプログラミング[[言語]]で独自の外部ルーチンを作成できる |
| − | *DLL なので、Microsoft SQL Server インスタンスで動的に読み込んで実行 | + | *DLL なので、Microsoft [[SQL Server]] インスタンスで動的に読み込んで実行 |
| − | <blockquote>この機能は、将来のバージョンの Microsoft SQL Server では削除される予定。代わりに、「CLR (共通言語ランタイム) 統合の概要」を使用すること。</blockquote> | + | <blockquote>この機能は、将来のバージョンの Microsoft [[SQL Server]] では削除される予定。代わりに、「CLR (共通言語ランタイム) 統合の概要」を使用すること。</blockquote> |
===[http://msdn.microsoft.com/ja-jp/library/ms187961(v=SQL.90).aspx システム ストアド プロシージャ]=== | ===[http://msdn.microsoft.com/ja-jp/library/ms187961(v=SQL.90).aspx システム ストアド プロシージャ]=== | ||
*管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行 | *管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行 | ||
| − | *システム ストアド プロシージャは | + | *システム ストアド プロシージャは [[R]]esource データベースが物理的な保存場所で、sp_ というプレフィックスが付きます |
==設計== | ==設計== | ||
| − | <blockquote>バッチとして記述できる Transact-SQL コードはほぼすべて、ストアド プロシージャの作成に使用できます</blockquote> | + | <blockquote>バッチとして記述できる [[Transact-SQL]] コードはほぼすべて、ストアド プロシージャの作成に使用できます</blockquote> |
===規則=== | ===規則=== | ||
*他のデータベース オブジェクトを作成できます。 | *他のデータベース オブジェクトを作成できます。 | ||
| 49行目: | 49行目: | ||
*ローカルな一時テーブルを作成した場合は、そのストアド プロシージャのためだけに存在するため、終了すると使用できなくなります。 | *ローカルな一時テーブルを作成した場合は、そのストアド プロシージャのためだけに存在するため、終了すると使用できなくなります。 | ||
*ストアド プロシージャから、呼び出されたストアド プロシージャは、呼び出し元が作成した、すべてのオブジェクトにアクセスできます。 | *ストアド プロシージャから、呼び出されたストアド プロシージャは、呼び出し元が作成した、すべてのオブジェクトにアクセスできます。 | ||
| − | *リモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド | + | *リモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド プロシージャは[[トランザクション]]にはかかわりません。 |
*パラメータの最大数は 2100 です。 | *パラメータの最大数は 2100 です。 | ||
*ローカル変数の最大数は、使用可能なメモリ量によってのみ制限されます。 | *ローカル変数の最大数は、使用可能なメモリ量によってのみ制限されます。 | ||
| 61行目: | 61行目: | ||
!_ | !_ | ||
|- | |- | ||
| − | | | + | |C[[R]]EATE AGG[[R]]EGATE |
| − | | | + | |C[[R]]EATE [[R]]ULE |
| − | | | + | |C[[R]]EATE DEFAULT |
| − | | | + | |C[[R]]EATE SCHEMA |
|- | |- | ||
|CREATE FUNCTION または ALTER FUNCTION | |CREATE FUNCTION または ALTER FUNCTION | ||
|CREATE TRIGGER または ALTER TRIGGER | |CREATE TRIGGER または ALTER TRIGGER | ||
|CREATE PROCEDURE または ALTER PROCEDURE | |CREATE PROCEDURE または ALTER PROCEDURE | ||
| − | |CREATE | + | |CREATE V[[IE]]W または ALTER V[[IE]]W |
|- | |- | ||
|SET PARSEONLY | |SET PARSEONLY | ||
|SET SHOWPLAN_ALL | |SET SHOWPLAN_ALL | ||
|SET SHOWPLAN_TEXT | |SET SHOWPLAN_TEXT | ||
| − | |SET | + | |SET SHOWPLAN_[[XML]] |
|- | |- | ||
|USE database_name | |USE database_name | ||
| 82行目: | 82行目: | ||
===名前の修飾=== | ===名前の修飾=== | ||
*スキーマにより修飾されていないオブジェクト名使用される場合、既定ではこの名前はストアド プロシージャのスキーマになります。 | *スキーマにより修飾されていないオブジェクト名使用される場合、既定ではこの名前はストアド プロシージャのスキーマになります。 | ||
| − | *他のユーザーが使用するには、すべての DDL ( | + | *他のユーザーが使用するには、すべての DDL (データ定義[[言語]]) ステートメントで使用するオブジェクト名を、スキーマの名前で修飾することにより、名前が確実に同じオブジェクトに解決されます。 |
<blockquote>スキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします</blockquote> | <blockquote>スキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします</blockquote> | ||
2020年2月16日 (日) 04:32時点における最新版
目次
SQL Server ストアド プロシージャ 基本
SQL Server | SQL Server 2005 | SQL Server ストアド プロシージャ | Transact-SQL |
基本
可能なこと
- 入力パラメータを受け取り、呼び出し元に出力パラメータの形式で複数の値を返す
- データベース内での操作を実行するステートメントを含む
- 呼び出し元にステータス値を返し、成功、失敗、および失敗の原因を示す
実行
- 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>
© 2006 矢木浩人
