参照整合性制約(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 Server)

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

IMG_0250

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

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

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

IMG_0254 皇居側から見た平川橋


<本日の題材>
テーブル値関数(SQL Server)

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

例1)まず、サンプルとして適当かどうか?というのはありますが、こういう使い方もあるということで、指定した日から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 表示順;

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

blog65_ufn_calender結果

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

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

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

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

bcpユーティリティ(SQL Server)

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

IMG_0218

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

話しは変わりますが、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 /? と実行すると以下のような画面が出ます。

bcpヘルプ

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

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

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

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

bcp_ファイル出力

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

more_bcptxt

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

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

bcp_ファイル出力2

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

more_bcptxt2

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

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

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

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

bcp_truncate

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

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

bcp_ファイル入力

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

bcp_商品マスタ

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

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

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

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

bcp_select出力

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

more_bcptxt3

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

今日は以上まで

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

UTL_FILEパッケージ

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

DSC_2634_2

4月に入り、学校や多くの職場で年度が新しくなって、心機一転出発された方も多いと思います。
我が家の長女も、専門学校を卒業して、新しく社会人として出発しました。また、娘の一人は高校受験を何とかクリアして、高校生として出発しました。
自分のその当時のことを思い出しながら、子供たちもひとりひとり、人生の様々な試練や関門にぶつかりながら、少しずつ成長していく姿を見ると、感慨深いものがあります。

<本日の題材>
UTL_FILEパッケージ

前回、前々回と、ORACLEのパッケージについて取り上げました。今回もその続きで、ORACLE側で用意されているユーティリティ・パッケージの一つである、UTL_FILEパッケージについて見てみたいと思います。

UTL_FILEパッケージを使うことで、PL/SQLでOSのテキストファイルの読み書きができます。
そのためには、あらかじめアクセス可能なディレクトリを設定しておかなければなりません。この、ディレクトリ・オブジェクトの作成は、管理ユーザで行い、その後、そのディレクトリの読み書きを行う権限を、実行するユーザに与える必要があります。

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

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

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

create_directory

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

UTL_FILEパッケージを使ってファイルを読み書きするユーザ「BLOG」にディレクトリに対する読込権限と書き込み権限を付与します。

GRANT READ, WRITE ON DIRECTORY temp_dir TO blog;

grant_read_write

 準備はできましたので、「商品マスタ(syomst)」のデータを、C:\temp ディレクトリ上に、「syomst.txt」という名前のテキストファイルに出力して保存するというプロシージャを、UTL_FILEパッケージを使って作成してみます。

CREATE OR REPLACE PROCEDURE file_output_syomst
IS
  CURSOR cur_syomst IS
     SELECT
       syo_cd||','||syo_name||','||bnrui||','||price||','||seqno AS syomst_data
       FROM syomst
      ORDER BY syo_cd;

  --ファイル・ハンドルを受け取る変数の定義(1)
   write_file  UTL_FILE.FILE_TYPE;

BEGIN
  --ファイルのオープン(2) (パラメータとして、ディレクトリ、ファイル名、オープンモード)
   write_file := UTL_FILE.FOPEN('TEMP_DIR', 'syomst.txt', 'a');
  --カーソルのループ
   FOR syomst_rec IN cur_syomst LOOP
   --データを1行ずつファイルに書き込む(3)
     UTL_FILE.PUT_LINE(write_file, syomst_rec.syomst_data);
   END LOOP;
  --ファイルのクローズ(4)
  UTL_FILE.FCLOSE(write_file);

EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('SQLCODE');
   DBMS_OUTPUT.PUT_LINE('SQLERRM');
   UTL_FILE.FCLOSE(write_file);
END;
/

使い方としては、まず、ファイル操作を行うためにはファイルごとにファイル・ハンドルというものが必要であり、それを格納する変数の定義を、UTL_FILEパッケージのFILE_TYPE型で定義します(1)。
その次に、FOPENファンクションを使用してファイルをオープンします。このFOPENファンクションは、戻り値としてファイルハンドルを戻します。構文は以下です。

UTL_FILE.FOPEN (
   location     IN VARCHAR2
  ,filename     IN VARCHAR2
  ,open_mode    IN VARCHAR2
   [,max_linesize IN BINARY_INTEGER]
)
  RETURN file_type;

各パラメータについて説明します。
 ・location:
 ファイルのディレクトリ位置。この文字列はディレクトリのオブジェクト名で、大/小文字が区別される。デフォルトは大文字。ユーザーがFOPENを実行するには、このディレクトリに対する読取り権限が付与されている必要がある。

・filename: 拡張子(ファイル・タイプ)も含めたファイル名。
・open_mode: ファイルのオープン方法を指定
    r -- テキストの読込み
    w -- テキストの書込み
    a -- テキストの追加
    rb -- バイトの読込み
    wb -- バイトの書込み
    ab -- バイトの追加

 ・max_linesize:
  改行文字を含むこのファイルの1行当たりの最大文字数(最小値は1、最大値は32767)。デフォルトは1024文字。

ファイルをオープンしたら、PUT_LINEプロシージャで1行のデータを書き込みます(3)。
※ファイルへの書き込みには、いくつかのプロシージャがあります。
  UTL_FILE.PUT_LINE(ファイルハンドル, 文字列)
       --ファイルにデータを書き込み、最後に改行コードも書き込む。
    UTL_FILE.PUT(ファイルハンドル, 文字列)
       --改行コードを付けずに、ファイルにデータを書き込む。
    UTL_FILE.NEW_LINE(ファイルハンドル, 行数)
       --改行コードのみをファイルに書き込む。

書込みが終了したら、FCLOSEプロシージャでOSファイルをクローズします(4)。

それでは、このプロシージャを実行してみます。
EXECUTE file_output_syomst

exec_utl_file

実行後、サーバの c:\temp ディレクトリに作成されたファイル「'syomst.txt」の中身を確認してみます。

syomst_txt

商品マスタのデータが、テキストファイルに出力されていることが確認できました。

次に、先ほど作成したテキストファイル「syomst.txt」を読込んで、画面に出力するプロシージャを作成します。

CREATE OR REPLACE PROCEDURE file_read_txt
IS
  --ファイル・ハンドルを受け取る変数の定義(1)
     read_file  UTL_FILE.FILE_TYPE;
     V_DATA     VARCHAR2(32767);

BEGIN
  --ファイルのオープン(2)
   read_file := UTL_FILE.FOPEN('TEMP_DIR', 'syomst.txt', 'r', 32767);
  --ループ
   LOOP
   --ファイルハンドルから1行ずつデータを読込む(3)
      UTL_FILE.GET_LINE(read_file, V_DATA, 32767);
   --画面に表示する
      DBMS_OUTPUT.PUT_LINE(V_DATA);
   END LOOP;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
  --読込む行がなくなると、ファイルをクローズする(4)
      UTL_FILE.FCLOSE(read_file);
END;
/

ここでも、まずファイル・ハンドルを受け取る変数の定義を行い(1)、その後、FOPENファンクションで読込モードでテキストファイルをオープンします(2)。その後、GET_LINEプロシージャで1行ずつデータを読込み(3)、DBMS_OUTPUT.PUT_LINEで画面に出力します。
※GET_LINEで読込むファイルは、読込モード(r)でオープンしておく必要があります。違うモードの場合には、実行時INVALID_OPERATION例外が発生します。
読込む行がなくなると「NO_DATA_FOUND例外」が発生するので、FCLOSEプロシージャでファイルをクローズします(4)。

これを実行してみます。
SET SERVEROUTPUT ON
EXECUTE file_read_txt

execute_file_read

テキストファイル「syomst.txt」の内容を読込んで画面に表示することができました。

今日は以上まで

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

パッケージ(ORACLE)のオーバーロード

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

DSC_2529

3月も後半に入りましたが、気温の寒暖差がまだ結構ありますね。歳もそれなりに取ってきたので、気温の変化に体がついて行かず、体調を崩しやすくなってきた気がします。花粉も結構飛んでますし!

また、体だけでなく、住まいについても長く住んでいるといろいろなところが傷んできますよね。家電製品などが調子が悪くなると、妻が私に対して、「せっかくそれなりの学校の電気工学科を出たんだから、何とかならないの?」 と言いますが、あまり機械いじりなどが得意でない私は、結局何もできないでお手上げ状態になることがままあります。
そんなとき、「実際の生活にはなんも役に立たないんだから!」と、妻の厳しい~ 一言。
一応、僕は、ソフトウェア開発者なんだけどな~ (ΠΔΠ)

前回、ORACLEのパッケージについて簡単に取り上げてみましたが、続きとして、今回はパッケージのオーバーロードの機能について取り上げてみたいと思います。

<本日の題材>
パッケージ(ORACLE)のオーバーロード

オーバーロードとは、同じ名前のサブプログラムを定義できる機能です。JavaやC++などのオブジェクト指向言語にもオーバーロードという仕組みがあり、同名のメソッドや演算子を複数定義し,プログラムの文脈応じて,その場面に合ったメソッドや演算子を選択させることで,内部的な処理の手法が違うものに対して同一の処理手法を提供することができます。
パッケージのオーバーロードも同じような意味合いであり、同じ名前のサブプログラムを複数定義しておくことで、パラメータの数や順序、データ型が異なっている場合でも、同じ名前のサブプログラムを呼び出して実行することができます。

簡単な例を作成してみます。

例)
前々回で使用した商品マスタ(syomst)について、商品の金額を検索するのに、パラメータとして商品CDを渡して検索するのと、商品名を渡して検索するのを同じサブプログラムで定義して実行してみます。
ただ、よくよく考えると、商品CDと商品名はともにVARCHAR2型であり、今回の例としてはパラメータの型が違うものである必要があるため、SEQNOというINT型の項目を追加して、SEQNOで検索するのと、商品名で検索するのを同じサブプログラム名で定義してみたいと思います。

まず、商品マスタテーブルに「SEQNO」項目を追加します。

ALTER TABLE SYOMST ADD SEQNO INT;

alter_table_syomst

「SEQNO」項目には、商品CD(syo_cd)でソートした順の番号を設定します。

UPDATE SYOMST SET
  SEQNO =
        (SELECT A.SEQNO
             FROM
             (SELECT
                 syo_cd
              ,  ROW_NUMBER() OVER (ORDER BY SYO_CD) AS SEQNO                       FROM syomst) A
            WHERE A.syo_cd = SYOMST.syo_cd);

blog62_upd_seqno

更新後のデータを見てみます。

SELECT * FROM SYOMST
ORDER BY SYO_CD;

blog62_select

 「SEQNO」が追加され、SYO_CD順に番号が振られていることが確認できます。

それでは、この商品マスタから商品の金額を検索するのに、商品名で検索するのとSEQNOで検索するのを同じサブプログラムで定義するパッケージを作成します。

CREATE OR REPLACE PACKAGE pack_test2
IS
    PROCEDURE syomst_price(p_syo_name VARCHAR2);
    PROCEDURE syomst_price(p_seqno INT);
    op_price NUMBER := 0;
END;
/

パッケージの本体部分は、

CREATE OR REPLACE PACKAGE BODY pack_test2
IS 
  PROCEDURE syomst_price(p_syo_name VARCHAR2)
  IS
  BEGIN
    SELECT price INTO op_price
      FROM syomst
     WHERE syo_name = p_syo_name;  

    DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;

  PROCEDURE syomst_price(p_seqno INT)
  IS
  BEGIN
    SELECT price INTO op_price
      FROM syomst
     WHERE seqno = p_seqno;

    DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;
END;
/

これを実行(コンパイル)すると、
cre_pack_over

パッケージは作成されました。
それでは、実際に実行してみます。
最初に、商品名「キャベツ」で検索します。
※プロシージャの中でDBMS_OUTPUT.PUT_LINEを使用して金額を出力するかたちにしていますので、SQL*Plusで実行する場合には、初めにSERVEROUTPUTシステム変数をONにします。

SET SERVEROUTPUT ON
BEGIN
pack_test2.syomst_price('キャベツ');
END;
/

exec_pack2_syoname

 160円というキャベツの金額が表示されました。

次に、このキャベツのSEQNOは「9」なので、「9」の値で検索してみます。

BEGIN
  pack_test2.syomst_price(9);
END;
/

exec_pack2_seqno

先ほどと同様に、160円というキャベツの金額が表示されました。
同じ pack_test2.syomst_price というサブプログラムを実行しますが、パラメータの型を認識して、自動的にどちらのプロシージャを実行するかを判断しているということですね。

ちなみに、最初にやりかけた、商品CDと商品名というどちらも同じVARCHAR2型のパラメータを渡すものでちょっと試してみます。

CREATE OR REPLACE PACKAGE pack_test3
IS
    PROCEDURE syomst_price(p_syo_cd VARCHAR2);
    PROCEDURE syomst_price(p_syo_name VARCHAR2);
    op_price NUMBER := 0;
END;
/

CREATE OR REPLACE PACKAGE BODY pack_test3
IS
  PROCEDURE syomst_price(p_syo_cd VARCHAR2)
  IS
  BEGIN
      SELECT price INTO op_price
          FROM syomst
       WHERE syo_cd = p_syo_cd;

       DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;

  PROCEDURE syomst_price(p_syo_name VARCHAR2)
  IS
  BEGIN
       SELECT price INTO op_price
         FROM syomst
       WHERE syo_name = p_syo_name;

       DBMS_OUTPUT.PUT_LINE(op_price);
  END syomst_price;
END;
/

このパッケージは、コンパイルはできますが、実行すると「PLS-00307」のエラーが表示されて実行はできません。

BEGIN
  pack_test3.syomst_price('キャベツ');
END;
/

exec_pack_err

このように、オーバーロードが可能となるためには、パラメータの数やデータ型の違い、またファンクションの場合はリターンするデータ型などの違いだけでも必要になるということです。

今日は以上まで

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

パッケージ(ORACLE)

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

IMG_0142

夜の浦和駅で撮った写真です。サッカーは好きなので、やはりどうしても地元の浦和レッズに関心が行きますね。昨年は第1ステージは優勝でしたが、年間では3位、ナビスコ杯はベスト8、天皇杯やゼロックス杯は準優勝と、頑張ってはいるんですが、あと一歩、なかなか最後勝ちきれずに、悔しい思いをしたファンが多かったのではないでしょうか。今年は、是非頑張ってほしいです!

今日は、ORACLEのパッケージについて取り上げてみたいと思います。今まで何度か例で使用してきたDBMS_OUTPUTパッケージや、DBMS_LOCKパッケージなど、ORACLE側で事前に用意されているユーティリティ・パッケージもそれに該当しますが、そのパッケージについて詳しく見てみたいと思います。

<本日の題材>
パッケージ(ORACLE)

パッケージは、複数のサブプログラムを1つにまとめるためのオブジェクトです。プロシージャやファンクションとは異なり、仕様部と本体を別々に作成します。仕様部には本体に含めているプログラム名などをまとめて記述し、本体には各プログラムのソースコードを個別に記述していきます。

●パッケージの構造

  パッケージ仕様部
    PROCEDURE  proc_1 (para_1 VARCHAR2);
    FUNCTION  func_1 (para_2 NUMBER);

 パッケージ本体
    PROCEDURE proc_1 (para_1 VARCHAR2)
    IS
    BEGIN
       …

    FUNCTION  func_1 (para_2 NUMBER)
    IS
    BEGIN
       …

パッケージの場合、この仕様部さえ定義できていれば、パッケージ本体が未完成であっても、コンパイルは正常になされます。このプロシージャ proc_1 を呼び出すプログラムは、proc_1 のパッケージ本体を直接参照するのではなく、proc_1を実行するのに必要な情報をパッケージの仕様部から得ることができるので、プロシージャ proc_1 の本体部が作成されていなくてもコンパイルできるわけです。

パッケージの仕様部の作成は以下のようになります。

CREATE  [ OR REPLACE ] PACKAGE <パッケージ名>
{ IS | AS }
  <仕様部>
END [ <パッケージ名> ] ;

例)
CREATE OR REPLACE PACKAGE pack_test
IS
    PROCEDURE customer_month_purchase(年月 VARCHAR2);
    PROCEDURE customer_total_purchase;
END;

この仕様部の作成の処理を実行すると、

cre_package

パッケージの仕様部は作成できました。

次に、パッケージの本体を作成してみます。本体の作成は以下のようになります。

CREATE  [ OR REPLACE ] PACKAGE BODY <パッケージ名>
{ IS | AS }
  <本体>
END [ <パッケージ名> ] ;

先ほど仕様部を作成したパッケージの本体を作成します。パッケージ名は、仕様部と本体で一致している必要があります。

CREATE OR REPLACE PACKAGE BODY pack_test
IS
    PROCEDURE customer_month_purchase(V年月 VARCHAR2)
      IS
      BEGIN
        DELETE FROM 顧客月別購入履歴
         WHERE 年月 = V年月;
 
        INSERT INTO 顧客月別購入履歴
        (顧客NO, 年月, 購入回数, 購入金額)
        SELECT 顧客NO, TO_CHAR(出荷日,'YYYYMM'),
                            COUNT(DISTINCT 売上NO), SUM(売上金額)
          FROM 売上TBL
         WHERE TO_CHAR(出荷日, 'YYYYMM') = V年月
         GROUP BY 顧客NO, TO_CHAR(出荷日,'YYYYMM');
      END customer_month_purchase;
     
    PROCEDURE customer_total_purchase
      IS
      BEGIN
        DELETE FROM 顧客購入履歴;
       
        INSERT INTO 顧客購入履歴
        (顧客NO, 累計購入回数, 累計購入金額)
        SELECT 顧客NO, COUNT(DISTINCT 売上NO), SUM(売上金額)
          FROM 売上TBL
         GROUP BY 顧客NO;
      END customer_total_purchase;
END;
/

これは、売上TBLのデータから、指定した年月についての顧客の月別購入回数、金額を抽出して、「顧客月別購入履歴」テーブルに登録する「customer_month_purchase」というプロシージャと、売上TBLから顧客の過去のトータルの購入回数、金額を抽出して、「顧客購入履歴」テーブルに登録する「customer_total_purchase」というプロシージャを定義しています。(どちらも一旦データを削除してから登録します)

これを実行すると、

cre_package_body

 パッケージ本体も作成されました。

では、実際に実行してみます。
最初に、パッケージの中の「customer_total_purchase」プロシージャを実行します。

BEGIN
  pack_test.customer_total_purchase;
END;
/

exec_pack_total

 処理結果を確認するため、顧客購入履歴テーブルを抽出します。

SELECT * FROM 顧客購入履歴
ORDER BY 顧客NO;

顧客購入履歴

 次に、パッケージの中の「customer_month_purchase」プロシージャを実行します。

BEGIN
  pack_test.customer_month_purchase('201601');
END;
/

exec_pack_monthl

処理結果を確認するため、顧客月別購入履歴テーブルを抽出します。

SELECT * FROM 顧客月別購入履歴
 WHERE 年月 = '201601'
ORDER BY 顧客NO;

顧客月別購入履歴

データが作成されていることが確認できました。

※なお、パッケージの利点、使用する理由については、ここでは詳しくは記述しませんが、日本オラクル社のOTNのサイトに記載があります。(ORACLE 11g2のマニュアルにそのような説明がありました)
https://docs.oracle.com/cd/E16338_01/appdev.112/b56260/packages.htm#i2408


今日は以上まで

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

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