This assignment will demonstrate the use of transactions. Specifically, changes
ID: 3736310 • Letter: T
Question
This assignment will demonstrate the use of transactions. Specifically, changes will be rolled back to a previous state prior to performing a commit. The assignment will be composed of SQL Plus DML and DCL statements. Write a script that performs the following.
Login to SQL Plus as user SCOTT
Issue a statement to display the employee number, employee name and salary from the "emp" table
Issue a statement to begin a transaction
Issue statement(s) to insert 4 new records into the "emp" table.
Issue a statement that repeats Step 2 (to verify that all changes have been added).
Issue a statement to rollback the changes
Issue a statement that repeats Step 2 ( to verify that all changes have been undone)
Save the script as Project10.sql
Notes:
The Oracle Universal Installer (OUI) creates the EMP, DEPT, .BONUS, SALGRADE, CUSTOMER, ORD, ITEM, and PRODUCT.
The following query will display all tables defined in a user's schema.
SELECT table_name FROM user_tables;
The EMP table has a FK constraint on the DEPTNO field (i.e. that value must exist in the DEPT table)
Explanation / Answer
Answer:
Project10.sql:
SELECT e.empno, e.ename, e.sal from EMP e;
BEGIN TRANSACTION T1
INSERT INTO emp VALUES(50, 'CHRIS', 'SALESMAN', null,to_date('24-03-2018','dd-mm-yyyy'),50000, null, 30 );
INSERT INTO emp VALUES( 51, 'RON', 'SALESMAN', null, to_date('24-03-2018','dd-mm-yyyy'), 50000, null, 30);
INSERT INTO emp VALUES( 52, 'VICKIE', 'MANAGER', null, to_date('24-03-2018','dd-mm-yyyy'), 50000, null, 30);
INSERT INTO emp VALUES( 53, 'NORMAN', 'MANAGER', 7839, to_date('24-03-2018','dd-mm-yyyy'), 50000, null, 30);
SELECT e.empno, e.ename, e.sal from EMP e;
ROLLBACK TRANSACTION T1
SELECT e.empno, e.ename, e.sal from EMP e;