Oracleの表の作成とビューの利用：ORACLE MASTER Bronze DBA講座（9）
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze SQL基礎I 講座」で合格を目指そう！（参考記事：「Oracle 10g対応のORACLE MASTERとは？」）
本連載第7回「Oracleユーザーを管理する」、第8回「Oracleユーザーの権限とロール」で、ユーザーとセキュリティの管理について紹介しました。今回から2回にわたり、スキーマオブジェクトの管理を学びましょう。
スキーマオブジェクトの管理
理解しておきたいこと
1．確認しておきたい内容
- 表の作成と変更
- ビューの管理
2．スキーマ
Oracleデータベースにおけるオブジェクトには所有者が存在します。所有者にはそれぞれに独自の名前空間が存在していると考えられ、オブジェクトは所有者ごとに区別されます。所有者が異なれば、同じオブジェクト名があっても大丈夫というわけです。
このような名前空間の考え方を「スキーマ」と呼びます。Oracleデータベースの場合、Oracleユーザーを作成すれば、そのユーザー名と同じスキーマが自動的に作成されます（図1）。
ほかのユーザーが所有するオブジェクトには、「スキーマ名.オブジェクト名」の形式でアクセスします。
3．基本のデータベースオブジェクト
データベースで扱うオブジェクトにはさまざまなものがあります。基本のデータベースオブジェクトは次のようなものです。
| オブジェクトのタイプ | 説明 |
|---|---|
| 表 | データを格納する基本となるオブジェクト。定義として列を持ち、行を格納する |
| ビュー | 1つ以上の表データに対する問い合わせ結果を表示するための論理的な定義。SELECT文に名前を付けたもの |
| シノニム | オブジェクトの別名 |
| 索引 | 問い合わせのパフォーマンスを向上させるため、列値と行へのポインタを格納したもの |
| 順序 | 一意の番号を生成 |
どのようなときにどのオブジェクトを使用するのかを把握しておきましょう。
4．表の作成
表を作成するには、事前に表の作成権限（CREATE TABLE権限）と領域の割り当てが必要です。
まず列を定義する必要があります。列には、格納するデータのタイプを識別するためのデータ型が必要です。Oracleデータベースの基本的なデータ型には次のものがあります。
| データ型 | 説明 | データ例 |
|---|---|---|
| CHAR(サイズ) | 固定長の文字データ。サイズ不足分は空白で埋める。サイズは1〜2000 | 'ABC ' |
| VARCHAR2(サイズ) | 可変長の文字データ。サイズは1〜4000 | 'ABC' |
| LONG | 可変長の文字データ。サイズは最大2GB | 'ABC' |
| CLOB | 可変長の文字データ。サイズは最大4GB | 'ABC' |
| NUMBER(p,s) | 数値データ。精度pと位取りsを指定しない場合は浮動小数点（38けた） | 123、123.45 |
| DATE | 日付データ。4けたの年、月、日、時間、分、秒を紀元前4712年1月1日から西暦9999年12月31日まで扱える | '2006-01-15 17:15:30' |
| TIMESTAMP | 秒以下の小数部（最大9けた、デフォルト6けた）を含むDATE型の拡張データ型 | 06-01-15 00:18:22.627000 |
| RAW | バイナリデータ。サイズは1〜2000 | 画像ファイルや音声ファイルなど |
| LONG RAW | バイナリデータ。サイズは最大2GB | 画像ファイルや音声ファイルなど |
| BLOB | バイナリデータ。サイズは最大4GB | 画像ファイルや音声ファイルなど |
| BFILE | 外部ファイルとしてのバイナリデータ。サイズは最大4GB | 画像ファイルや音声ファイルなど |
| ROWID | ROWID（データベース内の行を一意に識別できるアドレス）を格納 | AAAL+ZAAEAAAAAdAAA |
Database Controlで表を作成するには、表名、スキーマ、格納表領域、列名、データ型（サイズ）の指定を行います（図2）。
問題
問題1
可変長の文字列を格納できるデータ型を選択しなさい。
a．CHAR
b．NUMBER
c．VARCHAR2
d．DATE
正解：c
解説
表の列で使用できるデータ型の特徴に関する問題です。文字列を扱うことのできるデータ型には、CHAR、VARCHAR2、LONG、CLOBがあります。このうち、可変長（格納するデータどおりの領域を使用）となるのはVARCHAR2、LONG、CLOBデータ型です。正解cが該当しますね。
選択肢aのCHARも文字列のデータ型ですが、こちらは固定長（宣言した最大サイズになるよう、不足領域は空白で埋められる）ですから注意しましょう。
選択肢bは数値データ型、選択肢dは日付データ型です。文字列ではありません。
5．制約
表の列に「制約」を定義することで、格納されるデータに特定のルールを適用することができます。Oracleデータベースの表には次の制約を定義できます。
| 制約のタイプ | 説明 |
|---|---|
| 主キー | 表内の行を識別するために使用する列、または列の組み合わせであることの宣言。一意キー制約と同じ一意性の保証とNOT NULL制約と同じNULL値不可の制限が設定される |
| 一意キー | 一意であることを保証する宣言 |
| 外部キー | 参照表の参照キー列との間に参照関係があることを宣言。参照キーに存在する値またはNULL値のみが格納されることを保証する |
| チェック制約 | 条件式に対してTRUEとなる値のみが格納されることを宣言 |
| NOT NULL | NULL値は格納されないことを宣言 |
主キー制約、一意キー制約を設定する場合、その列に索引が存在しなければ、自動的に一意索引が作成されます。
問題
問題1
「Student_id」「Student_name」「Birth_of_date」、および「Course_id」列を持つSTUDENT表があります。次の制限を必要とする場合、どの制約を使用したらよいでしょうか。2つ選択しなさい。
- 学生番号（Student_id）は一意である
- 学生番号は必ず値が存在する
- コース番号はコース表に存在している
a．Student_idに主キー制約
b．Student_idに外部キー制約
c．Course_idに外部キー制約
d．Birth_of_dateにNOT NULL制約
e．Course_idにNOT NULL制約
f．Course_idにチェック制約
g．Student_idにNOT NULL制約
正解：a、c
解説
問題の要件をそれぞれ確認していくことで、必要な制約を見つけることができます。
- 学生番号（Student_id）は一意である
一意となる制約は、主キー制約と一意キー制約です。1つの表に1つだけ作成し、必ず値が存在するのであれば、主キー制約を宣言します。一意性だけを保証するのであれば一意キー制約を宣言します。選択肢aが該当します。
- 学生番号は必ず値が存在する
必ず値が存在することを保証するには、NOT NULL制約を使用します。選択肢gが該当します。
- コース番号はコース表に存在している
別の表に存在している値であることを保証するには、外部キー制約を使用します。選択肢cが該当します。
上記の要件すべてを満たすには、主キー制約と外部キー制約を使用します。値が存在し、かつ一意であることを保証するのは主キー制約です（正解a）。値が別の表に存在していることを保証するのは外部キー制約です（正解c）。
選択肢gのNOT NULL制約は、値の存在は保証しますが一意性は保証しません。問題で正解の選択肢数を指定されている場合は、最も要件を満たしている選択肢を指定数以内で選ぶようにしましょう。
そのほかの選択肢は、今回の要件には関係しません。
●選択肢b：外部キー制約では、値の存在や一意性の保証はできません。
●選択肢d：今回の要件にBirth_of_date列（誕生日）は含まれていませんので、値の存在を保証するNOT NULL制約を宣言する必要はありません。
●選択肢e：今回の要件では、コース番号はほかの表を参照するとしかありませんので、外部キー列を必須にする必要はありません。ただし一般的な表では、外部キー列に必ず値を格納し、結合できるようにする場合もあります。もしそのような要件であれば、NOT NULL制約を使用する必要があります。
●選択肢f：ほかの表を参照することをチェック制約で宣言することはできません。チェック制約では副問い合わせが使用できないからです。制約をチェックするレコード内のほかの列を参照することはできますので、「歩合給は給与の10％以内」など同じレコード内でチェックできるルールのみに使用しましょう。
問題2
列に一意な値のみが格納されるように制限できる制約を2つ選択しなさい。
a．主キー制約
b．外部キー制約
c．NOT NULL制約
d．一意キー制約
e．チェック制約
正解：a、d
解説
前回、宿題とした問題です。一意性を保証する制約は主キー制約（正解a）と一意キー制約（正解d）の2つです。このうち主キー制約は、1つの表に1つしか宣言できません。行を識別するために使用するというのが主キー制約の位置付けです。そのため、必ず値が存在し、一意性が保証されている必要があります。
一意キー制約は、一意であることのみを保証します。OracleサーバのSQL実行においては、一意性が保証されている列に等価記号（=）で検索が行われるとき、最も絞り込みができると判断されます。一意性を保証できるのであれば保証しておいた方がいいでしょう。
不正解となる選択肢についても、それぞれルールを確認しておきましょう。
6．ビューの作成
ビューは、SELECT文に名前を付けて保存したオブジェクトです。ビューを使用することで、表示する列を制限したり、複雑なSELECT文を隠したりすることができます。
ビューの作成SELECT文で列別名を使用しない場合、元の列名がビューにおける列名になります。そのため、列の計算や関数を使用した列が含まれる場合は、列別名を使用する必要があります。
問題
問題1
次の資料を確認してください。
EMP_DEPTビューの特性として正しいものを3つ選択しなさい。
a．列の別名を定義する必要がある
b．HRスキーマに割り当てた領域を使用しない
c．SYSスキーマに割り当てた領域を使用する
d．EMP_DEPTビューにアクセスすると、employees表とdepartments表への問い合わせ結果が戻される
e．EMP_DEPTビューにアクセスすると、employees表とdepartments表のすべての行が戻される
f．EMP_DEPTビューにアクセスできるユーザーは、employees表とdepartments表にもアクセスできる
g．ビューを使用することで、特定列にのみアクセスさせることが可能になる
正解：b、d、g
解説
ビューの特徴として、SELECT文に名前を付けて保存したオブジェクトであること、アクセスに必要な権限はビューに対する権限でよいことなどがあります。
今回問題で使用している資料に着目すると、次のことが分かります。
- HRスキーマに作成される
- 列に計算や関数は使用されていない
- 結合を行うビューである
これらの要素をビューの特徴と合わせて考えることで、正解とその理由が分かります。
○正解b：ビューはデータを保存するのではなく、SELECT文の定義のみをデータディクショナリに保存するものです。そのため、表や索引のようにスキーマとしての領域を使用することはありません。
○正解d：ビューにアクセスを行うと、ビューとして保存していたSELECT文が実行されます。問題のビューはemployees表とdepartments表を結合するものですから、その結果が表示されます。
○正解g：ビューのSELECT文で定義したSELECT列リストに記述した列にしかアクセスできませんので、特定列のみアクセスさせる目的でビューを使用することができます。
不正解の理由も考えてみましょう。
●選択肢a：列の別名が必要になるのは、ビューのSELECT文で定義したSELECT列リストに計算や関数が使用されている場合です。ビューにおける列名は、表における列名同様、()や算術演算子記号を使用することはできません。
●選択肢c：あいまいな選択肢です。ビューの定義はデータディクショナリに保存され、データディクショナリはSYSスキーマですから、SYSスキーマの領域を使用しているともいえます。しかし、表示される結果データはビューの基礎となる表の領域にのみあることから、領域を消費していることにはならないとしていると思われます。
●選択肢e：ビューにアクセスを行うと、ビューとして定義したSELECT文が実行されるのですから、すべての行が戻されるというのはおかしいです。今回の問題中のSELECT文で使用されているWHERE条件は内部結合条件だけです。この場合、外部キー列にNULL値があるレコードなどは表示されないので、すべての行が戻ることは保証されないといえます。
●選択肢f：ビューの利点の1つにアクセス制限があります。ビューの基礎となる表へのアクセス権限は、ビューの所有者が持っていればいいということです。ビューを利用するユーザーは、ビューへのアクセス権限は必要ですが、ビューの基礎表へのアクセス権限は必要ないということです。
この問題の選択肢cのように、出題される問題の選択肢の一部には表現があいまいなものがあります。そのような場合は、表現の明確な選択肢から正解を選ぶようにしましょう。
宿題
次回は、今回に引き続きスキーマオブジェクトの管理について確認します。次の宿題を解いておいてください。
データベース内に格納されるプログラム構造体を4つ選択しなさい。
a．トリガー
b．インデックス
c．パッケージ
d．プロシージャ
e．ファンクション
f．シノニム
IT資格試験の模擬問題をWebベースで学習できる＠IT自分戦略研究所の新サービス「＠IT資格攻略」では、Oracle関連の資格をテーマとして取り上げています。Bronze SQL 基礎 I、Bronze DBA 10gも近日中に追加予定です。「無料お試し版」もありますので、記事と併せてご覧ください。
Copyright © ITmedia, Inc. All Rights Reserved.