| ページ一覧 | ブログ | twitter |　 書式 | 書式(表) |

MyMemoWiki

「SQL Server ストアド プロシージャ 基本」の版間の差分

提供: MyMemoWiki
ナビゲーションに移動 検索に移動
(ページの作成:「==SQL Server ストアド プロシージャ 基本== [SQL Server][SQL Server 2005][SQL Server ストアド プロシージャ][Transact-SQL] *http://msdn.microsoft.com…」)
 
 
(同じ利用者による、間の3版が非表示)
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
 
==基本==
 
==基本==
 
===可能なこと===
 
===可能なこと===
9行目: 9行目:
 
===実行===
 
===実行===
 
*EXECUTE ステートメントを使用
 
*EXECUTE ステートメントを使用
−
<blockquote>関数と異なる点は、名前の代わりに値を返さないことと、式の中で直接使用できないことの 2 点</blockquote>
+
&lt;blockquote&gt;関数と異なる点は、名前の代わりに値を返さないことと、式の中で直接使用できないことの 2 点&lt;/blockquote&gt;
 
===利点===
 
===利点===
 
サーバーに登録
 
サーバーに登録
15行目: 15行目:
 
*参照するオブジェクトに対する直接的な権限がユーザーになくても、ストアド プロシージャを実行する権限をユーザーに許可することができる
 
*参照するオブジェクトに対する直接的な権限がユーザーになくても、ストアド プロシージャを実行する権限をユーザーに許可することができる
 
*セキュリティの向上
 
*セキュリティの向上
−
**パラメータ化されたストアド プロシージャは、SQL Injection 攻撃からアプリケーションを保護するうえで有効
+
**パラメータ化されたストアド プロシージャは、[[SQL]] Injection 攻撃からアプリケーションを保護するうえで有効
 
*モジュール プログラミングが可能です。  
 
*モジュール プログラミングが可能です。  
 
**一度プロシージャを作成すれば、プログラム中で何度でも呼び出せます。
 
**一度プロシージャを作成すれば、プログラム中で何度でも呼び出せます。
21行目: 21行目:
 
**間接指定が可能になり、コードの拡張が容易
 
**間接指定が可能になり、コードの拡張が容易
 
*ネットワーク トラフィックが減少します。  
 
*ネットワーク トラフィックが減少します。  
−
**数百行の Transact-SQL コードをネットワークに送信するのではなく、プロシージャ内のコードを実行する 1 つのステートメントを使用することで、数百行のコードが必要な処理を実行できます。
+
**数百行の [[Transact-SQL]] コードをネットワークに送信するのではなく、プロシージャ内のコードを実行する 1 つのステートメントを使用することで、数百行のコードが必要な処理を実行できます。
 
==種類==
 
==種類==
 
===ユーザー定義ストアド プロシージャ===
 
===ユーザー定義ストアド プロシージャ===
27行目: 27行目:
 
====可能なこと====
 
====可能なこと====
 
*入力パラメータを受け取ること、表形式かスカラ値の結果とメッセージをクライアントに返す
 
*入力パラメータを受け取ること、表形式かスカラ値の結果とメッセージをクライアントに返す
−
*DDL (データ定義言語) ステートメントおよび DML (データ操作言語) ステートメントを呼び出す
+
*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>
+
&lt;blockquote&gt;この機能は、将来のバージョンの Microsoft [[SQL Server]] では削除される予定。代わりに、「CLR (共通言語ランタイム) 統合の概要」を使用すること。&lt;/blockquote&gt;
 
===[http://msdn.microsoft.com/ja-jp/library/ms187961(v=SQL.90).aspx システム ストアド プロシージャ]===
 
===[http://msdn.microsoft.com/ja-jp/library/ms187961(v=SQL.90).aspx システム ストアド プロシージャ]===
 
*管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行
 
*管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行
−
*システム ストアド プロシージャは Resource データベースが物理的な保存場所で、sp_ というプレフィックスが付きます
+
*システム ストアド プロシージャは [[R]]esource データベースが物理的な保存場所で、sp_ というプレフィックスが付きます
 
==設計==
 
==設計==
−
<blockquote>バッチとして記述できる Transact-SQL コードはほぼすべて、ストアド プロシージャの作成に使用できます</blockquote>
+
&lt;blockquote&gt;バッチとして記述できる [[Transact-SQL]] コードはほぼすべて、ストアド プロシージャの作成に使用できます&lt;/blockquote&gt;
 
===規則===
 
===規則===
 
*他のデータベース オブジェクトを作成できます。
 
*他のデータベース オブジェクトを作成できます。
49行目: 49行目:
 
*ローカルな一時テーブルを作成した場合は、そのストアド プロシージャのためだけに存在するため、終了すると使用できなくなります。
 
*ローカルな一時テーブルを作成した場合は、そのストアド プロシージャのためだけに存在するため、終了すると使用できなくなります。
 
*ストアド プロシージャから、呼び出されたストアド プロシージャは、呼び出し元が作成した、すべてのオブジェクトにアクセスできます。
 
*ストアド プロシージャから、呼び出されたストアド プロシージャは、呼び出し元が作成した、すべてのオブジェクトにアクセスできます。
−
*リモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド プロシージャはトランザクションにはかかわりません。
+
*リモート インスタンス上で変更を行うリモート ストアド プロシージャを実行した場合は、それらの変更はロールバックできません。リモート ストアド プロシージャは[[トランザクション]]にはかかわりません。
 
*パラメータの最大数は 2100 です。
 
*パラメータの最大数は 2100 です。
 
*ローカル変数の最大数は、使用可能なメモリ量によってのみ制限されます。
 
*ローカル変数の最大数は、使用可能なメモリ量によってのみ制限されます。
61行目: 61行目:
 
!_
 
!_
 
|-
 
|-
−
|CREATE AGGREGATE
+
|C[[R]]EATE AGG[[R]]EGATE
−
|CREATE RULE
+
|C[[R]]EATE [[R]]ULE
−
|CREATE DEFAULT
+
|C[[R]]EATE DEFAULT
−
|CREATE SCHEMA
+
|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 VIEW または ALTER VIEW
+
|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 SHOWPLAN_XML
+
|SET SHOWPLAN_[[XML]]
 
|-
 
|-
 
|USE database_name
 
|USE database_name
82行目: 82行目:
 
===名前の修飾===
 
===名前の修飾===
 
*スキーマにより修飾されていないオブジェクト名使用される場合、既定ではこの名前はストアド プロシージャのスキーマになります。
 
*スキーマにより修飾されていないオブジェクト名使用される場合、既定ではこの名前はストアド プロシージャのスキーマになります。
−
*他のユーザーが使用するには、すべての DDL (データ定義言語) ステートメントで使用するオブジェクト名を、スキーマの名前で修飾することにより、名前が確実に同じオブジェクトに解決されます。
+
*他のユーザーが使用するには、すべての DDL (データ定義[[言語]]) ステートメントで使用するオブジェクト名を、スキーマの名前で修飾することにより、名前が確実に同じオブジェクトに解決されます。
−
<blockquote>スキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします</blockquote>
+
&lt;blockquote&gt;スキーマ名が指定されていないと、このオブジェクト名を、まず、プロシージャを呼び出したユーザーまたは EXECUTE AS 句に指定されているユーザーの既定のスキーマを使用し、次に dbo スキーマを使用して解決しようとします&lt;/blockquote&gt;

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>

「https://www.typea.info/tips_/index.php?title=SQL_Server_ストアド_プロシージャ_基本&oldid=6622」から取得

© 2006 矢木浩人