第５回「SQL文をGUIで作成できるフリーのデータベース開発環境を利用する」

もう、クポクポです。

では、第５回目のお題は「SQL文をGUIで作成できるフリーのデータベース開発環境を利用する」です。

何度も問い合わせして確認するのは面倒なものです。
そこで、SQL文をGUIで作成できるフリーソフト「黒猫 SQL Studio」です。
※前回までに作成したOracleXEを利用することを前提にしていきます。

まずはODBCの設定が必要です。
ODBCとは、データベースにアクセスするためのソフトウェアの標準仕様で、
各データベースの違いはODBCドライバによって吸収され、DBアクセスできるようになります。

・ODBCの設定
「コントロールパネル」→「管理ツール」→「データ ソース (ODBC)」→「ユーザーDSNタブの追加をクリック 」

「Oracle in XEClientを選択する」

「データソース名に「xe」、TNSサービス名に「OracleXEサーバのIPアドレス」を記述後、接続テストでユーザ名、パスワードを入力して、接続できることを確認」

・黒猫 SQL Studioでテーブルを調べる
黒猫 SQL Studioをダウンロードする。
「メニューバーの「データベース」→「データベースに接続」をクリック」

「ODBC接続タブの「XE」を選択して接続をクリック」→「ユーザ名、パスワードを入力してログインする」

DBエクスプローラ上にツリー上でユーザ、テーブル等が表示されている。
作成済みのテーブルを右クリックして、「テーブルを開く(全件)」をクリックすると、
そのテーブルの全件が表示される。
これで、いちいちselect文を入力しなくても内容を把握することができます。

また、ソートした形で表示させるためには、
作成済みのテーブルを右クリックして、「テーブルを開く(条件指定)」をクリックして、
ソート順序に数字を入力して、ソート方向(ASC、DESC)を選択する。

これで、SQLでの簡単な問い合わせや確認が楽になりました。

第４回「SQL*LoaderでDBに登録する」

はじめからネットワーク関連のことしか書いてないけど、
専門外のことばっかりしてる...

Oracleが使えるようになったので、家計簿でもつけようと思います。
ということで、第４回目のお題は「SQL*LoaderでDBに登録する」です。

買い物のたびに「insert into」で追加していくのもしんどいです。
そこで、SQL*Loaderの出番です。
SQL*Loaderは、CSVファイルからOracleのテーブルへデータを登録するツールです。
CSVファイルの編集はExcelで簡単にできるので便利です。

以下のようなテーブルにデータを追加していきます。
SQL> desc housekeeping
名前 NULL? 型
--------- -------- -------------

NO NOT NULL NUMBER(4)
BUYDATE VARCHAR2(8)
NAME VARCHAR2(20)
PRICE NUMBER(6)

NOはシーケンス番号。
BUYDATEは購入日。形式はYYYYMMDD。例：20080608
NAMEは商品名。
PRICEは価格です。

NOのシーケンス番号を実現するために、順序作成します。
次のようにすると、1 ～ 100、増分 1 のシーケンス番号作成。
例：
create sequence seq
　　increment by 1 /*増分値*/
　　start with 1 /*初期値*/
　　maxvalue 100 /*最大値*/
　　minvalue 1 /*最小値*/
　　nocycle /*ループなし*/
　　nocache /*キャッシュなし。メモリ上にキャッシュすることも可能で、異常終了した場合は、キャッシュされた値は欠番*/

※シーケンスの取得
select seq.nextval from dual;/*次のシーケンス*/
select seq.currval from dual;/*現在のシーケンス*/

シーケンス番号の利用方法は、
insert into housekeeping values(seq.nextval, 20080608, オカシ, 100);


SQL*Loaderには、コントロールファイル(.ctl)が必要になります。
「--」で始まっている行は コメントです。
例）housekeeping.ctl：

--LOAD ロードするレコード数
--SKIP スキップするレコード数
--ERRORS 許容するエラーの数 -1の場合、全て
--ROWS 何件毎にコミットするか -1の場合
OPTIONS(LOAD=100,SKIP=1,ERRORS=-1,ROWS=10)
--新しくデータ・ロードが開始
LOAD DATA
--データファイルを指定する。入力データであるCSVファイルや固定長ファイル。複数ファイルを指定することも可能。
INFILE 'csv/housekeeping.csv'
--何らかのエラーがあってDBに入れられないデータがあった場合、そのデータがこのファイルに出力される。
BADFILE 'bad/housekeeping.bad'
--データを追加する。
APPEND
--データを入れるテーブルを指定する。複数の出力先を指定することも可能。
INTO TABLE HOUSEKEEPING
--データを区切る文字を指定。カンマ区切りの場合は「TERMINATED BY ","」、タブ区切りにしたい場合は「TERMINATED BY X'09'」、固定長ファイルの場合は不要
FIELDS TERMINATED BY ","
--この指定があると、データの無い項目にNULLを入れる。
TRAILING NULLCOLS
--データ移送先テーブルの項目名を記述する。ファイル内のデータの並び順に合わせて列挙する。
--各項目の後ろには関数指定も可能。
(
NO "ユーザ名.seq.nextval",
BUYDATE,
NAME,
PRICE
)

DBに追加するCSVファイルを用意します。
例：housekeeping.csv

NO,BUYDATE,NAME,PRICE
,20080608,オカシ,100
,20080608,ノミモノ,150


実行方法は以下の通りです。
C:.
│
├─ctl
│ housekeeping.ctl
├─csv
│ housekeeping.csv


C:\sqlldr ユーザー/パスワード@SID control=ctl/housekeeping.ctl

実行すると、ログファイルが出来る。

housekeeping.log
スキップされた論理レコードの合計: 1
読み込まれた論理レコードの合計: 2
拒否された論理レコードの合計: 0
廃棄された論理レコードの合計: 0

失敗すると、badログファイル(bad/housekeeping)ができます。


これで何とか持続して家計簿が続けていくことができるのでしょうか...心配です。

５月分の経費を調べてみました。
SQL> select sum(price) from housekeeping where buydate between 20080501 and 20080601;

...うつです。