Mega Code Archive

TRANSACTION CONTROL

SQL> SQL> CREATE TABLE purchase ( 2 product_name VARCHAR2(25), 3 quantity NUMBER(4,2), 4 purchase_date DATE, 5 salesperson VARCHAR2(3) 6 ); Table created. SQL> SQL> SQL> INSERT INTO purchase VALUES ('Small Widget', 1, '14-JUL-03', 'CA'); 1 row created. SQL> INSERT INTO purchase VALUES ('Medium Widget', 75, '14-JUL-03', 'BB'); 1 row created. SQL> SQL> SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL ------------------------- ---------- --------- --- Small Widget 1 14-JUL-03 CA Medium Widget 75 14-JUL-03 BB SQL> SQL> ROLLBACK; Rollback complete. SQL> SQL> SELECT * FROM purchase; no rows selected SQL> SQL> SQL> INSERT INTO purchase VALUES ('Small Widget', 1, '14-JUL-03', 'CA'); 1 row created. SQL> SAVEPOINT a; Savepoint created. SQL> INSERT INTO purchase VALUES ('Medium Widget', 75, '14-JUL-03', 'BB'); 1 row created. SQL> SAVEPOINT sp_2; Savepoint created. SQL> INSERT INTO purchase VALUES ('Product Number', 2, '14-JUL-03', 'GA'); 1 row created. SQL> SAVEPOINT third; Savepoint created. SQL> INSERT INTO purchase VALUES ('Small Widget', 8, '15-JUL-03', 'GA'); 1 row created. SQL> SAVEPOINT final_sp; Savepoint created. SQL> INSERT INTO purchase VALUES ('Medium Widget', 20, '15-JUL-03', 'LB'); 1 row created. SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL ------------------------- ---------- --------- --- Small Widget 1 14-JUL-03 CA Medium Widget 75 14-JUL-03 BB Product Number 2 14-JUL-03 GA Small Widget 8 15-JUL-03 GA Medium Widget 20 15-JUL-03 LB SQL> ROLLBACK TO final_sp; Rollback complete. SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL ------------------------- ---------- --------- --- Small Widget 1 14-JUL-03 CA Medium Widget 75 14-JUL-03 BB Product Number 2 14-JUL-03 GA Small Widget 8 15-JUL-03 GA SQL> ROLLBACK TO third; Rollback complete. SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL ------------------------- ---------- --------- --- Small Widget 1 14-JUL-03 CA Medium Widget 75 14-JUL-03 BB Product Number 2 14-JUL-03 GA SQL> ROLLBACK TO sp_2; Rollback complete. SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL ------------------------- ---------- --------- --- Small Widget 1 14-JUL-03 CA Medium Widget 75 14-JUL-03 BB SQL> ROLLBACK TO a; Rollback complete. SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL ------------------------- ---------- --------- --- Small Widget 1 14-JUL-03 CA SQL> ROLLBACK; Rollback complete. SQL> SELECT * FROM purchase; no rows selected SQL> SQL> SQL> drop table purchase; Table dropped.