１．データベース設計
TOPへ1.1 ビュー1.1.1 ビューの作成1.2 テーブル1.2.1 データ型1.3 インデックスの作成1.3.1 インデックスの作成指針1.4 シーケンステーブル1.4.1 シーケンステーブルの作成1.5 シノニムの作成1.5.1 プライベートシノニムとパブリックシノニム1.6 オブジェクトの管理
| モデル | → | 概念設計 | → | 概念ＥＲ図 |
| 概念ＥＲ図
処理要件 Oracle要件 |
→ | 論理設計 | → | 論理ＥＲ図（表・索引）
ビュー 制約条件 |
| 論理ＥＲ図
物理要件 性能要件 |
→ | 物理設計 | → | ＤＤＬ定義
初期物理配置 init.oraなどの決定 |
ここでは、データベース設計手法の詳細には立ち入らず、論理設計により得られた、ビュー・テーブル・制約などの記述方法、およびシーケンスやインデックスの作成についてのみ述べる。
（基本形）create or replace view ビュー名 as
select カラムのリスト
from テーブル（のリスト）
where 条件;
（例）SQL> create view V_ＰＣ管理マスター_202 as
2 select ＩＰアドレス アドレス,利用者名 氏名
3 from ＰＣ管理マスター
4 where ＩＰアドレス like '202%';
＊参考＊
ビューの内容によっては、更新・削除・挿入の処理が行えるが、以下のようなビューではそれらの操作は行えない。
SQL> desc ビュー名と行う。
作成したビューのselect文の内容を確認するときは、USER_VIEWSを見る。
SQL> select view_name,text_length,text
2 from user_views
3 where view_name='ビュー名'
＊参考＊
where句で指定するビュー名は大文字になる。
textはlong型であり、表示上のDefault値は80になっている。そのため、通常表示しきれない分が切り落とされてしまうので、一度selectした後、
SQL> set long text_lengthの値を行い、バッファの再実行'/'で検索し直す。
同じビュー名でビューを作り直すときは、
SQL> create or replace view ビュー名 as select 〜と記述する。
（基本形）TOPへcreate table テーブル名
( 項目名 データ型 [DEFAULT 式] [NOT NULL]
[, ・・・]
[, constraint テーブル制約]
[, ・・・]
)
tablespace テーブルスペース名
storage ( 領域定義 );
TOPへ
CHAR(n) 最大2000Byte（Oracle7では255Byte）までの固定長の文字列 組織コード、原価部門コード等の固定長の文字列に適している
FETCH時に、後続空白の区別をつけないVARCHAR2(n) 最大4000Byte（Oracle7では2000Byte）までの可変長の文字列 組織名称、資材共通名称等の可変長で更新頻度の少ない文字列に適している
FETCH時に、後続空白によりデータが区別されるNUMBER(p,s) 最大38桁までの数値データ。精度pと位取りsで指定 整数や浮動小数等を含むあらゆる数字データを扱う DATE BC 4712/1/1 〜 AD 4712/12/31の日時データ 日付や時間のデータを扱う （7Byteの大きさを持つ） LONG
LONG RAW最大2GByteまでの文字列
バイナリデータ比較的長いデータ長を持つ文字列やバイナリ・データを扱う LOB型
CLOB
BLOB
BFILE
最大4GByteまでの可変長文字列
最大4GByteまでのバイナリ・データ
DB内にはファイルへのパス情報が格納されるOracle8から導入された
比較的長いデータ長を持つ文字列、バイナリ・データを扱うデータ型
DBMS_?LOBで扱う？（要調査）＊注意＊
LONG型およびLOB型の項目は、where句の条件に指定することはできない。
LONG型はOracle8以降ではLOB型への移行を推奨されている。
デフォルト値は、insert文などで、項目の値の指定を省略したときに取る値を指定するものである。
（ただし、defaultに指定できる式には若干の制限もある）
NOTNULL制約をつけると、その列の値としてNULL値を含むことができなくすることができる。
（列制約としてはNOTNULL制約の記述に限定する。それ以外の制約についてはテーブル制約で記述することとする）
（例）create table ＰＣ管理マスター (
ＩＰアドレス char(15) not null,
利用者名 varchar2(20),
ＰＣ名 varchar2(20),
利用開始日 date default sysdate not null
);
2つ以上の列を参照する制約でなければ、これらの制約は列制約として記述することも可能であるが、テーブル制約で記述するように統一した方が判りやすい。
（例）create table ＰＣ管理マスター (
ＩＰアドレス char(15) not null,
利用者名 varchar2(20),
ＰＣ名 varchar2(20),
利用開始日 date default sysdate not null,
constraint PK_PCMASTER primary key (ＩＰアドレス)
using index tablespace USER_IDX
storage ( initial 10K next 10K pctincrease 0 )
);create table ＷＥＢ利用履歴 (
ＩＰアドレス char(15),
URL varchar2(200),
アクセス日時 date,
constraint FK_WEB_PC foreign key (ＩＰアドレス)
refrences ＰＣ管理マスター(ＩＰアドレス)
);
制約の種類には次のようなものがある。
| PRIMARY KEY | 列の組合せを表の主キーとして指定 | USING INDEX句を指定すること |
| UNIQUE | 列の組合せを一意キーとして指定 | USING INDEX句を指定すること |
| FOREIGN KEY | 列の組合せを他のテーブルとの参照制約の外部キーとして指定 | FOREGIN KEY (columns) REFRENCES table (columns)
ON DELETE CASCADE ← CASCADE指定は必要があれば |
| CHECK | 表の各行が満足しなければならない条件を指定 | 条件としてはwhere句で指定するような式を記述する |
制約に絡むキーワードとして以下も参照
| USING INDEX | 主キーおよび一意キーの索引に対するパラメータの指定 | インデックス名は制約名と同じになる
INDEXについては1.3を参照 |
| ON DELETE CASCADE | 参照表の行が削除されたときあわせて削除される | 通常指定せずにアプリケーション側で対応するほうがよい |
（例）
＊参考＊create table ＰＣ管理マスター (
ＩＰアドレス char(15) not null,
利用者名 varchar2(20),
ＰＣ名 varchar2(20),
利用開始日 date default sysdate not null,
constraint PK_PCMASTER primary key (ＩＰアドレス)
using index tablespace USER_IDX
storage ( initial 10K next 10K pctincrease 0 )
)
tablespace users
storage ( initial 1M next 100K pctincrease 0 )
;
storage句で指定するパラメータについては、以下のものがある。
| INITIAL | 第１エクステントのサイズを指定する | KB、MB単位での指定も可能である。概算で容量を算定する必要がある |
| NEXT | 第２エクステントのサイズを指定する | INITIALの10%程度で作成するぐらいが良いと思われる |
| PCTINCREASE | 第３エクステント以降のサイズの増分をパーセント指定する | nextが100K、pctincreaseが50の場合、第３エクステントのサイズは150KBになる |
| MAXEXTENTS | 割り当てることのできるエクステントの総数を指定する | 指定しない（デフォルト値を使用）か、UNLIMITEDを指定する |
概算容量の算出については、ORACLEマニュアル「管理者ガイド」の付録Ａに「スキーマ・オブジェクトの領域の見積」として正確なやり方が載っている。
しかし、テーブルにnumberやvarchar2などの可変長のデータ型が含まれていて、それがどの程度の桁数の分布になるか想定できなかったり、行数自体の想定がざっくりとしたものであるので、以下の算出程度で作成しても良いのではないかと思う。
INITIAL
= 各列のバイト数の総和 × 推定行数
NEXT
= INITIAL × 0.1
PCTINCREASE = 0 （値に 0 を用いて、全て同じバイト数でエクステントを作成する方が、ブロックの再利用が行いやすくなるため）
（基本形）
| 項目追加 | alter table add (項目名 型 ［デフォルト値］ ［ not null］); |
| 制約の追加 | alter table add (constraint 制約名 制約); |
| 項目の型変更など | alter table modify (項目名 型 ［デフォルト値］ ［ not null］); |
| 制約の削除 | alter table drop constraint 制約名; |
＊参考＊
項目の追加を行うと位置はかならず最後になってしまい、任意の位置に追加することはできない。また項目の削除も行うことはできない。
これらの作業を行いたい場合は以下の手順で行う。
（基本形）
create index インデックス名
on テーブル名 ( 項目名 [ASC | DESC]
[, ・・・] )
tablespace テーブルスペース名
storage ( 領域定義 ) ;
インデックスは、指定した列の組合せにたいしての順序をデータとしてDBに格納しておくものであるため、表にデータを挿入すると同時にインデックスもデータとして作成される（ただし元の表から行が削除されても領域は開放しない）。
表に対して大量のデータ挿入がある場合は、インデックスの挿入処理も発生するため、検索処理とのパフォーマンスのトレードオフについても考慮をしておく必要がある。
テーブル作成時においても、主キー制約およびユニークキー制約を宣言すると、指定をしていなくとも暗黙的にインデックスが作成される。そのときのインデックスのSTORAGE句とTABLESPACE句にはデフォルト値が使用されてしまうため、USING
INDEX句を指定して明示的にパラメータを指定して作成する。
（基本形）create sequence シーケンス名
increment by 増分
maxvalue 最大値
minvelue 最小値
[start with 初期値]
[cycle];
| increment by | 増分の間隔を指定。0以外の28桁以内の整数。
MAXVALUEとMINVALUEの間の値でなければならない。 |
| minvalue | 順序の最小値を指定。 |
| maxvalue | 順序の最大値を指定。minvalue以上の値。 |
| start with | 最初に生成される順序の番号を指定。省略可。
省略時はincremenntの方向によってminvalueまたはmaxvalueが使用される。 |
| cycle | 指定があった時は、順序が最大値に達したときに、最小値から再度生成される。
指定がない場合は、最大値に達した以降は生成されない。 |
＊注意＊
次に使える値 select シーケンス名.NEXTVAL from dual; 現在の値 select シーケンス名.CURRVAL from dual;
（基本形）create [public] synonym シノニム名 for オブジェクト名;
| table | TABS、TABなど |
| tablespace | USER_TABLESPACES |
| view | USER_VIEWS |
| index | USER_INDEXES |
| constraint | USER_CONSTRAINTS |
| sequence | USER_SEQUENCES |
| synonym | USER_SYNONYMS |
| public synonym | ALL_SYNONYMS |
| 全オブジェクト | USER_OBJECTS |
（基本形）rename 元オブジェクト名 新オブジェクト名;
（基本形）drop オブジェクト種別 オブジェクト名;
（基本形）オブジェクト権限には以下に示すものがある。grant オブジェクト権限 on オブジェクト名 to 授与ユーザー名;
| SELECT | 問合せ | 表・ビュー・順序 |
| INSERT | データ挿入 | 表・ビュー |
| UPDATE | データ変更 | 表・ビュー |
| DELETE | データ削除 | 表・ビュー |
| INDEX | 索引の付加 | 表 |
| ALTER | 表定義の変更 | 表・順序 |
| REFRENCES | 参照制約の付加 | 表 |
| EXECUTE | 実行 | プロシージャ・ファンクション・パッケージ（未出） |
＊注意＊
権限を与えるときは、その影響に注意すること。信用できないユーザーに対して、ALTER権限を与えるなどしてはならない。