TRANSACTION CONTROL : Transaction Rollback Commit « Table « Oracle PL / SQL

1. Oracle PL / SQL
2. Table
3. Transaction Rollback Commit

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.

   
    
  

Related examples in the same category