宇宙仮面の
C# Programming

 
Image

ストアド プロシージャ

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

 

1.目次

1.目次
2.ストアド 実行例
3.ストアド プロシージャの特徴
4.ストアド プロシージャからデータを返す方法例
5.ストアド構文
6.良く使うシステムストアド
.メール通知
8.その他

.ストアド 実行例

 

ストアドの作成例
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。次は、拡張ストアドの例。
    Image
     
  • リモートストアドプロシージャ
    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

.ストアド構文

 

.1 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]

.2 ALTER PROCEDURE 構文 
ALTER PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH
    { RECOMPILE | ENCRYPTION
        | RECOMPILE , ENCRYPTION
    }
]
[ FOR REPLICATION ]
AS
    sql_statement [ ...n ]
.3 DROP PROCEDURE 構文
DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ] 

.良く使うシステムストアド

sp_columuns

列を取得する。

Image

sp_helptext

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

Image

sp_monitor

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

Image

sp_help

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

Image

sp_who

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

Image

sp_lock

lockの状態を取得します。

Image

sp_dboptions

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

Image

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 の場合。

Image

.メール通知

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

最低〜〜!

Image

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

Image

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

Image

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

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

.その他

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

検証してみる。

Image