Committing/Rollbacking Changes
Inserting data is with the syntax: INSERT INTO <table> [<columns,..>] VALUES (value [...]);
Only one row is inserted into a table at one time using this syntax.
For example,
SQL> INSERT INTO employee (empno, name, dept_no) VALUES (1, 'TOM LEE', 4);
Assume the employee table is only of three fields empno, name and dept_no. Then the above statement can be rewritten in a simplified format (by omitting the column list) as:
SQL> INSERT INTO employee VALUES (1, 'TOM LEE', 4);
Let's assume the fourth column of the employee table is NULL-able field called comment, then the SQL statement can be rewritten to explicitly specify the NULL value.
SQL> INSERT INTO employee VALUES (1, 'TOM LEE', 4, NULL);
Let's assume the fifth column of the employee table is a DATE field called hiredate, then the SQL statement can be rewritten to include the SYSDATE function call.
SQL> INSERT INTO employee VALUES (1, 'TOM LEE', 4, NULL, SYSDATE);
To interactively prompt users to enter values for the fields at SQL*Plus terminal, use the following syntax:
SQL> INSERT INTO employee VALUES (&employee_id, &name, &salary, &comment, SYSDATE);
The SQL*Plus terminal then will prompt users to enter values for the three variables employee_id, name, and salary.
To inserting rows by copying from another table, use a subquery.
For example,
SQL> INSERT INTO managers (empno, name, salary)
SELECT empno, name, salary FROM employee WHERE jobtitle="MANAGER";
Note that to insert into a table a large amount data that are stored external files, click here for guide.
To change the data, use the syntax
UPDATE <table name>
SET columname = value [, column name = value ...]
WHERE <condition>;
The WHERE condition specifies what data to be updated. Without WHERE condition all table rows will be updated.
For example,
SQL> UPDATE employee
SET sal = sal + 1000, COMMENT="GOOD PERFORMANCE"
WHERE empno = 53;
Updating from a subquery is possible. For example,
UPDATE employee
SET (jobtitle, sal) = (SELECT jobtitle, sal FROM employee WHERE empno=87)
WHERE empno= 54;
To delete a row from a table, use the syntax
DELETE FROM <table name> WHERE <condition>;
The FROM keyword is often omitted for simplicity.
For example,
DELETE FROM employee WHERE empno=8999;
It is illegal to delete a row that contains a primary key that is a foreign key in another table. Oracle will throw an execution error on this.
For example, attempt to delete a row in the department table with dept_no = 10 is not allowed if the employee table has a foreign key referential constraint in the department table on dept_no column, and some employees in the employee table have the dept_no as 10.
Database transactions end end with the following events:
With COMMIT/ROLLBACK, the server is able to achieve data consistency and allow users to preview the data changes before making the changes permanently.
The COMMIT statement ends the current transaction and makes
permanent any changes made during that transaction. Until you commit the
changes, other users cannot access the changed data; they see the data as it was
before you made the changes. An automatic COMMIT is performed when a DDL
statement is issued or normal exit from SQL*Plus without explicitly issuing
COMMIT or ROLLBACK.
Consider a simple transaction that transfers money from one bank account to another. The transaction requires two updates because it debits the first account, then credits the second. In the example below, after crediting the second account, you issue a commit, which makes the changes permanent. Only then do other users see the changes.
The ROLLBACK statement ends the current transaction and undoes
any changes made during that transaction. Rolling back is useful for two
reasons. First, if you make a mistake like deleting the wrong row from a table,
a rollback restores the original data. Second, if you start a transaction that
you cannot finish because an exception is raised or a SQL statement fails, a
rollback lets you return to the starting point to take corrective action and
perhaps try again. An automatic ROLLBACK is performed when the
abnormal termination of SQL*Plus or system failure.
SAVEPOINT names and marks the current point in the processing of
a transaction. Used with the ROLLBACK TO statement,
savepoints let you undo parts of a transaction instead of the whole transaction.
In the example below, you mark a savepoint before doing an insert. If the
INSERT statement tries to store a duplicate value in the empno
column, the predefined exception DUP_VAL_ON_INDEX is raised. In
that case, you roll back to the savepoint, undoing just the insert.
The following figure illustrates the description of COMMIT, SAVEPOINT and ROLLBACK.
The following commands are the sample use of COMMIT, SAVEPOINT, and ROLLBACK commands.
SQL> UPDATE employee
SET sal = sal + 1000, COMMENT="GOOD PERFORMANCE"
WHERE empno = 53;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> DELETE FROM employee;
49 rows deleted.
SQL> ROLLBACK;
Rollback complete.
SQL> UPDATE .....
SQL> SAVEPOINT UPDATE_pt1
Savepoint created.
SQL> UPDATE ....
SQL> SAVEPOINT UPDATE_pt2
Savepoint created.
SQL> ROLLBACK TO UPDATE_pt1;
Rollback complete.