Transact-SQL

Transact-SQL (T-SQL) は、マイクロソフトとSybaseが独自に拡張したSQL言語である。マイクロソフトによる実装は Microsoft SQL Server として出荷されている。Sybase ではこの言語を Sybase SQL Server の後継である Adaptive Server Enterprise で使っている。
SQL を強化するため、次のような機能が追加されている。
制御フロー言語.
Transact-SQL の制御フローのためのキーワードとしては、codice_1 と codice_2、codice_3、codice_4、codice_5、codice_6 と codice_7、codice_8、codice_9、codice_10 がある。
codice_6 と codice_7 によって条件付実行が可能となる。例えば、日付が週末であれば "weekend" と表示し、そうでなければ "weekday" と表示するといった処理が可能である。
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
 PRINT 'It is the weekend.'
ELSE
 PRINT 'It is a weekday.'
codice_1 と codice_2 は文のブロック化を可能とする。例えば、上記のコードで複数の文を条件付で実行する場合、BEGIN と END を使って次のように書く。
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
BEGIN
 PRINT 'It is the weekend.'
 PRINT 'Get some rest!'
END
ELSE
BEGIN
 PRINT 'It is a weekday.'
 PRINT 'Get to work!'
END
codice_9 は、指定された時間だけ待つか、指定された時刻まで待つ。遅延制御に使ったり、指定時刻まで実行をブロックするのに使われる。
codice_8 は、ストアドプロシージャや関数から即座に戻るときに使う。
codice_3 は codice_10 ループからの脱出、codice_4 はループの次の繰り返しへの飛び越しである。codice_10 ループの例は下記にある。
局所変数.
局所変数は実行中のスクリプト内でのみ使われる。Transact-SQL はユーザー定義の広域変数をサポートしていない。
codice_21 によって変数名と型を指定して変数を宣言する。SET 文で値を代入し、その後の文で変数名を使うことでその値を参照できる。
次のスクリプトは整数の変数を宣言し、値を初期化し、codice_10 ループで処理を実行している。
DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
 PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
 SET @Counter = @Counter - 1
END
このループ本体は、変数の値を含むメッセージを表示し、その値をデクリメントするものである。
変数の初期化は次のようにもできる。
DECLARE @ArticleCount INT
SELECT @ArticleCount = COUNT(*) FROM Articles
INSERT INTO SizeLog (SampleTime, ArticleCount) VALUES (GETDATE(), @ArticleCount)
これは、Articles 表の行数を取得し、その値と現在時刻を SizeLog 表の行として挿入するものである。
グローバル変数.
グローバル変数は実行中のスクリプト内での様々なステータスを監視・取得ができる。
Transact-SQLではグローバル変数は主として@@で書き始める。
良く使われるグローバル変数としては以下のものがある。
codice_23 直前に実行したクエリのエラー状態を保持
codice_24 直前に実行したクエリの処理数を保持
codice_25 現在実行中のカーソルのFETCH状態を保持(codice_26の場合、正常終了)
下記にグローバル変数を利用したエラー処理の例を示す
DECLARE @ERROR_STATUS INT
SET @ERROR_STATUS = 0
SELECT DATE 
FROM CALENDAR WITH (NOLOCK)
WHERE YEAR = '2007' AND MONTH = '01' 
-- グローバル変数@@ERRORにて直前のクエリのエラー状況を取得
-- 0 の場合はエラーなし
SET @ERROR_STATUS = @@ERROR
IF @ERROR_STATUS <> 0
BEGIN
 PRINT 'ERROR OCCURD'
 RETURN
END
DECLARE @ROW_COUNT INT
SET @ROW_COUNT = 0
UPDATE CALENDAR
SET DATE = GETDATE()
WHERE YEAR = '2007' AND MONTH = '01' 
-- グローバル変数@@ROWCOUNTにて直前のクエリの結果件数を取得
SET @ROW_COUNT = @@ROWCOUNT
IF @ROW_COUNT = 0
BEGIN
 PRINT 'NO RECORD UPDATED'
 RETURN
END
DELETE文とUPDATE文の変更.
Transact-SQL では、DELETE文とUPDATE文にFROM節を指定可能となっている。
次の例では、'Idle' フラグの立っている全ての codice_27 を削除する。
DELETE FROM users as u
 JOIN user_flags as f
 ON u.id=f.id
 WHERE f.name = 'Idle'
カーソルの実装.
Transact-SQL では、codice_28を使用することで、テーブルを逐次処理することが可能である。
次の例では、codice_28を利用し、codice_30テーブルを条件分けしながら更新する処理である。
-- カーソルの定義
DECLARE 
 CUR_CALENDAR_UPDATE 
CURSOR FOR 
 SELECT 
 YEAR, 
 MONTH, 
 DAY
 FROM 
 CALENDAR
-- 変数宣言/初期化
DECLARE @wk_year CHAR(4)
DECLARE @wk_month VARCHAR(2)
DECLARE @wk_day VARCHAR(2)
SET @wk_year = "
SET @wk_month = "
SET @wk_day = "
-- カーソルを開く
OPEN CUR_CALENDAR_UPDATE
-- カーソルより最初の1行を取得
FETCH NEXT FROM 
 CUR_CALENDAR_UPDATE 
INTO
 @wk_year, 
 @wk_month, 
 @wk_day
-- カーソルで取得した行が終端に達するまで処理を継続する
WHILE @@FETCH_STATUS = 0
BEGIN
 -- カーソルで取得したYEARが2006より大きい場合は処理を行う
 IF @wk_year > 2006
 BEGIN
 UPDATE 
 CALENDAR
 SET
 DATE = GETDATE()
 WHERE
 YEAR = @wk_year
 AND 
 MONTH = @wk_month
 AND
 DAY = @wk_day
 END
 -- 次の1件を取得する
 FETCH NEXT FROM 
 CUR_CALENDAR_UPDATE 
 INTO
 @wk_year, 
 @wk_month, 
 @wk_day
END
-- カーソルを閉じる
CLOSE CUR_CALENDAR_UPDATE
-- カーソルのメモリを開放
DEALLOCATE CUR_CALENDAR_UPDATE
批判.
Transact-SQL はPL/SQL同様、機能を追加することで SQL 標準との互換性が損なわれているだけでなく、SQLが本来保持すべきモジュール性を破壊していると批判されている。換言すれば、Transact-SQL の追加機能は普通ならプログラミング言語や埋め込みSQLに実装されるべきものである。そのため、制御構造をプログラミング言語でも SQL でも指定可能になってしまい、混乱が生じる。