PIVOT、UNPIVOT(SQL Server)

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

夏みかん

知人の方から、家に夏みかんの木があって、たくさん実がなるということで頂きました。その写真です。

さて、米国では、トランプ大統領が就任しましたが、就任直後から大統領令を連発し、大きな話題になるとともに、中にはきわめて大きな反発も起こっていますね。オバマケア見直し、TPPの離脱をはじめ、選挙のときから掲げてきた内容を具体的に実施しているわけですが、大きな波紋を呼んだものとしては、メキシコ国境の壁の建設、そして、中東・アフリカ7カ国からの渡航者の入国禁止(シリアからの難民受け入れの拒否も含む)です。
特に入国禁止については、米国政府内でも批判的な声が上がり、反対するデモも各地で起きていて、かなり混乱した状況ですね。この大統領令は憲法に違反しているということで、差し止めを命じるワシントン州の連邦地裁も現れました。強力な力を持つ大統領令ですが、司法のほうで歯止めをすることが可能だということが今回の内容で確認できました。さすが、民主主義の国!

しかし、おっかない世の中になってきました。これからどうなるのやら? できれば平和裏に進んでほしい。僕もやっぱり平和が好きですから。でもよく考えてみると、うちのかみさんもおっかなかったっけなあ?

 <本日の題材>
PIVOT、UNPIVOT (SQL Server)

以前、複数行のデータを集計して横展開という記事をアップしたことがありましたが、今回はこれと同様のことを、SQL ServerのPIVOTという関係演算子を使って試してみたいと思います。

前回の記事では、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケースについて、case文を使いながら各月を横に並べて表示させるようにしていました。
このテーブルのデータを単純に抽出すると以下のようになります。

SELECT * FROM dbo.部品発注表;

blog75_部品発注表

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、pivot演算子を使って試してみます。

SELECT *
FROM (select 部品コード, substring(年月日,5,2)+'月' as '月', 数量
       from [dbo].[部品発注表]) as B_tab
PIVOT (
   SUM(数量) FOR 月 IN
  ([01月], [02月], [03月], [04月], [05月], [06月], [07月], [08月], [09月], [10月], [11月], [12月])
) as PVTab
ORDER BY PVTab.部品コード;

 blog75_pivot

 上記のSQLでは、FROM の後の集計対象テーブルの後に、PIVOT句を設定し、FOR .. IN で設定した値を列として出力して、それ毎にSUM関数で設定した項目[数量]を集計しています。なお、SUM関数とFOR .. IN で指定されていない「部品コード」でGROUP BYがされていて、それぞれの行が出力されています。最後のORDER BY句はオプションですが、並び順を設定できます。
Excelのピボットと同様のことが、SQLでできるのが確認できました。

また、以前別の記事「横に並んだ項目を縦の行データに変換」で、最初から列として登録されているデータを、複数行の縦のデータに変換して抽出するという件を取り上げましたが、UNPIVOT という関数がこれに該当します。

前回のデータをそのまま利用する場合に、まず、「商品売上」テーブルのデータをそのまま抽出すると、

SELECT * FROM dbo. 商品売上;

blog75_商品売上

 上記データについて、各月ごとのデータを行として表示する場合に、UNPIVOT演算子を使ったSQLは以下のようになります。

SELECT 商品CD, 月, 月売上
FROM
  (SELECT * FROM dbo.商品売上) p
UNPIVOT
  (月売上 FOR 月 IN
     (売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月, 売上10月, 売上11月, 売上12月, 売上1月, 売上2月, 売上3月)
)AS unpvt
ORDER BY unpvt.商品CD desc;

blog75_unpivot

 上記のSQLでは、FROM の後の対象テーブル(最初から横に並んだ項目を持つ)の後に、UNPIVOT句を設定し、FOR .. IN で設定した値を行として出力して、それ毎に月の売上を抽出しています。
また、FOR .. IN で指定されていない「商品CD」でGROUP BYがされていて、商品CD毎の、各月毎の売上が出力されています。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

Oracle12cでのユーザー作成でエラー「ORA-65096」

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

IMG_0585

新年あけましておめでとうございます。
ごあいさつがとても遅くなってしまいました。本年もよろしくお願いいたします。

昨年2016年は、IOTとともにAI(人工知能)がとてもクローズアップされるようになってきた1年ではなかったかと思います。Googleの「AlphaGo」は囲碁で韓国のトッププロ棋士を破り、話題になりましたね。囲碁は打ち手のパターンが10の360乗通り、あるいは400乗通り存在するといわれており、AIがプロ棋士に匹敵する強さになるにはあと10年かかるといわれていたのを、10年前倒しで達成したということです。また、IBMのワトソンは患者の正確な白血病の病名を10分で見抜き、割り出した病名に対する適切な治療によって患者の命を救ったと報道され、AIが医療の世界で大きく貢献する事例となりました。また、自動運転、ディープラーニングなど、様々な技術へのチャレンジ、大きな投資がなされています。
そして、20年後には現在日常で行われている仕事のほどんどをロボットが行う時代が来ると断言する有名なCEOもいれば、科学者や有識者の間では、2045年には人工知能が人間の知能を超えるという予測「シンギュラリティ」についての議論もあり、人工知能は人類史上最悪の脅威と主張する英ノーベル賞授賞物理学者・ホーキング博士のような学者もいます。
いよいよ、過去SFでしか想像できなかったような世界が具体的にやってくると予感させる時代になってきたと感じますが、この科学を正しく使いこなせるかという点が、昔から言われてきたことではありますが、今後の人類にとってのさらに重要な課題、焦点になってくるのではないでしょうか?
将来は、仕事や技術関連は、ロボットが受け持ち、人は、心情、文化、芸術などで楽しむ世界になるかも知れませんね!

<本日の題材>
Oracle12cでのユーザー作成でエラー「ORA-65096」

Oracle12cが国内で提供開始されたのは、2013年7月、既に3年半近くが経過しました。クラウドの『c』を製品名に掲げ、コンテナ、プラガブル、マルチテナントなど、これまでのOracle Databaseにはなかった多くのコンセプトを引っさげ、実に500を超える新機能が実装された12cですが、なかなか開発で12cに触れる機会が少なく、残念に思っていたところ、少し触れる機会がありましたので、今回はそれについて取り上げてみたいと思います。

まず、最初に行うことの一つがユーザの作成だと思いますが、ここで、11gまでと同じようにユーザを作成しようとすると、「ORA-65096: 共通ユーザーまたはロール名が無効です」のエラーが発生します。

まず、SQL*Plusを立ち上げ、sysユーザでログインします。

sqlplus / as sysdba

oracle12cログイン

このあと、「BLOG_TEST」というユーザを作成してみます。

create user blog_test
identified by passwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

create_userエラー

すると、上記のように、「ORA-65096: 共通ユーザーまたはロール名が無効です」のエラーが発生します。
11gまでしか知らないユーザでは、ここでまずびっくりしてしまいますが、このエラーに対応するためには、Oracle12c で大幅に変更された、マルチテナント・アーキテクチャという新しいアーキテクチャの仕組みを理解する必要があります。

Oracle11gまでは、1つのインスタンスには1つのデータベースしかありませんでしたが、12cからは1つのインスタンスには1つのコンテナ・データベース(CDB)と、その下にプラガブル・データベース(PDB)と呼ばれる子DB(複数作成可能)が存在するかたちになります。
このマルチテナントのメリット、魅力については、既にいろいろな資料がWEB上などで出ていますが、例えば、OTNの「Oracle Database 12c マルチテナントの魅力」というHPでは大きく5つの内容が上がっています。

1.サーバー最適化の向上
2.多数のデータベースを一元管理
3.多数のデータベースをまとめてバックアップ
4.データベースの迅速なプロビジョニング
5.データベースの迅速な移動
詳細はそちらのHPのほうを確認してみてください。

さて、ユーザー作成のエラーの対応の仕方に戻ります。11gまでと同様にOracleに接続した場合は、コンテナ・データベース(CDB)に接続されるのですが、CDBではユーザスキーマ(ローカルユーザー)を作成することができません。上記のような ORA-65096 のエラーになります。ローカルユーザーは、実際にデータを管理するプラガブル・データベース(PDB)に接続して作成する必要があります。

※ローカル・ユーザーとは、特定のPDBのみに存在するユーザーのことで、共通ユーザーとは、各コンテナ(ルートと各PDB)に同名のユーザーが存在する場合のことで、ユーザー名に接頭辞(C##)が必要になります。

それでは、現在接続しているデータベースを確認します。
SQL*Plus上で、show con_name を実行します。

12c_show_con_name

上記のように、「CDB$ROOT」と表示されたら、コンテナ・データベース(CDB)に接続していることがわかります。プラガブル・データベース(PDB)に接続し直すために、まず、PDBの名前と現在のOPEN_MODEを確認します。

select name, open_mode from v$pdbs;

12c_pdb

今回接続したいPDBは「PDBORCL」で、OPEN_MODEは「READ WRITE」となっていて、起動できていることが確認されましたので、PDBORCLに接続し直します。

alter session set container = pdborcl;

12c_pdb_session

再度、現在接続しているデータベースを確認します。

12c_show_con_name2

プラガブル・データベースのほうに接続していることが確認できました。
もしも、PDBのOPEN_MODEが「MOUNTED」の状態であれば、まだPDBが起動していない状態のため、以下のようにしてPDBを起動する必要があります。
alter pluggable database pdborcl open;

また、停止する場合は、以下のようにします。
alter pluggable database pdborcl close immediate;

それでは、PDBに接続した状態でユーザーを作成してみます。

create user blog_test
identified by passwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

12c_cre_user

次に、connect, resource のロールを付与します。

grant connect, resource to blog_test;

12c_roll

それでは、作成したユーザ「blog_test」でログインしてみます。

sqlplus blog_test/passwd@pdborcl

12c_pdb_login

作成したユーザーでログインできました。
※ただし、PDBには、たとえサーバー内からの接続であっても、上記のように @ネット・サービス名 をつけて、必ずリスナー経由でログインする必要があるようです。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

varchar(max)、nvarchar(max) (SQL Server)

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

%e5%ae%9f%e4%bd%b3img_0565

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

さて、年末も間近に迫ってきました。ブログをアップしたいと思いつつも、日々の仕事に追われて、ヘトヘトです。そう言えば、先日まで日本で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の1レコードの制限が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');

blog73_var_max_cre

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

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;

blog73_datalength

 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';

blog73_max_equal

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

blog73_text_equal

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

blog73_ntext_equal

上記から、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%';

blog73_text_like

次は、nvarchar(max)型を1つのテーブルに複数持ち、それぞれに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
……. (後ろは省略)

blog73_insert

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

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

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

blog73_max_%e3%81%9f%e3%81%8f%e3%81%95%e3%82%93%e3%83%87%e3%83%bc%e3%82%bf

 これは、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技術ブログへ
にほんブログ村

FileTable(SQL Server)

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

img_0511

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

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

<本日の題材>
FileTable(SQL Server)

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

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

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

blog72_%e6%a7%8b%e6%88%90%e3%83%9e%e3%83%8d%e3%83%bc%e3%82%b8%e3%83%a3

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

blog72_%e3%82%b5%e3%83%bc%e3%83%90%e3%83%97%e3%83%ad%e3%83%91%e3%83%86%e3%82%a3

2)FileStreamを有効にしたデータベースの作成

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

blog72_%e3%83%87%e3%83%bc%e3%82%bf%e3%83%99%e3%83%bc%e3%82%b9_%e3%83%95%e3%82%a1%e3%82%a4%e3%83%ab%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97

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

blog72_%e3%83%87%e3%83%bc%e3%82%bf%e3%83%99%e3%83%bc%e3%82%b9%e5%85%a8%e8%88%ac

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

blog72_%e3%83%87%e3%83%bc%e3%82%bf%e3%83%99%e3%83%bc%e3%82%b9_%e3%82%aa%e3%83%97%e3%82%b7%e3%83%a7%e3%83%b32

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

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

blog72_cre_filetable

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

blog72_manstudio1

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

blog72_manstudio2

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

filetable1_%e3%82%a8%e3%82%af%e3%82%b9%e3%83%97%e3%83%ad%e3%83%bc%e3%83%a9

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

filetable_%e3%82%a8%e3%82%af%e3%82%b9%e3%83%97%e3%83%ad%e3%83%bc%e3%83%a9_%e7%94%bb%e5%83%8f

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

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

blog72_insert

この商品マスタ「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.商品コード;

blog72_select_filetable

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

blog72_dbsheet

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

一時テーブルのキー(SQL Server)

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

img_0509

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

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

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

<本日の題材>
一時テーブルのキー(SQL Server)

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

 ストアドプロシージャなどの中で、一時テーブルを作成してそこに一旦データを登録し、それを使っていろいろな条件によって処理を実行するようなことがよくあるかと思いますが、データ量が多くなると、やはり処理速度が遅くなってきます。
(一時テーブルは、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

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

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

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

blog71_exec_1

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

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

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

blog71_exec_2

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

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

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

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

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

blog71_exec_1

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

blog71_exec_2ok

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

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

blog71_%e4%b8%80%e6%99%82%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab

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

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

参照整合性制約(SQL Server)

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

img_0754

知人が松本城に行ったときに撮ってきた写真を頂きました。
ちょっと曇りの天気なのが残念ですが、天守閣が堀と一緒にきれいに写っています。松本城の天守は国宝で、その周りにある石垣や堀も国の史跡に指定されているそうです。天守の建造年は、戦国時代~江戸時代初期の頃だそうで、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;

blog70_%e9%83%a8%e9%96%80m

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

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

blog70_insert_err

 上記のように、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';

blog70_del_err

 この場合も、上記のように、「メッセージ 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';

blog70_upd_err

この場合も、やはり「メッセージ 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;

blog70_alter%e5%88%b6%e7%b4%841

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

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

blog70_%e7%a4%be%e5%93%a1

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

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

blog70_upd_cascade

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

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

blog70_%e7%a4%be%e5%93%a1_upd

社員マスタの部門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;

blog70_alter%e5%88%b6%e7%b4%842

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

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

blog70_upd_setnull

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

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

blog70_%e7%a4%be%e5%93%a1_updnull

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

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

参照整合性制約(外部キー制約)

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

img_0334

近くの川で、かもが泳いでいるのが見えたので、写真にとってみました。
9月のシルバーウィークも終わり、もうすぐ10月を迎えようとしています。今年は台風も多く日本に上陸し、いろいろと大変な天候が続いていました。地球温暖化の影響もますます大きくなってきている気がしますね。
この前、NHKで放映していたNHKスペシャル MEGA CRISIS 巨大危機~脅威と闘う者たち~ 第1集「加速する異常気象との闘い」をたまたま見たのですが、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;

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf

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

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

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_insert

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

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

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_del

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

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

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

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_upd

 この場合も、やはり「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;

blog69_%e5%88%b6%e7%b4%84_%e5%86%8d%e4%bd%9c%e6%88%90

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

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

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf_100

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

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e9%83%a8%e9%96%80del

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

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

blog69_%e7%a4%be%e5%93%a1%e3%83%9e%e3%82%b9%e3%82%bf%e7%a2%ba%e8%aa%8d1

 社員マスタの部門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;

blog69_%e5%88%b6%e7%b4%84_%e5%86%8d%e4%bd%9c%e6%88%902

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

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

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf_100

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

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e9%83%a8%e9%96%80del

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

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

blog69_%e7%a4%be%e5%93%a1%e3%83%9e%e3%82%b9%e3%82%bf%e7%a2%ba%e8%aa%8d2

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

外部表(2)(ORACLE)

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

img_0418

写真は、前回も載せましたが、富山の五箇山という世界遺産合掌造り集落に行ったときの写真です。

さて、とうとうブラジル・リオデジャネイロで行われていたパラリンピックも終わりました。オリンピック、パラリンピックとも、多くの感動を私たちに与えて頂いたと、選手の方々に本当に感謝します。
パラリンピックでは金メダルはなかったということですが、過去最高のメダル数を獲得。また、ちょっと話題は古くなりますが、先月のオリンピックでは、体操団体での悲願の金メダル、個人総合2連覇の内村選手の最後の大逆転は圧巻でしたね。伊調馨選手のレスリング4連覇は、女子個人競技で史上初めてのこと。卓球でも男子団体で銀メダル、女子団体も銅メダル、女子バドミントンダブルスの日本初の金メダル、本当に手に汗握って応援しました。柔道も男子は全階級でメダル、男子の陸上4*100メートルリレー銀メダル、競泳陣の活躍、数え上げればきりがないです。

次はいよいよ東京オリンピック、パラリンピック! 4年間という長い努力と訓練の期間、想像を絶する戦いの末に、栄光があるのでしょう。私たちも、道は違いますが、選手の方々に負けないよう、与えられた環境の中で精いっぱい頑張って行かなければと背中を押される気がします。
選手の皆さん、本当にお疲れ様でした。そして、感動をありがとう!

<本日の題材>
Oracleの外部表(2)

前回、ORACLEの外部表についてORACLE_LOADER型を取り上げましたが、今回は、ORACLE_DATAPUMP型というものを試してみたいと思います。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルにデータを書き込んだり、読込んだりすることができます。

例)
商品売上という、商品コード毎、年度毎の各月の売上金額を集計したテーブルがあり、そこに、2014年、2015年、2016年のデータが登録されているとします。
テーブルの定義は以下のようになっています。
DESC 商品売上

desc_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

データとしてはこんな感じ。

%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a%e3%83%87%e3%83%bc%e3%82%bf

 年度毎の件数は、
SELECT 年度, COUNT(*) FROM 商品売上
GROUP BY 年度
ORDER BY 年度;

%e5%b9%b4%e5%ba%a6%e3%81%94%e3%81%a8%e4%bb%b6%e6%95%b0

サンプルデータなので件数は少ないです。
それで、今年度(2016)のデータだけ残して、過去の年度のデータは、テーブルとしては持たないでダンプファイルに保存し、必要なときには外部表としてアクセスするようにしたいと思います。

まず、2014年のデータの外部表を作成し、外部表のダンプファイルにデータを登録します。

CREATE TABLE SYO_URIAGE_2014
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2014.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2014';

syo_uriage_2014

 次に、同様にして、2015年のデータの外部表を作成します。

CREATE TABLE SYO_URIAGE_2015
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY temp_dir
  LOCATION ('uriage_2015.dmp')
)
AS SELECT * FROM 商品売上
    WHERE 年度 = '2015';

syo_uriage_2015

実際に、ダンプファイルが指定のフォルダに作成されていることを確認してみます。今回ディレクトリに指定した「temp_dir」は、前回のブログで作成したように、「C:\temp」フォルダになりますので、エクスプローラで確認すると、

temp_dir

確かに、「URIAGE_2014.DMP」「URIAGE_2015.DMP」ファイルが作成されていました。

それぞれ作成した外部表のデータ件数を確認してみます。
SELECT COUNT(*) FROM SYO_URIAGE_2014;

syo_uri_2014_count

SELECT COUNT(*) FROM SYO_URIAGE_2015;

syo_uri_2015_count

元の商品売上テーブルからは、過去の年度のデータは削除し、2016年度のデータのみを残すことにします。

DELETE FROM 商品売上
WHERE 年度 < '2016';

del_%e5%95%86%e5%93%81%e5%a3%b2%e4%b8%8a

 処理の上で、2016年度のデータを見ればよい場合は「商品売上」テーブルに対してSELECTを実施しますが、過去のデータも見たい場合には、外部表を使うことで検索できます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM 商品売上
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2015
 WHERE 商品CD = 'A0001'
UNION
SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
  FROM SYO_URIAGE_2014
 WHERE 商品CD = 'A0001'
ORDER BY 年度;

%e5%a4%96%e9%83%a8%e8%a1%a8_union

 また、上記でユーザ「BLOG」によって作成された外部ファイル(ダンプファイル)を、別のユーザで使用するようなこともできます。

今回、「BLOG2」というユーザを作成し、「temp_dir」をBLOG2ユーザからも読み書きできるようにします。

CREATE USER BLOG2
IDENTIFIED BY< password>    -- パスワードを設定します
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

GRANT READ, WRITE ON DIRECTORY temp_dir TO BLOG2;

grant_read_write_blog2

作成したユーザー「BLOG2」でログインし、上記で作成した2015年度の商品売上データのダンプファイル(uriage_2015.dmp)を元にBLOG2ユーザ用の外部表「SYO_URIAGE2_2015」を作成してみます。

  CREATE TABLE SYO_URIAGE2_2015
   ("商品CD" VARCHAR2(20 BYTE),
              "年度" VARCHAR2(4 BYTE),
              "売上4月" NUMBER(12,0),
              "売上5月" NUMBER(12,0),
              "売上6月" NUMBER(12,0),
              "売上7月" NUMBER(12,0),
              "売上8月" NUMBER(12,0),
              "売上9月" NUMBER(12,0),
              "売上10月" NUMBER(12,0),
              "売上11月" NUMBER(12,0),
              "売上12月" NUMBER(12,0),
              "売上1月" NUMBER(12,0),
              "売上2月" NUMBER(12,0),
              "売上3月" NUMBER(12,0)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY TEMP_DIR
      LOCATION
       ( 'uriage_2015.dmp'
       )
    );

 %e5%a4%96%e9%83%a8%e8%a1%a8%e4%bd%9c%e6%88%902

作成した外部表を検索してみます。

SELECT 商品CD, 年度, 売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月
 FROM SYO_URIAGE2_2015
ORDER BY 商品CD;

select_%e5%a4%96%e9%83%a8%e8%a1%a82

 データの検索ができました。
上記のように、一度作成した外部ファイル(ダンプファイル)は、別のユーザや別のデータベース環境に移動してそこで使用することも可能なんですね。
さらに、PREPROCESSOR句を使用すると、外部ファイルを圧縮したファイルに対して解凍しながら使用するということも可能なようです。

(※)注意点として、DATAPUMP(expdp)で作成したdmpファイルは外部表のダンプファイルとしては使用できないようです。

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

外部表(ORACLE)

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

IMG_0441

夏休みに、妻の実家に行って来ました。長女だけは仕事の都合がつかず、一緒に行けなかったのが残念でしたが、あとの子供たちを引き連れて行ってきました。また、実家からそれほど遠くはないところに、世界遺産があるということで、そこに行ってきたのが、上の写真です。

ここは世界遺産合掌造り集落といって、岐阜県の白川郷・荻町集落、富山県の五箇山・菅沼集落、五箇山・相倉集落があり、今回行ってみたのは五箇山・菅沼集落です。東海北陸自動車道の五箇山インターチェンジからわりとすぐのところにあり、合掌造りの家屋が9棟くらいの3つの集落では一番小さな集落です。
そこに行くのに、駐車場のあるところからはエレベータで降りて行くのですが、別世界というか、昔にタイムスリップしたような不思議な感覚になりました。屋根がとても高く独特で、両手を合わせたような形になっていることから、合掌造りと呼ばれるようになったと思われるとのことです。
1軒1軒がとても大きかったですね。妻や子供たちも感動しながら、いっぱい写真を撮ってました。
それなりに多くの観光客(外国の方も結構いました)の人も来ていて、結構人気があるんですね。できれば、白川郷や相倉集落など他の集落も見て見たかったのですが、時間も遅くなってしまいそうだったので、菅沼集落だけを見て帰りました。
今度時間があれば、また妻と一緒に行ってみようっと!

IMG_0412_

 <本日の題材>
Oracleの外部表

ORACLEの場合に、テーブルにCSVファイル用のようなデータを読込む処理にSQL*Loaderというツールがあります(前回の投稿の中で、ちょこっと使っています)が、実際のテーブルには読込まずに、あたかもテーブルが存在するかのようにそういった外部ファイルにアクセスすることができるツールとして、外部表というものがあります。今回はそれを取り上げてみたいと思います。

まず、準備として、外部表によって読取りまたは書込みが行われるファイルのデフォルトの位置としてのディレクトリを作成します。以前、UTL_FILEパッケージについて取り上げたときに、ディレクトリの作成を行いましたが、今回も同様に設定します。

構文:
CREATE [OR REPLACE] DIRECTORY <
ディレクトリ名>
 AS ‘<
ディレクトリ・パス>

) SYSDBA権限でログインし、ディレクトリを作成します。

CREATE OR REPLACE DIRECTORY temp_dir
AS 'C:\temp';

create_directory

※このディレクトリは、サーバ上のディレクトリになります。

実行ユーザ「BLOG」にディレクトリに対する読込権限と書き込み権限を付与します。

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

grant_read_write

 外部表の作成は、CREATE TABLE...ORGANIZATION EXTERNAL文を使用します。
今まで何度か使用している商品マスタについて、その内容をCSVファイルにしたものを外部表としてアクセスしてみたいと思います。

例)
CREATE TABLE syomst_load
  (syo_cd         VARCHAR2(10),
   syo_name   VARCHAR2(20),
   bnrui             VARCHAR2(20),
   price             NUMBER(10))
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY temp_dir
   ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY ','
             (syo_cd,
              syo_name,
              bnrui,
              price
             )
     )
   LOCATION ('syomst.txt')
  )
REJECT LIMIT UNLIMITED;

cre_外部表

 上記のSQL文にあるように、temp_dir というディレクトリ(実際は、c:\temp )に、「syomst.txt」 というカンマ区切りの以下のようなCSVファイルを置いておきます。

A0001,チョコレート,お菓子,120
A0002,ビスケット,お菓子,200
A0003,ガム,お菓子,100
A0004,スナック,お菓子,140
B0001,りんご,果物,100
B0002,桃,果物,160
B0003,みかん,果物,80
B0004,梨,果物,120
C0001,キャベツ,野菜,160
C0002,にんじん,野菜,150
C0003,じゃがいも,野菜,100
C0004,玉ねぎ,野菜,150

ここまでの設定で、以下のSQLを実行すると、CSVファイルのデータを、あたかもテーブルが既に存在するかのように表示することができます。

SELECT * FROM syomst_load;

select_外部表

CSVデータの中身を変更してみます。
1行目と2行目のPRICEを130,210と変更して、CSVファイルを保存します。その後、再度先ほどのSQL文を実行します。

SELECT * FROM syomst_load;

select_外部表変更後

結果は、たしかに、1行目の金額が130円、2行目が210円に変わっていることが確認できました。

上記は、カンマ区切りのCSVファイルでしたが、当然固定長のファイルでも可能です。
また今回は、CREATE TABLE のときの外部表の型として、ORACLE_LOADER型を使用しましたが、もう一つ、ORACLE_DATAPUMP型というのもあります。ORACLE_LOADER型では、CSVファイルからの読込しかできませんが、ORACLE_DATAPUMP型では、ダンプファイルを使用しての処理が可能となります。これについては、また機会があれば、試してみたいと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

テーブルの項目数、レコード長の制限

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

IMG_0301

先日、会社の同僚の誘いで海釣りに行く機会がありました。写真は、そのときにボートから撮った写真です。小学生の息子も連れて行って来ました。ボートに乗る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)
) ;
列数1024

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

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

 エラーメッセージは、「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'
);

bulk_insert

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

SELECT * FROM dbo.test_col_max
 ORDER BY col_001key;

列数1024_確認

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

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

行バイト数超過エラー

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

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

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

oracle_行するMAX

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

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

oracle_retu

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

oracle_sqlldr_ctl

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

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

ora_sqlldr_exec

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

ora_col1000

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

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

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

 今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

主にSQLについて書いていきたいと思います。