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

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

# 目次
- [TransactSQL(T-SQL)とは](#transactsqlt-sqlとは)
- [T-SQLの主要な機能](#t-sqlの主要な機能)
- - [変数宣言](#変数宣言)
- - [IF文](#if文)
- - [While文](#while文)
- - [例外処理](#例外処理)
- - [トランザクション](#トランザクション)
- [ストアドプロシージャの作成](#ストアドプロシージャの作成)
- [ストアドファンクションの作成](#ストアドファンクションの作成)
- [トリガーの作成](#トリガーの作成)

# 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)
```

https://learn.microsoft.com/ja-jp/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16

## 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
```
例外が発生した際の詳細はシステム関数を使用して取得することが可能です。
<table> 
  <thead>
    <tr>
      <th>システム関数　　　　</th> <th>説明</th>
    </tr>
  </thead>
  <tr>
    <td> ERROR_LINE </td> <td>エラーが発生したルーチン内の行番号を返す。</td>
  </tr>
  <tr>
    <td> ERROR_MESSAGE </td> <td>エラー メッセージの完全なテキストを返す</td>
  </tr>
   <tr>
    <td> ERROR_NUMBER</td> <td>エラー番号を返す</td>
  </tr>
  <tr>
    <td> ERROR_PROCEDURE </td> <td>エラーが発生したストアド プロシージャまたはトリガーの名前を返す</td>
  </tr>
  <tr>
    <td> ERROR_SEVERITY </td> <td>重大度を返す</td>
  </tr>
   <tr>
    <td> ERROR_STATE </td> <td>エラー状態番号を返す</td>
  </tr>
</table>

https://learn.microsoft.com/ja-jp/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver16

## トランザクション
```トランザクション.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 ステートメントによって変更される前に、影響を受ける行のコピーがトリガー テーブルに格納されます

https://learn.microsoft.com/ja-jp/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver16