SQLでデータを自在に操作する：ORACLE MASTER Bronze SQL基礎I 講座（10）
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！
本連載第8回「SELECT文中のSELECT文、副問い合わせ」、第9回「副問い合わせの構文を覚える」の2回にわたり、副問い合わせによって問い合わせ結果をSQL文の条件に使用する方法を紹介しました。第10回に当たる今回は、データ操作について学びましょう。
ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス
- 第1回 Oracleで使うSQLの基礎を学ぶ
- 第2回 SQL問い合わせによる行の制限とソート
- 第3回 SQLで使える関数の基礎知識
- 第4回 SQLの関数を使いこなす
- 第5回 SQLの関数でデータを集計する
- 第6回 SQLで複数の表からデータを取り出す
- 第7回 SQLの外部結合でデータを取り出す
- 第8回 SELECT文中のSELECT文、副問い合わせ
- 第9回 副問い合わせの構文を覚える
- 第10回 SQLでデータを自在に操作する
- 第11回 SQLで表のデータを制限する
- 最終回 SQLで便利なオブジェクトを使いこなす
理解しておきたいこと
1．確認しておきたい内容
- DML（データ操作言語）の各構文について
- DMLの実行
2．INSERT文
表に新しく行を挿入するには、INSERT文を使用します。
・列名：表の列定義順にすべての列を指定するのであれば不要・値：列定義順に実際の行を構成する列値を指定
表定義には存在するのに列名として指定しない場合、その列にはNULL値が格納されます（列にデフォルト値が設定されていれば、その値を格納）。
例：
SQL> INSERT INTO emp(empno, ename, deptno)
VALUES(8000, 'TEST', 10);
SQL> SELECT * FROM emp WHERE empno=8000;
EMPNO ENAME JOB COMM DEPTNO
---------- ---------- ---------- ... ---------- ----------
8000 TEST 10
|
INSERT文では、VALUES句の代わりに副問い合わせを使用することができます。副問い合わせの結果は、INSERTの列の数とデータ型に一致する必要があります。
例： SQL> INSERT INTO emp2 SELECT * FROM emp; |
問題1
次の表定義を確認してください。
EMPNO NUMBER(4) 主キー ENAME VARCHAR2(15) JOB VARCHAR2(10) DEPTNO NUMBER(2) |
エラーとならずに実行できる文を3つ選択しなさい。
a．INSERT INTO emp VALUES (1000,'SCOTT','CLERK');
b．INSERT INTO emp(ename,job) VALUES ('SCOTT','CLERK');
c．INSERT INTO emp(empno) VALUES (1000);
d．INSERT INTO emp VALUES (1000,'SCOTT','CLERK',10);
e．INSERT INTO emp(ename, job, empno) VALUES (1000,'SCOTT','CLERK');
f．INSERT INTO emp(empno, job) VALUES (1000,'CLERK');
正解：c、d、f
解説
この問題では、empno列が主キーですので、INSERT時にも必ず値を格納する必要があります。そこに着目すると、選択肢bはempno列をINSERTの列リストに含めていないのでエラーとなります。
選択肢eは、INSERTの列リストを使用していますが、INSERT列リストとVALUESリストの順番がずれているためエラーになってしまいます。選択肢eの方法で記述するのであれば、次のように記述します。
選択肢aはINSERTの列リストを使用していないので、すべての列値をVALUESリストに含める必要がありますが、1列足りないのでエラーとなります。残りのc、d、fが正解となります。
3．UPDATE文
表内の既存行を変更するには、UPDATE文を使用します。
- 列名：変更を行いたい列名
- 値：変更後の値。副問い合わせを使用した値も可能
- WHERE条件式：変更を行いたい行を選択する条件式
WHERE句を使用せずにUPDATE文を実行すると、その表のすべての行が更新されます。
例：
SQL> UPDATE emp SET job='SALESMAN', sal=2000 WHERE empno=8000;
SQL> SELECT * FROM emp WHERE empno=8000;
EMPNO ENAME JOB SAL COMM DEPTNO
---------- ---------- ---------- ... ---------- ---------- ----------
8000 TEST SALESMAN 2000 10
|
問題1
EMP表の従業員番号が7369と7788の行を次の要件で更新します。
・職種（JOB）は、デフォルト値を使用する
・歩合給（COMM）は、100とする
・部門番号（deptno）は、置換変数を使用して値を入力する
・入社日（hiredate）は、デフォルト値を使用するがNULLにすることもできる
この要件を満たしている文を選択しなさい。
a．
UPDATE emp SET
job = DEFAULT,
comm = 100,
deptno = &deptno,
hiredate = DEFAULT OR NULL
WHERE empno IN (7369, 7788);
b．
UPDATE emp SET
job = DEFAULT,
comm = 100,
deptno = &deptno,
hiredate = DEFAULT
WHERE empno IN (7369, 7788);
c．
UPDATE emp SET
job = DEFAULT,
comm = 100,
hiredate = DEFAULT
WHERE empno IN (7369, 7788)
AND deptno = &deptno;
d．
UPDATE emp SET
job IS DEFAULT,
comm = 100,
deptno = &deptno,
hiredate IS DEFAULT
WHERE empno IN (7369, 7788);
正解：b
解説
表の列に設定したデフォルト値を利用するには、値として「DEFAULT」キーワードを使用します。このキーワードはOracle9iより使用可能になっており、INSERT時やUPDATE時に使用できます。選択肢aの「= DEFAULT OR NULL」や選択肢dの「IS DEFAULT」のような使い方はできません。
また、実行時に値を入力させるのであれば、置換変数を使用します。SQL*Plus、iSQL*Plus限定ですが、「&置換変数」にて実行時入力ができます。
一見すると選択肢cは間違っていませんが、問題の要件に合っていません。選択肢cは、「従業員番号7369または7788、かつ指定した部門番号の従業員を更新」になってしまいます。問題要件はよく読むようにしましょう。残りのbが正解となります。
4．DELETE文
表内の既存行を削除するには、DELETE文を使用します。
DELETE [FROM] 表名 [WHERE 条件式];
- WHERE条件式：削除を行いたい行を選択する条件式
WHERE句を使用せずにDELETE文を実行すると、その表のすべての行が削除されますが、表定義は残ります。
列を削除するにはALTER TABLE文、表そのものを削除するにはDROP TABLE文を使用します。間違わないようにしましょう。
例： SQL> DELETE FROM emp WHERE empno=8000; SQL> SELECT * FROM emp WHERE empno=8000; レコードが選択されませんでした。 |
問題1
次の文を確認してください。
DELETE emp; |
この文の結果として正しいものを選択しなさい。
a．EMP表の表定義が削除される
b．EMP表のすべての行が削除される
c．ROLLBACKできない
d．構文エラーのため、実行できない
正解：b
解説
ANSIで定義しているSQLでは「DELETE FROM 表名」ですが、OracleのDELETE文では、FROM句は任意です。省略しても構文エラーにはなりません（選択肢d）。
DELETE文でWHERE句を省略すると、すべての行が削除されます（正解b）。しかし、空のテーブルになるだけで、表としての定義は残されています（選択肢a）。
また、DELETE文はDMLですから、COMMITするまでは確定されず、ROLLBACK（取り消し）することができます（選択肢c）。
5．TRUNCATE文
表構造は残し、表内のすべての行を削除する方法としてWHERE句なしのDELETE文がありますが、TRUNCATE文を使用すると、DELETE文よりも高速に行を削除することができます。
ただし、DELETE文はDMLですが、TRUNCATE文はDDL（データ定義言語）であり、暗黙のうちにコミットされます。そのため、取り消し（ROLLBACK）することができません。
例：
SQL> SELECT COUNT(*) FROM emp2;
COUNT(*)
----------
14
SQL> TRUNCATE TABLE emp2;
表が切り捨てられました。
SQL> SELECT COUNT(*) FROM emp2;
COUNT(*)
----------
0 |
問題1
次の文を確認してください。
TRUNCATE TABLE emp; |
この文の特徴を3つ選択しなさい。
a．EMP表の定義が削除される
b．EMP表の定義は残される
c．EMP表の領域は残される
d．EMP表の領域は解放される
e．DESC empとすると、結果が表示されない
f．表の所有者かDROP ANY TABLE権限を持っている必要がある
正解：b、d、f
解説
TRUNCATE文は、表の定義を残したまま、高速に表の行のみを削除します。見掛け上の動作は、WHERE句なしのDELETE文と同じです。表の定義は削除されません（選択肢a、正解b）。定義が残されるので、SQL*PlusのDESCコマンドを使用して定義を確認することが可能です（選択肢e）。
DELETE文の場合は、一度使用した領域フラグ（高水位標、High Water Markという）は変化しませんが、TRUNCATE文の場合は領域が解放されます（選択肢c、正解d）。
また、Oracleサーバの場合、表の所有者はその表に対するひととおりの権限を持ちますがほかのユーザーが所有する表に対しては何らかの権限が必要です。正解fのDROP ANY TABLE権限はほかのユーザーが所有する表を削除できる権限で、TRUNCATE文の実行にもこの権限が必要です。
6．トランザクション
DMLによる表データの操作は、1つ1つの操作を確定する（コミット）のではなく、関連する一連の操作をまとめて確定するべきであると考えられています。一連の操作が確定できないときは、すべてを取り消す（ロールバック）ことでデータの整合性を保証します。このような一連の操作を「トランザクション」と呼びます。
Oracleデータベースでは、DDLとDCL（データ制御言語）は個々の文を1つのトランザクションとして扱いますが、DMLでは次のいずれかのイベントが発生するまでを1つのトランザクションとして扱います。
- COMMIT文またはROLLBACK文の発行
- DDLまたはDCLの発行（暗黙コミット、図1）図1）
- ユーザーがSQL*Plus、iSQL*Plusを切断（明示的に切断した場合はコミット、異常終了の場合はロールバック）
- システムがクラッシュしたとき（次回Oracleデータベース起動時にロールバック）
図1 暗黙コミット。CREATEなどのDDL文を発行すると、トランザクションはコミットされるDDLが発行されると、そのセッションで発行されていたトランザクションは自動的にコミットされます。DDLだけで1つのトランザクションになるので、DDL後のDMLで新たなトランザクションが開始されます。
SQL*PlusやiSQL*Plusでは、1つのトランザクションが終わると、自動的に次のトランザクションが開始するようになっています。
ロールバックでは、事前に「セーブポイント」を設定しておくことで、トランザクション全体ではなく、セーブポイントまでのロールバックを行うこともできます。
図2 セーブポイントまでのロールバック図2のようなトランザクションの場合、ROLLBACK文によって次のように処理が行われます。
- ROLLBACK：（1）（2）（3）（4）の文をロールバック
- ROLLBACK TO B：（4）の文をロールバック
- ROLLBACK TO A：（2）（3）（4）の文をロールバック
ロールバックされる中に含まれるセーブポイントは消去されます。図2の例で「ROLLBACK TO A」を実行すると、セーブポイントAは残りますが、セーブポイントBは消去されます。
問題1
トランザクションを完了する文を2つ選択しなさい。
a．DELETE FROM emp;
b．ROLLBACK TO SAVEPOINT a;
c．GRANT SELECT ON emp TO hr;
d．ALTER TABLE emp ADD COLUMN jobs VARCHAR2(10);
e．SELECT empno, ename FROM emp ORDER BY sal;
正解：c、d
解説
前回、宿題とした問題です。トランザクションは、COMMITやROLLBACK文によってだけでなく、ALTERなどのDDL（正解d）やGRANTなどのDCL（正解c）によって暗黙にコミットされます。DDLやDCLを使うときは注意しましょう。DDLに関しては、次回「オブジェクトの作成および管理」で解説します。
紛らわしいのが選択肢bだと思います。選択肢bは、「セーブポイントまでロールバック」ですから、トランザクションは続行しています。選択肢a、選択肢eのようなDMLでは、暗黙コミットはされません。
7．読み取り一貫性
Oracleデータベースでは、あるトランザクションで変更中のデータをほかのトランザクションから参照することはできません。これはトランザクションが確定されない状態を読み取ることによるデータの不整合を防止するための動作です。
代わりに、トランザクションが変更する前のデータを参照することができます。変更前のデータは確定済みのデータですから、整合が取れた状態で参照することができます。このような動作のことを「読み取り一貫性」といいます。読み取り一貫性は、トランザクションによってデータが変更される際、「UNDOブロック」と呼ばれる変更前のイメージを保存しておくことで提供されます。
図3では、トランザクションAが変更したデータ（20という値）を別のトランザクションBが参照できるのは、トランザクションAがコミットされた後です。
図3 読み取り一貫性まとめ
次の内容をチェックしておきましょう。
- DMLに分類されるのは、SELECT、INSERT、UPDATE、DELETE、MERGE
- INSERT時、NOT NULL制約や主キー制約が設定されている列には必ず値が必要
- INSERT列リストを使用する場合、列リストで指定した順番にVALUESリストに値を記述する
- INSERT文、UPDATE文で列のデフォルト値を使用する場合は「DEFAULT」キーワードを使用
- DELETE文とTRUNCATE文では、表の定義は削除されず、行のみが削除される
- TRUNCATE文はDDLのためロールバックできず、表の領域を開放する
- トランザクションはCOMMITやROLLBACKだけでなく、DDLやDCLによる暗黙コミットで完了できる
宿題
次回は、「オブジェクトの作成および管理」を確認します。次の宿題を解いておいてください。
次の文を確認してください。
CREATE TABLE orders( empno NUMBER(4) UNIQUE, ename VARCHAR2(15) NOT NULL, sal NUMBER(7,2) CHECK(sal>0), ord_id NUMBER(3), ord_status CHAR(2), CONSTRAINT orders_pk PRIMARY KEY(ord_id, ord_status)); |
この文で自動的に索引が作成される列を2つ選択しなさい。
a．empno
b．ename
c．sal
d．ord_id
e．ord_status
f．ord_idとord_statusの複合
Copyright © ITmedia, Inc. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。