| Oracle® Database Express Edition 2日で開発者ガイド 11g リリース2 (11.2) B66463-01 |
|
 前 |
 次 |
この章の内容は次のとおりです。
データ操作言語(DML)文は、既存の表のデータにアクセスし、操作します。
SQL*Plus環境では、SQL>プロンプトに続いてDML文を入力できます。
SQL Developer環境では、SQLワークシートにDML文を入力できます。または、SQL Developerのナビゲーション・フレームとツールを使用して、データにアクセスして操作することもできます。
SQL DeveloperでのDML文の影響を表示するには、変更されたオブジェクトのスキーマ・オブジェクト・タイプを「接続」ペインで選択し、「リフレッシュ」アイコンをクリックする必要があります。
DML文を含むトランザクションをコミットするまでは、その効果は永続的ではありません。トランザクションは、Oracle Database Express Edition (Oracle Database XE)が1つの単位として扱うSQL文の順序です(1つのDML文のこともあります)。トランザクションをコミットするまでは、ロールバックする(元に戻す)ことができます。トランザクションの詳細は、「トランザクション制御文について」を参照してください。
トピック:
|
参照: DML文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
推奨されている最も単純な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-1のINSERT文では、すべての列の値がわかっている従業員として、表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-2のINSERT文では、SALARYを除くすべての列の値がわかっている従業員として、表EMPLOYEESに行を挿入します。ここでは、SALARYをNULL値にすることができます。給与がわかった際に、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-3のINSERT文では、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 table_name SET column_name = value [, column_name = value]... [ WHERE condition ];
各valueは対応するcolumn_nameに対して有効である必要があります。 WHERE句を使用する場合、その文ではconditionを満たす行の列値のみを更新します。
例5-4のUPDATE文では、例5-2で従業員の給与がわかる前にEMPLOYEES表に挿入された行のSALARY列の値を更新します。
例5-4 データを追加するUPDATE文の使用
UPDATE EMPLOYEES
SET SALARY = 8500
WHERE LAST_NAME = 'Keats';
結果:
1 row updated.
例5-5のUPDATE文では、部門80の全従業員の歩合率を更新します。
例5-5 複数の行を更新するUPDATE文の使用
UPDATE EMPLOYEES SET COMMISSION_PCT = COMMISSION_PCT + 0.05 WHERE DEPARTMENT_ID = 80;
結果:
36 rows updated.
|
参照:
|
単純なDELETE文の構文は、次のとおりです。
DELETE FROM table_name [ WHERE condition ];
WHERE句を含む文では、conditionを満たす行のみが削除されます。 WHERE句を省略した場合、その文では表からすべての行が削除されますが、空の表は残ります。表を削除するには、DROP TABLE文を使用します。
例5-6のDELETE文は、例5-1および例5-2で挿入した行を削除します。
|
参照:
|
トランザクションとは、Oracle Database XEが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には、「変更のコミット」および変更をロールバック・アイコンがあります。これらのアイコンの詳細は、「トランザクションのコミット」および「トランザクションのロールバック」を参照してください。
|
注意: 明示的にトランザクションをコミットせずプログラムが異常終了した場合、データベースはコミットされていないトランザクションを自動的にロールバックします。アプリケーション・プログラム内でトランザクションをコミットまたはロールバックして、トランザクションを明示的に終了することをお薦めします。 |
|
参照:
|
トランザクションをコミットすると、変更が永続的になり、セーブポイントが消去され、ロックが解除されます。
トランザクションを明示的にコミットするには、COMMIT文を使用するか、(SQL Developer環境で)「変更のコミット」アイコンを使用します。
トランザクションをコミットする前:
トランザクションをコミットした後:
自分で行った変更が他のユーザーから参照可能になり、トランザクションのコミット後に他のユーザーの文が実行されます。
変更は完了しており、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環境で)「変更のロールバック」アイコンを使用します。
トランザクションを終了します
すべての変更を戻します
すべてのセーブポイントを消去します
トランザクションのロックを解除します
現在のトランザクションの指定したセーブポイントまでのみのロールバック:
トランザクションを終了しません
指定したセーブポイントの後で行われた変更のみを戻します
指定したセーブポイントの後にあるセーブポイントのみを消去します(指定したセーブポイント自体は除く)
指定したセーブポイントの後で取得された、すべての表と行のロックを解除します
指定したセーブポイントの後で、ロックされた行に対するアクセスを要求した他のトランザクションは、トランザクションがコミットまたはロールバックされるまで引き続き待機する必要があります。その行を要求していない他のトランザクションは、直ちにその行を要求してアクセスすることができます。
SQL Developerでロールバックの効果を確認するには、「リフレッシュ」アイコンをクリックする必要のある場合があります。
例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言語リファレンス』を参照してください。 |
