Oracle DDL文で作成するサンプル

nas2019/10/20

Oracleのtable,view等を作成するDDL文のサンプルです。
DDLとは、Data Definition Languageの略でデータを定義する言語です。

テーブル テーブルを作成 / テーブルを削除
  alter table テーブルの列の属性を変更 / テーブルに列を追加 / テーブルの列を削除
  テーブルの列名を変更 / テーブルの列をNOT NULLにする
  テーブルの列をNULL可にする / テーブル名を変更
権限 権限を付与する(grant) / 権限を取り消す(revoke)
ビュー ビューを作成 / ビューを変更 / ビューを削除
シノニム シノニムを作成 / シノニムを削除 /
シーケンス シーケンスを作成 / シーケンスの採番 / シーケンスを削除
その他 Oracleのバージョン確認

テーブルを作成

CREATE TABLE employee(
	id NUMBER(6,0),
	name VARCHAR2(20) not null,
	romaji VARCHAR2(20),
	created_at TIMESTAMP,
	updated_at TIMESTAMP,
	primary key(id)
);

2行目は、number型です。整数6桁を指定しています。
3行目は、not null制約をつけています。
5,6行目は、作成日時(created_at)と更新日時(updated_at)でTIMESTAMP型です。
年、月、日、時、分、秒、秒の小数部を指定できます。
7行目は、プライマリーキーを指定しています。2行目のidを指しています。

※MySQLやPostgresqlにある、テーブルが存在しない場合のみテーブルを作成するIF NOT EXISTSは、Oracle 23cから利用可能です。

 

以下は、上記テーブルにデータを登録するinsert文です。

INSERT INTO employee VALUES (1,'鈴木','suzuki','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');
INSERT INTO employee VALUES (2,'田中','tanaka','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');
INSERT INTO employee VALUES (3,'佐藤','sato','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');

 

テーブルを削除

drop table employee;

drop tableを使用します。

※MySQLやPostgresqlにある、テーブルが既に存在していた場合のみテーブルを削除するIF EXISTSは、Oracle 23cから利用可能です。

 

テーブルの列の属性を変更

ALTER TABLE employee MODIFY(
	id VARCHAR2(20)
);

employeeテーブルの項目idをvarchar2型に変更しています。

テーブルに列を追加

ALTER TABLE employee ADD(
	mail VARCHAR2(30),
	url VARCHAR2(100)
);

テーブルの最後尾に項目mail,urlが追加されます。

テーブルの列を削除

ALTER TABLE employee DROP(
	mail,
	url
);

テーブルの項目mailとurlを削除しています。桁数の指定は不要です。

テーブルの列名を変更

ALTER TABLE employee 
RENAME COLUMN name1 TO name2;

テーブルの項目名のname1をname2に変更しています。

テーブルの列をNOT NULLにする

ALTER TABLE employee MODIFY(
	name NOT NULL
);

テーブルの項目名のnameをNOT NULLに変更しています。

テーブルの列をNULL可にする

ALTER TABLE employee MODIFY(
	name NULL
);

テーブルの項目名のnameをNULL可能に変更しています。

テーブル名を変更

ALTER TABLE employee1
RENAME TO employee2;

テーブル名のemployee1をemployee2に変更しています。

 

権限を付与する(grant)

GRANT 権限名 TO ユーザー名

grantで権限とユーザーを指定します。grantは付与するという意味です。

権限を取り消す(revoke)

REVOKE 権限名 FROM ユーザー名;

revokeで権限とユーザーを指定します。revokeは取り消すという意味です。

 

ビュー

ビューは、sqlで仮想の表を作成します。

ビューを作成

CREATE VIEW v_employee AS
	select
		id,
		name
	from
		employee
with read only;

select文でemployeeテーブルからビューを作成しています。
複数のテーブルを結合したsqlも作成できます。
7行目は、読み取り専用を指定しています。

ビューを変更

CREATE OR REPLACE VIEW v_employee AS
	select
		id,
		name,
		romaji
	from
		employee
with read only;

1行目は、OR REPLACEを追加しています。
5行目は、viewに項目を追加しています。

ビューを削除

DROP VIEW v_employee;

drop viewを使用します。項目の指定は不要です。

 

シノニム

シノニムとは、同義語という意味です。テーブルに別名をつけれます。

シノニムを作成

CREATE SYNONYM sy_employee FOR employee;

employeeテーブルからsy_employeeというシノニムを作成しています。
sy_employeeというテーブルにinsertした場合、employeeテーブルにデータが追加されます。

シノニムを削除

DROP SYNONYM sy_employee;

 

シーケンス

シーケンスは、順序とも呼ばれます。指定した値の増分を取得できます。

シーケンスを作成

CREATE SEQUENCE employee_id
	START WITH 1
	INCREMENT BY 1;

2行目は、最初は1から始まると指定しています。
3行目は、増分です。1ずつ増えます。

シーケンスの採番

SQL> select employee_id.nextval from dual;
   NEXTVAL
----------
         1

SQL> select employee_id.currval from dual;
   CURRVAL
----------
         1

SQL> select employee_id.currval from dual;
   CURRVAL
----------
         1

1行目は、nextval関数で次の値を取得します。再度、nextvalを使用すると次は2(設定した増分)になります。
6,11行目は、currval関数で現在の値を取得します。何度行っても値は変わりません。

シーケンスを削除

DROP SEQUENCE employee_id;

DROP SEQUENCEを使用します。

 

Oracleのバージョン確認

select * from v$version;

関連の記事

Oracle SQLコマンドラインを使用するサンプル
Oracle PL/SQLのストアドプロシージャのサンプル

△上に戻る