DDLステートメントの使用

テーブルを作成および管理するDDLステートメントの使用

スキーマとはスキーマオブジェクトと呼ばれる複数のデータベースオブジェクトの集合体であり、それらのオブジェクトには所有者のスキーマによって直接アクセスできます。

• Table – データを格納する

• View – 1つまたは複数のテーブルから目的の形式でデータを表示する

• Sequence – 数値を生成する

• Index – 数値の生成に使用する

• Index – 1つまたは複数のテーブルから目的の形式でデータを表示する。 テーブルに対するクエリのパフォーマンスを向上させるため

• Synonym – オブジェクトの代替名

データベース作成の最初のステップの1つは、組織のデータを格納するテーブルを作成することです。データベース設計では、注文入力、在庫管理、および売掛金などのさまざまな組織システムのシステム ユーザー要件を特定します。 2616>

Creating the table

DBA がデータベースにテーブルを作成するには、テーブル名、列名、列データ型、および列サイズなどの特定の情報を手元に置いておく必要があります。 これらの情報はすべて、DDL コマンドを使用して後で変更することができます。

テーブル命名規則 –

• テーブルに選択する名前は、以下の標準規則に従わなければなりません。

• 名前は、A-Z または a- で始まる必要があります。z

• 数字とアンダースコアを含むことができる

• 小文字または大文字

• 30文字まで

可能

• スキーマ内の他の既存オブジェクトと同じ名前は使用できません

• SQL予約語であってはなりません

上記のガイドラインに従います。 EMP85」は有効なテーブル名とすることができます。同様に、UPDATEはSQL予約キーワードであるため、テーブル名として選択することはできません。

ここに基本的なCREATE TABLE文の構文があります。 SYSDATEとUSER以外の他のテーブル列や疑似列（CURRVAL、NEXTVAL、LEVEL、ROWNUM）、または完全に指定されていない日付定数への参照は含めることができません。

制約とは、列レベルまたはテーブルレベル（この章の後半で説明します）で任意に定義される規則です。これらの規則は、テーブルに対するデータ操作（挿入、更新）中にチェックされ、違反した場合にエラーを発生させて操作を中止します。 列の仕様、データ型、精度に注意してください。

CREATE TABLE SCOTT.EMP_TEST(EMPID NUMBER,ENAME VARCHAR2(100),DEPARTMENT_ID NUMBER,SALARY NUMBER,JOB_ID VARCHAR2(3),HIREDATE DATE,COMM NUMBER);

ユーザ名またはスキーマをテーブル名の前に付けることにより、他のユーザのスキーマからテーブルを参照することができます。例えば、ユーザGUESTがSCOTTが所有するEMP_TESTテーブルから従業員名と給与を照会したいとします。 カラムはテーブル作成時にデフォルト値を保持することができ、カラムに入るNULL値を制限するのに役立ちます。 デフォルト値は、カラムと互換性のあるデータ型を返す必要があるリテラル、式、SQL 関数のいずれかから推測することができます。 以下のCREATE TABLE文では、LOCATION_ID列のデフォルト値が100であることに注意してください。

CREATE TABLE SCOTT.DEPARTMENT(DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – サブクエリを使用してテーブルを作成

テーブルは、サブクエリオプションを使用してデータベース内の既存のテーブルから作成できます。 明示的に課されたNOT NULL制約を含む列のデータ型定義は、新しいテーブルにコピーされます。

次のCTASスクリプトは、新しいテーブルEMP_BACKUPを作成します。

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

データ型

データ型は、テーブル内の列の基本動作を指定するために使用されます。

NUMBER (p, s), ここでpは38桁までの精度、sはスケール（小数点の右側の桁数）です。

DATE データ型

各DATEデータ型について、世紀、年、月、日、時、分、秒がデータベースに格納されます。 すべてのデータベースシステムは、初期化パラメータNLS_DATE_FORMATで定義されるデフォルトの日付書式を持っています。 このパラメータは通常 DD-MON-YY に設定されます。時刻を指定しない場合、デフォルト時刻は 12:00:00 a.m.

Character data type

Oracle は CHAR、VARCHAR、VARCHAR2、LONG の 3 つの定義済み文字データ型がサポートされます。例えば、米国の社会保障番号 (SSN) はすべての国民に割り当てられており、常に 9 文字のサイズです (SSN は厳密に数字で構成されていますが、数字は文字として扱われます)。 VARCHAR2 データ型は、可変長の英数字データを格納するために使用します。例えば、顧客名や住所は、格納される文字数がかなり異なります。

LOB データ型

Oracle は CLOB (character large object) および BLOB (binary large object) を含むいくつかの異なる LOB データ型を提供します。これらのデータ型の列はテキスト、イメージ、ビデオおよび空間データを含む非構造化データを格納することが可能です。BLOBデータ型は、データが単に「ビット」値のストリームである画像やビデオデータに関連するものなど、非構造化バイナリラージオブジェクトを格納するために使用されます。BLOBデータ型は、最大8テラバイトのバイナリデータを格納することができる。NCLOBデータ型は、最大8TBから128TBのマルチバイト国家文字セットで文字ラージオブジェクトを格納することができます。 サポートされる最大ファイルサイズは 8TB から 128TB です。

Constraints

Constraints は、データの整合性を確保するために Oracle テーブルで定義される規則のセットです。これらの規則は各列または列のセットに対して適用され、テーブルがデータ操作に参加すると、これらの規則は検証され違反時に例外が生成されます。 利用可能な制約タイプは、NOT NULL、プライマリキー、ユニーク、チェック、および外部キーです。

以下の構文は、列レベルで制約を課すために使用することができます。

column constraint_type

NOT NULL以外のすべての制約は、テーブル レベルで定義することもできます。 複合制約はテーブル・レベルでのみ指定できます。

NOT NULL制約

NOT NULL制約は、データ行がNOT NULLとして指定された列の値を持っていなければならないことを意味します。

構文。

構文:

列レベル。

COLUMN 

テーブル・レベル: CONSTRAINT UNIQUE (列名)

注意: Oracleは、列値の重複を防ぐために内部的にユニークなインデックスを作成します。

CREATE TABLE TEST( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

主キー

各テーブルには通常、そのテーブルに格納されるデータ行を一意に識別する列または列のセットが含まれていなければなりません。

注意点 –

• テーブルは1つの主キーしか持つことができません。

• 複数の列は複合主キーの下でクラブ化することができます。インデックスはPL/SQLで後述します。

構文：

列レベル：

COLUMN 

テーブルレベル：

CONSTRAINT PRIMARY KEY 

次の例は、列レベルでPRIMARY KEY制約を使用する方法について示しています。

CREATE TABLE TEST( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... ); 

次の例は、テーブルレベルでPRIMARY KEY制約を使用して複合主キーを定義する方法を示しています。

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) ); 

外部キー

特定の列に基づいて2つのテーブルが親子関係を共有しているとき、子テーブルの結合列は外部キーとして知られています。子テーブルの外部キーカラムの値は、NULLであるか、親テーブルの既存の値でなければなりません。参照されるテーブルの主キーカラムのみが参照整合性を実施する資格があることに注意してください。

子テーブルのカラムに外部キーが定義されている場合、Oracle は親行が子行を含んでいる場合、その行を削除することを許可しません。同様に、ON DELETE SET NULL は、親テーブルの行が削除されたとき、外部キー値が NULL に設定されることを示します。

構文：

列レベル：

COLUMN 

テーブルレベル：

CONSTRAINT 

次の例は、列レベルで FOREIGN KEY 制約を使用する方法を示しています。

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ...);

ON DELETE CASCADE句の使用

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ...);

チェック制約

時には、特定の列に格納されているデータ値が、値の許容範囲内に収まらなければなりません。チェック制約では、テーブルに格納されている各行に対して、指定したチェック条件が真または不明であることが必要です。 チェック制約は、列に条件付きルールを課すことができ、列にデータを挿入する前に検証する必要があります。条件は、サブクエリまたは疑似列CURRVAL NEXTVAL、LEVEL、ROWNUM、またはSYSDATEを含んでいてはなりません。

Oracle は1つの列に複数のCHECK制約を持つことを許可しています。 実際、1 つの列に対して定義できる CHECK 制約の数には実用的な制限はありません。

Column level:

COLUMN CONSTRAINT 

Table level:

CONSTRAINT CHECK (condition)

次の例は、列レベルでCHECK制約を使用する方法を示しています。

CREATE TABLE TEST( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in ('A','B','C')), ...);

次の例は、テーブルレベルでCHECK制約を使用する方法を示しています。

CREATE TABLE TEST( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate),);

ALTER TABLE文

DBAは、データベースでテーブルが作成された後に、テーブル構造や列の定義を変更することができます。ALTER TABLE文は、テーブルの列を追加、削除、名前変更、および変更するために使用されます。

以下のALTER TABLE文は、テーブルEMPをEMP_NEWに名前変更します。

ALTER TABLE EMP RENAME TO EMP_NEW;

The below ALTER TABLE statement adds a new column TESTCOL to the EMP_NEW table

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

The below ALTER TABLE statement renames the column TESTCOL to TESTNEW.ALTER TABLEステートメントは、カラムの名前をTESTCOLからTESTNEWに変更しました。

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

The following ALTER TABLE statement drop the column TESTNEW from EMP_NEW table

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

The following ALTER TABLE statement adds primary key on the EMPLOYEE_ID column.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

下のALTER TABLEステートメントは主キーを削除します。

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

下のALTER TABLEステートメントはテーブルモードを読み取り専用に切り替えます。

ALTER TABLE EMP_NEW READ ONLY;

読み取り専用テーブル

読み取り専用テーブルはOracle 11gでの強化機能として登場し、読み取り専用のテーブルとして使用することを可能にしました。 以前の Oracle バージョンでは、テーブルは他のユーザーに SELECT 権限を与えることで読み取り専用にされましたが、所有者には読み取り書き込み権限が残っていました。

構文：

ALTER TALE READ ONLY

ALTER TALE READ WRITE

図解：

SQL>CREATE TABLE ORATEST (id NUMBER)SQL>INSERT INTO ORATEST VALUES (1);SQL>ALTER TABLE ORATEST READ ONLY;SQL> INSERT INTO ORATEST VALUES (2);INSERT INTO ORATEST VALUES (2) *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> UPDATE ORATEST SET id = 2;UPDATE ORATEST SET id = 2 *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> DELETE FROM ORATEST;DELETE FROM ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> TRUNCATE TABLE ORATEST;TRUNCATE TABLE ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));ALTER TABLE ORATEST ADD (description VARCHAR2 (50))*ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST READ WRITE;Table altered.SQL> DELETE FROM ORATEST;1 row deleted.

DROP TABLE文

DROP TABLE文は、データベースからテーブルを削除するために使用されます。 ドロップされたテーブルとそのデータは、もはや選択することができません。ドロップされたテーブルは、リサイクル・ビン内にあれば、FLASHBACK ユーティリティを使用して回復することができます。

構文：

DROP TABLE 

以下のステートメントはテーブルを削除し、ごみ箱に入れます。

DROP TABLE emp_new;

以下のステートメントはテーブルを削除し、ごみ箱からそれを洗い流します。

DROP TABLE emp_new PURGE;