CASE 文を使って結果を分岐させて取得する SQL 文の備忘録です。
CASE 式によって、SQL 文で if-then-else の機能を実装できます。

CASE文は、一連の条件を評価して、考えられる結果式のうちの 1 つを返します。
CASE 式には 2 つの形式があります。
　単純 CASE 式では、1 つの式を一連の単純式と比較して結果を決定します。
　検索 CASE 式では、一連のブール式を評価して結果を判定します。
どちらの形式も、ELSE 引数 (省略可) をサポートしています。
CASE は、有効な式を使用できる任意のステートメントや句で使用できます。 たとえば、SELECT、UPDATE、DELETE、SET などのステートメントや、select_list、IN、WHERE、ORDER BY、HAVING などの句で使用できます。

簡単な例を記述します。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)
 
select * from @tbl1

--例１　単純 CASE 式
select a_cd,
    case b_kbn when '1' then 'OK' when '2' then 'NG' else '-' end as 判定
from
    @tbl1

--例２　検索 CASE 式
select a_cd,
	case
		when b_kbn = '1' OR c_kbn = '0' then 'OK1'
		when b_kbn = '1' OR c_kbn = '1' then 'OK2'
		when b_kbn = '2' OR c_kbn = '0' then 'NG1'
		when b_kbn = '2' OR c_kbn = '1' then 'NG2'
		else '-' 
	end as 判定
from @tbl1

ちなみに、合致する条件がなく、 else の指定もなければ、 CASE 式は null を返します。
以上、 CASE 文を使って結果を分岐させて取得する SQL 文の備忘録でした。

データベースを管理していると、いろいろな理由でデータを読み替えて取得したいことがあります。
例えば、プログラム決め打ちで登録したデータが間違っていて、登録ミスが発生したいた場合には一括でデータの文字列を変えたいです。
また、安易に考えたタグ名などを変えたい時などもたまにあります。

そんな時、SQL SERVERであれば、replace関数を使います。
使い方は至って簡単です。

replace([項目],’置き換え前の文字’,’置き換え後の文字’)

例）select replace(‘abcde’,’abc’,’cba’)
結果）’cbade’

テーブルのデータを一括で置き換えたい時は、
update tableA
set [項目]=replace([項目],’前の文字’,’後の文字’)
from tableA
where 条件

などとすれば一括で文字列置き換えができます。
以上、replace 文字列置き換えの開発備忘録でした。

SQL SERVERでデータを更新する時に簡易にテーブルデータのバックアップを取って置きたいことがあります。そんな場合は以下のようなSQL文を実行します。
私は日付やOLDをつけてコピーしています。

select into [DestinationTable] from [CopyTable]

DestinationTableをあらかじめ作成する必要は無くお手軽です。
ただし、CopyTableの行動とデータのみをコピーするため、キーやインデックス等のCONSTRAINT条件は付加されません。
あくまでも簡易バックアップなので。
必要がなくなったらごみになるので、コピーしたテーブルは削除しましょう。

以上、簡単なテーブルのコピーの開発備忘録でした。

今日はSQL Serverネタです。
最近、Azure SQLの開発をしているのですが、SSMS（SQL Server Management studio)からテーブルの構造を更新できないのでデータ定義言語(Data Definition Language, DDL)で更新しています。

開発時はローカルのSQL Serverで全て行っていたのですが、いざAzure SQLにリリースという段階になるとローカルで修正して本番に移すという作業が出来ない状況になることがあります。
客先で桁あふれデータを見つけたりしてね。。。
そんな時、常日頃SSMSで行っていたテーブル構造の変更をDDLでやらなくてはいけないので、あれ？なんだったけ？ってなります。

ちなみに、Azure SQLをSSMSで行うことはSQL Server 2016のSSMSまではできませんでしたが、SQL Server 2016のSSMSでは可能になりました。この記事を書いている段階ではCTP3版ですが、問題なく編集できます。
非常に助かります。今まで当たり前にできてたことができないって、ほんと面倒に思えますから。
テーブル構造も早く変更できるようにしてもらえればありがたいのですが。。。

本題に戻ります。
忘れてしまっていたテーブル構造を変更するDDLですが、以下の通りです。
追加：Alert Table [table] add [column_name] [column_datatype](,[column_name] [column_datatype]);
変更：Alter Table [table] alter column [column_name] [column_datatype](,[column_name] [column_datatype]);
column名の変更：sp_rename @objname='[table].[old_column_name]’, @newname='[new_column_name]’, @objtype=’column’;
削除：Alter Table [table] drop column [column_name](,[column_name]);

こんな感じです。
その内SSMSで変更できるようになると思います。
本来、Azure SQLにテストリリースする段階では、仕様の変更など無いにこしたことなのですが、実データを流し込むと桁あふれが起こったり、エンドユーザが実画面を見て「こうだったらもっと便利なのに」ってフラグを追加して処理を分岐させたり、いろいろなケースでちょっと修正となることも多いと思います。
特に中小企業では業務設計も仕様確定も固まっていないことも多く、手戻りを覚悟して構築していくことも多々あるので。
この辺りの事前把握を行う構築スキルをもっと磨いていきたいです。

Alter TableのSQL Serverの備忘録まで。

今日は、設定の備忘録です。
最近最近ノートPCに、Windows 10、Visual　Studio　2015とSQL Server 2014をインストールして、出先でデモとか仕様確認をする際に開発中のアプリを動かして使用しています。

で、出先でデータスキーマを触ってしまうことも有って、開発マシンからノートPCのSQL Serverにアクセスしたくなることも多くて。。。
今回はそのWindows10上のSQL Server 2014に外部から接続する設定を書いておこうかと。まぁ、設定自体は今までと変わりないのですが、最近ブログを書く機会も少ないので＾＾；書いておこうかと。。。＾＾；；；

まずは、SQL Server Configuration Manager (SQL Server 構成マネージャー）を立ち上げます。
これって、SQL Server 2012と違ってまたスタートメニューから起動できるようになりましたね。ちょっと便利になって嬉しいです。

で、名前付きパイプかTCP/IP使う方を有効にします。私は面倒なので両方とも有効にしちゃいます。
ちなみにTCP/IPの既定のポート番号は1433ですが、ここをセキュリティの為に変更する人はここに見に来ませんよね？変える時はちょっと注意が必要なので。。。

で、次はfirewallに穴を空けてSQL Serverの通信が通るようにしてあげます。
windows 10 で穴を空けるのは初めてなので、画面のキャプチャーを12枚も撮ってしまいました。。。

まずはWindows 10の設定画面を立ち上げます。

イーサネットをクリックして、更にWindowsファイアウォールをクリックします。

Windowsのファイアウォール設定画面が開きます。

詳細設定をクリックします。

ファイアウォールの設定が見られる画面が開きます。

受信の規則をクリックします。
新しい規則。。。をクリックします。

SQL Serverの通信を通る規則を作ります。
TCP/IPのポート1433で作っても良いし、プログラムを指定して作ってもOKです。
今回はプログラムを指定しています。

プログラムのパスを指定します。自力で書くには難しいので、参照をクリックしてsqlserverの本体プログラムを指定します。
参照をクリックしましょう。

SQL Serverのファイルの在りかを事前に確かめておくと良いですが、大体は「C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn」辺りに居ると思います。

SQL Serverをインストールする時に規定にするか覚えておくと探さなくて済みます。
ファイルをクリックして、開くをクリックします。

先ほど指定したパスが張り付いていると思います。
次へをクリックします。

ここら辺は自分の環境に合わせて設定してください。
私は開発環境なので特にセキュリティは意識していませんが、実務に使う設定なら気にする必要があるかもしれないので。

ここも同じですね。自分の環境に合わせて設定してください。

ここでは後で見た時にわかりやすく名前で登録しておきます。
私は何も考えず、SQL Serverとしています。
名前を入力したら完了をクリックします。

設定した規則が登録されているはずですので確認します。

後は、実際に他のPCから接続が確認出来れば、作業は完了です。
Windows10でSQLServer2014に外部から接続出来るようにファイアウォールを設定したシステム開発備忘録でした。

今回はカーソルについてです。
基本的にデータベースのデータ操作を行う際はメモリIO、ディスクIOを抑えるため一括して行う方が効率良くデータ処理ができます。しかしながら、一括でデータ操作ができないケースも出てきます。
そんな時に活躍するのがカーソル（CURSOR）です。

カーソル CURSORとは

詳細な説明はSQL Serverのドキュメントで見て欲しいのですが、カーソル（CURSOR）はselectで抽出したデータに対して一行づつ取り出すことができる逐次処理ができる仕組み・機能です。
一行づつ取り出しができるので、その行に対して処理を書けることができます。一括で処置ができない、または一括では処理が重たくてレスポンスが悪くなるなどのケースに使用します。
カーソル CURSORの使い方

カーソル（CURSOR）の使い方は難しくありません。カーソル（CURSOR）のネストもできます。ただし、もともと複雑なことを対象に処理を行うことを考えるとトランザクション設計などはきちんとしないといけませんが。。。

カーソル（CURSOR）を使うには、カーソルの宣言を行います。カーソルの宣言にはデータを抽出するSQLも合わせて示すことになります。
また、抽出した際に抽出データを入れる変数も予め宣言しておかなくてはいけません。
その後、カーソル（CURSOR）をOPENして、データが無くなる（Fetchが失敗する）までループさせ一行づつ処理を行います。
データが無くなる（強制的にループを終了させる）った後は、カーソル（CURSOR）をCLOSEして、カーソル（CURSOR）をDEALLOCATEして終わりです。

DEALLOCATEを使ったSQL例

行っていることは、

declare @tbl1 table(
	id int,
	kbn nchar(10) ,
	point int
)

declare @start_id as int
set @start_id=0

while @start_id < 10
	begin
		print @start_id
		insert into @tbl1 values(@start_id, @start_id % 3, @start_id * Rand() * 10)
		set @start_id = @start_id +1
	end

select * from @tbl1

declare @test_id int, @test_point int
declare @add_point int
set @add_point=100

--カーソル
declare test_cursor cursor for
select id, point from @tbl1 where kbn = 1 order by id
open test_cursor

fetch next from test_cursor into @test_id, @test_point

while @@FETCH_STATUS = 0
begin
	update @tbl1 set point+=@add_point where id=@test_id
	set @add_point=@test_point
	fetch next from test_cursor into @test_id, @test_point
end
close test_cursor
deallocate test_cursor

select * from @tbl1

実行結果

以上、カーソル CURSORの使い方まで