ORACLE -> SQL Server 移植Tips

タイトルママ。文法の違いをどのように吸収するかの方針。

##変数の宣言

以下がほぼ等価。


	ORACLE		:	Declare
						code as char
						name as varchar
					Begin

	SQLServer	:	Declare @name nChar(10);
					Declare @name nVarChar(20);

なお、ORACLEはヘッダで一括宣言ぽい（未確定)が、SQLServerはどこでも宣言可能。
・注意点
　NUMBER型は存在しない→INTEGERやDECIMAL型へ
　NCHAR / NVARCHARはきちんと桁指定すること。しないと1桁として処理される。

##変数の代入

ex)各「Test」という文字型変数があるとき、"Test"に"abc"を設定する場合

	ORACLE		: Test := 'abc'
	SQLServer	: Set @Test = 'abc'

##SQLServerのIF文

	・基本形
		If <条件式1>
			BEGIN
				<実行式1>
				<実行式2>
			END
		ELSE IF <条件式2>
				<実行式3>
		ELSE
				<実行式4>
	
THEN不要。END IF不要。条件式にかっこ不要。
実行式行が1行の時はBEGIN - ENDでくくらなくてもよいが、混乱のもとになるのでなるべく。
（END IFも不要なので余計混乱する元になる）

##代替関数
適宜追加できるといいな。

・第一引数がNULLの場合は第2引数を、NULLでない場合は第1引数の値を返す。

	ORACLE     : NVL()
	SQLSERVER  : ISNULL()	

・条件分岐式

	ORACLE     : DECORDE()
	SQLSERVER  : CASE()	

```EX.ORACLE
	SELECT
	DECODE (charId,
		10, 'Keito',
		20, 'Makoto',
		30, 'Akazukin',
		623, 'Mutsumi-san',
		'NONE') name, charId 
	FROM charactor; 
```

```EX.SQLServer

	SELECT 
	CASE  charId 
		WHEN 10  THEN 'Keito' 
		WHEN 20  THEN 'Makoto' 
		WHEN 30  THEN 'Akazukin'
		WHEN 623  THEN 'Mutsumi-san'
		ELSE 'NONE' END name, charId 
	FROM charactor; 
```

##ストアドプロシージャの呼び出し方

ex)「Test_Proc(var1, var2)」というプロシージャをコールする場合

	ORACLE		: Test_Proc var1, var2
	SQLServer	: exec Test_Proc @var1, @var2


##SELECT文中の値の変数への設定

ex)COLUMN1の値を変数TESTに設定する場合

	ORACLE		: SELECT COLUMN1 INTO TEST FROM TABLE1;
	SQLServer	: SELECT @Test = COLUMN1 FROM TABLE1;


##NEWとOLD、insertedとdeleted
・トリガー起動のもととなったレコード情報。
  OLD/deletedはDELETEまたはUPDATE(更新前)のレコード,
  NEW/insertedはINSERTまたはUPDATE(更新後)のレコードが格納されている。
  SQLServerのinserted/deletedは手ブルなのでこれをフェッチする必要がある。

		ORACLE		: ヘッダにて"REFERENCING OLD AS OLD NEW AS NEW"を定義
		SQLServer	: deleted / insertedテーブルとして使用可能(特に宣言などは不要)

##通常のカーソル
ORACLEにてヘッダで定義しておけば勝手に各行を処理してくれる
FOR EACH ROWが存在しない、SQLServerの代替手段。
	
	宣言		：DECLARE Cursol_TEST Cursor for SELECT COLUMN1、COLUMN2 FROM TABLE1
	開く		：OPEN Cursol_TEST;
	値をセット	：FETCH NEXT FROM Cursol_TEST into @VAR1, @VAR2　※
	閉じる	：CLOSE Cursol_TEST;
	破棄		：DEALLOCATE Cursol_TEST;

	※FETCH NEXT の他、FETCH FIRST, FETCH LAST,なんかもある。

基本は宣言→ループ開始→開く→値をセットして処理実施 * N →処理完了後に閉じる→破棄の一連。
なお、CLOSEと破棄は必ず行うこと。忘れるとリークします。
処理が完了したかは後述の@FETCH_STATUSについて参照。

##"Before"(Trigger Header)と"Instead of"
ORACLEにてヘッダに(略)のBEFOREが存在しない、SQLServerの代替手段。
https://qiita.com/so_nkbys/items/f09d3c5d7e1beb639eca　
も参照のこと。

なお、ORACLEは実行対象行が0件の場合トリガーは起動しないが
**SQLServerは0件でも作動**する。注意。

Instead Of仕様:実行されるはずの動作をキャンセルし、トリガー内部の動作を実行する
  →最後に(For Each rowならFetchするたび）本来行われるはずの処理を追加で実施する。

##"For Each Row"(Trigger Header)
ORACLEで(ry)代替手段。
SQLSErver側ではWhile(1 = 1)を定義し、ループ内部でカーソル"inserted" "deleted"のレコードを使用して実装する。

ex)SQLServer例：削除または更新での更新前のレコードの値をID検索し、
   Fetch可能な限り値を変数RECに格納する場合

	while(1=1)
	begin
		fetch next from deleted
		if @@FETCH_STATUS <> 0
		begin
			break;
		end
		
		SELECT @REC = COLUMN1 FROM DELETED WHERE @ID = ID
	end

##処理区分の判定
Fetchしなくても種別は判別可能。面倒ならもうこれ固定でいいかも。

	declare @IsDelete bit;

	declare @ProcDiv	integer;	--処理区分	1:削除 2:挿入 3:更新
	declare newTable cursor for select * from inserted order by ID;
	declare oldTable cursor for select * from deleted order by ID;

	If Exists(select * from deleted)
		set @ProcDiv = 1;
		
	If Exists(select * from inserted)
		set @ProcDiv = @ProcDiv + 2;


##@@FETCH_STATUSについて

カーソルに対してFETCHを行った際の処理結果
FETCHが正常終了した場合は0が返る。
最終レコード時にFETCH NEXTすると-1が返るため、最終行かどうかの判定に使用できる。

##環境について

①カーソルのスコープ
デフォルトだとグローバルスコープのため、同時実行時に衝突する可能性がある。
（とはいってもトランザクションで排他制御されるとは思うが…）
なので念のため、スコープの寿命をローカルに変更しておく。

	SELECT is_local_cursor_default FROM sys.databases WHERE name = '<DB_Name>'

結果が0:グローバル　1:ローカル

スコープ範囲をローカルにしたい場合は下記を実行する。

	ALTER DATABASE <DB_Name> SET CURSOR_DEFAULT LOCAL;

スコープ範囲をグローバルにしたい場合は下記を実行する。

	ALTER DATABASE <DB_Name> SET SET CURSOR_DEFAULT GLOBAL;

2018/03/08
　初稿　適宜追加する。かも。 
2018/03/09
　"Before"(Trigger Header)と"Instead of"に以下記述追記
　・別記事へのリンク
　・OracleとSqlServerでのトリガー実行条件(処理件数0時)について