DML文の使用による表の管理
問９
次のSQLを実行しました。
CREATE TABLE products (product_id number ,product_name varchar2(30)); INSERT INTO products VALUES(1,'Tennis Racket'); SAVEPOINT a; INSERT INTO products VALUES(2,'Tennis Ball Pack 4'); SAVEPOINT b; ALTER TABLE products ADD (price NUMBER); UPDATE products set price = 30000 WHERE product_id = 1; SAVEPOINT c; UPDATE products set price = 2000 WHERE product_id = 2; SAVEPOINT d; DELETE FROM products WHERE product_id = 2; ROLLBACK TO SAVEPOINT a;
結果に関して正しい記述はどれですか。最適なものを選択しなさい。
- a. 2つ目のINSERTとprice列の追加および2つのUPDATEと1つのDELETEがロールバックされる
- b. 2つ目のINSERTと2つのUPDATEと1つのDELETEがロールバックされる
- c. エラーになる
- d. 2つのUPDATEと1つのDELETEがロールバックされる
これは、実行結果を見るのが早いですね。
SQL> CREATE TABLE products 2 (product_id number 3 ,product_name varchar2(30)); 表が作成されました。 SQL> INSERT INTO products VALUES(1,'Tennis Racket'); 1行が作成されました。 SQL> SAVEPOINT a; セーブ・ポイントが作成されました。 SQL> INSERT INTO products VALUES(2,'Tennis Ball Pack 4'); 1行が作成されました。 SQL> SAVEPOINT b; セーブ・ポイントが作成されました。 SQL> ALTER TABLE products ADD (price NUMBER); 表が変更されました。 SQL> UPDATE products set price = 30000 2 WHERE product_id = 1; 1行が更新されました。 SQL> SAVEPOINT c; セーブ・ポイントが作成されました。 SQL> UPDATE products set price = 2000 2 WHERE product_id = 2; 1行が更新されました。 SQL> SAVEPOINT d; セーブ・ポイントが作成されました。 SQL> DELETE FROM products WHERE product_id = 2; 1行が削除されました。 SQL> SQL> ROLLBACK TO SAVEPOINT a; ROLLBACK TO SAVEPOINT a * 行1でエラーが発生しました。: ORA-01086: セーブポイント'A'はこのセッションで設定されていないか、無効です
実行結果のとおり、正解は選択肢cです。
私は、SAVEPOINT（セーブポイント）は「進行中のトランサクションに付箋を貼るイメージ」などと説明するのですが、ROLLBACK時に指定することで「付箋（SAVEPOINT）の位置まで取り消しなさい」と指示することができます。
したがって、本来なら選択肢bのように1つ目のINSERTは残り2つ目のINSERT以降のトランザクションが取り消し（ROLLBACK）されるのですが（したがって、選択肢bは不正解）――そうです、正解した人は気づいていましたよね。
ALTERのようなDDL（データ定義言語）は暗黙のコミット（COMMIT）が含まれるため、2つのINSERTはすでにコミットされており、取り消すことができません。
さらに説明すると、コミットされたということは、そこでトランザクションが終了したということです。つまり、2つのINSERTが実行されたトランザクションは終了し、UPDATE以降のDMLは別のトランザクションであるということです。
冒頭SAVEPOINTの説明で「進行中のトランザクション」と言いましたよね。進行中のトランザクションに「a」というSAVEPOINTは存在しないから、ORA-01086エラーになるのです。「存在しないなら、トランザクションの先頭までROLLBACKしてしまえ（選択肢d）」ということにはなりませんし、DDL操作をROLLBACKすること（選択肢a）はできません。
トランザクションの管理に関する出題は必ずあると思いますので、間違えた人は復習しておいてください。
