3 DML文とトランザクションについて
データ操作言語(DML)文は、Oracle Database表のデータを追加、変更および削除します。トランザクションは、Oracle Databaseが1つの単位として処理する連続した1つ以上のSQL文です(これらの文は、すべて実行されるか、1つも実行されないかのいずれかです)。
内容は次のとおりです。
- データ操作言語(DML)文について
データ操作言語(DML)文は、既存の表のデータにアクセスし、操作します。 - トランザクション制御文について
トランザクションは、Oracle Databaseが1つの単位として扱う、1つ以上の連続するSQL文です。すべての文が実行されるか、1つも実行されないかのいずれかになります。トランザクションは、複数の操作を1つの単位として実行する必要があるビジネス・プロセスをモデル化する場合に必要になります。 - トランザクションのコミット
トランザクションをコミットすると、変更が永続的になり、セーブポイントが消去され、ロックが解除されます。 - トランザクションのロールバック
トランザクションをロールバックすると、変更が取り消されます。現在のトランザクション全体をロールバックするか、指定したセーブポイントまでのみロールバックすることができます。 - トランザクションでのセーブポイントの設定
SAVEPOINT文は、トランザクションにセーブポイントをマークします。このポイントからトランザクションをロールバックできます。セーブポイントはオプションで、1つのトランザクションで複数のセーブポイントを設定できます。
3.1 データ操作言語(DML)文について
データ操作言語(DML)文は、既存の表のデータにアクセスし、操作します。
SQL*Plus環境では、SQL>プロンプトに続いてDML文を入力できます。
SQL Developer環境では、ワークシートにDML文を入力できます。または、SQL Developerの「接続」フレームとツールを使用して、データにアクセスして操作することもできます。
SQL DeveloperでDML文の結果を確認するには、「接続」フレームで変更されたオブジェクトのスキーマ・オブジェクト・タイプを選択して、「リフレッシュ」アイコンをクリックします。
DML文を含むトランザクションをコミットするまでは、その効果は永続的ではありません。トランザクションは、Oracle Databaseが1つの単位として扱うSQL文の順序です(1つのDML文のこともあります)。トランザクションをコミットするまでは、ロールバックする(元に戻す)ことができます。トランザクションの詳細は、「トランザクション制御文について」を参照してください。
内容は次のとおりです。
- INSERT文について
INSERT文は、既存の表に行を挿入します。 - UPDATE文について
UPDATE文は、既存の表の行のセットを更新(値を変更)します。 - DELETE文について
DELETE文は、表から行を削除します。
関連項目:
DML文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.1.1 INSERT文について
INSERT文は、既存の表に行を挿入します。
推奨されている最も単純なINSERT文の構文は、次のとおりです。
INSERT INTO table_name (list_of_columns) VALUES (list_of_values);
list_of_columnsの各列に対して、list_of_valuesの対応する位置に有効な値が必要です。そのため、表に列を挿入する前に、表にどのような列があり、どのような値が有効かを知っておく必要があります。SQL Developerを使用してこの情報を取得するには、「チュートリアル: 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)
例3-1のINSERT文では、すべての列の値がわかっている従業員として、表EMPLOYEESに行を挿入します。
表に行を挿入するためにすべての列の値を公開する必要はありませんが、NOT NULL列の値はすべて公開します。NULLの可能性がある列の値がわからない場合は、その列をlist_of_columnsから省略することができます。この列の値はデフォルトでNULLとなります。
例3-2のINSERT文では、SALARYを除くすべての列の値がわかっている従業員として、表EMPLOYEESに行を挿入します。ここでは、SALARYをNULL値にすることができます。給与がわかった際に、UPDATE文で更新できます(例3-4を参照)。
例3-3のINSERT文では、LAST_NAMEが不明の従業員として、表EMPLOYEESに行を挿入しようとしています。
例3-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.
例3-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.
例3-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")
関連項目:
-
INSERT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
データ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.1.2 UPDATE文について
UPDATE文は、既存の表の行のセットを更新(値を変更)します。
単純なUPDATE文の構文は、次のとおりです。
UPDATE table_name SET column_name = value [, column_name = value]... [ WHERE condition ];
各valueは対応するcolumn_nameに対して有効である必要があります。 WHERE句を使用する場合、その文ではconditionを満たす行の列値のみを更新します。
例3-4のUPDATE文では、例3-2で従業員の給与がわかる前にEMPLOYEES表に挿入された行のSALARY列の値を更新します。
例3-5のUPDATE文では、部門80の全従業員の歩合率を更新します。
例3-4 データを追加するUPDATE文の使用
UPDATE EMPLOYEES
SET SALARY = 8500
WHERE LAST_NAME = 'Keats';
結果:
1 row updated.
例3-5 複数の行を更新するUPDATE文の使用
UPDATE EMPLOYEES SET COMMISSION_PCT = COMMISSION_PCT + 0.05 WHERE DEPARTMENT_ID = 80;
結果:
34 rows updated.
関連項目:
-
UPDATE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
データ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.1.3 DELETE文について
DELETE文は、表から行を削除します。
単純なDELETE文の構文は、次のとおりです。
DELETE FROM table_name [ WHERE condition ];
WHERE句を含む文では、conditionを満たす行のみが削除されます。 WHERE句を省略した場合、その文では表からすべての行が削除されますが、空の表は残ります。表を削除するには、DROP TABLE文を使用します。
例3-6のDELETE文は、例3-1および例3-2で挿入した行を削除します。
例3-6 DELETE文の使用
DELETE FROM EMPLOYEES WHERE HIRE_DATE = TO_DATE('01-JAN-07', 'dd-mon-yy');
結果:
2 rows deleted.
関連項目:
-
DELETE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
DROP TABLE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.2 トランザクション制御文について
トランザクションは、Oracle Databaseが1つの単位として処理する連続した1つ以上のSQL文です(これらの文は、すべて実行されるか、1つも実行されないかのいずれかです)。トランザクションは、複数の操作を1つの単位として実行する必要があるビジネス・プロセスをモデル化する場合に必要になります。
たとえば、マネージャが退職する場合、JOB_HISTORY表に退社日を示す行が挿入され、このマネージャにレポートする全従業員について、EMPLOYEES表でMANAGER_IDを更新する必要があります。アプリケーションでこのプロセスをモデル化するには、INSERT文とUPDATE文を1つのトランザクションとしてグループ化する必要があります。
基本的なトランザクション制御文は、次のとおりです。
-
SAVEPOINT: トランザクションにセーブポイントをマークします。このポイントからトランザクションをロールバックできます。セーブポイントはオプションで、1つのトランザクションで複数のセーブポイントを設定できます。
-
COMMITは、現在のトランザクションを終了して、変更を永続的にし、セーブポイントを消去して、ロックを解除します。
-
ROLLBACKは、現在のトランザクション全体か、または指定したセーブポイント以降に行われた変更のみをロールバックします(取り消す)。
SQL*Plus環境では、SQL>プロンプトに続いてトランザクション制御文を入力できます。
SQL Develope環境では、ワークシートにトランザクション制御文を入力できます。また、SQL Developerには、「変更のコミット」および「変更のロールバック」アイコンがあります。これらのアイコンの詳細は、「トランザクションのコミット」および"「トランザクションのロールバック」を参照してください。
注意:
明示的にトランザクションをコミットせずプログラムが異常終了した場合、データベースはコミットされていないトランザクションを自動的にロールバックします。
アプリケーション・プログラム内でトランザクションをコミットまたはロールバックして、トランザクションを明示的に終了することをお薦めします。
関連項目:
-
トランザクション管理の詳細は、『Oracle Database概要』を参照してください。
-
トランザクション制御文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.3 トランザクションのコミット
トランザクションをコミットすると、そのトランザクションの変更を確定して、セーブポイントを消去しロックを解除します。
トランザクションを明示的にコミットするには、COMMIT文を使用するか、(SQL Developer環境で)「変更のコミット」アイコンを使用します。
注意:
Oracle Databaseでは、データ定義言語(DDL)文の前後で暗黙的にCOMMITが発行されます。DDL文の詳細は、「データ定義言語(DDL)文について」を参照してください。
トランザクションをコミットする前:
-
変更は表示できますが、他のデータベース・インスタンスのユーザーには表示されません。
-
変更は完了しておらず、ROLLBACK文で取り消すことができます。
トランザクションをコミットした後:
-
自分で行った変更が他のユーザーから参照可能になり、トランザクションのコミット後に他のユーザーの文が実行されます。
-
変更は完了しており、ROLLBACK文で取り消すことはできません。
例3-7では、REGIONS表に1行追加し(単純なトランザクション)、結果を確認してから、トランザクションをコミットします。
例3-7 トランザクションのコミット
トランザクションの前:
SELECT * FROM REGIONS ORDER BY REGION_ID;
結果:
REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 4 rows selected.
トランザクション(表に行を追加):
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
5 rows selected.
トランザクションのコミット:
COMMIT;
結果:
Commit complete.
関連項目:
COMMIT文の詳細は、『Oracle Database SQL言語リファレンス』 を参照してください。
3.4 トランザクションのロールバック
トランザクションのロールバックでは変更は取り消されます。現在のトランザクション全体をロールバックするか、指定したセーブポイントまでのみロールバックすることができます。
現在のトランザクションを指定したセーブポイントまでのみロールバックするには、ROLLBACK文をTO SAVEPOINT句とともに使用します。
現在のトランザクション全体をロールバックするには、ROLLBACK文をTO SAVEPOINT句なしで使用するか、(SQL Developer環境で)「変更のロールバック」アイコンを使用します。
現在のトランザクション全体のロールバック
-
トランザクションを終了します
-
すべての変更を戻します
-
すべてのセーブポイントを消去します
-
トランザクションのロックの解除
現在のトランザクションの指定したセーブポイントまでのみのロールバック:
-
トランザクションを終了しません
-
指定したセーブポイントの後で行われた変更のみを戻します
-
指定したセーブポイントの後にあるセーブポイントのみを消去します(指定したセーブポイント自体は除く)
-
指定したセーブポイントの後で取得された、すべての表と行のロックを解除します
指定したセーブポイントの後で、ロックされた行に対するアクセスを要求した他のトランザクションは、トランザクションがコミットまたはロールバックされるまで引き続き待機する必要があります。その行を要求していない他のトランザクションは、直ちにその行を要求してアクセスすることができます。
SQL Developerでロールバックの効果を確認するには、「リフレッシュ」アイコンをクリックする必要のある場合があります。
例3-7の結果、REGIONS
表には「Middle East and Africa」という地域と「Africa」という地域があります。例3-8では、この問題を修正(単純なトランザクション)して変更を確認した後、トランザクションをロールバックし、ロールバックされたことを確認します。
例3-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 5 rows selected.
トランザクション(表を変更):
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
5 rows selected.
トランザクションのロールバック:
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
5 rows selected.
関連項目:
ROLLBACK文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
3.5 トランザクションでのセーブポイントの設定
SAVEPOINT文は、トランザクションにセーブポイントをマークします。このポイントからトランザクションをロールバックできます。セーブポイントはオプションで、1つのトランザクションで複数のセーブポイントを設定できます。
例3-9では、複数のDML文と複数のセーブポイントがあるトランザクションを実行した後、あるセーブポイントへトランザクションをロールバックして、そのセーブポイントの後で行われた変更のみを元に戻します。
例3-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言語リファレンス』を参照してください。