ITコーディネータのシュウです。

2番目の娘が、土浦市の霞ケ浦総合公園で行われているイルミネーションを見てきたということで送ってきてくれた写真です。水郷桜イルミネーションと言うんですね。高さ25ｍ・羽根直径20ｍのオランダ型風車のイルミネーションを中心に，市の花である『桜』や『土浦の花火』『霞ケ浦』『帆引き船』『ハス田』など土浦市の資源をモチーフにしたイルミネーションだそうです。私も行ってみたいですね。

さて、年末も間近に迫ってきました。ブログをアップしたいと思いつつも、日々の仕事に追われて、ヘトヘトです。そう言えば、先日まで日本でFIFAクラブワールドカップが行われ、開催国代表の鹿島アントラーズが、何と準優勝！それも、あのクリスティアーノ・ロナウド擁するレアル・マドリードを一時は逆転して追い込む場面もあり、最後は延長でのロナウドの2得点というさすがと思わせるゴールも見られた、とても面白い試合だったと思います。日本のJリーグのチームがあそこまでやるとはと、とても興奮しましたね。今後の日本のサッカー界にとても希望を持たせる活躍だったと思います。

話しは変わりますが、dbSheetClientのユーザー事例に、株式会社みうら様の事例が載りました。
Excelのインターフェースを活かした見積作成、及び納期・工程進捗管理システムを短期間で構築、内製化を実現し、ERPとの連携を効率化した事例ということで、お客様もとても喜ばれているようです。ご興味ある方は、是非こちらをご参照ください！
http://www.newcom07.jp/dbsheetclient/usrvoice/miura_system.html

なお、今回が今年最後のブログになりそうです。
1年間どうもありがとうございました。_m(. .)m_  来年も、よろしくお願いいたします。

＜本日の題材＞
varchar(max)、nvarchar(max) (SQL Server)

何回か前に、テーブルの項目数、レコード長の制限という内容で、SQL Serverの１レコードの制限が8060バイトという内容についてブログで取り上げましたが、SQL Server 2005から、大きな値のデータ型とよばれる、varchar(max)、nvarchar(max)型というものが機能として増えていますので、今回はこれを取り上げてみたいと思います。（他に、varbinary(max)型というバイナリデータを扱うものもあります）

システムの開発を行っていると、ときには、文字の桁数をどれくらいにしていいかを決めかねる場合があります。普通はそれほど多く入力しないけれども、ときにはいろいろな情報や説明を書きこむ必要があって、とても大きな桁数の入力をする可能性があるケース、そして、それも1レコード上に複数そのような項目が発生してしまう可能性があるケースなどもときに遭遇することもあります。その場合に、テーブルをうまく分割して対応するなどもありますが、いろいろと処理が複雑になってしまったりで悩んでしまうこともあると思います。

そんなときに、varchar(max)、nvarchar(max) を使うと、1項目の最大バイト数が varchar(max)では2^31-1、nvarchar(max)では文字数が最大2^30-1 というとてつもない大きさまで持つことができ、当然1レコードもその分は確保されるということになるため、テーブルの分割なども気にせずに対応することができることになります。

ちなみに、以前から text型、ntext型というサイズの大きなデータ型は用意されていましたが、whereの条件で = を使用することができないなどの制約がありました。将来のバージョンでは削除される予定ですので、これらを使うのではなく、varchar(max)、nvarchar(max)を使うように推奨されていますね。

今回は、それを試してみます。
例）

create table dbo.test_big(
a varchar(10),
b varchar(max),
c nvarchar(max),
d text,
e ntext
constraint pk_test_big primary key (a));

insert into dbo.test_big values('001','abcde',N'abcde','abcde',N'abcde');
insert into dbo.test_big values('002','fgabc',N'fgabc','fgabc',N'fgabc');
insert into dbo.test_big values('003','jklab',N'jklab','jklab',N'jklab');

このときの、各項目のバイト数を確認してみると、

select
a, datalength(b) b_byte, datalength(c) c_byte, datalength(d) d_byte, datalength(e) e_byte
from dbo.test_big
order by a;

 nvarchar(max)、ntext型は、5文字で10バイトを取っていて、varchar(max)、text型の2倍のサイズになっていることが確認できます。頭に n がつく nvarcharやncharなどは、unicode文字列をサポートするデータ型であり、2バイトを使用して1つの文字をエンコードする仕組みのため、文字数の2倍のバイト数が取られることになります。

それでは、varchar(max)、nvarchar(max)型は、whereの条件に「=」を使えますが、text型、ntext型では使用できないことを確認してみます。

select * from dbo.test_big
where b = 'abcde'
 and c = N'abcde';

select * from dbo.test_big
where d = 'abcde';

select * from dbo.test_big
where e = N'abcde';

上記から、varchar(max)型、nvarchar(max)型は、通常の varchar, nvarchar 型と同様に、whereに「=」の条件を設定して使用できるけれども、text、ntext型では、whereに「=」演算子は使用できないことが確認できました。
ちなみに、like 演算子は使用できます。

select * from dbo.test_big
where d like 'abc%'
  or e like N'abc%';

次は、nvarchar(max)型を１つのテーブルに複数持ち、それぞれに8000文字（100文字ずつを改行して80行分）のデータを登録してみます。

create table dbo.test_big2(
a varchar(10),
b nvarchar(max),
c nvarchar(max),
d nvarchar(max),
e nvarchar(max),
constraint PK_test_big2 Primary key (a));

insert into dbo.test_big2(a,b,c,d,e) values(
'123',
N'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
……. （後ろは省略）

※画面は insert文の最初の部分のみ

すると、各項目のバイト数を抽出してみると、

select
a, datalength(b) b_byte, datalength(c) c_byte, datalength(d) d_byte, datalength(e) e_byte
from dbo.test_big2;

 これは、8000文字分に加えて、改行コードはCHAR(13) - CR: キャリッジリターンと、CHAR(10) - LF: ラインフィードの組合せで合わせて2バイトになるので、8000+80*2=8160バイトのところ、nvarcharはvarcharの2倍のサイズを取るため、8160*2=16,320バイトとなっています。

上記から、1レコードの合計のレコード長も、65,283バイトとなり、以前紹介した8,060バイトの壁も問題なく超えて、長いサイズのレコードが登録できることが確認できました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

先日、会社の親睦会でさいたま市のさぎ山記念公園というところに行ったときに撮った写真です。

仕事が結構忙しくなってせわしない状況ですが、世界の情勢もいろいろと変化が激しいことを感じるこの頃ですね。アメリカでは、大統領選で、当初の予想を覆してトランプ大統領が次期大統領に決定し、TPPを離脱するというメッセージを出しましたし、お隣の韓国では、朴大統領のスキャンダルによる弾劾手続きが始まろうとしている状況で、朴大統領自身も任期満了前に辞任する意向を示しました。日本にも影響があると思われる出来事がさまざま起きている現状です。
目まぐるしく変わる国際情勢の中、先がなかなか見えない状況がありますが、今できることをしっかりとやって行くしかないと思いながら、、、
今日も頑張るぞ！

＜本日の題材＞
FileTable（SQL Server）

システムでは、画像データを画面上に表示させたいというケースがときにあるかと思います。そのときに、その画像データを物理ファイルとして持ちつつ、データベースではファイルパスを管理する方法と、データベースにバイナリデータ自体を保存する方法のどちらを採用するか、など判断に迷うことがあるかと思います。
今回は、ファイルパスを管理する方法で、SQL Server 2012で追加された機能であるFileTableを使う方法を取り上げてみたいと思います。FileTableでは、Windowsファイル名前空間をサポートすることで、エクスプローラからファイルのツリー階層を確認したり、データを登録することができますし、アプリケーションからファイルパスを指定してファイルを表示したりすることができます。

例）
SQL ServerのDB上に商品マスタを作成し、その商品イメージの画像データをFileTableを使用して保存します。
まず、そのための準備として、SQL Serverの設定が必要になります。
これについては、参考になるホームページもいくつかあり、そちらの内容も参考にしながら設定してみました。

１）インスタンスレベルでFileStreamを有効化
スタートメニューから、[Microsoft SQL Server 2014] の [構成ツール] の SQL Server 2014 構成マネージャを起動し、[SQL Serverのサービス]から FileStreamを有効化したいインスタンスを選択します。そして、マウスの右クリックでプロパティを選択し、[FILESTREAM]タブを選択します。そこで、下図のように、[Transact-SQLアクセスに対してFILESTREAMを有効にする] [ファイルI/Oアクセスに対してFILESTREAMを有効にする]にチェックを入れます。

 次に、SQL Server 2014 Management Studioを起動し、サーバーのプロパティ画面の、[詳細設定]メニューから、[FILESTREAMアクセスレベル]を[有効なフルアクセス]に変更します。

２）FileStreamを有効にしたデータベースの作成

SQL Server 2014 Management Studioで新しいデータベース（BLOG_DATA）を作成します。このとき、左側メニューの[ファイルグループ]を選択し、下側のFILESTREAM用のファイルグループを追加し、任意の名前を設定します。

次に、左側メニューの[全般]を選択し、[追加]ボタンにより[ファイルの種類]が[FILESTREAMデータ]のデータベースファイルを1行追加します。このとき、 [ファイルグループ]は先ほど追加したFILESTREAM用のファイルグループが自動的に選択され、[論理名]は任意の名前にします。

さらに、左側メニューの[オプション]を選択し、[FILESTREAMディレクトリ名]に任意の文字列を入力、[FILESTREAM非トランザクションアクセス]をFullにしてデータベースを作成します。

 ３）FileTableの作成
FILESTREAMデータやファイル、ディレクトリなどの情報を格納する固定スキーマを含んだ特殊なユーザーテーブルとして、FileTable というものを作成します。FileTableを作成するスクリプトは以下のようになります。

CREATE TABLE FileTable_gazo1 AS FILETABLE
 WITH
 (
   FILETABLE_DIRECTORY = 'FileTable_gazo1',
   FILETABLE_COLLATE_FILENAME = database_default
 )
GO

実際のデータベースをManagement Studioで見てみると、「FileTable_gazo1」というテーブルが、「FileTables」というカテゴリの中に作成されているのがわかります。

４）画像データの登録
上記で作成したFileTableにエクスプローラで接続して、画像データを登録します。SQLServer Management Studioから先ほど作成したFileTableを右クリックし、コンテキストメニューから［FileTableディレクトリの探索］を選択します。

すると、エクスプローラが開いてきます。

ここに、エクスプローラで、別のフォルダから画像データ（今回はペンの画像）をドラッグ＆ドロップで持ってきます。ドラッグ＆ドロップした後の状態が以下。

次に、この画像情報を持つ商品マスタテーブルを作成し、データを登録します。

CREATE TABLE dbo.syomst(
             商品コード varchar(10) NOT NULL,
             商品名 nvarchar(20) NULL,
             ファイル名 varchar(50) NULL,
  CONSTRAINT PK_syomst PRIMARY KEY (商品コード)
GO

 INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0001', N'ペン_01', 'images01.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0002', N'ペン_02', 'images02.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0003', N'ペン_03', 'images03.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0004', N'ペン_04', 'images04.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0005', N'ペン_05', 'images05.jpg')
…（途中省略）
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0040', N'ペン_40', 'images40.jpg')
GO

この商品マスタ「syomst」と、画像データの「FileTable_gazo1」をジョインして、画像データの名前（name）を取得してみます。ここでは、GetFileNamespacePath　というファンクションを使用して、FileTable 内のファイルの相対パスを取得しています。

select s.商品コード, file_stream.GetFileNamespacePath() f_name
from dbo.syomst s
join dbo.FileTable_gazo1 f
 on s.ファイル名 = f.name
order by s.商品コード;

上記で作成したデータを、試しにdbSheetClientというツールで、EXCELに表示させる画面を作成してみると、以下のように一覧で表示させることができました。

 今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

毎朝、自転車で通っている道にコスモスが植えてあるので、ちょこっと写真を撮ってみました。

さて、dbSheetClientのユーザー事例にＪＲ九州様が載りました。
Excelを利用して行ってきた勘定明細内訳システムを、dbSheetClientを採用してシステム化したということ。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/jr_kyushu.html

ＪＲ九州様と言えば、東京証券取引所に株式を上場したということで、とてもホットなニュースになっていますね。今年の大型IPO（新規株式公開）案件としては、LINEに次ぐ案件として注目を浴びていましたが、初値は公募価格を大きく上回る金額になったとか。今後の動きにも注目です！

＜本日の題材＞
一時テーブルのキー（SQL Server）

以前、一時テーブルについての記事をアップしたことがありました。一時テーブル、一時テーブル（２）。そのときには、一時テーブルの主キーやインデックスなどのキーについては特に記事としては上げていませんでしたが、今回、実際のシステムの開発時に気付いたことがありましたので、それについて取り上げてみたいと思います。

 ストアドプロシージャなどの中で、一時テーブルを作成してそこに一旦データを登録し、それを使っていろいろな条件によって処理を実行するようなことがよくあるかと思いますが、データ量が多くなると、やはり処理速度が遅くなってきます。
（一時テーブルは、SQL Serverの場合は、「tempdb」というシステムデータベースを利用して処理が行われていますし、Oracleの場合も、テンポラリー用の表領域を使うかたちになります。）

ですので、インデックスを作成することで、処理を早めるということは可能になります。このとき、主キーを設定したいと思う場合もあるかと思いますが、そのときには注意が必要です。
実は、SQL ServerをDBとして一時テーブルを使った処理のレスポンスをアップしようと思ったときに、主キーを設定したのですが、Create table文のときに、実テーブル作成時と同じように、Constraint句で主キー制約を設定し、制約名を設定しました。すると、そのプロシージャをほぼ同時のタイミングで複数のユーザで実行したところ、制約名が同じという理由で主キーを作成できないというエラーが起きてしまいました。
一時テーブルだから、別々のユーザで行う別々のセッションの処理では問題なく動作するはずという認識でいたのに、思わぬ落とし穴に引っかかってしまったような感覚でした。
ですので、一時テーブルでパフォーマンスを上げるためにインデックスが必要であれば、主キーではなく普通のインデックスを作成するほうが無難かと思われます。処理として主キー制約が必要な場合は、Create table 文で一時テーブルを作成した後に、Alter table文で主キーを追加するようにし、そのときに制約名を指定しないかたちを取る方法がよいかと思われます。

例）
以前ブログに一時テーブルの内容を取り上げたときの例を使い、主キーを設定して試してみたいと思います。そのときに、処理がすぐに終わらないように、一時テーブル作成後に、30秒間待機させるコマンド「WAITFOR DELAY」を設定します。（「スリープ処理」という標題で以前ブログで取り上げました）

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME,
              CONSTRAINT PK_t顧客初回購入 PRIMARY KEY (顧客ID)
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9),
              CONSTRAINT PK_t顧客購入履歴 PRIMARY KEY (顧客ID)
       );

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

このプロシージャの実行を、２つのセッションで同時に行ってみます。
１つめのセッションで以下を実行：

DECLARE @顧客ID varchar(8)='K0000054'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、30秒後に以下のような結果が返ってきます。

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、下記のようにエラーが返ってきます。

１つめのセッションのプロシージャで一時テーブルを作成し、それを使った処理が終わってないうちに、２つめのセッションの処理を行おうとすると、一時テーブル作成時に、主キーに設定した制約名が既に存在しているものと同じということでエラーになったということです。

ということなので、一時テーブルの作成時に主キーを設定する場合には、制約名を固定で指定しないかたちにしたほうがいいと思われます。
具体的には、以下のようにします。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9)
       );

    ALTER TABLE #顧客初回購入 ADD PRIMARY KEY (顧客ID);
    ALTER TABLE #顧客購入履歴 ADD PRIMARY KEY (顧客ID);

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

上記のように修正したプロシージャをコンパイルし、その実行を、再度２つのセッションで同時に行ってみます。

１つめのセッションで実行：
すると、30秒後に以下のような結果が返ってきます。

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

今度は、こちらもエラーは出ず、30秒後には上記のような結果が返ってきました。

プロシージャの実行時に、SQL Server Management Studio のほうで一時テーブルを確認してみると、下記のように、２つずつ一時テーブルが作成されているのがわかります。

また、主キーである必要がなく、インデックスの作成でよければ、インデックスを追加するかたちで行うこともできます。
この場合は、ALTER TABLE … ADD PRIMARY KEY の部分を、CREATE INDEX xxx …に変更します。具体的には、

CREATE INDEX IX_t顧客初回購入 ON #顧客初回購入(顧客ID);
CREATE INDEX IX_t顧客購入履歴 ON #顧客購入履歴(顧客ID);

上記に変更したプロシージャをコンパイルして実行しても、先ほどと同様にエラーなく処理は実行されました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

知人が松本城に行ったときに撮ってきた写真を頂きました。
ちょっと曇りの天気なのが残念ですが、天守閣が堀と一緒にきれいに写っています。松本城の天守は国宝で、その周りにある石垣や堀も国の史跡に指定されているそうです。天守の建造年は、戦国時代～江戸時代初期の頃だそうで、NHK大河ドラマで放映中の「真田丸」の主人公　真田信繁（幸村）が活躍した時代に重なるようです。
真田丸のほうも関ヶ原の戦いも終わり、徐々にクライマックスに向かっていきますね。ちなみに、「真田丸」とは、真田信繁が、大阪城に入城した後、大坂冬の陣の際に、大阪城の最弱部であったとされるところの近辺に築いた土づくりの出城のことのようです。今後、大河ドラマで放映されると思われる真田丸での戦いを、是非見てみたいです。

＜本日の題材＞
参照整合性制約（SQL Server）

前回、参照整合性制約（外部キー制約）について、ORACLEでの例を上げて記事をアップしました。今回は、SQL Serverで試してみたいと思います。

例）
前回ORACLEで試したものと同様のテーブルをSQL Serverで作成します。社員マスタと部門マスタを作成し、部門マスタの部門CDを社員マスタから参照するかたちの設定をしてみます。
まず、部門マスタを作成します。

CREATE TABLE dbo.BUMON_M(
BUMON_CD VARCHAR(3)
, BUMON_NAME VARCHAR(20)
, CONSTRAINT PK_BUMON PRIMARY KEY (BUMON_CD)
);

次に、社員マスタを作成します。

CREATE TABLE dbo.SYAIN_M(
 SYAIN_CD VARCHAR(10)
, SYAIN_NAME VARCHAR(20)
, BUMON_CD VARCHAR(3) DEFAULT ‘ ‘
, POSITION VARCHAR(40)
, CONSTRAINT PK_SYAIN PRIMARY KEY (SYAIN_CD)
, CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES dbo.BUMON_M (BUMON_CD)
);

上記の、7～8行目の CONSTRAINT .. FOREIGN KEY .. REFERENCES ..という表記部分が参照整合性制約の設定箇所で、ORACLEでの記述と基本同じです。

部門マスタ（BUMON_M）に何件かデータを登録し、現在以下の部門が存在しているとします。

SELECT * FROM dbo.BUMON_M
ORDER BY BUMON_CD;

この状況で、部門マスタに存在しない部門CD「500」で社員を登録しようとしてみると、

INSERT INTO dbo.SYAIN_M VALUES('10311', 'メンバーＫ', '500', '一般社員');

 上記のように、SQL Serverの場合も、ORACLE同様エラーが出てレコードを登録できません。SQL Serverでのエラーメッセージは、「メッセージ 547、レベル 16、状態 0、行 38　INSERT ステートメントは FOREIGN KEY 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.BUMON_M", column 'BUMON_CD' です。」となります。

また、各部門CDを持つ社員データを社員マスタに登録した状態で、部門マスタから既存の部門CDのレコードを削除しようとしてみます。

DELETE FROM dbo.BUMON_M
WHERE BUMON_CD = '100';

 この場合も、上記のように、「メッセージ 547、レベル 16、状態 0、行 41　DELETE ステートメントは REFERENCE 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.SYAIN_M", column 'BUMON_CD' です。」というエラーが出て、削除することはできません。

それでは、子表にデータが存在している親表のキーの値を更新してみます。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '300';

この場合も、やはり「メッセージ 547、レベル 16、状態 0、行 45　UPDATE ステートメントは REFERENCE 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.SYAIN_M", column 'BUMON_CD' です。」というエラーが出て、更新することはできません。

以上のように、参照整合性制約（外部キー制約）のデフォルトの設定は、NO ACTION と言われるもので、参照先のデータの更新や削除を禁止するかたちになります。

SQL Serverでも、オプションの設定として、下記のようなものがあります。
・ON DELETE { CASCADE | SET NULL | SET DEFAULT }
・ON UPDATE { CASCADE | SET NULL | SET DEFAULT }

「ON DELETE CASCADE」「ON DELETE SET NULL」については、前回、ORACLEで動作を確認しましたので、今回は省略し、それ以外のオプションについて確認してみます。

・ON UPDATE { CASCADE | SET NULL | SET DEFAULT }
ORACLEにはなかったオプションです。動作を確認してみます。

参照整合性制約のオプションを変更するため、現在の制約を削除し、オプションを設定した制約を追加します。このとき、ON DELETE CASCADE に加えて ON UPDATE CASCADE も設定します。

ALTER TABLE dbo.SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE dbo.SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M(BUMON_CD)
        ON DELETE CASCADE
        ON UPDATE CASCADE;

 まず、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

部門マスタのBUMON_CD = ‘100’のデータを'500'に更新します。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '100';

このとき、先ほどの社員マスタのデータを確認します。

SELECT * FROM dbo.SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

社員マスタの部門CDが「100」であったデータが、「500」に変わっていることが確認できます。

次に、参照整合性制約のオプションを、ON UPDATE SET NULL に変更します。

ALTER TABLE dbo.SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE dbo.SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M(BUMON_CD)
        ON DELETE CASCADE
        ON UPDATE SET NULL;

 先ほど変更した部門マスタのBUMON_CD = ‘500’のデータを、元の’100’に変更します。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '100'
WHERE BUMON_CD = '500';

このとき、先ほど部門CD「500」になっていた社員マスタのデータを確認します。

SELECT * FROM dbo.SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

先ほど、部門マスタ側で変更した部門CDをもつ社員マスタの部門CDは、NULL に変更されていることが確認できました。

※ON UPDATE SET DEFAULT、ON DELETE SET DEFAULT については、親表の対応する行が削除、更新されると、子表の外部キーを構成する値にデフォルト値がセットされるという認識でしたが、テストしてみたところ、思ったような動作にならなかったため、今回は省略します。

以上から、SQL Serverの場合には、ON UPDATE … という更新時の対応のオプションがあるのが確認できました。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

近くの川で、かもが泳いでいるのが見えたので、写真にとってみました。
9月のシルバーウィークも終わり、もうすぐ10月を迎えようとしています。今年は台風も多く日本に上陸し、いろいろと大変な天候が続いていました。地球温暖化の影響もますます大きくなってきている気がしますね。
この前、NHKで放映していたＮＨＫスペシャル ＭＥＧＡ ＣＲＩＳＩＳ 巨大危機～脅威と闘う者たち～ 第１集「加速する異常気象との闘い」をたまたま見たのですが、21世紀末には平均気温が今より4℃上昇し、最高気温が東京で43・0度、名古屋では44.5度にもなるという予測が出ていました。また、巨大積乱雲(スーパーセル)も頻繁に発生し、東京都の最大雨量も1日あたり現在100mm程度なのが、310mmまで増加し、大雨のときには地下街は水没してしまうかもしれないというような、とてつもない状況をこのままでは迎えてしまうという話しです。
21世紀末と言えば、80数年後なので、私たちの孫や曾孫が生きている世代です。いよいよ、環境問題は待ったなしの状況に来ている気がしますね。このような脅威と如何に向き合い、温暖化を抑制していくのか、科学の英知の結集と世界的な連携が必要だということを改めて感じるこの頃です。

 ＜本日の題材＞
参照整合性制約（外部キー制約）

リレーショナルデータベースには、整合性制約と言って、データの整合性を保つための制約を設定することができ、主キー制約、一意キー制約、NOT NULL制約、CHECK制約、参照整合性制約などがあります。

今回は、参照整合性制約（外部キー制約）について取り上げてみたいと思います。参照整合性制約とは、関連付けられた表の共通する列の関係が保たれていることをDB側で保証するもので、簡単にいえば、参照先のテーブルにデータが存在しないようなデータの入力を許可しないということになります。
よく例として挙げられるのが、ORACLEにあらかじめ用意されているEMP表（従業員表）とDEPT表（部門表）で、EMP表の部門番号(DEPTNO)は、DEPT表の部門番号(DEPTNO)に存在するデータしか登録できないというものです。
 このとき、DEPT表を親表、EMP表を子表、DEPTNOを外部キーと言います。そして、DEPT表のDEPTNOは主キー、もしくは一意キーである必要があります。
この参照整合性制約の設定は、基本的にテーブル作成時に設定しますが、後から制約を追加することも可能です。

例）
それでは、例として、社員マスタと部門マスタを作成し、部門マスタの部門CDを社員マスタから参照するかたちの設定をしてみます。（ORACLE）
まず、部門マスタを作成します。

CREATE TABLE BUMON_M(
 BUMON_CD VARCHAR2(3)
, BUMON_NAME VARCHAR2(20)
, CONSTRAINT PK_BUMON PRIMARY KEY (BUMON_CD)
);

社員マスタを作成します。

CREATE TABLE SYAIN_M(
 SYAIN_CD VARCHAR2(10)
, SYAIN_NAME VARCHAR2(20)
, BUMON_CD VARCHAR2(3)
, POSITION VARCHAR2(40)
, CONSTRAINT PK_SYAIN PRIMARY KEY (SYAIN_CD)
, CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M (BUMON_CD)
);

上記の、7～8行目の CONSTRAINT .. FOREIGN KEY .. REFERENCES ..という表記部分が参照整合性制約の設定箇所になります。

部門マスタ（BUMON_M）に何件かデータを登録し、現在以下の部門が存在しているとします。

SELECT * FROM BUMON_M
ORDER BY BUMON_CD;

この状況で、部門マスタに存在しない部門CD「500」で社員を登録しようとしてみると、

INSERT INTO SYAIN_M VALUES('10311', 'メンバーＫ', '500', '一般社員');

上記のように、「ORA-02291: 整合性制約(BLOG.FK_BUMON)に違反しました - 親キーがありません」というエラーが出て、レコードを登録できません。

また、各部門CDを持つ社員データを社員マスタに登録した状態で、部門マスタから既存の部門CDのレコードを削除しようとしてみます。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

 この場合も、上記のように、「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、削除することはできません。

それでは、子表にデータが存在している親表のキーの値を更新してみます。

UPDATE BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '300';

 この場合も、やはり「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、更新することはできません。

以上のように、参照整合性制約（外部キー制約）のデフォルトの設定は、NO ACTION と言われるもので、参照先のデータの更新や削除を禁止するかたちになります。
（※親表のテーブルをDROPしようとしてもエラーになります。）
そのため、どうしても親表のデータを更新したいときには、更新する値と一致する子表の部門CDの値を一旦親表に存在する違う値に更新してから、親表のキー値を更新するというような手順が必要になります。

ただ、オプションの設定として、「ON DELETE SET NULL」「ON DELETE CASCADE」というものがあります。それぞれ、どういう動作になるかというと、

・ON DELETE SET NULL
　これは、親表のキー（部門CD）の値を削除すると、その値と同じ値を持っていた子表のほうの部門CDは NULL に更新されるというものです。

・ON DELETE CASCADE
　これは、親表のキー（部門CD）の値を削除すると、その値と同じ値を持っていた子表のほうのレコード自体が削除されるというものです。

試してみます。
参照整合性制約のオプションを変更するため、現在の制約を削除し、オプションを設定した制約を追加します。

ALTER TABLE SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M (BUMON_CD) ON DELETE SET NULL;

まず、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

部門マスタのBUMON_CD = ‘100’のデータを削除します。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

このとき、先ほど抽出した、部門CD=100 だった社員マスタのデータを確認します。

SELECT * FROM SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

 社員マスタの部門CDが「100」であったデータが、NULL に変わっていることが確認できます。

一旦元に戻します。
ROLLBACK;

次に、参照整合性制約のオプションを、ON DELETE CASCADE に変更します。

ALTER TABLE SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M (BUMON_CD) ON DELETE CASCADE;

再度、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

部門マスタのBUMON_CD = ‘100’のデータを削除します。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

このとき、先ほどの社員マスタのデータを確認します。

SELECT * FROM SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

親表から削除した部門CD=’100’を持つ、子表のレコードが削除されたことが確認できます。
※実際に使用する場合には、子表のレコードが自動的に削除されてしまうので、運用上問題がないか注意が必要です。

このように、DBのほうでデータの整合性を保つように制御することができますが、実際のシステムではあまりこれを細かく設定したという経験は少ないのが実情です。これには賛否両論があり、実際にシステムに実装した場合に、テストデータなどの作成がいろいろと大変になるとか、レスポンスへの影響、また、整合性のエラーが起きた際のエラーメッセージをユーザ向けに変更する処理が必要になり、アプリ側での負担になる場合が多いということなどが影響しているようです。
次回は、SQL Serverで試してみたいと思います。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

先日、会社の同僚の誘いで海釣りに行く機会がありました。写真は、そのときにボートから撮った写真です。小学生の息子も連れて行って来ました。ボートに乗る30分から1時間くらい前には酔い止めの薬を飲んで（妻から、忘れちゃダメと何度も言われたので、そこはしっかりとやって）、いよいよ乗船！　

最初のポイントでは、キスとかが釣れたらと思っていたところ、私も息子も同じく得体のしれない20cm超の魚をゲット！　釣れたのはたぶん「オキエソ」と言われるあまりかっこうのよくない魚。知人が「外道」が釣れたねと言っていたので、すごい名前の魚もいるものだと思いましたが、実は、狙っていた魚ではないとき、外道が釣れたと言うんですね！

次のポイントの岩場で、息子はカサゴをゲット。私も結構な引きがあったので、もう少しでカサゴが釣れそうだったけれど、リールを巻いているときに逃げられてしまい、とても残念。
息子は初めての海釣りの経験だったのですが、なかなか釣れない中、頑張っていました。その日は潮時としては「小潮」と言われるときで、魚のエサの捕食行動が鈍り、釣りにくい日だったようで、確かに全体的に釣れなかったですね。
それでも、いい経験ができてよかったです。家に帰ったら、釣った魚を妻に料理してもらって、その日はおいしい魚料理でした。

　　

＜本日の題材＞
テーブルの項目数、レコード長の制限

仕事でEXCELを使って様々な業務を行う場合に、列数がものすごく多いシートを扱わざるを得ないケースが出てきますよね。そのようなEXCELのシートをそのまま使ってWebシステム化するためのツールとして、dbSheetClientというものがあることは、以前ご紹介したと思います。
http://www.newcom07.jp/dbsheetclient/dbsclan/index.html

そのソフトを使って開発する際に、EXCELの内容をDBと連結するための設計を行うのですが、たくさんの列数をそのままのかたちでデータベースに持たせたいと思う場合もときにあります。
そんなときに、1つのテーブルに持てる列数っていくつまでなんだろう？　1レコードに持てるレコード長に制限はあるのだろうか？　などということを考える場合があるので、今回その内容を取り上げてみます。

SQL Serverの場合、調べてみると、通常のテーブルでは、列数は1024まで、行ごとの最大のバイト数は8060バイトとなっています。
https://msdn.microsoft.com/ja-jp/library/ms143432.aspx

それでは、実際に1024個の列を持つテーブルを作成してみます。

CREATE TABLE test_col_max(
    col_001key VARCHAR(10) NOT NULL
  , col_002 VARCHAR(10)
  , col_003 VARCHAR(10)
  , col_004 VARCHAR(10)
  , col_005 VARCHAR(10)
  , col_006 VARCHAR(10)
  , col_007 VARCHAR(10)
.....  途中省略
  , col_1020 VARCHAR(10)
  , col_1021 VARCHAR(10)
  , col_1022 VARCHAR(10)
  , col_1023 VARCHAR(10)
  , col_1024 VARCHAR(10)
  , CONSTRAINT PK_test_col_max PRIMARY KEY (col_001key)
) ;

 テーブルは作成できました。
ただし、以下のような警告が表示されます。
「警告: 行の最大長が許容制限の 8060 バイトを超えています。一部の大きな値の組み合わせでは、挿入操作または更新操作が失敗します。」。これは、各列のタイプを VARCHAR(10)としたので、各列にMAXで値を詰め込んだ場合、10 * 1000 = 10000 バイトになってしまうので、このような警告が出るということです。

それでは、列数が 1025個ではテーブル作成がどうなるか試してみます。CREATE TABLE文で項目を1つ追加して、再度作成を実行してみると、下記のようなエラーが出て作成できません。

 エラーメッセージは、「CREATE TABLE が失敗しました。テーブル 'test_col_max' の列 'col_1025' が、列の最大数 1024 列を超えています。」。やはり、1024列までしか作成できないことが確認できました。（これは、一時テーブルでも同じ）

では、1024列までの項目でテーブルの作成まではできたとしても、行サイズのトータルが8060バイトを超える場合はどうなるでしょうか？　それも試してみます。

まずは、8060バイトを超えない場合として、EXCELで作成したCSVファイルを、1024個の列を持つ「test_col_max」テーブルに登録してみます。前々回、BCPコマンドを紹介しましたが、今回は、BULK INSERTコマンドで登録してみます。各列に「1」、「2」など、1文字のみを設定したCSVファイル（サンプルとして3行）を読込んでみます。

BULK INSERT dbo.test_col_max
FROM 'c:\WORK\blog\blog_1024.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
);

登録できました。データを確認してみます。

SELECT * FROM dbo.test_col_max
 ORDER BY col_001key;

データが3行作成されているのが確認できます。
次に、各列に「1000000000」など10文字ずつの値が設定されたcsvファイルを読込んでみます。

先ほどと同様に、BULK INSERTを実行してみると、下記のようなエラーが発生してデータを取込めません。

 これは、行の合計のレコード長が10 * 1024 = 10240 バイトとなり、8060バイトを超えてしまっているためです。

なお、テーブルの列数の制限は、1024ですが、SELECT文での列数の最大値は 4096ということですので、以前取り上げた縦横変換などで、縦のデータを横に項目を並べて抽出する場合など、SELECT文で多くの項目を抽出する場合、1024を超えて4096列まで行うことができるということです。

<ORACLEの場合>
ちなみに、ORACLEではどうかというと、1つのテーブルに持てる最大列数は、1000列です。
上記と同様に、1001列を持つテーブルを作成しようとすると、下記のようにエラーになります。

 「ORA-01792：表またはビューに指定できる最大列数は1000です。」というエラーが出て、テーブルを作成できません。

上記から1列減らして1000列を持つテーブルを作成しようとすると、下記のように無事作成することができました。

ここで、SQL Serverでは行の合計サイズの制限のためエラーとなった、各項目が全て10バイトまで詰まったCSVファイルを取込んでみます。ORACLEのSQL*Loaderというツールで行ってみます。
制御ファイルは以下のような感じ：

（項目が多いので、上記の制御ファイルの画面コピーは、途中で項目が切れてます。）

それでは実行してみます。
sqlldr userid=blog control=blog.ctl

問題なく取り込めました。結果を確認してみます。
SELECT * FROM TEST_COL_MAX
ORDER BY COL_001KEY;

1000個のすべての列に、10バイトずつ登録されたデータが確認できます。

ちなみに、ORACLEの場合は、行の合計レコード長が 8060バイトのような制限は特にはなく、例えば全ての項目がVARCHAR2型であれば、1項目で4000バイトまで持てるので、1000列 * 4000 バイト = 4000000 バイトまでは持てることになりますね。

※SQL Serverでも SQL Server 2005から、大きな値のデータ型とよばれる、varchar(max)、nvarchar(max)型というものが機能として増えています。これについては、別の機会に取り上げてみたいと思います。

 今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

お久しぶりです。ここのところ、仕事のほうがなかなか忙しく、ブログをアップすることができませんでした。写真は、皇居の平川門を撮ったものです。東西線竹橋駅のすぐそばにあり、たまたま仕事で近くまで行ったので行ってみたところ、平川橋という橋を渡ると警備の方がいらっしゃいましたが、中に入れるということで、寄ってみました（皇居東御苑は、火曜日～木曜日なら年末年始以外なら基本的にはいつでも公開しているとのこと）。時間がなくてすぐに出てきてしまいましたが、テレビで見たことのあるようなお堀があって、季節によっては花とかとてもきれいではないかと感じました。外国の方も結構見にいらっしゃっていましたね。

ずっと前、学生の頃に、正月1月2日の新年の一般参賀に一度皇居に行ったことがあり、おぼろげにそのときのことを覚えていますが、それ以外は一度も行ったことがなかったです。普通の日でも入れるんですね～。今度休暇でもとって、ゆっくりと歩いてみたいな～！（嫁を誘ってみる？）

ちなみに平川門とは、江戸城の裏門で、大奥に最も近く、奥女中の通用門であったことから「お局門」とも呼ばれていたそうです。また、平川門の脇に小さな門があって、城内で罪人や死人が出るとこの門から出されたので「不浄門」ともいわれていたとのこと。
うんちくネタになるかな！

　皇居側から見た平川橋

＜本日の題材＞
テーブル値関数（SQL Server）

今回は、テーブル値関数を取り上げてみたいと思います。私も今まで使ったことはなかったのですが、今回途中から応援に入ったプロジェクトでは、SQL Serverを利用していましたが、テーブル値関数がよく使われていて、結構便利なものだと感じました。
どういうものか調べてみると、ユーザー定義テーブル値関数と呼ばれ、ストアドプロシージャのように様々なロジックを組めますが、結果としてtableデータ型の値を返すことができ、ビューに変わる強力なツールになるようです。

例１）まず、サンプルとして適当かどうか？というのはありますが、こういう使い方もあるということで、指定した日から1ヶ月（31日間）の日付と曜日をそれぞれ1行目、2行目に表示しつつ、日付を横並びに表示するためのものを作成してみます。

CREATE FUNCTION dbo.ufn_calender(
        @p開始日                datetime
)
RETURNS TABLE
AS
RETURN(
SELECT
        1 表示順,N'日付'表示名
        ,CONVERT(VARCHAR(10),@p開始日+0,111) 日付1
        ,CONVERT(VARCHAR(10),@p開始日+1,111) 日付2
        ,CONVERT(VARCHAR(10),@p開始日+2,111) 日付3
        ,CONVERT(VARCHAR(10),@p開始日+3,111) 日付4
        ,CONVERT(VARCHAR(10),@p開始日+4,111) 日付5
        ,CONVERT(VARCHAR(10),@p開始日+5,111) 日付6
        ,CONVERT(VARCHAR(10),@p開始日+6,111) 日付7
        ,CONVERT(VARCHAR(10),@p開始日+7,111) 日付8
        ,CONVERT(VARCHAR(10),@p開始日+8,111) 日付9
        ,CONVERT(VARCHAR(10),@p開始日+9,111) 日付10
        ,CONVERT(VARCHAR(10),@p開始日+10,111) 日付11
        ,CONVERT(VARCHAR(10),@p開始日+11,111) 日付12
        ,CONVERT(VARCHAR(10),@p開始日+12,111) 日付13
        ,CONVERT(VARCHAR(10),@p開始日+13,111) 日付14
        ,CONVERT(VARCHAR(10),@p開始日+14,111) 日付15
        ,CONVERT(VARCHAR(10),@p開始日+15,111) 日付16
        ,CONVERT(VARCHAR(10),@p開始日+16,111) 日付17
        ,CONVERT(VARCHAR(10),@p開始日+17,111) 日付18
        ,CONVERT(VARCHAR(10),@p開始日+18,111) 日付19
        ,CONVERT(VARCHAR(10),@p開始日+19,111) 日付20
        ,CONVERT(VARCHAR(10),@p開始日+20,111) 日付21
        ,CONVERT(VARCHAR(10),@p開始日+21,111) 日付22
        ,CONVERT(VARCHAR(10),@p開始日+22,111) 日付23
        ,CONVERT(VARCHAR(10),@p開始日+23,111) 日付24
        ,CONVERT(VARCHAR(10),@p開始日+24,111) 日付25
        ,CONVERT(VARCHAR(10),@p開始日+25,111) 日付26
        ,CONVERT(VARCHAR(10),@p開始日+26,111) 日付27
        ,CONVERT(VARCHAR(10),@p開始日+27,111) 日付28
        ,CONVERT(VARCHAR(10),@p開始日+28,111) 日付29
        ,CONVERT(VARCHAR(10),@p開始日+29,111) 日付30
        ,CONVERT(VARCHAR(10),@p開始日+30,111) 日付31
UNION ALL SELECT
        2,N'曜日'
        ,LEFT(DATENAME(dw,@p開始日+0),1)
        ,LEFT(DATENAME(dw,@p開始日+1),1)
        ,LEFT(DATENAME(dw,@p開始日+2),1)
        ,LEFT(DATENAME(dw,@p開始日+3),1)
        ,LEFT(DATENAME(dw,@p開始日+4),1)
        ,LEFT(DATENAME(dw,@p開始日+5),1)
        ,LEFT(DATENAME(dw,@p開始日+6),1)
        ,LEFT(DATENAME(dw,@p開始日+7),1)
        ,LEFT(DATENAME(dw,@p開始日+8),1)
        ,LEFT(DATENAME(dw,@p開始日+9),1)
        ,LEFT(DATENAME(dw,@p開始日+10),1)
        ,LEFT(DATENAME(dw,@p開始日+11),1)
        ,LEFT(DATENAME(dw,@p開始日+12),1)
        ,LEFT(DATENAME(dw,@p開始日+13),1)
        ,LEFT(DATENAME(dw,@p開始日+14),1)
        ,LEFT(DATENAME(dw,@p開始日+15),1)
        ,LEFT(DATENAME(dw,@p開始日+16),1)
        ,LEFT(DATENAME(dw,@p開始日+17),1)
        ,LEFT(DATENAME(dw,@p開始日+18),1)
        ,LEFT(DATENAME(dw,@p開始日+19),1)
        ,LEFT(DATENAME(dw,@p開始日+20),1)
        ,LEFT(DATENAME(dw,@p開始日+21),1)
        ,LEFT(DATENAME(dw,@p開始日+22),1)
        ,LEFT(DATENAME(dw,@p開始日+23),1)
        ,LEFT(DATENAME(dw,@p開始日+24),1)
        ,LEFT(DATENAME(dw,@p開始日+25),1)
        ,LEFT(DATENAME(dw,@p開始日+26),1)
        ,LEFT(DATENAME(dw,@p開始日+27),1)
        ,LEFT(DATENAME(dw,@p開始日+28),1)
        ,LEFT(DATENAME(dw,@p開始日+29),1)
        ,LEFT(DATENAME(dw,@p開始日+30),1)
);

7月の日付について試したいので、パラメータとして「2016/07/01」を渡して抽出してみます。

SELECT * FROM [dbo].[ufn_calender]('2016/07/01')
ORDER BY 表示順;

結果は以下のようになります。

カレンダーのように、日付が横並びになり、2行目に曜日が表示されますね。

他の使い方として、最初にテーブルの定義を指定して、その後いろいろな処理や条件を組込みながら、その定義にあった結果をINSERTして、それを利用するようなやり方があります。

例２）以前も使用したことがある売上テーブルについて、指定した年月の商品別の売上金額の高いものからランクをつけながら抽出する処理を行うテーブル値関数を作成してみます。

CREATE FUNCTION dbo.ufn_商品CD別月次出荷
(
        @p出荷年月      VARCHAR(6)
) RETURNS @月次出荷 TABLE (
        出荷年月 VARCHAR(6),
        ランキング DECIMAL(3,0),
        商品CD NVARCHAR(20),
        商品名 NVARCHAR(20),
        出荷数量 INT,
        売上金額 DECIMAL(10,0)
)
AS
BEGIN
        INSERT @月次出荷
        SELECT
                 FORMAT(U.受注日, 'yyyyMM')
                ,RANK() OVER (ORDER BY SUM(U.売上金額) DESC)
                ,U.商品CD
                ,S.商品名
                ,SUM(U.商品数量)
                ,SUM(U.売上金額)
        FROM dbo.売上テーブル U
        JOIN dbo.商品マスタ S ON U.商品CD = S.商品CD
        WHERE FORMAT(U.受注日, 'yyyyMM') = @p出荷年月
        GROUP BY FORMAT(U.受注日, 'yyyyMM'), U.商品CD, S.商品名;
       
        RETURN;
END;

このテーブル値関数を使って、2016年05月の売上について、商品別の売上金額を高い順に表示します。

SELECT * FROM dbo.ufn_商品CD別月次出荷('201605')
ORDER BY ランキング;

ストアドプロシージャのようにいろいろな処理を組み込んで、テーブルのかたちで結果を返すことができるので、使い方によっては結構便利かと思います。今まで、ワーク用のテーブルを用意して、そこに一旦データを登録してから処理をしていたような場合でも、ワークのテーブルを作成せずに、テーブル値関数を使って対応するようなことも可能な場合もあると思います。

ちなみに、ORACLEでも、同じように使用することができるユーザー定義レコードというようなものが結構以前からあるようですね。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

お久しぶりです。あっという間にゴールデンウィークも終盤になってしまいました。写真は、去年も行った加須市玉敷神社の騎西藤まつりに行ったときの写真です。きれいでしたね！

話しは変わりますが、GW中に息子を連れて近くの温水プールに行ったところ、プロのインストラクターと思われるお姉さんが現れて、これからエクササイズをします！とのこと。プールに来ていた大半の方（おばちゃんやおばあちゃんが多かったけど）が参加するようだったので、ものは試しにと一緒に参加しました。そうしたら、みっちり1時間、水の中でステップを踏んだり手足をいろいろと動かしてのエクササイズ、結構疲れました！！　体力無いな～。　他の方は慣れているのか、おばちゃんやおばあちゃんが元気にこなしているのを見ると、すごいというか、女性は強し。自分ももっと体力を付けないと！と、強く感じさせられました。

＜本日の題材＞
Bcpユーティリティ（SQL Server）

前回、ORACLEのUTL_FILEパッケージを取り上げました。PL/SQLでテキストファイルの読み書きができるというものですが、SQL ServerのT-SQLで同様のことができるかどうかを調べてみたところ、xp_cmdshellというシステム拡張ストアドプロシージャを使用して、bcpコマンドなどを呼び出してテーブルのデータをテキストファイルへ書き出したり、テキストファイルのデータをテーブルに読込んだりすることは可能という情報がありました。ただし、このストアドプロシージャxp_cmdshellは様々なOSコマンドを実行できるということから、セキュリティ的にはとても危険だということで、デフォルトでは無効となっています。これを有効にする方法もあるとのことですが、それは次の機会に回すとして、今回は、bcpコマンドのほうを取り上げてみたいと思います。

BCPユーティリティでは、テーブルやビューのデータをテキストに出力する、テキストデータをテーブルへ取込むなど、一般にエクスポート／インポートと言われる内容ができますが、queryoutオプションを使用すると、指定したクエリーの結果を出力することが可能になります。

構文は、以下：

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
   [-a packet_size]
   [-b batch_size]
   [-c]
   [-C { ACP | OEM | RAW | code_page } ]
   [-d database_name]
   [-e err_file]
   [-E]
   [-f format_file]
   [-F first_row]
   [-h"hint [,...n]"]
   [-i input_file]
   [-k]
   [-K application_intent]
   [-L last_row]
   [-m max_errors]
   [-n]
   [-N]
   [-o output_file]
   [-P password]
   [-q]
   [-r row_term]
   [-R]
   [-S [server_name[\instance_name]]
   [-t field_term]
   [-T]
   [-U login_id]
   [-v]
   [-V (80 | 90 | 100 | 110)]
   [-w]
   [-x]

※パラメータはアルファベット順。

ちなみに、コマンドプロンプト画面で、bcp /? と実行すると以下のような画面が出ます。

構文の上記のパラメータを全て説明するのはとても大変なので、詳細はMicrosoft社のコマンドの説明のHPに任せて、サンプルを使ってよく使う箇所について説明したいと思います。

１）テーブルやビューのデータをテキストファイルに出力する

※SQL ServerにはWindows認証でアクセスするとします。Windows認証の場合は -T を使用します。BLOGデータベースの「商品マスタ」のデータを、「c:\temp」ディレクトリに「商品マスタ.txt」という名前のファイルにカンマ区切りで出力します。

bcp BLOG.dbo.商品マスタ out "C:\temp\商品マスタ.txt" -c -t, -S サーバ名 –T

作成されたテキストファイルを確認してみると、

商品マスタテーブルのデータが出力されています。
カンマ区切りではなく、カンマの代わりに空白で試してみると

bcp BLOG.dbo.商品マスタ out "C:\temp\商品マスタ2.txt" -c –t  -S サーバ名 –T

 作成された中身を確認すると、

たしかにカンマ区切りではなく、空白で区切られていることが確認できました。

※ちなみに、SQL Server認証では、-T ではなく、-U ユーザ名 –P パスワードとなります。

２）テキストファイルのデータをテーブルに取込む

先ほど出力したテキストファイルをテーブルに読込んでみます。
まず、商品マスタテーブルのデータをtruncateコマンドで削除します。

次に、bcpコマンドでテキストファイルを読込みます。パラメータとして、out ではなく in を使います。

bcp BLOG.dbo.商品マスタ in "C:\temp\商品マスタ.txt" -c -t, -S サーバ名–T

 テーブルの中身を確認してみると、
SELECT * FROM dbo.商品マスタ;

先ほどデータを削除した後、bcpコマンドによってテキストファイルからデータが取込まれたことが確認できました。

３）テーブルやビューから、指定したクエリで取得したデータをテキストファイルに出力する

今度は、テーブルやビューのデータを全て出力するのではなく、指定した条件でSELECTした結果をテキストファイルに出力する場合です。
これは、bcpコマンドの後に、”で囲んだSELECT文を記載し、パラメータとして out ではなく queryout を使用します。例として、商品マスタの分類が「果物」のデータをテキストファイルに出力してみます。

bcp "SELECT 商品CD, 商品名, 分類, 値段 FROM BLOG.dbo.商品マスタ WHERE 分類='果物'" queryout "C:\temp\商品マスタ3.txt" -c -t, -S サーバ名 –T

作成されたテキストファイルを確認します。

指定したSELECT文の条件通りの結果がテキストファイルに出力されていることが確認できました。

今日は以上まで

にほんブログ村