Page 142
The following are the various ways in which a column's value is set to NULL:
You can explicitly set a column to NULL in an INSERT statement as shown Listing 6.19.
Listing 6.19. Setting a column value to NULL in an INSERT.
SQL> insert into Instructor
2 (Instructor_ID, Department_ID, Last_Name, Telephone)
3 values
4 (`P331', `ANTHRO', `POULSON', NULL);
1 row created.
You can use the UPDATE statement to set a column's value to NULL. Listing 6.20 presents an example. After you have set the telephone number for Instructor ID Y561 to NULL, you can verify that Oracle has indeed made the change.
Listing 6.20. Setting a column value to NULL in an UPDATE.
SQL> select Last_Name, Telephone
2 from Instructor
3 where
4 Instructor_ID = `Y561';
LAST_NAME TELEPHONE
------------------------ ----------
YOUNG 8055550123
SQL> update Instructor
2 set
3 Telephone = NULL
4 where
5 Instructor_ID = `Y561';
1 row updated.
SQL> select Last_Name, Telephone
2 from Instructor
Page 143
3 where 4 Instructor_ID = `Y561'; LAST_NAME TELEPHONE ------------------------ ---------- YOUNG
One of the complaints about SQL is its inconsistent syntax. In the UPDATE statement, a NULL value is assigned to a column with the equal sign, (=). However, in the WHERE clause of a SELECT statement, use the word IS (or IS NOT) rather than an equal sign to test for a NULL value for a column. Therefore, you can wind up with UPDATE statements that look like what is shown in Listing 6.21.
Listing 6.21. Illustration of confusing SQL syntax.
SQL> update Student
2 set Fax = NULL
3 where
4 Fax is not null;
3 rows updated.
When you create a table, specify a column as mandatory by adding NOT NULL after you name the column's datatype. A mandatory column must be assigned a value each time a row is inserted into a table. If you try to insert a row without specifying a value for a mandatory column, Oracle returns an error message. For instance, every course at Flugle College must have a Course ID, be associated with a particular department, and have a title. Listing 6.22 contains an INSERT statement that attempts to add a row to the Course table without specifying a value for the Title column, which is defined as NOT NULL.
Listing 6.22. Value not specified for a mandatory column in an INSERT.
SQL> insert into Course
2 (Course_ID, Department_ID)
3 values
4 (`901', `ANTHRO');
insert into Course
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
Page 144
Unfortunately, Oracle doesn't indicate the mandatory column (or columns) that need to be referenced in the INSERT statement. When you see an ORA-01400 error code, you have to compare the column list in the INSERT statement with the table definition.
Another powerful concept that you need to master is the transaction.
A transaction is defined as a logical unit of work—a set of database changes to one or more tables that accomplishes a defined task.
A transaction begins after a COMMIT statement, a ROLLBACK statement, or an initial Oracle connection. A transaction ends when any of the following events occurs:
For example, suppose that Flugle College wants to increase the units for all courses by 50 percent. This can be accomplished with a single UPDATE statement that affects all the rows in the Course table.
You can think of a transaction as a change you make in a document using your favorite word processor. You may make several changes and either undo them or exit the program without saving the changes. When you instruct the word processor to save the file, you are permanently changing the file stored on disk.
Committing a transaction is similar to saving a file in Microsoft Word. The COMMIT statement commits a transaction. The COMMIT statement makes permanent all the database changes made since the execution of the previous COMMIT (or ROLLBACK). You can COMMIT only the database changes that you personally have made; the COMMIT statement that one user issues has no effect on another user's database changes.
Similarly, the ROLLBACK statement does the same thing that an Undo command does in a word processor, with one major exception: the ROLLBACK statement will undo all database changes made by the user since the last committed transaction or since the beginning of the session.
Look at the interplay between COMMIT and
ROLLBACK. Listing 6.23 illustrates this concept. A simple query from table_1 returns four rows. If you delete the rows from table_1 and
then query table_1 after you delete them, the query returns no rows. If you issue a
ROLLBACK and query the table again, you see that the table is restored to the state in which it existed
before you issued the DELETE statement.
Page 145
Listing 6.23. Demonstration of COMMIT and ROLLBACK.
SQL> select * from table_1;
TABLE_1_COL
-----------
99
99
99
99
SQL> delete from table_1;
4 rows deleted.
SQL> select * from table_1;
no rows selected
SQL> rollback;
Rollback complete.
SQL> select * from table_1;
TABLE_1_COL
-----------
99
99
99
99
| NOTE |
Oracle performs an automatic commit for DDL statements such as CREATE TABLE. Also, any changes you make to the database are automatically committed after you enter a DDL statement. A ROLLBACK statement does not remove a table created via a CREATE TABLE statement. If you want to eliminate a table, you must use the DROP TABLE statement. |
For transactions that involve the execution of multiple SQL statements, you might want to consider using savepoints as intermediate steps for the transaction. A savepoint is a label within a transaction that contains a subset of the changes made by the transaction. Listing 6.24 demonstrates how a savepoint is declared; you name a savepoint null_fax_numbers.
NEW TERM
You can think of a savepoint as a label within a transaction that references a subset of a transaction's changes.
Listing 6.24. Declaring a savepoint.
SQL> savepoint null_fax_numbers; Savepoint created.