ヘッダーをスキップ
Oracle® Database 2日で開発者ガイド
11gリリース2 (11.2)
B56265-05
  目次へ移動
目次
索引へ移動
索引

前
 
次
 

5 DML文とトランザクションについて

この章の内容は次のとおりです。

データ操作言語(DML)文について

データ操作言語(DML)文は、既存の表のデータにアクセスし、操作します。

SQL*Plus環境では、SQL>プロンプトに続いてDML文を入力できます。

SQL Developer環境では、SQLワークシートにDML文を入力できます。または、SQL Developerのナビゲーション・フレームとツールを使用して、データにアクセスして操作することもできます。

SQL DeveloperでDML文の効果を確認するには、「リフレッシュ」アイコンをクリックする必要のある場合があります。

refresh_icon.gifの説明が続きます。
refresh_icon.gifの説明

DML文を含むトランザクションをコミットするまでは、その効果は永続的ではありません。トランザクションは、Oracle Databaseが1つの単位として扱うSQL文の順序です(1つのDML文のこともあります)。トランザクションをコミットするまでは、ロールバックする(元に戻す)ことができます。トランザクションの詳細は、「トランザクション制御文について」を参照してください。

トピック:


参照:

DML文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

INSERT文について

INSERT文は、既存の表に行を挿入します。

推奨されている最も単純なINSERT文の構文は、次のとおりです。

INSERT INTO table_name (list_of_columns)
VALUES (list_of_values);

list_of_columnsの各列に対して、list_of_valuesの対応する位置に有効な値が必要です。そのため、表に列を挿入する前に、表にどのような列があり、どのような値が有効かを知っておく必要があります。SQL Developerを使用してこうした情報を取得するには、「チュートリアル: EMPLOYEES表のプロパティとデータの表示」を参照してください。SQL*Plusを使用してこうした情報を取得するには、DESCRIBE文を使用します。次に例を示します。

DESCRIBE EMPLOYEES;

結果:

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

例5-1INSERT文では、すべての列の値がわかっている従業員として、表EMPLOYEESに行を挿入します。

例5-1 すべての情報が使用できる場合でのINSERT文の使用

INSERT INTO EMPLOYEES (
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  PHONE_NUMBER,
  HIRE_DATE,
  JOB_ID,
  SALARY,
  COMMISSION_PCT,
  MANAGER_ID,
  DEPARTMENT_ID
)
VALUES (
  10,              -- EMPLOYEE_ID
  'George',        -- FIRST_NAME
  'Gordon',        -- LAST_NAME
  'GGORDON',       -- EMAIL
  '650.506.2222',  -- PHONE_NUMBER
  '01-JAN-07',     -- HIRE_DATE
  'SA_REP',        -- JOB_ID
  9000,            -- SALARY
  .1,              -- COMMISSION_PCT
  148,             -- MANAGER_ID
  80               -- DEPARTMENT_ID
);

結果:

1 row created.

表に行を挿入するのにすべての列の値がわかっている必要はありませんが、NOT NULLの列の値はすべてわかっている必要があります。NULLの可能性がある列の値がわからない場合は、その列をlist_of_columnsから省略することができます。この列の値はデフォルトでNULLとなります。

例5-2INSERT文では、SALARYを除くすべての列の値がわかっている従業員として、表EMPLOYEESに行を挿入します。ここでは、SALARYNULL値にすることができます。給与がわかった際に、UPDATE文で更新できます(例5-4を参照)。

例5-2 情報をすべては使用できない場合でのINSERT文の使用

INSERT INTO EMPLOYEES (
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  PHONE_NUMBER,
  HIRE_DATE,
  JOB_ID,          -- Omit SALARY; its value defaults to NULL.
  COMMISSION_PCT,
  MANAGER_ID,
  DEPARTMENT_ID
)
VALUES (
  20,              -- EMPLOYEE_ID
  'John',          -- FIRST_NAME
  'Keats',         -- LAST_NAME
  'JKEATS',        -- EMAIL
  '650.506.3333',  -- PHONE_NUMBER
  '01-JAN-07',     -- HIRE_DATE
  'SA_REP',        -- JOB_ID
  .1,              -- COMMISSION_PCT
  148,             -- MANAGER_ID
  80               -- DEPARTMENT_ID
);

結果:

1 row created.

例5-3INSERT文では、LAST_NAMEが不明の従業員として、表EMPLOYEESに行を挿入しようとしています。

例5-3 INSERT文の誤った使用

INSERT INTO EMPLOYEES (
  EMPLOYEE_ID,
  FIRST_NAME,      -- Omit LAST_NAME (error)
  EMAIL,
  PHONE_NUMBER,
  HIRE_DATE,
  JOB_ID,
  COMMISSION_PCT,
  MANAGER_ID,
  DEPARTMENT_ID
)
VALUES (
  20,              -- EMPLOYEE_ID
  'John',          -- FIRST_NAME
  'JOHN',          -- EMAIL
  '650.506.3333',  -- PHONE_NUMBER
  '01-JAN-07',     -- HIRE_DATE
  'SA_REP',        -- JOB_ID
  .1,              -- COMMISSION_PCT
  148,             -- MANAGER_ID
  80               -- DEPARTMENT_ID
);

結果:

ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."LAST_NAME")

参照:


UPDATE文について

UPDATE文は、既存の表の行のセットを更新(値を変更)します。

単純なUPDATE文の構文は、次のとおりです。

UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];

valueは対応するcolumn_nameに対して有効である必要があります。 WHERE句を使用する場合、その文ではconditionを満たす行の列値のみを更新します。

例5-4UPDATE文では、例5-2で従業員の給与がわかる前にEMPLOYEES表に挿入された行のSALARY列の値を更新します。

例5-4 データを追加するUPDATE文の使用

UPDATE EMPLOYEES
SET SALARY = 8500
WHERE LAST_NAME = 'Keats';

結果:

1 row updated.

例5-5UPDATE文では、部門80の全従業員の歩合率を更新します。

例5-5 複数の行を更新するUPDATE文の使用

UPDATE EMPLOYEES
SET COMMISSION_PCT = COMMISSION_PCT + 0.05
WHERE DEPARTMENT_ID = 80;

結果:

36 rows updated.

参照:


DELETE文について

DELETE文は、表から行を削除します。

単純なDELETE文の構文は、次のとおりです。

DELETE FROM table_name
[ WHERE condition ];

WHERE句を含む文では、conditionを満たす行のみが削除されます。 WHERE句を省略した場合、その文では表からすべての行が削除されますが、空の表は残ります。表を削除するには、DROP TABLE文を使用します。

例5-6DELETE文は、例5-1および例5-2で挿入した行を削除します。

例5-6 DELETE文の使用

DELETE FROM EMPLOYEES
WHERE HIRE_DATE = '01-JAN-07';

結果:

2 rows deleted.

参照:


トランザクション制御文について

トランザクションは、Oracle Databaseが1つの単位として扱う、1つ以上のSQL文の順序です。すべての文が実行されるか、1つも実行されないかのいずれかになります。

トランザクションは、複数の操作を1つの単位として実行する必要があるビジネス・プロセスをモデル化する場合に必要になります。たとえば、マネージャが退職する場合、JOB_HISTORY表に退社日を示す行が挿入され、このマネージャにレポートする全従業員について、EMPLOYEES表でMANAGER_IDを更新する必要があります。アプリケーションでこのプロセスをモデル化するには、INSERT文とUPDATE文を1つのトランザクションとしてグループ化する必要があります。

基本的なトランザクション制御文は、次のとおりです。

  • SAVEPOINT: トランザクションにセーブポイントをマークします。このポイントからトランザクションをロールバックできます。セーブポイントはオプションで、1つのトランザクションで複数のセーブポイントを設定できます。

  • COMMITは、現在のトランザクションを終了して、変更を永続的にし、セーブポイントを消去して、ロックを解除します。

  • ROLLBACKは、現在のトランザクション全体か、または指定したセーブポイント以降に行われた変更のみをロールバックします(取り消す)。

SQL*Plus環境では、SQL>プロンプトに続いてトランザクション制御文を入力できます。

SQL Developer環境では、SQLワークシートにトランザクション制御文を入力できます。また、SQL Developerには、「変更のコミット」および変更をロールバック・アイコンがあります。これらのアイコンの詳細は、「トランザクションのコミット」および「トランザクションのロールバック」を参照してください。


注意:

明示的にトランザクションをコミットせずプログラムが異常終了した場合、データベースはコミットされていないトランザクションを自動的にロールバックします。

アプリケーション・プログラム内でトランザクションをコミットまたはロールバックして、トランザクションを明示的に終了することをお薦めします。



参照:

  • トランザクション管理の詳細は、『Oracle Database概要』を参照してください。

  • トランザクション制御文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。


トランザクションのコミット

トランザクションをコミットすると、変更が永続的になり、セーブポイントが消去され、ロックが解除されます。

トランザクションを明示的にコミットするには、COMMIT文を使用するか、(SQL Developer環境で)「変更のコミット」アイコンを使用します。

commit_icon.gifの説明が続きます。
commit_icon.gifの説明


注意:

Oracle Databaseは、データ定義言語(DDL)文の前後で、暗黙的COMMIT文を発行します。DDL文の詳細は、「データ定義言語(DDL)文について」を参照してください。

トランザクションをコミットする前:

  • 自分で行った変更は参照可能ですが、データベース・インスタンスの他のユーザーからは参照できません。

  • 変更は完了しておらず、ROLLBACK文で取り消すことができます。

トランザクションをコミットした後:

  • 自分で行った変更が他のユーザーから参照可能になり、トランザクションのコミット後に他のユーザーの文が実行されます。

  • 変更は完了しており、ROLLBACK文で取り消すことはできません。

例5-7では、REGIONS表に1行追加し(単純なトランザクション)、結果を確認してから、トランザクションをコミットします。

例5-7 トランザクションのコミット

トランザクションの前:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
 

トランザクション(表に行を追加):

INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');
 

結果:

1 row created.
 

行が追加されたことを確認:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

トランザクションのコミット:

COMMIT;

結果:

Commit complete.

参照:

COMMIT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

トランザクションのロールバック

トランザクションをロールバックすると、変更が取り消されます。現在のトランザクション全体をロールバックするか、指定したセーブポイントまでのみロールバックすることができます。

現在のトランザクションを指定したセーブポイントまでのみロールバックするには、ROLLBACK文をTO SAVEPOINT句とともに使用します。

現在のトランザクション全体をロールバックするには、ROLLBACK文をTO SAVEPOINT句なしで使用するか、(SQL Developer環境で)「変更のロールバック」アイコンを使用します。

rollback_icon.gifの説明が続きます。
rollback_icon.gifの説明

現在のトランザクション全体のロールバック:

  • トランザクションを終了します

  • すべての変更を戻します

  • すべてのセーブポイントを消去します

  • トランザクションのロックを解除します

現在のトランザクションの指定したセーブポイントまでのみのロールバック:

  • トランザクションを終了しません

  • 指定したセーブポイントの後で行われた変更のみを戻します

  • 指定したセーブポイントの後にあるセーブポイントのみを消去します(指定したセーブポイント自体は除く)

  • 指定したセーブポイントの後で取得された、すべての表と行のロックを解除します

    指定したセーブポイントの後で、ロックされた行に対するアクセスを要求した他のトランザクションは、トランザクションがコミットまたはロールバックされるまで引き続き待機する必要があります。その行を要求していない他のトランザクションは、直ちにその行を要求してアクセスすることができます。

SQL Developerでロールバックの効果を確認するには、「リフレッシュ」アイコンをクリックする必要のある場合があります。

refresh_icon.gifの説明が続きます。
refresh_icon.gifの説明

例5-7の結果、REGIONS表には「Middle East and Africa」という地域と「Africa」という地域があります。例5-8では、この問題を修正(単純なトランザクション)して変更を確認した後、トランザクションをロールバックし、ロールバックされたことを確認します。

例5-8 トランザクション全体のロールバック

トランザクションの前:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

トランザクション(表を変更):

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

結果:

1 row updated.
 

変更のチェック:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

トランザクションのロールバック:

ROLLBACK;

結果:

Rollback complete.

ロールバックのチェック:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

参照:

ROLLBACK文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

トランザクションでのセーブポイントの設定

SAVEPOINT文は、トランザクションにセーブポイントをマークします。このポイントからトランザクションをロールバックできます。セーブポイントはオプションで、1つのトランザクションで複数のセーブポイントを設定できます。

例5-9では、複数のDML文と複数のセーブポイントがあるトランザクションを実行した後、あるセーブポイントへトランザクションをロールバックして、そのセーブポイントの後で行われた変更のみを元に戻します。

例5-9 セーブポイントへのトランザクションのロールバック

トランザクション前のREGIONS表のチェック:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa
 
5 rows selected.

トランザクション前に地域4にある国の確認:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;
 

結果:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Nigeria                                  NG          4
Zambia                                   ZM          4
Zimbabwe                                 ZW          4
 
6 rows selected.
 

トランザクション前に地域5にある国の確認:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;
 

結果:

no rows selected
 

複数のセーブポイントがあるトランザクション:

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZM';
SAVEPOINT zambia;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'NG';
SAVEPOINT nigeria;
 
UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZW';
SAVEPOINT zimbabwe;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'EG';
SAVEPOINT egypt;

トランザクション後のREGIONS表の確認:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa
 
5 rows selected.

トランザクション後に地域4にある国の確認:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;
 

結果:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Israel                                   IL          4
Kuwait                                   KW          4
 
2 rows selected.
 

トランザクション後に地域5にある国の確認:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;
 

結果:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          5
Nigeria                                  NG          5
Zambia                                   ZM          5
Zimbabwe                                 ZW          5
 
4 rows selected.
 
ROLLBACK TO SAVEPOINT nigeria;

ロールバック後のREGIONS表の確認:

SELECT * FROM REGIONS
ORDER BY REGION_ID;
 

結果:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa
 
5 rows selected.

ロールバック後に地域4にある国の確認:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;
 

結果:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Zimbabwe                                 ZW          4
 
4 rows selected.
 

ロールバック後に地域5にある国の確認:

 
SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;
 

結果:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Nigeria                                  NG          5
Zambia                                   ZM          5
 
2 rows selected.

参照:

SAVEPOINT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。