DDLステートメントの使用

DDLステートメントを使用したテーブルの作成と管理

スキーマは、スキーマオブジェクトと呼ばれる複数のデータベースオブジェクトのコレクションです。これらのオブジェクトは、所有者のスキーマから直接アクセスできます。以下の表に、スキーマオブジェクトを示します。

テーブル-データを保存する
表示-1つ以上のテーブルから目的の形式でデータを投影します
シーケンス-数値を生成します
インデックス-テーブルに対するクエリのパフォーマンスを向上させる
同義語-オブジェクトの別名

データベースを作成する最初のステップの1つは、組織のデータを格納するテーブルを作成することです。データベースの設計には、注文入力、在庫管理、売掛金など、さまざまな組織システムのシステムユーザー要件を特定することが含まれます。データベースのサイズや複雑さに関係なく、各データベースはテーブルで構成されています。

テーブルの作成

データベースにテーブルを作成するには、DBAが特定の情報（テーブル名、列名、列データ型、列サイズ）を持っている必要があります。この情報はすべて、後でDDLコマンドを使用して変更できます。

テーブルの命名規則-

テーブルに選択する名前は、次の標準ルールに従う必要があります。
名前は文字AZまたはazで始まる必要があります
数字とアンダースコアを含めることができます
小文字の大文字にすることができます
長さは最大30文字です
スキーマ内の別の既存のオブジェクトと同じ名前を使用することはできません
SQL予約語であってはなりません

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

CREATETABLEステートメント

CREATE TABLEは、データベースにテーブルを作成するために使用されるDDLステートメントです。テーブルはCREATE TABLEスクリプトが実行されるとすぐに作成され、データを保持する準備が整います。ユーザーが作成するには、CREATETABLEシステム権限が必要です。独自のスキーマ内のテーブル。ただし、任意のユーザーのスキーマにテーブルを作成するには、ユーザーはCREATE ANYTABLEスキーマを持っている必要があります。

基本的なCREATETABLEステートメントの構文は次のとおりです。ストレージ仕様またはセグメント値を明示的に提供するための追加の句が多数ある場合があります。

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

上記の構文で、DEFAULTは、列が無視された場合にINSERTステートメント中に使用できるデフォルト値を指定します。SYSDATEとUSER、または完全に指定されていない日付定数を除いて、他の表列または疑似列（CURRVAL、NEXTVAL、LEVEL、およびROWNUM）への参照を含めることはできません。

制約は、列レベルまたはテーブルレベル（この章の後半で説明）でオプションで定義されるルールです。これらのルールは、テーブルでのデータアクション（挿入、更新）中にチェックされ、違反時にアクションを中止するエラーを発生させます。

たとえば、以下のCREATE TABLEステートメントは、テーブルEMP_TESTを作成します。列の仕様、データ型、および精度に注意してください。

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テーブルから従業員名と給与を照会したいと考えています。彼は以下のクエリを発行できます-

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

列は、テーブルの作成時にデフォルト値を保持できます。これは、列に入るNULL値を制限するのに役立ちます。デフォルト値は、互換性のあるデータ型を列に返す必要があるリテラル、式、またはSQL関数のいずれかから推測できます。以下のCREATETABLEステートメントでは、LOCATION_ID列のデフォルト値が100であることに注意してください。

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

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

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

以下のCTASスクリプトは、新しいテーブルEMP_BACKUPを作成します。部門20の従業員データが新しいテーブルにコピーされます。

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

データ型

データ型は、テーブル内の列の基本的な動作を指定するために使用されます。大まかに言えば、列の動作は、数値、文字、または日付ファミリのいずれかに属することができます。これらのファミリに属するサブタイプは他にも複数あります。

数値データ型

NUMBERデータ型には、整数、固定小数点、浮動小数点の両方の数値が含まれます。初期のバージョンのOracleは、これらのさまざまな種類の数値ごとに異なるデータ型を定義していましたが、現在はNUMBERデータ型がこれらすべての目的に使用されます。列には、数学計算で使用できる数値データを格納する必要があります。場合によっては、NUMBERデータ型を使用して、DBMSによって連続番号として生成される識別番号を格納します。

NUMBER（p、s）、ここでpは38桁までの精度、sはスケール（小数点の右側の桁数）です。スケールの範囲は-84〜127です。

NUMBER（p）は、スケールがゼロで精度がpの固定小数点数です。

FLOAT [（p）]、ここで、pは1から126の範囲のバイナリ精度です。pが指定されていない場合、デフォルト値はバイナリ126です。

日付データ型

DATEデータ型ごとに、Century、Year、Month、Day、Hour、Minute、Secondがデータベースに保存されます。すべてのデータベースシステムには、初期化パラメータNLS_DATE_FORMATによって定義されるデフォルトの日付形式があります。このパラメーターは通常、DD-MON-YYに設定されます。時刻を指定しない場合、デフォルトの時刻は12:00:00 amです。

文字データ型

Oracleは、CHAR、VARCHAR、VARCHAR2、LONGを含む3つの定義済み文字データタイプをサポートしています。VARCHARとVARCHAR2は実際には同義であり、VARCHARではなくVARCHAR2を使用することをお勧めします。列に固定長の文字値を格納する場合は、CHARデータタイプを使用してください。たとえば、米国の社会保障番号（SSN）はすべての市民に割り当てられ、サイズは常に9文字です（SSNは厳密に数字で構成されていますが、数字は文字として扱われます）。CHARとして指定されます。（9）。VARCHAR2データ型を使用して、可変長の英数字データを格納します。たとえば、顧客の名前または住所は、格納される文字数によって大幅に異なります。VARCHAR2列の最大サイズは4,000文字です。

LOBデータ型

Oracleは、CLOB（文字ラージオブジェクト）やBLOB（バイナリラージオブジェクト）など、いくつかの異なるLOBデータ型を提供しています。これらのデータ型の列には、テキスト、画像、ビデオ、空間データなどの非構造化データを格納できます。CLOBデータ型には、最大8テラバイトを格納できます。 CHARデータベースの文字セットを使用した文字データの分析。BLOBデータ型は、データが単に「ビット」値のストリームである画像やビデオデータに関連付けられたオブジェクトなどの非構造化バイナリラージオブジェクトを格納するために使用されます。BLOBデータ型は最大8テラバイトのバイナリデータ。NCLOBデータ型は、最大8TB〜128TBのマルチバイト国別文字セットで文字ラージオブジェクトを格納できます。BFILEデータ型の値は、サーバーのファイルシステム上のファイルロケーターまたはファイルへのポインターとして機能します。サポートされる最大ファイルサイズは8TBから128TBです。

制約

制約は、データの整合性を確保するためにOracleテーブルで定義されたルールのセットです。これらのルールは、各列または列のセットに適用されます。テーブルがデータアクションに参加するたびに、これらのルールが検証され、違反時に例外が発生します。使用可能な制約タイプは、NOT NULL、主キー、一意、チェック、および外部キーです。

以下の構文を使用して、列レベルで制約を課すことができます。

構文：

column [data type] [CONSTRAINT constraint_name] constraint_type

NOT NULLを除くすべての制約は、テーブルレベルでも定義できます。複合制約は、テーブルレベルでのみ指定できます。

NOTNULL制約

NOT NULL制約は、データ行にNOTNULLとして指定された列の値が必要であることを意味します。列がNOTNULLとして指定されている場合、Oracle RDBMSは、この制約に違反する行を従業員テーブルに格納することを許可しません。列レベルでのみ定義でき、テーブルレベルでは定義できません。

構文：

COLUMN [data type] [NOT NULL]

UNIQUE制約

主キー列ではない列値に一意性を適用する必要がある場合があります。UNIQUE制約を使用してこのルールを適用すると、Oracleは一意性制約に違反する行を拒否します。一意性制約により、列値が区別されます。、重複なし。

構文：

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [制約名] UNIQUE（列名）

注意：Oracleは、列値の重複を防ぐために、内部で一意の索引を作成します。索引については、PL / SQLで後述します。

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

複合一意キーの場合、以下のようにテーブルレベルで定義する必要があります。

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

主キー

通常、各テーブルには、テーブルに格納されているデータの行を一意に識別する列または列のセットが含まれている必要があります。この列または列のセットは、主キーと呼ばれます。ほとんどのテーブルには、主キーとして単一の列があります。キー列は、NULLおよび重複値に対して制限されています。

注意点-

テーブルに含めることができる主キーは1つだけです。
複数の列を複合主キーの下に配置できます。
Oracleは、列値の重複を防ぐために一意の索引を内部的に作成します。索引については、PL / SQLで後述します。

構文：

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

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

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

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

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

外部キー

2つのテーブルが特定の列に基づいて親子関係を共有する場合、子テーブルの結合列は外部キーと呼ばれます。親テーブルの対応する列のこのプロパティは参照整合性と呼ばれます。子テーブルの外部キー列の値は次のようになります。 nullであるか、親テーブルの既存の値である必要があります。参照整合性を適用できるのは、参照されるテーブルのプライマリキー列のみであることに注意してください。

子テーブルの列に外部キーが定義されている場合、親行に子行が含まれていると、Oracleは親行の削除を許可しません。ただし、外部キーの定義時にON DELETE CASCADEオプションが指定されている場合、Oracleは削除します。同様に、ON DELETE SET NULLは、親テーブルの行が削除されると、外部キー値がnullに設定されることを示します。

構文：

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

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

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

Usage of ON DELETE CASCADE clause

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

チェック制約

特定の列に格納されているデータ値は、許容可能な値の範囲内にある必要がある場合があります。CHECK制約では、テーブルに格納されている各行について、指定されたチェック条件がtrueまたはunknownである必要があります。Check制約を使用すると、に条件付きルールを課すことができます。列。データを列に挿入する前に検証する必要があります。条件には、サブクエリまたは疑似列CURRVAL NEXTVAL、LEVEL、ROWNUM、またはSYSDATEを含めることはできません。

Oracleでは、1つの列に複数のCHECK制約を設定できます。実際、列に定義できるCHECK制約の数に実際的な制限はありません。

構文：

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] 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),
);

ALTERTABLEステートメント

DBAは、データベースにテーブルが作成された後、テーブル構造または列定義を変更できます。DDLコマンドALTER TABLEは、このようなアクションを実行するために使用されます。Alterコマンドは、スキーマオブジェクト専用の複数のユーティリティを提供します。ALTERTABLEステートメントが使用されます。テーブルの列を追加、削除、名前変更、および変更します。

以下のALTERTABLEステートメントは、テーブルEMPの名前をEMP_NEWに変更します。

ALTER TABLE EMP RENAME TO EMP_NEW;

以下のALTERTABLEステートメントは、新しい列TESTCOLをEMP_NEWテーブルに追加します。

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

以下のALTERTABLEステートメントは、列の名前をTESTCOLからTESTNEWに変更します。

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

以下のALTERTABLEステートメントは、EMP_NEWテーブルから列TESTNEWを削除します。

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

以下のALTERTABLEステートメントは、EMPLOYEE_ID列に主キーを追加します。

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

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

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

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

ALTER TABLE EMP_NEW READ ONLY;

読み取り専用テーブル

読み取り専用テーブルは、Oracle 11gの拡張機能として提供されました。これにより、テーブルを読み取り専用の目的で使用できるようになります。以前のOracleバージョンでは、他のユーザーにSELECT権限を付与することでテーブルが読み取り専用になりましたが、所有者には引き続き読み取り/書き込み権限がありました。ただし、テーブルが読み取り専用に設定されている場合、所有者でさえデータ操作にアクセスできません。。

構文：

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] 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.

DROPTABLEステートメント

DROP TABLEステートメントは、データベースからテーブルを削除するために使用されます。ドロップされたテーブルとそのデータは選択できなくなります。ドロップされたテーブルは、recyclebinで使用可能な場合、FLASHBACKユーティリティを使用して回復できます。テーブルをドロップすると、インデックスとそれに関連付けられたトリガーがドロップされます。