C# Programming

ストアド プロシージャ

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

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

　

ストアドの作成例

CREATE PROCEDURE [dbo].[NewStoredProc] @param1 [int] WITH EXECUTE AS CALLER AS SELECT 都道府県 FROM dbo.都道府県 WHERE ID = @param1

　

ストアドの変更例

ALTER PROCEDURE [dbo].[NewStoredProc] @param1 [int] WITH EXECUTE AS CALLER AS SELECT 都道府県 FROM dbo.都道府県 WHERE ID = @param1

　

ストアドの実行例

SET @param1 = 10 EXECUTE @RC = [test].[dbo].[NewStoredProc] @param1

ストアドの削除例

DROP PROCEDURE [dbo].[NewStoredProc2]

SQL Enterprise Manager によるスクリプト作成例

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateLocation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[UpdateLocation] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE UpdateLocation @GUID nvarchar(50), @Latitude float, @Longitude float AS IF EXISTS (SELECT * FROM Location WHERE GUID=@GUID) UPDATE Location SET Latitude = @Latitude, Longitude= @Longitude WHERE GUID=@GUID ELSE INSERT INTO Location (GUID, Latitude, Longitude) VALUES (@GUID, @Latitude, @Longitude) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

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

• ローカルストアドプロシージャ
  ユーザが作成するストアドプロシージャ。
  　
• 一時ストアドプロシージャ
  tempdbに格納され、ユーザーの接続内でしか使えない。名前の先頭に#をつけて作成する。すべてのユーザの接続で使用できるグローバルな一時ストアドは、##で始まる名前をつける。
  SQL Server 2000 は、以前の SQL ステートメントの実行プランを再利用する機能があり、積極的に使う理由は無い。
  　
• 拡張ストアドプロシージャ
  masterデータベースに格納され、dllが動的にロード＆実行される。プリフィックスは xp。次は、拡張ストアドの例。
  
  　
• リモートストアドプロシージャ
  SQL Server 6.0までの話。SQL 2000 では、使用することは無い。

SQL Server 2000 のストアド プロシージャは、次の 4 つの方法でデータを返します。

• 出力パラメータ。
  データ (整数や文字の値など) またはカーソル変数 (カーソルは、一度に 1 行を取り出せる結果セットです) のどちらかを返します。
• リターン コード。常に整数値です。
• ストアド プロシージャ内、またはストアド プロシージャが呼び出したほかのいずれかのストアド プロシージャ内の、各SELECT ステートメントの結果セット。
• ストアド プロシージャの外部で参照可能なグローバル カーソル。
  　

ストアドのメリット

早い
　

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

簡単

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

SQL Server 2000 では、ストアド プロシージャの実行プランだけでなく、すべての SQL ステートメントの実行プランがプロシージャ キャッシュに保持されます。データベース エンジンは、効率的なアルゴリズムを使用して、新しい Transact-SQL ステートメントと、既存の実行プランの Transact-SQL ステートメントを比較します。データベース エンジンが、新しい Transact-SQL ステートメントと、既存の実行プランの Transact-SQL ステートメントが一致すると判断すると、プランが再利用されます。このため、通常の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

lockの状態を取得します。

sp_dboptions

オプションの値を求める。

sp_autostats

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

テーブルのsp_autostats [ @tblname = ] 'table_name'
    [ , [ @flagc = ] 'stats_flag' ]
    [ , [ @indname = ] 'index_name' ]

例

sp_autostats '販売', 'ON'

sp_server_info

サーバの情報を表示。バージョンがわかる。
↓SQL 2005 の場合。

メール通知には、SQL Server Agent と、SQL Mailの２つがあります。しかし、この２つとも、拡張MAPIしかサポートされていません。このため、Outlook をインストールして拡張MAPIを有効にしないと、SQLのメール通知機能が使えません。

最低〜〜！

以下の例では、SQL Server Agent で、Outlook のUchukamenというメールプロファイルを設定した例です。この場合、SQL Server Agentは、メールプロファイルに対するアクセス権がある必要があります。

拡張MAPIのメールプロファイルは、次のようにコントロールパネル→メール→メール設定ダイアログで確認できます。

注意：　Outlook Expressでは、拡張MAPIに対応していないので、このコントロールパネル→メール自体が現れません。

SQL Mail プロシージャには、xp_sendmail, xp_readmail などがあります。

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

検証してみる。