ストアド プロシージャ
複数のユーザーが同時にデータベースにアクセスすると、ダーティ
リード、ファントム読み取りなどが発生する場合があり、十分注意して設計する必要があります。
１．目次
２．ストアド 実行例
３．ストアド プロシージャの特徴
４．ストアド プロシージャからデータを返す方法例
５．ストアド構文
６．良く使うシステムストアド
７．メール通知
８．その他
| ストアドの作成例 |
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 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 ジョブ
ステップでは、データベース メールを使用してクエリ結果を受信者の一覧に送信できます。
ストアドの最大ネストレベルはデフォルトで３２。
|