Previous | Table of Contents | Next

Page 159

FIG. 7.1
Truncate table syntax.




Listing 7.9L_07_09.SQL—Truncating a Table

SQL> truncate table wge_maint;
Table truncated

Saving the Changes to Your Tables

The DML commands do not permanently change database table records until the commit command is issued. Commit causes the database manager to make the changes permanent. Previous to the commit, the records are marked and the changes wait in temporary storage for the commit to occur. The reason the changes are not made permanent is because Oracle7 gives you the opportunity to undo or eliminate the changes using the rollback command. All changes caused by the DMS commands are temporary between database commits.

NOTE
I once was modifying records in a table. I was working on a Unix platform and had two different sessions going at the same time. I modified a table on one of the sessions. When I ran a select command on the other session against the modified table, I didn't see any of the changes. My first thought was that the update statement did not work. I checked the statement and executed it again. When I ran the select again on the second session, there were no changes. This really had me scratching my head. After a while, I ran the select on the session on which I had performed the update statement. The select statement listing showed the records were all modified.

I was dumbfounded at this turn of events until I remembered I am never issued a commit to save the records permanently. The point is that when you execute DML commands against a table and do not commit the changes, it may appear to you that the table has changed, but it will the changes will not appear to other sessions that may be looking at the table. In addition, if your session is abnormally terminated (such as shutting off your machine before logging off of Oracle7) the changes will not be permanently retained.n

Page 160

To make changes permanent, issue the commit command. Listing 7.10 illustrates the command. It consists of the word commit followed by a semicolon. This causes all records modified by your session since the last commit issued to be permanently recorded. The second way to permanently save the changes is to log off the session. You can autocommit the changes by two more means: issue a create or drop command.

Listing 7.10L_07_10.SQL—Saving Changes Permanently by Using the Commit Command

SQL> commit;

Commit complete.

You can undo the changes by using the rollback command. This command reverses all changes that have occurred since the last commit. The command consists of the word rollback followed by a semicolon. If the commit procedure is inconvenient, you can use the autocommit set command to permanently record the changes every time the DML commands are issued. The normal setting is off. Changing the setting to on makes the changes permanent without having to use the commit.

Summary

Records are added to tables by using the insert command. The command consists of a columns clause and a values clause. You can add multiple records to the table by using a select statement in the values clause. You use the update command to modify records. The command has two parts. The first part is the items to be updated and the new values. The second section is a where clause that identifies the records to be updated. The delete and truncate commands delete records. Delete removes the records from the table and stores a copy in temporary storage for restoration purposes. It also has an optional where clause that identifies the records to be deleted. Truncate removes all the records in the table. You cannot restore these records. Changes to the database are temporary until you issue the commit command or you log off the database. To restore the database, use the rollback command. Rollback restores records since the last commit was issued.

From Here…

This concludes part II of the book. The next section covers Oracle's procedure language called PL/SQL and Oracle's table loader called SQL*LOADER. PL/SQL is an excellent tool used in all the Oracle products to perform special functions. It is used extensively in Oracle Forms to fine-tune the form mechanics. It is also used for data conversion purposes. The SQL*LOADER product loads Oracle tables with records from flat text files. It is used when converting data from an older system into the new Oracle database If you are anxious to get into learning Oracle Forms, you could skip this next section and start with part IV. You need to return to this

Page 161

section, however, at some point. Without an understanding of PL/SQL, you do not fully understand the power of Oracle Forms.

Review Exercises

  1. Insert a record into the employee table for AL Gore. He was born on April 1, 1948. His payroll number is 40, and he makes $18,567 a year in the POL department. His address is 444 S. Main St, Nashville, Tennessee. His employment date is `20-JAN-92'. His social security number is `508-34-8912'. His phone number is 894-123-8765, and his position is `BILL COLLECTOR'.

  2. Insert a record into the employee table for Dan Quayle. He was born Dec. 4, 1947. His payroll number is 41, and he makes $20,456 a year in the POL department. His address is 1600 Pennsylvania Ave, Washington, DC. His employment date is `20-JAN-2000'. His social security number is `405-39-1212'. His phone number is 101-100-0001, and his position is `Chief Executive'.

  3. Create a temporary table with the same columns as the employee table. Insert records for the employees from the Welfare Department in employee table into this temporary table.

  4. Update the wages in the temporary table. Each employee gets a 15 percent raise. Be certain to commit the changes.5.Update the wages in the temporary table again. Give each employee that has never purchased glasses another one percent.

  5. Delete Al Gore's record from the employee table. Check to see whether the record deleted. Perform the rollback command. Check to see whether the record is still deleted. If it hasn't been deleted, delete it again and be certain to commit the change. Perform the rollback. Has the Al Gore's record been deleted?

  6. Update the wages in the employee table with the values contained in the temporary table.

  7. Truncate the temporary table created in problem 2. Perform a rollback. Are there any records in the table?

  8. Drop the temporary table.

Previous | Table of Contents | Next