■サンプル
BEGIN
DECLARE Cur1 cursor FOR
SELECT col1 FROM TABLEA
--カーソルopen
OPEN Cur1
--カーソルから1行fetchし変数に値取得
FETCH NEXT FROM Cur1 INTO @col1Val
WHILE (@@fetch_status = 0)
BEGIN
--@col1Valを使ってなんかしら処理
--次の項目名を取得
FETCH NEXT FROM Cur1 INTO @col1Val
END
--globalスコープの当該カーソルをclose(openされていた場合のみ)
IF (SELECT CURSOR_STATUS('global', 'Cur1')) >= 0
BEGIN
CLOSE Cur1
DEALLOCATE Cur1
END
END
■カーソルのスコープについて
・global(0)とlocal(1)がある。
・globalの場合、DB接続に対して１つのみ存在できる。
（同名カーソルをopenする２つの別ストアドを同時実行すると、
既にカーソルが存在しますエラーになる)
・localの場合、各ストアド内で一意であればＯＫ。
（他のストアドとダブってないか気にする必要なし）
・デフォルトはglobal(0) (sqlserver2008以降。多分)
※デフォルトのスコープ確認方法
SELECT is_local_cursor_default FROM sys.databases WHERE name = 'database1';
⇒0が出たら、global
[参考]
http://technet.microsoft.com/ja-jp/library/ms189238%28v=sql.105%29.aspx
■カーソル終了処理について
・CLOSE：カーソルを閉じる。再オープンで再利用可能。
・DEALLOCATE：カーソルの解放。当カーソルは二度と使えなくなる。
例）
CLOSE cur1; --クローズ
OPEN cur1; --再オープンして利用可能
DECLARE cur1 cursor FOR SELECT col1 FROM TABLEA
DEALLOCATE cur1; --解放
OPEN cur1; --オープン不可（エラーになる）
--------------------------------------------------
◆例外処理
--------------------------------------------------
■例外の補足方法
以下のように書けばエラー時にCATCH句でエラーハンドリングできる。
BEGIN TRY
--通常処理
END TRY
BEGIN CATCH
--異常処理
END CATCH
注）ストアドＡのTRY句内で、ストアド(または関数)Ｂ呼出し、
Ｂで例外発生してCATCHしなかった場合、Ａに戻ってCATCHに入る。
■わざと例外スロー
THROW 10000, 'エラー発生', 1;
[参考]http://msdn.microsoft.com/ja-jp/library/ee677615.aspx
--------------------------------------------------
◆WIndowsバッチからの実行
--------------------------------------------------
【サンプルソース(execStored1.bat)】
REM DB接続情報
SET DBSV=localhost
SET INSTANCE=MSSQLSERVER
SET PORT=1433
SET DBNAME=DATABASE1
sqlcmd -S %DBSV%\%INSTANCE%,%PORT% -d DBNAME -v Param1=0001 -i D:\sql\execStored1.sql
【参考】
sqlcmd ユーティリティ
http://technet.microsoft.com/ja-jp/library/ms162773(v=sql.105).aspx
--------------------------------------------------
◆テーブル型変数
--------------------------------------------------
実行中のストアド内だけで有効なテーブル型変数を定義し利用することができる。
例）
--テーブル型変数定義
DECLARE @wkTbl TABLE(
code1 nvarchar(5) NOT NULL,
name1 nvarchar(10) NULL
)
--テーブル型変数へinsert
INSERT INTO @wkTbl VALUES('12345', 'Hoge')
--テーブル型変数参照
SELECT code1,name1 FROM @wkTbl
--------------------------------------------------
◆年月ループサンプル
--------------------------------------------------
複数月の処理を繰り返し行いたい場合の例
DECLARE @dtDateFrom datetime = '20150101'
DECLARE @dtDateTo datetime = '20150601'
DECLARE @dtDate datetime
SET @ldtDate = @pdtDateFrom
WHILE @ldtDate <= @pdtDateTo
BEGIN
--現在の年月
print CONVERT(VARCHAR,@dtDate,112)
--dtDateを使用してなんしか処理実施
--月+1
SET @ldtDate = DATEADD(m,1,@ldtDate)
END
--------------------------------------------------
◆Tips
--------------------------------------------------
■X件処理されました のメッセージ出力抑制
SET NOCOUNT ON
⇒ストアドでは(特にループ処理では)select文を繰り返し発行する可能性が高いが、
OFFのままだと、その度に処理件数の情報をクライアントに送信し
ネットワーク等に無駄な負荷をかけてしまう。
これを回避するため、ストアド実行冒頭で上記を記述することが望ましい。
http://www.atmarkit.co.jp/ait/articles/0209/12/news001.html
