ストアド プロシージャ

複数のユーザーが同時にデータベースにアクセスすると、ダーティ リード、ファントム読み取りなどが発生する場合があり、十分注意して設計する必要があります。

１．目次

１．目次
２．ストアド　実行例
３．ストアド プロシージャの特徴
４．ストアド プロシージャからデータを返す方法例
５．ストアド構文
６．良く使うシステムストアド
７．メール通知
８．その他

２．ストアド　実行例

　

ストアドの作成例

CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]     @EmployeeID [int] AS BEGIN     ...... END

　

ストアドの変更例

ALTER PROCEDURE [dbo].[uspPrintError] AS BEGIN     SET NOCOUNT ON;     -- Print error information.     PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +     ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +     ', State ' + CONVERT(varchar(5), ERROR_STATE()) +     ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +     ', Line ' + CONVERT(varchar(5), ERROR_LINE());     PRINT ERROR_MESSAGE(); END;

　

ストアドの実行例

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'uspGetEmployeeManagers'

　

ストアドの削除例

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetManagerEmployees]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[uspGetManagerEmployees]

３．ストアド プロシージャの特徴

SQL Server 2005 のストアド プロシージャは、次の 4 つの種類があります。

ストアドの種類
ユーザー定義ストアド	Transact-SQL	今までと同じ、T-SQL ベースのストアド
	CLR	ユーザーが入力したパラメータを取得したり返したりできる .NET Framework の CLR メソッドへの参照で、.NET Framework アセンブリのクラスの静的パブリック メソッドとして実装されます。
拡張ストアド	masterデータベースに格納され、dllが動的にロード＆実行される。2005では、dllではなくCLRによるストアドが推奨されている。
システムストアド	SQL Server 2005 の管理作業の多くは、システム ストアド プロシージャと呼ばれる特殊なプロシージャにより実行されます。たとえば、sys.sp_changedbowner はシステム ストアド プロシージャの 1 つです。

　

ストアドのメリット

早い
　

アプリケーションが送信する必要があるのは、プロシージャの名前およびパラメータの値が含まれた EXECUTE ステートメントまたは CALL ステートメントのみで、プロシージャ内のすべての SQL ステートメントを送信する必要がない。
特に結果セットから条件分岐をするような場合、結果セットからの判断ロジックがサーバサイドで閉じることができる。

簡単

ストアド プロシージャを実行するだけで、データベース内のテーブルの詳細を知る必要が無い。

４．ストアド プロシージャからデータを返す方法例

USE Northwind
GO

DROP PROCEDURE OrderSummary
GO

CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]

RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO

DECLARE @OrderSum INT
DECLARE @LargestOrder INT

EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT

PRINT 'The size of the largest single order was: ' +
CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
CONVERT(CHAR(6), @OrderSum)
GO

５．ストアド構文

５．１　CREATE PROCEDURE 構文

CREATE PROC [ EDURE ] [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name ]data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,...n ]
[ WITH < procedure_option > [ ,...n ]
[ FOR REPLICATION ]
AS { < sql_statement > [ ...n ] | <method_specifier> }

< procedure_option > ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]

< sql_statement > ::=
{ [ BEGIN ] statements [ END ] }

<method_specifier > ::=
EXTERNAL NAME assembly_name.class_name[.method_name]

ALTER PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH
    { RECOMPILE | ENCRYPTION
        | RECOMPILE , ENCRYPTION
    }
]
[ FOR REPLICATION ]
AS
    sql_statement [ ...n ]

DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ]　

６．良く使うシステムストアド

sp_columuns

列を取得する。

sp_helptext

ルール、デフォルト、暗号化されていないストアド、ユーザー定義関数、トリガ、ビューなどのテキストを出力します。

sp_monitor

SQL Serverの統計を表示します。

sp_help

オブジェクトの情報を表示します。

sp_who

ユーザーおよびプロセスに関する情報を返します。

sp_lock

sp_lockは、旧バージョンとの互換性を維持するためだけに Microsoft SQL Server 2005 に含まれています。sys.dm_tran_locks 動的管理ビューを使用してください。

sp_dboptions

sp_dboptionsは、将来のバージョンで削除されます。ALTER DATABASE を使用してください。レプリケーション (merge publish、published、subscribed) に関連付けられたデータベース オプションを変更するには、sp_replicationdboption を使用します。

sp_autostats

テーブルまたはインデックス付きのビューのインデックスおよび統計の自動 UPDATE STATISTICS 設定を表示、またはON/OFFの変更します。

sp_server_info

サーバの情報を表示。バージョンがわかる。

７．メール通知

データベース メールストアド

旧バージョンとの互換性を維持するため、Microsoft SQL Server 2005 には SQL Mail が用意されています。しかし、既定ではSQL Mail は SQL Server 2005 にインストールされません。

SQL Server 2005 からメールを送信するには、データベース メールを使用します。しかし、既定では、データベース メールはアクティブになっていません。データベース メールを使用するには、SQL Server セキュリティ構成 ツールまたはデータベース メール構成ウィザードを使用して、データベース メールを明示的に有効にする必要があります。

SQL Server エージェントのメール機能

既定では、SQL Server エージェントのメール機能は無効になっています。この機能を構成するには、[SQL Server エージェントのプロパティ] ダイアログ ボックスの [警告システム] ペインを使用します。SQL Server エージェント メールは、警告通知およびジョブ完了時の自動通知にのみ必要だという点に注意してください。また、ジョブ内の個別のジョブ ステップでは、SQL Server エージェント メールが有効になっているかどうかに関係なく、電子メールを送信できます。たとえば、Transact-SQL ジョブ ステップでは、データベース メールを使用してクエリ結果を受信者の一覧に送信できます。

　

８．その他

ストアドの最大ネストレベルはデフォルトで３２。