DELETING ROWS USING DELETE COMMAND

DELETE command is used to delete rows from a table. The following is the syntax of

DELETE command.DELETE FROM table[WHERE condition;]If WHERE clause is not given then all rows of the table will be deleted.The following command will delete row where CCODE is “c”.
Delete from coursesWhere ccode = ‘c’;It is not possible to delete a parent row while it has child rows. For example, it is not possible to delete a row from COURSES table if the row has dependent rows in BATCHES table or in COURSE_FACULTY table.However, it is possible to delete parent row along with its child rows provided ON DELETE CASCADE option is given at the time of create foreign key constraint in child table.Changes made using INSERT, UPDATE and DELETE are not made permanent until explicitly or implicitly they are committed. See next section for more information.

Transaction :

A transaction is a collection of statements used to perform a single task. These statements are logically related as they perform a single task. All these statements must be executed to successfully complete the task. If any of the statements fails then the all the statements that were executed prior to the statement that failed should be undone otherwise data in the database becomes invalid and inconsistent.

The following example will illustrate the process.

Assume that faculty with code kl (Kevin Loney) is leaving the institute. So all his batches areto be assigned to jc (Jason Couchman). For this the following steps are to be taken.

• Change FCODE of all the batches that are currently being handled by kl to jc.

• Delete rows from COURSE_FACULTY where FCODE is kl.

• Delete row from FACULTY where FCODE is kl.

That means the following are the commands to be executed to perform the above mentioned task.

update batches set fcode = ‘jc’ where fcode = ‘kl’;
delete from course_faculty where fcode = ‘kl’;
delete from faculty where fcode = ‘kl’;

It is important to make sure that all three statements are either successfully completed or all of them are rolled back. To ensure this Oracle provides transaction mechanism.If UPDATE command in the above set of commands begins the transaction then only COMMIT command is given after the second DELETE is executed, the changes are committed. If ROLLBACK command is given then all the changes up to UPDATE will be rolled back. So COMMIT and ROLLBACK command are used to ensure either everything is committed or everything is rolled back.A transaction is a collection of statements which is to be either completely done or not done at all. In other words the process should not be half-done. That means ALL or NOTHING.A transaction begins when previous transaction ends or when the session begins. A transaction ends when COMMIT or ROLLBACK is issued.  A new session starts when you connect to Oracle. For example, when you log on using SQL*PLUS you start a new session. When you exit SQL*PLUS the session is terminated.