LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ShotaIchikawa1208(市川 翔大)in株式会社オフグリッド

TransactSQLの基本とストアドプロシージャなどの作成

Posted at

TransactSQL(T-SQL)やストアドプロシージャなどの作成の基本をまとめます

目次

TransactSQL(T-SQL)とは

Transact-SQL（T-SQL）とは、Microsoft社が独自拡張したSQL言語です。標準SQLにはない制御フロー（IF・While文）、変数、独自関数などが利用できるよう拡張されており、「順次実行」「分岐」「繰り返し処理」を行うことができます。
ただ、T-SQLはMicrosoft SQL Serverで使用できる拡張SQLであり、SQL Server以外では使用できません。(Oracleの拡張SQLは、PL/SQL)

T-SQLの主要な機能

変数宣言

構文 : DECLARE [@変数名]　[変数のデータ型]

変数宣言.sql
DECLARE @number INT
SET @number = 1
print(@number) -- 出力結果　1

DECLARE @word NVARCHAR(10) = 'こんにちは'
print(@word) -- 出力結果　こんにちは

下記のように、SELECT文の値を変数に代入することも可能です。また、SQL Serverではデータ型の優先順位があり、異なる型を結合する際には優先順位の高い方に暗黙的に変換されるため、常に明示的な型変換を行うことが必要です。

sample.sql
DECLARE @id INT;
DECLARE @name NVARCHAR(50);

SELECT @id = id, @name = name  FROM test

PRINT(CONVERT(NVARCHAR(10), @id) + ':' + @name)

IF文

IF文.sql
DECLARE @number INT = 100;

IF @number = 100
BEGIN
	PRINT('条件はTRUEです')
END
ELSE
BEGIN
	PRINT('条件はFALSEです')
END

While文

「CONTINUE」や「BREAK」も使用できます。

While文.sql
DECLARE @number INT = 0;

WHILE @number <= 10
BEGIN
	IF @number = 5
	BEGIN
		SET @number += 1
		CONTINUE;
	END

	IF @number = 9
	BEGIN
		BREAK;
	END

	PRINT(@number)

	SET @number += 1
END

■FOR文
T-SQLにFor文はなく、WhileループやCURSOR操作を用いて、繰り返し処理を行う必要があります。

例外処理

TRY-CAHTCH.sql
BEGIN TRY
	SELECT 1/0; 
END TRY
BEGIN CATCH
    SELECT
        ERROR_LINE() AS　ERROR_LINE
        , ERROR_MESSAGE() AS ERROR_MESSAGE
        , ERROR_NUMBER() AS ERROR_NUMBER
        , ERROR_PROCEDURE() AS ERROR_PROCEDURE
        , ERROR_SEVERITY() AS ERROR_SEVERITY
        , ERROR_STATE() AS ERROR_STATE
END CATCH

例外が発生した際の詳細はシステム関数を使用して取得することが可能です。

システム関数　　　　 説明
ERROR_LINE エラーが発生したルーチン内の行番号を返す。
ERROR_MESSAGE エラー メッセージの完全なテキストを返す
ERROR_NUMBER エラー番号を返す
ERROR_PROCEDURE エラーが発生したストアド プロシージャまたはトリガーの名前を返す
ERROR_SEVERITY 重大度を返す
ERROR_STATE エラー状態番号を返す

トランザクション

トランザクション.sql
BEGIN TRANSACTION

BEGIN TRY
    -- トランザクション内の処理 idかプライマリーキーのテーブルにINSERT処理
    INSERT INTO test (id, name) VALUES (1, 'Bob')
    INSERT INTO test (id, name) VALUES (2, 'Ken')
    INSERT INTO test (id, name) VALUES (1, 'Nancy') -- プライマリーキー違反でエラー

    -- トランザクションのコミット
    COMMIT TRANSACTION
    
END TRY
BEGIN CATCH
    -- エラー発生時のロールバック
    ROLLBACK TRANSACTION

    SELECT
    ERROR_LINE() AS　ERROR_LINE
    , ERROR_MESSAGE() AS ERROR_MESSAGE
    , ERROR_NUMBER() AS ERROR_NUMBER
END CATCH

ストアドプロシージャの作成

■ ストアドプロシージャとは
一連のデータベース操作の手順をまとめ、事前にデータベースオブジェクトとして保存したものです。複雑な処理をカプセル化することにより、再利用性や保守性が高まります。

ストアドプロシージャ構文.SQL
-- ストアドプロシージャの作成
CREATE PROCEDURE [ストアドプロシージャ名]
    @パラメータ名　パラメータのデータ型
    , @パラメータ名 パラメータのデータ型
    ・・・
    , @パラメータ名 パラメータのデータ型
AS
BEGIN
   -- SELECTやUPDATEなどのクエリ群
END

-- ストアドプロシージャの実行
EXECUTE [ストアドプロシージャ名] [パラメータ１],[パラメータ２],・・・

下記は、employeeテーブルから対象の社員名と年齢を取得するストアドプロシージャの例です。

ストアドプロシージャ作成.sql
CREATE PROCEDURE GetEmployee
(
	@target_employee_id INT
	, @employee_name NVARCHAR(50) OUTPUT
	, @age INT OUTPUT
)
AS
BEGIN
	SELECT @employee_name = employee_name
			, @age = age
	FROM employee
	WHERE employee_id = @target_employee_id

END
プロシージャ呼び出し側.sql
DECLARE @name NVARCHAR(50)
DECLARE @age INT

exec dbo.GETEmployee 1,@name OUTPUT,@age OUTPUT

print('社員名：' + @name + ' 年齢：' + CONVERT(NVARCHAR(10), @age))

■ OUTPUTキーワードについて
ストアドプロシージャ作成.sqlのパラメータのデータ型の後にOUTPUTをつけることにより、該当のパラメータが変更された際、変更後の値をストアドプロシージャの呼び出し元に返すことができます。(参照渡しとなる)注意点としては、呼び出し側でもパラメータの後にOUTPUTを記述する必要があります。

ストアドファンクションの作成

■ ストアドファンクションとは
特定の値の計算やデータ変換するための、事前に定義されたSQL文の集合です。ストアドプロシージャと似ていますが、ストアドファンクションには戻り値があります。

ストアドファンクション構文.SQL
-- ストアドプロシージャの作成
CREATE FUNCTION [ストアドファンクション名]
(
    @パラメータ名　パラメータのデータ型
    ,  @パラメータ名　パラメータのデータ型
    ・・・
    , @パラメータ名　パラメータのデータ型
)
RETURNS [ストアドファンクションの戻り値のデータ型]
AS
BEGIN
    [クエリ群]

    RETUNR [ストアドファンクションの戻り値]
END

ストアドファンクションは、ユーザー定義関数である為、通常のSQL文のSELECT句で、呼び出すことが可能です。

下記は、西暦の年月日(DATE型)を、和暦変換（NVARCHAR型）するストアドファンクションの例です。明治以降の西暦は、全て明治変換されてしまします。

sample.sql
CREATE FUNCTION ConvertToWareki
(
	@date DATE
)
RETURNS NVARCHAR(20)
AS
BEGIN
	DECLARE @result NVARCHAR(20)
	DECLARE @year INT = YEAR(@date)
	DECLARE @month INT = MONTH(@date)
	DECLARE @day INT = DAY(@date)

	IF @date >= '2019-05-01'
	BEGIN
		SET @result = '令和' + CONVERT(NVARCHAR(4), @year - 2018)
	END
	ELSE IF @date >= '1989-01-08'
	BEGIN
		SET @result = '平成' + CONVERT(NVARCHAR(4), @year - 1988)
	END
	ELSE IF @date >= '1926-12-25'
	BEGIN
		SET @result = '昭和' + CONVERT(NVARCHAR(4), @year - 1925)
	END
	ELSE IF @date >= '1912-07-30'
	BEGIN
		SET @result = '大正' + CONVERT(NVARCHAR(4), @year - 1911)
	END
	ELSE
	BEGIN
		SET @result = '明治' + CONVERT(NVARCHAR(4), @year -1867)
	END

	RETURN @result + '年' + CONVERT(NVARCHAR(2),@month) + '月' + CONVERT(NVARCHAR(2), @day) + '日'
END

トリガーの作成

■ トリガーとは
テーブルに付随するデータベースオブジェクトで、テーブルに対する変更（INSERTやUPDATE、DELETEなど）があった際に、発生するイベントを定義できます。

トリガー構文.SQL
-- トリガーの作成
CREATE TRIGGER [トリガー名] ON [データ操作を行う元テーブル名]
    ｛FOR | AFTER｝ {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- トリガー起動時に行いたい処理・クエリ群
END

{FOR | AFTER}は、データ操作が行われる前にトリガーが実行されるか(FOR)、データ操作後にトリガーが実行されるか(AFTER)を指定します。{INSERT | UPDATE | DELETE}では、どのデータ操作後にトリガーが実行されるかを指定します。

下記は、employeeテーブルに変更があった際にemployee_changed_logテーブルを更新するトリガーのサンプルです。

sample.sql
CREATE TRIGGER EmployeeChange ON employee
	 -- employeeに「INSERT,UPDATE,DELETE」が発生した際に下記の処理が発動
     AFTER INSERT,UPDATE,DELETE
AS
BEGIN
	SET NOCOUNT ON;

    -- INSERT,UPDATE,DELETE」のどの処理が行われたか格納
	DECLARE @action nvarchar(10); 

    -- inserted,deketedテーブルのデータの有無で処理内容を判別
	IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
	BEGIN
		SET @action = 'UPDATE'
	END
	ELSE IF EXISTS (SELECT 1 FROM inserted)
	BEGIN
		SET @action = 'INSERT'
	END
	ELSE IF EXISTS (SELECT 1 FROM deleted)
	BEGIN
		SET @action = 'DELETE'
	END

    -- 変更履歴テーブルを更新
	INSERT INTO employee_changed_log(employee_id, Action, changed_at) 
	SELECT
		COALESCE(i.employee_id, d.employee_id)
		, @action
		, GETDATE() 
	FROM
		inserted AS i 
		FULL OUTER JOIN deleted as d
			on i.employee_id = d.employee_id
END

■ inserted/deletedテーブルについて

  • insertedテーブル : INSERT または UPDATE ステートメントの後に、新しいまたは変更された行のコピーが格納されます。

  • deletedテーブル : DELETE または UPDATE ステートメントによって変更される前に、影響を受ける行のコピーがトリガー テーブルに格納されます

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?