Page 151
To add records to a relational table, you use the insert SQL command. The command has three parts. It begins with the word insert. This is followed by a list of the table's columns that will be populated by the statement. The column name is separated by commas and enclosed by parentheses. The third section contains the list of values that will populate the columns listed in the previous section. The values are enclosed by parentheses and separated by commas. The statement must have the same number of values as columns to be populated. The statement need not contain all the table's columns. It must, however, contain all columns that have the not null constraint. String values must be enclosed by a single quotation mark and numeric values do not contain single quotation marks.n
Page 152
Listing 7.1 illustrates the insert command. You might notice that the value section has a
character string that will be placed into the birth_date column. The statement performs
the to_date function, automatically converting the character string into a numeric date value.
This occurs so long as the character string is in the default date format of `dd-mon-yy'. If the
string value has a different format than the default, the last expression in the
values clause must be enclosed by the to_char function. The function must have the proper date picture.
The following example populates the record with the wrong birth date. Because the default picture or format does not contain the century, Oracle populates the century in the column with the current century. If the record was inserted before the year 2000, the birth date would be `02-FEB-1909'. If it is populated in the twenty-first century, the birth date will be `02-FEB-2009'. Because Abraham Lincoln was born in the nineteenth century, the statement must use the to_char function with a date format of `dd-mon-yyyy'. You must also change the string value to `02-FEB-1809'.
Listing 7.1L_07_01.sqlInserting a Record into the Employee Table
SQL> insert into employee (payroll_number, last_name, 2 first_name, fk_department, birth_date) 3 values (16, `LINCOLN', `ABRAHAM', `POL', 4 to_date('02-FEB-1809', `DD-MON-YYYY')); 1 row created.
Listing 7.1's insert statement adds one row or record to the table. Sometimes you want to add a number of rows to the table. If the rows are contained in another Oracle table, you can replace the values clause with a select statement. An insert statement that contains a select statement adds a row to the table for each row fetched or obtained by the select statement.
Listing 7.2 illustrates the insert command using a select statement. Each employee in the Welfare Department was given a personal organizer. The department manager wanted to record the cost as a tool. The statement identifies the employees from the employee file and adds a record into the tool table for each selected employee. The select clause enables you to modify data that will be inserted into the table.
NOTE |
You can use all the functions identified in Chapter 4, "Formatting Reports in SQL*PLUS," to modify data in the row. I use this command whenever I convert data from an old system into a new one. One problem that always seems to hit me is converting alphanumeric data into numbers. The original data always has an alpha character in it or is longer than the field it will be moved into. So be certain to check out the data before inserting it into the new table.n |
Page 153
Listing 7.2L_07_02.sqlAdding Multiple Records to a Table by Using a Select Statement in Place of a Values Clause
SQL> insert into tools (fk_payroll_number, tool_name, 2 purchase_date, payroll_deduct, tool_cost) 3 select payroll_number, `ORGANIZER', 4 sysdate, `N', 89.95 5 from employee 6 where fk_department = `WEL'; 6 rows created. SQL> select fk_payroll_number, tool_name, purchase_date 2 from tools where tool_name = `ORGANIZER'; FK_PAYROLL_NUMBER TOOL_NAME PURCHASE_ ----------------- --------------- --------- 35 ORGANIZER 23-AUG-97 34 ORGANIZER 23-AUG-97 26 ORGANIZER 23-AUG-97 23 ORGANIZER 23-AUG-97 20 ORGANIZER 23-AUG-97 19 ORGANIZER 23-AUG-97 6 rows selected.
You can modify values contained in items or columns of a table by using the update command. This statement also has three parts. The first part contains the name of the table that contains the records; the second is the name of the columns to be updated along with the value(s) to place in the column; and the third is an optional conditional statement that identifies the subset of records to update.
Listing 7.3 illustrates a simple update statement that changes the values in the last_name column of the employee table. The statement causes the first character to be uppercase and the remainder lowercase. Because this statement does not have the optional conditional clause, all records in the table are updated.
Listing 7.3L_07_03.sqlUpdate All the last_name Values in the Employee Table
SQL> update employee 2 set last_name = initcap(last_name); 19 rows updated.
continues
Page 154
Listing 7.3Continued
SQL> select last_name from employee; LAST_NAME --------------- Taft Roosevelt Anthony Roosevelt Coolidge Johnson Reagan Bush Johnson Clinton Carter Ford Nixon Kennedy Eisenhower Truman Roosevelt Hoover Wilson 19 rows selected.
The conditional clause of the statement is the same as the where clause used in select statements. The select statement was discussed in Chapter 4. The clause follows the same rules, uses the same operators, and produces the same subset of records. Listing 7.4 illustrates the use of the where clause to update a set of subset of records. The statement changes the value in the first_name column to lower case when the last_name equals `ROOSEVELT.' Notice that SQL*PLUS returns a value indicating the number of updated records. This is a useful indicator, telling you whether the update statement actually worked.
Listing 7.4L_07_04.SQLUpdating Specific Records Through the Use of a Where Clause
SQL> update employee 2 set first_name = initcap(first_name) 3 where last_name = `Roosevelt'; 3 rows updated. SQL> select first_name, last_name from employee 2 where last_name = `Roosevelt'; FIRST_NAME LAST_NAME --------------- ---------------