[SQL_Server] システムSP: sp_executesql での注意点。

　Stored Procedure で動的に Query を生成・実行したいトキは多々ある。
　ので、よく使うシステム Stored Procedure がある。

　それが『 sp_executesql 』。

　ところが。
　これの細かい仕様を理解せずに今まで使用しておりました。
　Stored を組み始めて・・・早数ヶ月。お恥ずかしい。

　[ sp_executesql ] のパラメーターは大雑把に ( 詳細はBOL参照 )
　　　@stmt ( SQL ステートメント: 必須 )
,@params ( @stmt で使用するパラメーター変数定義(型 , IN / OUT ) : 既定値 NULL )
,@param1 ( @params の値設定 : 既定値 NULL )

　で、第一引数の「@stmt」に注意があったんですよ。
　今日、BOL をきちんと読んだら書いてあった。

　--*--*--*--*--*
　～。+ 演算子で 2 つの文字列を連結するなどの複雑な Unicode 式は使用できません。
　--*--*--*--*--*

　あたしはコレを知らず。
　「@stmt」の型は 『NVARCHAR(MAX)』だけど、「+ 演算子で 2 つの文字列を連結する」と、どうもストレージを最大に使えないんだかなんだか、文字列が切り詰められちゃう。
　( N''プレフィックスをつけると 4000 文字位。つけないと 8000文字位 )

　で、Query が不完全になってエラーを発すると。。。

　そんなワケで、『NVARCHAR(MAX)』って『NVARCHAR(4000)』と変わらない？？
　と勘違いしていました。
　無知っておそろしい。つくづく。

　ちなみに、『NVARCHAR(MAX)』のサイズ説明は BOL には下記のようにあります。(抜粋)
　--*--*--*--*--*
　max はストレージの最大サイズが 2^31-1 バイトであることを示しています。
　ストレージのサイズは、入力文字数の 2 倍のバイト数に 2 バイトを足した数です。
　--*--*--*--*--*
　単純に計算して・・・536870911 文字？？
　( 計算苦手なんで違うかも )


　今更だけど、知ってよかった。で、メモ。

　
　
　
　

[SQL_Server] Query で割算の見落とし。

【目的】Queryで割算して、小数点第一位までパーセンテージを求める。

　ええ、常識かもしれないんですけどね、MEMO。

　割算には ［ / ］(スラッシュ) を用いるワケですが、
　「整数 ÷ 整数」して、戻り値には小数点付の精度を求める場合、ちょっと一手間必要。

　というのも、「整数(A) ÷ 整数(B)」の戻り値は『整数』だそうで。
　だから、[ 整数A ] か [ 整数B ] （若しくは両方）を、演算前に
　小数持ちタイプのデータ型に型変換してあげる必要があると。

** 例 **
SELECT ( 20 / 57 ) * 100 AS Straight
,( CAST( 20 AS FLOAT ) / 57 ) * 100 AS castFLOAT_Single
,( CAST( 20 AS FLOAT ) / CAST( 57 AS FLOAT ) ) * 100 AS castFLOAT
,( CAST( 20 AS DECIMAL ) / 57 ) * 100 AS castDecimal_Single
,( CAST( 20 AS DECIMAL ) / CAST( 57 AS DECIMAL ) ) * 100 AS castDecimal;

[Result]
Straight : 0
castFLOAT_Single : 35.0877192982456
castFLOAT : 35.0877192982456
castDecimal_Single: 35.087700
castDecimal : 35.0877192982456140
** ** **

　というコトで、ジブンは片側だけ [ FLOAT ] にキャストしてあげる方法をとった。

** 解決 **
SELECT CAST( ROUND( ( CAST( 20 AS FLOAT ) / 57 ) * 100 ,1 ,1 ) AS DECIMAL(4 ,1) ) AS N'切捨て'
,CAST( ROUND( ( CAST( 20 AS FLOAT ) / 57 ) * 100 ,1 ,0 ) AS DECIMAL(4 ,1) ) AS N'四捨五入';
** **** **

　何故に更に [ DECIMAL(4 ,1) ] にキャストしているかは、実行してみればわかる。




　

[SQL_Server]bcpユーティリティと「xp_cmdshell」の利用。

【目的】ストアドから、SELECT結果をファイル出力してみよう。

ってコトで、調べつつ組んでみた。
結論から言うと、「xp_cmdshell」は権限がなくて使えなかった。
（ローカルの SQL Server ではなくて、共有のテストサーバーを利用しているので、権限変更できない）

でも、折角なのでメモ。

--*--* bcpユーティリティ
　「bcpユーティリティは、一度に１つのテーブルを処理するコマンドラインユーティリティです。」
　と説明されています。
SQL Server 2005 ストアドプロシージャプログラミングに書いてある。


　この「bcp」を使うと、DBのテーブルからデータをテキストファイルにエクスポート（出力）することが可能。
　また、ファイルからDBにインポートすることも可能らしい。（試していない）
　bcpユーティリティ詳細(MSDN)


　1.　先ず、基本的な形のコマンドを流す。

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--

-- 特定テーブルを一括出力

bcp database名.schema名.table名 out "ファイル出力先パス(ファイル名)" -U username -P password
　↓
bcp dbHoge.dbo.tbHuge out "C:\ListBackUp.csv" -U sa -P hogehoge

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


　上記は、
　dbHogeというデータベースのdbo(デフォルトのスキーマ名)というスキーマにある、
　tbHuge というテーブルのデータを、SQL ServerのあるPC(ローカル)の
　【C:\】ドライブ直下に『ListBackUp.csv』というファイル名の CSVファイルに出力する。
　ちなみに、指定したファイル名のファイルが既に存在すれば、内容を上書き(元の内容は削除)
　して、存在しなければ新規に作成して書き込んでくれる。

　ローカルではなく、ネットワーク上の SQL Server とデータをやりとりするには、
　「-S」オプションをつけてサーバを指定する必要がある。
　この場合のファイル名は、コマンド発行したPC(自分のPC)のパスを指す。

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


bcp dbHoge.dbo.tbHuge out "C:\ListBackUp.csv" -S 192.168.xx.ｘｘ -U sa -P hogehoge

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


　オプションは他にも色々ある。必要に応じて追記したらいいと思う。
　詳細はMSDNで。

　2.上記のコマンドを実行すると、出力形式について対話形式で確認が行われる。
　　指定したテーブルのフィールド数分。

　　確認される質問は以下な感じ。アタクシがやってみた例。

--------------------------------------------------------------------------------
"フィールド cColA [nchar] のファイルストレージ型を入力してください"
　⇒「nchar」と入力してEnter押下
"フィールド cColA [1] のプレフィックス長を入力してください"
　⇒「1」と入力してEnter
"フィールド ターミネータ [none] を入力してください"
　⇒何も入力しないでEnter
--------------------------------------------------------------------------------


　↑は、フィールド一つ分の確認。これが１セット。
　指定したテーブルのフィールドが１０個あれば、これが１０回繰り返される。
　「ファイルストレージ型」と「プレフィックス長」については、フィールドのデータ型
　などで変わるので、全て同じではない。

　3.この確認が終わると、最後の質問がある。


--------------------------------------------------------------------------------
"このフォーマット情報をファイルに保存しますか？ [y/n]"
　⇒「y」と入力してEnter、もっかいEnter
--------------------------------------------------------------------------------


　すると、ファイル出力先に指定したパスに、「ListBackUp.csv」と「bcp.fmt」という
　ファイルが作成される。
　「ListBackUp.csv」を開いてみると、思ったようにきれいにはデータはできていない。

　4.これを整形するために、「bcp.fmt」を編集する。

　　「bcp.fmt」をメモ帳(NotePad)で開く。

============================================================

9.0
5
1       SQLNUMERIC    1       19      ""   1     cColA              Japanese_CI_AS
2       SQLNUMERIC    1       19      ""   2     vcColB             Japanese_CI_AS
3       SQLTINYINT    0       1       ""   3     tiColC             ""
4       SQLDATETIME   0       8       ""   4     dtColD             ""
5       SQLNUMERIC    1       19      ""   5     cColE              Japanese_CI_AS

============================================================

　↑さっき、上記の対話確認の結果作成された内容(らしい)。

１行目　：「9.0」 ⇒ バージョン
2行目　：「5」フィールド数
3行目～：フィールドの出力形式

　3行目以降に各々色づけしたのは以下の説明のため。

●1 ファイルでの、フィールド番号
●2 フィールドの　データ型
●3 フィールドの　プレフィックス長
●4 フィールドの　データサイズ
●5 フィールドの　終端文字
●6 DBテーブル内での、フィールド番号
●7 DBテーブル内での、フィールド名
●8 フィールドの　照合順序

　正直、「nchar」と入力した項目(1, 2, 5) が『SQLNUMERIC』となっているのは
　よくわからん。なぜにニューメリック。

　とにもかくにも、コレを以下のように編集と思うように出力される（た）。
　太文字。

============================================================

9.0
5
1       SYBCHAR    0       19      ","       1     cColA              ""
2       SYBCHAR    0       19      ","       2     vcColB             ""
3       SYBCHAR    0       1       ","       3     tiColC             ""
4       SYBCHAR    0       8       ","       4     dtColD             ""
5       SYBCHAR    0       19      "\r\n"    5     cColE              ""

============================================================

　フィールド番号1～4 の終端文字を「,」にしたのは、 CSVファイルにしたかったから。
　好きなものにしたらいい。
　フィールド番号5 は、改行コード。データ１行毎に改行したいので。

　6.編集したファイルを名前を付けて保存する。
　　名称は仮に『OutputList.fmt』にする。保存先はCドライブ直下。

　7.編集した『OutputList.fmt』をフォーマットオプションで指定して、データ出力してみる。

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


bcp dbHoge.dbo.tbHuge out "C:\ListBackUp.csv" -f C:\OutputList.fmt -U sa -P hogehoge

--*--*--*--*--*--*--*--*--*--*--*--*--*--*--*--


ちょっと中断。編集中。

　テーブルの一括出力だけでなく、オプションでクエリを利用することも可能。
　ほしいデータだけを出力できる。
　ワタクシが使ってみたのは以下な感じ。

[SQL_Server]レコードが○件以上あり、かつ□日以上経過したレコードの抽出(削除)

こんな要望がありました。

　操作ログを、一定期間で削除して欲しい。
　でも、レコードが100件以内だったら、一定期間が経過していても、
　そのユーザーのレコード(操作ログ)は削除しないでほしい。

ちなみに、この対象テーブルには「No列」のようなものはありません。
前回【ROW_NUMBER()を使ってみる】で使用したテーブルです。
これです。

--*--*  操作ログテーブル  *--*--*--*--*--
テーブル：　tbOperationLog -- 操作履歴を格納するテーブル
　カラム：　dtOperation    DATETIME    -- 操作日時
　　　　　　cUserID        NCHAR(8)    -- 操作ユーザーID
　　　　　　cProgramID     NCHAR(8)    -- 操作画面ID
　　　　　　vcContents     NVARCHAR(2) -- 操作種別コード
--*--*--*--*--*--*--*--*--*--*--*--*--*--

で、前回の「ROW_NUMBER()」が登場するワケです。
とりあえず、こんな感じで。
いきなり削除しちゃうのはアレなんで、まずは抽出してみる。
抽出結果が、「削除したいものである」と確認できたら、
01行目の 'SELECT *' を 'DELETE' に直せばOK。

※エディタな感じで 行に番号付けてみた。

------------------------------------------------------------------------------------------
01:  SELECT * 
02:    FROM tbOperationLog
03:   WHERE  EXISTS( SELECT *
04:                    FROM ( SELECT ROW_NUMBER() OVER( PARTITION BY cUserID
05:                                                         ORDER BY dtOperation ASC ) AS RNum
06:                                 ,dtOperation
07:                                 ,cUserID
08:                                 ,cProgramID
09:                                 ,vcContents
10:                             FROM tbOperationLog ) Obj
11:                   WHERE tbOperationLog.cUserID = Obj.cUserID
12:                     AND tbOperationLog.dtOperation = Obj.dtOperation
13:                     AND Obj.RNum > 100
14:                     AND DATEDIFF( DAY, Obj.dtOperation, CURRENT_TIMESTAMP ) > 90 )
------------------------------------------------------------------------------------------

解説：
１つ目のポイントは、03行目の「EXISTS」で条件を指定しているトコロ。
-- というか、要件を満たす条件を指定するには、と考えたトキ、この方法しか思いつかなかった。

２つ目のポイントは、03-10行目で「EXISTS」の条件を、更にサブクエリにしているトコロ。
先ずサブクエリ(04行の「SELECT～」-10行目)で、
仮想の行番号を「ROW_NUMBER()」で付与してテーブルを作ってあげる。
この時点で、ユーザー毎に日付順で行番号が配される。(詳しくは前回を参照)

で、このテーブルを「EXISTS」の条件で SELECTして、
「WHERE句」条件(13行目)で仮想の行番号を指定( Obj.RNum > 100 )する。
こうすることで、100件以内のレコードは条件より外される。

-- ちなみに、サブクエリとして使用しないと、仮想の行番号を「WHERE句」に指定することができない。
-- ソコで初めて作成されるのだから、このQuery内では条件には使えないよね。

あとは、「DATEDIFF()」関数(返り値はINT)で削除期間を指定してあげる。
上記の場合、
DATEDIFF( 日付を, Obj.dtOperationから, 本日日時を引いた ) 数値が、'90' より大きい( > 90 )
なら、という条件になる。


Query一発でできるなんて、ステキング。

　
　
　
　

[SQL_Server]ROW_NUMBER() を使ってみる。

SQL Serverでは、'2005'から「ROW_NUMBER()」という機能(関数)を実装したらしい。
私は使ったことないが、Oracleのソレと同じ(？)ようなものみたい。

MSDN⇒ ROW_NUMBER (Transact-SQL)

簡単に言うと、「ROW_NUMBER()」は SELECT した結果(レコードの集合)に対して、
指定された条件で順位付けして番号を振ってくれる便利関数。
ちなみに、「OVER()句」とセットで使用。条件は、この「OVER( 条件 )」に書く。


先月より触り始めた SQL Server の Stored Procude で、これを使う機会がありそうなので、
ちょいと練習で使ってみた。
以下は、操作履歴テーブル【tbOperationLog】を条件なしに(全件)抽出したもの。
※ここで使用するテーブル、カラムはあくまでサンプル。


テーブル：　tbOperationLog -- 操作履歴を格納するテーブル
　カラム：　dtOperation -- 操作日時
　　　　　　cUserID -- 操作ユーザーID
　　　　　　cProgramID -- 操作画面ID
　　　　　　vcContents -- 操作種別コード


サンプル その1：

SELECT ROW_NUMBER() OVER( ORDER BY dtOperation ASC ) AS 'RowNum'
     ,dtOperation AS 'Date'
     ,cUserID AS 'uID'
     ,cProgramID AS 'pID'
     ,vcContents AS 'Code'
  FROM tbOperationLog
-- ORDER BY vcContents
;

結果：
RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-06-26    SAKURA39    A2008085    02
5         2008-07-19    BLOSSOMS    C2008086    03
6         2008-07-19    SAKURA39    C2008086    03
7         2008-07-27    SAKURA39    C2008086    00
8         2008-08-02    SAKURA39    C2008086    01

解説：
「ROW_NUMBER()」は、直後の「OVER( ORDER BY dtOperation ASC )」を条件に順位付けを行う。
サンプル1 では『操作日時( dtOperation )』の昇順( ASC )で順位付けを行った。

ちなみに、コメントアウトで無効にしている最終行の【ORDER BY vcContents】を有効にすると、
順位は「OVER()句」で指定された条件で行われるが、表示される順序がこっちで行われる。


結果：(外側のORDER BY を有効にした場合)

RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
7         2008-07-27    SAKURA39    C2008086    00
8         2008-08-02    SAKURA39    C2008086    01
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-06-26    SAKURA39    A2008085    02
5         2008-07-19    BLOSSOMS    C2008086    03
6         2008-07-19    SAKURA39    C2008086    03

で、条件指定にはもう少し便利なものがある。
「PARTITION BY」というもの。
「ORDER BY 句」の直前に指定する。
私もコレは初めて知ったものなので、とりあえず使ってみる。

サンプル その2：

SELECT ROW_NUMBER() OVER( PARTITION BY cUserID ORDER BY dtOperation ASC ) AS 'RowNum'
     ,dtOperation AS 'Date'
     ,cUserID AS 'uID'
     ,cProgramID AS 'pID'
     ,vcContents AS 'Code'
  FROM tbOperationLog
;

結果：
RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-07-19    BLOSSOMS    C2008086    03
1         2008-06-26    SAKURA39    A2008085    02
2         2008-07-19    SAKURA39    C2008086    03
3         2008-07-27    SAKURA39    C2008086    00
4         2008-08-02    SAKURA39    C2008086    01

解説：
「PARTITION BY」を指定すると、順位付けを行うグループを設定できる。
サンプル2 は「操作ユーザーID( cUserID )」内で、『操作日時( dtOperation )』の昇順( ASC )に順位付けを行った。


もちろん、「ORDER BY 句」は降順( DESC )の指定もできますよ。

　
　
　
　

[その他]--Firefox3「Download Day」

Firefox(ブラウザ)がNewバージョンの「３」を公開するようです。
明日。

で、24時間内の世界中でのダウンロード合計数で、ギネス登録を目指すらしい。
そして、アタクシはこのイベントに参加しております。
面白そうだったから。

以下のような方は、是非ともダウンロードしてみてください。
・「Google最高っ」な人。
・「Firefox好いよね」な人。
・Firefoxに興味がある人。
・ブラウザを自分好みにカスタマイズ(UI、操作感(プラグイン)など)したい人。
・IEしか使ったことがない、好奇心旺盛な人。
・他のブラウザに飽きた人。
・ギネス登録に参加してみたかった人。
・イベント好きな人。
・レッサーパンダが好きな人。


ダウンロードは簡単ですから。
もちろん、「オレはIE命なんだーっ」という方は無理にダウンロードすることもないのです。

▼クリックしてみたってください（即ダウンロード、ぢゃないです）▼

[MySQL]--SELECTで文字化の対応。

【目的】CONCAT()関数で日本語を連結して、文字化したトキの対処。


●切っ掛け
　以下の様に、CONCAT()関数を使用して文字を連結して、SELECTで抽出してファイルに出力したら、文字化けした。
　　　　　　＊MySQLマニュアル：文字列関数

　(各カラムの値： a.intA = 1, a.intB = 2, a.intC = 3 )
　[修正前SQL] SELECT CONCAT( a.intA, '年 ', a.intB, 'ヶ月 ', a.intC, '日' )
　　　　　 FROM DataTbl a
　[実際の結果] 1蟷ｴ2繝ｶ譛・譌･
　[欲しい結果] 1年 2ヶ月 3日

　で、ネットで検索して解決したので、メモっとくのです。


○解決方法
　1.先ず、CONCAT()関数を使用しているカラムの文字セットを確認。
　　　文字セットの確認は、CHARSET(ｓｔｒ)関数を使う。
　　　CHARSET()関数は、'str'の文字セットを返してくれる関数。
　　　　　　＊MySQLマニュアル：情報関数

　　　[SQL] SELECT CHARSET( CONCAT( a.intA, '年 ', a.intB, 'ヶ月 ', a.intC, '日' ) )
　　　[結果] binary

　　　文字列(型)で返してほしいのに「binary」という結果。
　　　なので、型を文字列型にキャストすることにした。

　2.integer型⇒char型へキャストして、連結する。
　　　キャストには、CAST()関数を使用。
　　　CAST()関数は以下のように使用する。
　　　　　CAST( 変換する値 AS 変換したい型)
　　　　　　＊MySQLマニュアル：キャスト関数と演算子

　　　ちなみに、各値に対してキャストする必要がある。
　　　CONCAT()毎括ってキャストしようとしても、エラーになる。
　　　[例SQL] CAST(CONCAT( a.intA, '年 ', a.intB, 'ヶ月 ', a.intC, '日' ) AS CHAR)
　　　これはエラーを返される。

　　　で、以下のように修正。

　　　[SQL]SELECT CONCAT( CAST( a.intA ) AS CHAR, '年 ', CAST( a.intB ) AS CHAR, 'ヶ月 ', CAST( a.intC ) AS CHAR, '日' )
　　　　 FROM DataTbl a
　　　[結果]1年 2ヶ月 3日


　　　うん。思うとおりの結果。各値毎にキャストするのは、ちょいと面倒ですけど。
　　　念のため、CHARSET()関数で文字セットを確認。

　　　[SQL] SELECT CHARSET( CONCAT( CAST( 1 AS CHAR ), '年 ', CAST( 2 AS CHAR ), 'ヶ月 ', CAST( 3 AS CHAR ), '日' ) )
　　　[結果] utf8

　　　OK。