連載
» 2006年02月10日 00時00分 公開

ORACLE MASTER Bronze SQL基礎I 講座(10):SQLでデータを自在に操作する

Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう!

[有限会社G.F.インフィニティ,@IT]

 本連載第8回「SELECT文中のSELECT文、副問い合わせ」、第9回「副問い合わせの構文を覚える」の2回にわたり、副問い合わせによって問い合わせ結果をSQL文の条件に使用する方法を紹介しました。第10回に当たる今回は、データ操作について学びましょう。

理解しておきたいこと

1.確認しておきたい内容

  • DML(データ操作言語)の各構文について
  • DMLの実行

2.INSERT文

 表に新しく行を挿入するには、INSERT文を使用します。

INSERT INTO 表名 [(列名1, 列名2, ...)] VALUES (値1, 値2, ...);

・列名:表の列定義順にすべての列を指定するのであれば不要・値:列定義順に実際の行を構成する列値を指定

 表定義には存在するのに列名として指定しない場合、その列には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の列の数とデータ型に一致する必要があります。

INSERT INTO 表名 [(列名1, 列名2, ...)] SELECT 列名1, 列名2 FROM 表名;

例:
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の方法で記述するのであれば、次のように記述します。

INSERT INTO emp(ename, job, empno) VALUES ('SCOTT','CLERK',1000);

 選択肢aはINSERTの列リストを使用していないので、すべての列値をVALUESリストに含める必要がありますが、1列足りないのでエラーとなります。残りのc、d、fが正解となります。

3.UPDATE文

 表内の既存行を変更するには、UPDATE文を使用します。

UPDATE 表名 SET 列名1=値1 [列名2=値2, ...] [WHERE 条件式];

  • 列名:変更を行いたい列名
  • :変更後の値。副問い合わせを使用した値も可能
  • 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文よりも高速に行を削除することができます。

TRUNCATE TABLE 表名;

 ただし、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文を発行すると、トランザクションはコミットされる 図1 暗黙コミット。CREATEなどのDDL文を発行すると、トランザクションはコミットされる

 DDLが発行されると、そのセッションで発行されていたトランザクションは自動的にコミットされます。DDLだけで1つのトランザクションになるので、DDL後のDMLで新たなトランザクションが開始されます。

 SQL*PlusやiSQL*Plusでは、1つのトランザクションが終わると、自動的に次のトランザクションが開始するようになっています。

参考:「SET AUTOCOMMIT ON」を発行していると、文ごとに自動コミットになります。

 ロールバックでは、事前に「セーブポイント」を設定しておくことで、トランザクション全体ではなく、セーブポイントまでのロールバックを行うこともできます。

図2 セーブポイントまでのロールバック 図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 読み取り一貫性 図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.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。