連載

2007年3月6日

SQLでのデータ操作方法を覚えよう：ORACLE MASTER Silver DBA講座（9）

ORACLE MASTER資格の中級に位置付けられ、取得すればOracle技術者としてグローバルに認定される「ORACLE MASTER Silver Oracle Database 10g」。例題を利用してポイントを押さえ、確実な合格を目指そう！

[有限会社 G.F.インフィニティ（Project - ∞）] PC用表示関連情報

LINE

Hatena

　前回「Oracleデータベースのオブジェクトを管理する」で、スキーマオブジェクトの管理について学びました。今回から2回にわたって、データの管理方法を紹介します。

データの管理：SQL

ポイント

　SQL文の動作を理解しておきましょう。「ORACLE MASTER Bronze Oracle Database 10g」試験の範囲が理解できていれば、それほど難しくはないはずです。

　SQL構文の詳細については「ORACLE MASTER Bronze SQL基礎I 講座」を参照してください。基本的な構文だけでなく、トランザクション時の動作なども確認しておくとよいでしょう。

問い合わせと結合

　データ問い合わせを行うSELECT文では、いろいろなキーワードを使用することができます。必須となるキーワードは次の2つです。

SELECT列リスト　取り出す列のリストを指定
FROM句　問い合わせ対象の表を指定

　WHERE句（取り出すレコードを制限）はオプションです。WHERE句がない場合は、FROM句で指定した集合のすべてを取り出します。WHERE句で指定した抽出条件を満たすレコードが存在しなければ、抽出はされません。

　複数のテーブルからの問い合わせ結果を結合して取得する場合、次のような方式があります。


等価結合	各テーブルの結合条件が同じ値のみを戻す
自己結合	1つのテーブルを2つ以上のテーブルに見立てて結合する
内部結合	結合条件を満たす行のみを戻す
外部結合	結合条件を満たしていないデータも同時に戻す

データの操作

　レコードの挿入を行うINSERT文、データの更新を行うUPDATE文、レコードの削除を行うDELETE文を確認しましょう。

　INSERT文でVALUES句を使用した場合、1レコードのみが挿入されますが、副問い合わせを使用した複数レコードの挿入も可能です。

　副問い合わせを使用する場合、INSERT文の列リストで指定する対象テーブルの列と、副問い合わせで指定する列のデータ型および個数が一致している必要があります。一致していない場合、次のようなエラーとなります。

SQL> INSERT INTO emp20(empno,ename)
2 SELECT * FROM emp;
INSERT INTO emp20(empno,ename)
                *
行1でエラーが発生しました。:
ORA-00913: 値の個数が多すぎます。

PRIMARY KEY

UNIQUE KEY制約とNOT NULL制約が適用される

UNIQUE KEY


INSERT	ほかのレコードと同じ値が存在しないことが確認される。 NOT NULL制約が宣言されていなければ、NULL値は許可
UPDATE	ほかのレコードと同じ値が存在しないことが確認される。 NOT NULL制約が宣言されていなければ、NULL値は許可
DELETE	適用なし

FOREIGN KEY


INSERT	親テーブルに値が存在していることが確認される
UPDATE	親テーブルに値が存在していることが確認される
DELETE	親テーブルに対するDELETE文で確認される・DELETE CASCADEにてFOREIGN KEYが宣言されていない場合、子テーブルの外部キーで参照されているかどうかが確認される。参照されていればエラーとなる・DELETE CASCADEでFOREIGN KEYが宣言されている場合、親テーブルのレコード削除と同時に子テーブルの依存レコードも削除される

CHECK


INSERT	条件を満たしているかどうかが確認される
UPDATE	条件を満たしているかどうかが確認される
DELETE	適用なし

NOT NULL


INSERT	値なしの場合、エラーとなる
UPDATE	値なしに変更しようとした場合、エラーとなる
DELETE	適用なし

トランザクション

　トランザクションとは、一連の処理ブロックのことです。トランザクションを確定するCOMMIT文、トランザクションを取り消すROLLBACK文で終了します。CREATE、ALTER、DROPといったデータ定義文（DDL）を発行した場合も、直前のトランザクションは終了します。

　トランザクション中のデータ更新は、変更前の情報がUNDOデータとして残されているため、取り消しが可能になっています。また、ほかのセッションからはデータ更新は見えません。ほかのセッションからの問い合わせに対しては、UNDOデータを使用して変更前のデータを戻します。

　このように確定されたデータだけを参照させることを、「読み取り一貫性」と呼びます。

図1　読み取り一貫性

問題

問題1

scottとtomは、同じEMP表のデータを使用しています。scottがEMP表のSAL列の値を2倍に更新しました。しかし、tomがEMP表を検索しても更新前の値しか表示されません。この状態となる正しい理由を選択しなさい。

a．メモリキャッシュの破損のため
b．scottが更新をコミットしていないため
c．scottがセッションを終了していないため
d．インスタンスを再起動していないため

正解：b

解説

　読み取り一貫性に関する問題です。Oracleサーバでは、ほかのトランザクションで変更中のデータを見ることはできず、確定されたデータだけが問い合わせ結果として戻されます。確定されたデータ、つまりコミットされたデータのみを見せるために、更新中のデータはUNDOデータから取得された結果が戻されるようになっています。更新中のデータをほかのトランザクションでも参照できるようにするには、更新中のトランザクションをコミットする必要があります（正解b）。

　そのほかの選択肢にあるようなセッションの終了やインスタンスの再起動では、コミットされることは保証されません。トランザクションの取り消し（ロールバック）となる可能性が高いといえます。

問題2

次のコマンドを確認してください。

SQL> SELECT empno, ename, sal, deptno, dname
  2  FROM   emp
  3  JOIN dept USING(deptno);

この問い合わせで使用されている結合方式を選択しなさい。

a．等価結合
b．内部結合
c．自然結合
d．外部結合

正解：a、b

解説

　USING句を使用した結合は、分類として等価結合と内部結合になります（正解a、b）。つまり、2つの表の結合キー列値が等しい場合のみ結果として戻されます。

　そのほかの選択肢の不正解の理由は次のとおりです。

●選択肢c：自然結合は、2つの表の同じ名前の列が自動的に結合キーになります。これはNATURAL JOIN句で結合した場合の特徴です。USING句の場合は、指定した列のみが結合キーになります。

●選択肢d：外部結合は、結合条件を満たさないデータも問い合わせ結果として戻します。

問題3

トランザクションが進行中のセッションが異常終了した場合の状態として正しい説明を選択しなさい。

a．異常終了したセッションが開始した状態に戻る
b．Oracleサーバが起動した状態に戻る
c．最後にコミットされた状態に戻る
d．最初にコミットされた状態に戻る

正解：c

解説

　トランザクションがコミットされずに中断された場合、OracleサーバのPMONバックグラウンドプロセスにより、そのセッションに対応付けられていたサーバプロセスがクリーンアップされます。クリーンアップではトランザクションはロールバックされ、ロックが解放されます。そのため、セッションで最後にコミットした後のデータ変更は失われることになります（正解c）。

　そのほかの選択肢のような、セッションが開始した状態に戻す、最初にコミットされた状態に戻すという機能はありません。コミットした以上は、そのトランザクションが自動的に戻されることはないと考えましょう。コミットされたものを取り消すためには、フラッシュバックテーブルなどのリカバリ作業が必要になります。

宿題

　次回は、「データの管理：DataPumpとSQL*Loader」を確認します。次の宿題を解いておいてください。

問題

PRODデータベースからSALESデータベースにデータを移動するに当たり、DataPumpを使用することにしました。データのスキーマを変更するために必要なオプションを選択しなさい。

a．TOUSER
b．REMAP_SCHEMA
c．FROMUSER
d．RESCHEMA

IT資格試験の模擬問題をWebベースで学習できる＠IT自分戦略研究所の新サービス「＠IT資格攻略」では、「Silver DBA（Oracle10g）」をはじめOracle関連の資格をテーマとして取り上げています。「無料お試し版」もありますので、記事と併せてご覧ください。

「次回」へ

「ORACLE MASTER Silver DBA講座」バックナンバー