Oracle『レコードを登録する』テーブルにデータを入れる|COMMITとROLLBACK

Oracle『レコードを登録する』テーブルにデータを入れる|COMMITとROLLBACKOracle SQL入門

テーブルにデータを入れる方法

テーブルにはSQLやクライアントツールの機能からデータを入れることができます。

データを準備するという作業から考えると、
クライアントツールの便利機能を使うのが効率的ですが、

Javaなどのプログラムからデータを登録する際に、
データ登録用のSQLはどの道必要になってくるので、

Oracleをこれから学ぶ人は、クライアントツールの機能を使わずに、
まずはSQLを実行することでデータを1件1件登録して構文を覚えましょう

そう!『急がば回れ』です。最初から甘い蜜を吸うのではなくて、
SQLでデータ登録してから、ツールの便利さを覚えてください。

 速いプログラム

テーブル作成の時と同じく、SQL実行の際は、
テーブルを保持しているユーザーでログインしておきましょう。

レコードを登録する(INSERT)

テーブルに入る1件1件のデータのことをレコードと呼びます。

データはレコードのまとまり、または、1つのレコード、
または、データベースに入っている情報というイメージで、
どちらかと言えば、レコードの方が少し具体的です。

レコードはINSERT文を使うことでテーブルに登録できます。

INSERT INTO [テーブル名] (
    [カラム1],
    [カラム2],
    ・・・
)
VALUES (
    [カラム1に入れる値],
    [カラム2に入れる値],
    ・・・
);

テーブルにあるカラムをすべて指定する必要はありません
登録したいカラムを指定して、VALUES句の中で順番通りに値を指定します。

カラムの順番と登録値の順番を間違えると
意図しないデータが出来上がってしまうので注意
しましょう。

前回の記事で作成したクラスの生徒を管理するテーブルを使って、
以下のようなレコードを作成してみます。

出席番号名前5教科の平均点データ登録日
01ジョニー75データを登録する時に取得

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

INSERT INTO STUDENT (
    STUDENT_NO,
    NAME,
    AVG_SCORE,
    REGIST_DATE
)
VALUES (
    '01',
    'ジョニー',
    75,
    SYSTIMESTAMP    
);

これでテーブルにレコードがひとまず登録されます。

と、ちょっと引っかかる言い方をしましたが、
この「ひとまず」というところがポイントなので覚えておいてください。

詳しくは後でお話する「トランザクション」で説明します。

SYSDATEとSYSTIMESTAMPについて

先ほどの例でデータ登録日の登録値として使った
「SYSTIMESTAMP」というワードについて説明します。

SYSTIMESTAMPシステムが持つ日時を取得するOracleの関数です。

例のようにSQL内で呼び出すことで、
TIMESTAMP型の値でミリ秒まで日時を返してくれます。

同じく日付を取得する関数としてSYSDATEがありますが、
こちらはDATE型の値で秒まで日時を返します

ということで、SQLを実行した日時を取得する際は、
SYSDATE、SYSTIMESTAMPのいずれかを使いましょう!

トランザクションとは?(TRANSACTION)

トランザクション(TRANSACTION)とは、データベースに対して
何らかの処理を実行してから終了するまでの一連の作業単位のことです。

トランザクションの中に入る処理は1つではありません、
1つ目のSQLを実行してから終了の合図があるまで
1まとまりのトランザクションとして扱います。

例えば、テーブルAとテーブルBの2つがあって、両方にデータをINSERTする時、
テーブルAにINSERTしたタイミングでトランザクションが始まりますが、

テーブルBにINSERTして終了の合図を送るまで、
データベースは処理中の状態になります。

 パソコンを操作するロボット

つまり、テーブルAに新しくINSERTしたデータは
確定せずに仮決定の状態で保持しているということになります。

これの何がメリットなのかというと、
テーブルAにINSERTしたデータが間違っていたり、
途中で停電などが発生して処理が止まったなどした場合に、

データベースをトランザクションを開始する前の状態に戻すことが出来るという点です。

テーブルAとBに親子関係がある場合に、親だけにデータが入って
処理が強制的に止まってしまうと、データの整合性を保てなくなり、
データベースの持つ情報の信頼性がなくなって使えない状態になります。

社員のデータを入れている時にトラブルが発生して、
部署は登録できてるけど、社員は中途半端に入ってたりすると
あとで目で追ってリカバリーするのが大変ですよね。

 泣いている人

このような不測の事態を避ける為に、
Oracleではトランザクションという考え方が導入
されています。

コミットとは?(COMMIT)

では、トランザクション終了の合図は
どうやってデータベースに伝えるのでしょう?

それが、コミット(COMMIT)と呼ばれる処理です。

コミットとは、トランザクションを完了させる処理で、
実行したタイミングでデータの更新処理を確定します。

コミットは以下のSQLで実行します。

COMMIT;

コミットはSQLとSQLの間であればどのタイミングでも実行できますが、

一度コミットを実行すると、
SQL実行前の状態にデータを差し戻すことは基本的にできなくなるので、

どこでトランザクションを区切れば
データの整合性が保てるのかをよく考えて実行する
ようにしましょう。

ロールバックとは?(ROLLBACK)

トラブル発生時以外でも、トランザクションの途中で、
処理に誤りがあることに気づいたりして、やっぱり前の状態に戻したい!

といったこともあると思います。

そこで登場するのがロールバック(ROLLBACK)です。

ロールバックとは、トランザクションを中断して、
データベースをトランザクション開始前の状態に戻す処理
です。

SQLをキャンセルするイメージですね。

ロールバック処理は以下のSQLで実行できます。

ROLLBACK;

当然ながら、コミットしてからロールバックすることはできません

トランザクションがアクティブなうちにロールバックする必要があるので、
コミットとの使い分けに注意しましょう。

A5:SQL Mk-2のトランザクション設定について

クライアントツールの説明の際にご紹介した「A5:SQL Mk-2」ですが、

初期状態では、オートコミットと呼ばれる状態になっています。

オートコミットとは、コミット処理を実行することなく、
更新処理などを自動で確定する機能
です。

例えば、先ほどのINSERT文であれば、
ツール上でSQLを流すだけでデータが確定されます。

確かに便利な面もありますが、ロールバックできないというデメリットもあります。

個人的には、データベースの取り扱いには細心の注意を払って欲しいので、

これからOracleの学習を進める皆さんには、以下の設定手順で、
この機能を「オフ」にしておくことをおすすめします。

ちなみに、「オフにする=トランザクションを発生させる」ということになります。

A5:SQL Mk-2のトランザクション設定手順①

画面左上にある歯車のアイコンからオプション画面を開きます。

A5:SQL Mk-2のトランザクション設定手順②

「データベース接続」タブの「・・・トランザクションを開始する」の設定が
2つあるので、両方にチェックを入れます。

Oracleクライアントツールのトランザクション設定_1

A5:SQL Mk-2のトランザクション設定手順③

オプション画面右下の「OK」ボタンを押して設定完了です。

A5:SQL Mk-2でトランザクションを確認する

それでは、実際にトランザクションが発生しているか確認してみましょう!

まずは、先ほどの例で使ったSQLを実行してみます。

すると、画面左上の2つのアイコンに赤枠がつきました!
左が「コミット」右が「ロールバック」のアイコンです。

Oracleクライアントツールのトランザクション設定_2

A5:SQL Mk-2では、SQLを流さずとも、
コミット、ロールバックがボタン操作で実行できるようになっています。
(もちろん、SQLでも実行できます。)

コミットするまではトランザクションが発生している状態なので、

これらのボタンを押さずにテーブルを見てみると、
今の時点ではテーブルにデータが入っているのがわかります。

Oracleクライアントツールのトランザクション設定_3

と、ここで!

トランザクションの途中で強制的にデータベースを閉じます!

データベース名を右クリックして「データベースを閉じる」を選択します。

Oracleクライアントツールのトランザクション設定_4

え!そんなことしたらどうなるの?

ということで、再度データベースに接続してテーブルを開くとどうでしょう、

やっぱり!先ほど入れたデータがなくなっています!

INSERT文を実行した時にコンソールに出力された
「挿入しました。」の文字が空しく表示されてますね。

Oracleクライアントツールのトランザクション設定_5

データがなくなったということは、データベースを閉じたタイミングで、
トランザクションが中断されて、最初に実行したSQLがキャンセルされ
自動的にロールバックしたということになります。

つまり、トランザクションが発生していたことになるので、
これで設定はうまくできている
ということです。

ちなみに、コミットしてからデータベースを閉じると、しっかりデータは残っています。

他にも、自分でロールバックするとどうなるのかなど、
トランザクションの性質を確認する為に、いろいろ操作して確認してみましょう!

ファイルから複数のレコードをインポートする

SQLとは別にファイルからデータを登録する方法を説明します。

今回は、A5:SQL Mk-2を使ってファイルをインポートしてみます。

ファイルのインポート手順①

まずは、インポートするファイルを用意します。
以下を参考に、テキストファイルで作成してください。

・ファイル名は任意。

・拡張子は「.csv」。

・データとデータの間を「,」(カンマ)で区切る。

・1レコード1行で書く。

・1行目はヘッダー行とする。(任意)

・文字データは「”」(ダブルクォーテーション)で囲む。

・TIMESTAMPやDATE型は、形式に従って、ミリ秒、秒まで正確に指定する。

Oracleクライアントツールでファイルをインポートする_1

カンマ区切りでデータを並べたファイルを「CSVファイル」と呼びます

データの入出力などでよく使われるので覚えておきましょう!

ファイルのインポート手順②

ツールにログインして、登録したいテーブルを開きます。

画面の上部にある「ファイルのインポート」のアイコンをクリックします。

Oracleクライアントツールでファイルをインポートする_2

ファイルのインポート手順③

ファイルの選択画面が出るので、先ほど作成した
CSVファイルを選択して、「開く」ボタンをクリックします。

Oracleクライアントツールでファイルをインポートする_3

ファイルのインポート手順④

ツールが読み取ったファイルの中身から推測して、
テーブルのカラムと、ファイル内のデータ位置の対比を表示してくれます。

問題なければ「インポート」をクリックします。

Oracleクライアントツールでファイルをインポートする_4

補足として、手順①のファイル作成で1行目をヘッダー行にした人は、
「1行目をデータとして扱わない」にチェックが入っているか確認しましょう。

ファイルの状態とオプション設定の整合性があっていないと、
1行目のデータ行がインポートされてしまったり、
1行目に書いたデータがインポートされなかったりする
ので注意しましょう。

また、テーブルの中にあるデータを総入れ替えしたい場合は、
「テーブルを空にしてから読み込む」にチェックを入れてください。

ファイルのインポート手順⑤

インポート処理が完了すると、メッセージが表示されます。

最後にテーブルの中がファイルの通りになっているか必ず確認しましょう。

Oracleクライアントツールでファイルをインポートする_5

今回はテキストで作成したCSVファイルでのインポートについて説明しましたが、
タブ区切りでデータを並べた「TSVファイル」もファイル入出力ではよく使われます

A5:SQL Mk-2ではいずれの形式も対応しているので試してみてください。

さらに、CSV、TSVともに、Excelを使って編集することもできます
使い方によっては、さらに効率的に作業ができるので、Excelでファイルを作成して、
ツールでインポートする方法にもチャレンジしてみましょう!

レコードの登録 まとめ

Oracleのレコード登録について以下説明しました。

・INSERT文の使い方

・SYSDATEとSYSTIMESTAMP

・トランザクションについて

・コミット(COMMIT)について

・ロールバック(ROLLBACK)について

・A5:SQL Mk-2のトランザクション設定

・A5:SQL Mk-2のファイルインポート

データを入れるというだけですが、その裏では
データベースの整合性を保つためにいろいろな機能が動いています。

トランザクションの仕組みを理解することは、
データベースの状態を把握する上で大変重要なポイント
になるので、

クライアントツールでいろいろと操作して、
データベースの動きを自分の目で確認しておきましょう!