Chapter 6, "The Basics of SQL," briefly talked about the Data Manipulation Language (DML) facet of SQL and delved into the use of the SELECT statement. The SELECT statement only looks at the contents of tables; it cannot create or modify data. This chapter considers the three remaining DML statements--;INSERT, UPDATE, and DELETE--;in Personal Oracle7.
This chapter also covers some topics that are critical in multiuser database applications, rather than in a single-user database like Personal Oracle7. Although Personal Oracle7 cannot function as a database server, it is an excellent platform for database developers for two reasons:
In the course of developing an application for Personal Oracle7, you will probably be using a front-end tool, such as Oracle Forms, Visual Basic, or PowerBuilder, in which the application development environment will internally generate many INSERT, UPDATE, and DELETE statements. However, almost every application will require the development of scripts containing SQL statements.
Chapter 5, "Your First Personal Oracle7 Database," explained the three perspectives of a database: the user perspective, the conceptual perspective, and the physical perspective. The best way to fully comprehend how SQL modifies data is to focus on the conceptual perspective. Think only of tables, columns, and rows, and you'll master SQL and Personal Oracle7 quickly. Initially, don't worry about how Oracle executes SQL statements; instead, concentrate on the purpose of the SQL statement.
Here's another helpful hint for successful use of DML. When you think about the effect of a SQL statement (INSERT, UPDATE, or DELETE), visualize a set of rows, rather than individual rows, being affected.
The first things that many programmers and developers want to learn about Oracle are the internal operating system file formats and special codes. However, this approach is wrong for the following reasons:
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 accomplish a defined task. A transaction begins after a COMMIT statement, a ROLLBACK statement, or an initial Oracle connection. A transaction ends when one of the following events occurs:
For example, suppose Mishuga Electronics has just introduced an amplifier that provides twice the performance of existing amps for the same price. Consequently, the market value of existing amplifiers will drop by 50 percent. If you need to keep track of the market value of Mishuga's products, you can change the market value for Mishuga's older amplifiers with a single UPDATE statement.
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 only COMMIT the database changes that you make; the COMMIT statement that Jim Helmholtz issues has no effect on Max Gilroy's database changes.
Similarly, the ROLLBACK statement is comparable to the Undo command 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.
To illustrate the interplay between COMMIT and ROLLBACK, I'll use a simple table--;table_1--;with a single column--;table_1_col. Initially, table_1 has four rows.
SQL> select * from table_1;
TABLE_1_COL
-----------
99
99
99
99
If you delete the rows from table_1 and then query table_1 after you delete them, you see that the table is empty.
SQL> delete from table_1; 4 rows deleted. SQL> select * from table_1; no rows selected
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.
SQL> rollback;
Rollback complete.
SQL> select * from table_1;
TABLE_1_COL
-----------
99
99
99
99
NoteOracle performs an automatic commit for DDL statements such as CREATE TABLE. Also, any changes that you make to the database will be automatically committed after you enter a DDL statement. A ROLLBACK statement will 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. You can think of a savepoint as a label within a transaction that references a subset of a transaction's changes. You can establish a savepoint in the following way:
SQL> savepoint null_fax_numbers; Savepoint created.
You use a savepoint along with a ROLLBACK statement; the savepoint gives you the option of rolling back a transaction to an intermediate point (a savepoint). The syntax for the ROLLBACK statement is
ROLLBACK [TO savepoint]; where savepoint is a previously named savepoint.
Consider an example. Imagine that your application has a transaction that updates three tables: table_1, table_2, and table_3. If Oracle returns an error on the update to table_2, you can roll back to the first savepoint, which is table_1_update.
SQL> update table_1
2 set table_1_col = 11;
4 rows updated.
SQL> savepoint table_1_update;
Savepoint created.
SQL> delete from table_2;
3 rows deleted.
SQL> rollback to table_1_update;
Rollback complete.
SQL> select * from table_2;
TABLE_2_COL
-----------
99
99
99
You should use savepoints with care, however, because they add an additional layer of complexity to an application. Be sure that your transactions are well-defined before you decide to implement savepoints.
The INSERT statement adds rows to a table. You supply literal values or expressions to be stored as rows in the table.
The INSERT statement takes two forms. The first form is
INSERT INTO table_name [(column_name[,column_name]...[,column_name])] VALUES (column_value[,column_value]...[,column_value])
where table_name is the table in which to insert the row, column_name is a column belonging to table_name, and column_value is a literal value or an expression whose type matches the corresponding column_name.
For instance, if you want to add a new depot to the Depot table, the INSERT statement might be
SQL> insert into Depot 2 (Depot_ID, Company_Name) 3 values 4 (2001, 'Minivox Electronics'); 1 row created.
Notice that the number of columns in the list of column names must match the number of literal values or expressions that appear in parentheses after the keyword VALUES. The example that follows has three columns but only two literal values. If you specify more column names than values, Oracle returns the following:
SQL> insert into Depot 2 (Depot_ID, Company_Name, Street_Address) 3 values 4 (3001, 'Consumer Electronic Depot'); values * ERROR at line 3: ORA-00947: not enough values
On the other hand, if you specify fewer column names than values, Oracle returns the following error message:
SQL> insert into Depot 2 (Depot_ID, Company_Name) 3 values 4 (3001, 'Consumer Electronic Depot', '2323 Main St.'); values * ERROR at line 3: ORA-00913: too many values
If a column name referenced in an INSERT statement is misspelled, the following occurs:
SQL> insert into Customer
2 (Customer_ID, Street_Adress)
3 values
4 (4001, '123 Main St.');
(Customer_ID, Street_Adress)
*
ERROR at line 2:
ORA-00904: invalid column name
NoteIf you're executing an INSERT with a long list of column names and Oracle returns ORA-00947 or ORA-00913, it's your responsibility to do the dirty work of matching the list of column names with the list of values or expressions. After you've checked and still can't find the problem, try reducing the number of columns and values to isolate the problem.
Each column value supplied in an INSERT statement must be one of the following:
In an INSERT statement, you can mix literal values with expressions. In the following example, the Initial_Retail_Value of $275 is increased by 10 percent:
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 ('D301','SEN101',275*1.10);
1 row created.
With a few exceptions, the datatypes for a column and its corresponding value must be identical. Inserting an alphanumeric string into a numeric column doesn't make any sense, as shown in the following example:
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Initial_Retail_Value)
3 values
4 (1020, 'SEN101', 'Two hundred and fifty dollars');
(1020, 'SEN101', 'Two hundred and fifty dollars')
*
ERROR at line 4:
ORA-01722: invalid number
In the preceding example, Oracle returned an error code ORA-01722 because the string 'Two hundred and fifty dollars' cannot be stored in a column defined as a number. However, if the string is a valid number, the INSERT statement will be processed successfully, as in the following example:
insert into Product (Product_ID,Manufacturer_ID,Initial_Retail_Value) values (1020,2001,'347.42'); 1 row created.
Another exception to this rule involves strings and dates. For example, you can insert a literal string that adheres to the Oracle default date format (DD-MON-YY) into a date column.
SQL> insert into Product
2 (Product_ID, Manufacturer_ID, Description, Date_of_Manufacture)
3 values
4 ('TR901','TES801','Tuner','01-JAN-91');
1 row created.
Oracle provides a set of functions called pseudocolumns. Oracle named these functions pseudocolumns because to the uninitiated they appear to be columns. Two commonly used pseudocolumns are USER[ms]the Oracle user who is currently connected to the Oracle database
SYSDATE[ms]the current date and time
You can use pseudocolumns in an INSERT statement to assign a value to a column. For instance, USER and SYSDATE can store the name of the Oracle user who created the row and the date and time when the row was inserted.
insert into Product (Product_ID,Manufacturer_ID,Initial_Retail_Value,Created_By,Created_Date) values (1020,2001,275.00*1.10,USER,SYSDATE);
You can assign the pseudocolumn USER only to a string column. Similarly, you can assign the pseudocolumn SYSDATE only to a date column. You can manipulate these pseudocolumns by applying functions and operators. For example, you could insert the first four characters of the current user's name into a string column by using USER as an argument to the SUBSTR function in the following way:
SQL> show user
user is "FRAYED_WIRES"
SQL>
SQL> insert into Customer
2 (Customer_ID, Last_Name)
3 values
4 (2222, SUBSTR(USER,1,4));
1 row created.
SQL> select Customer_ID, Last_Name
2 from Customer
3 where Customer_ID = 2222;
CUSTOMER_ID LAST_NAME
----------- ------------------------------
2222 FRAY
If you look carefully at the syntax diagram for the INSERT statement in the online Oracle SQL Language Reference Manual, you'll notice that the column list is an optional element. Therefore, if you don't specify the column names to be assigned values, Oracle will, by default, use all of the columns. In addition, the column order that Oracle uses is the order in which the columns were specified when the table was created; this order is the same order that you see when you apply the DESCRIBE command to a table in SQL*Plus. Look at the following example:
insert into Depot values (101,'RCB ELECTRONICS SERVICE CENTER','123 Alvarado Ave.', 'San Felipe','CA','90000','2135550123','2135559876', 'RICHARD HEINZ'); 1 row created.
On the surface, everything looks fine--;the row was successfully inserted into the table. However, several dangers may result from this syntax.
Up to this point, the examples have demonstrated how each execution of an INSERT statement can add a single row to a table. The following examples demonstrate how to perform an INSERT without specifying literal values.
The alternative form of the INSERT statement replaces the list of column values with a SELECT statement. The syntax is
INSERT INTO table_name [(column_name[,column_name]...[,column_name])] select_statement
where table_name is the table in which to insert the row, column_name is a column belonging to table_name, and select_statement is a valid SELECT statement.
Suppose that, as part of a new marketing campaign, you want to insert rows from the Inactive_Customer table into the Customer table. To accomplish this task, you use a subquery to select the rows from the Inactive_Customer table and insert them into the Customer table with the following statement:
SQL> insert into Customer 2 (Customer_ID, Last_Name, First_Name, Salutation) 3 select Customer_ID, Last_Name, First_Name, Salutation 4 from Inactive_Customer; 5 rows created.
To use a subquery with an INSERT statement, the number of columns referenced in the INSERT statement must equal the number of items in the subquery's select list.
When you develop a database application, you'll need data to test the software. Developing a sizable set of test data can be a tedious task. Fortunately, you can use the INSERT statement to duplicate and increase the size of the test data.
You can use a subquery to copy the existing rows in a table to the same table. Suppose that the Product table initially contains 12 rows. If you perform an INSERT that SELECTs those 12 rows, your table will grow to 24 rows. If you perform the same INSERT once more, the Product table will end up with 48 rows. As you can see, the number of rows will double each time you perform an INSERT.
SQL> insert into Product 2 (Product_ID, Manufacturer_ID, Initial_Retail_Value) 3 select Product_ID, Manufacturer_ID, Initial_Retail_Value 4 from Product; 12 rows created.
Of course, this method won't generate realistic test data: the primary key (Product_ID and Manufacturer_ID) cannot have duplicate values. But you can circumvent that problem by using an expression in the SELECT statement. For example, the following code shows you how to concatenate two substrings to form a value for Product_ID and Manufacturer_ID that doesn't already exist in the table:
SQL> insert into Product 2 (Product_ID, Manufacturer_ID, Initial_Retail_Value) 3 select SUBSTR(Product_ID,1,3) || SUBSTR(Product_ID,1,3), 4 SUBSTR(Manufacturer_ID,1,3) || SUBSTR(Manufacturer_ID,1,3), 5 Initial_Retail_Value * 1.1 6 from Product; 12 rows created.
NoteIf you use the technique outlined here to generate test data, the number of rows will grow geometrically. If you start with 100 rows, the table will hold 12,800 rows after INSERT executes seven times. If you don't perform a COMMIT after each INSERT, the rollback segments may not be able to store the uncommitted transaction and Oracle will return an error code of ORA-01653; the tablespace in which the rollback segments were trying to allocate more space will appear in the error message.
If you want to modify existing data in your Personal Oracle7 database, you'll need use the UPDATE statement. This statement enables you to update zero or more rows in a table.
Like the INSERT statement, the syntax of the UPDATE statement is far simpler than that of the SELECT statement. The basic syntax of the UPDATE statement is
UPDATE table_name
SET column_name = expression [, column = expression] ...
[, column = expression]
[WHERE condition]
where table_name is the table to be updated, column_name is a column in the table being updated, expression is a valid SQL expression, and condition is a valid SQL condition.
As you can see, the UPDATE statement references a single table and assigns an expression to at least one column. The WHERE clause is optional; if an UPDATE statement doesn't contain a WHERE clause, all rows in the table will have the column in question set to the specified value.
As the syntax for the UPDATE statement illustrates, an UPDATE statement can change the value for more than one column in a table. For example, the following UPDATE statement assigns values to two columns: Salutation and Last_Name.
SQL> update Inactive_Customer 2 set 3 Salutation = 'Mister', 4 Last_Name = 'Johnson'; 5 rows updated.
One way to demonstrate that SQL is set oriented is to look at an UPDATE statement that exchanges the values between two columns. The following query shows the current values for Customer_ID, Home_Telephone_Number, and Fax_Number in the Customer table.
SQL> select Customer_ID, Home_Telephone_Number, Fax_Number
2 from Customer
3 order by Customer_ID;
CUSTOMER_ID HOME_TELEP FAX_NUMBER
----------- ---------- ----------
1001 7145550123
1002 6025553833 6025553811
1003 5035551283
1004 8015558194
1005 3015558331 3015556020
1006 8085558183
1007 6025551384
1008 8105558356
1009 8105554199 8105553535
9 rows selected.
You can swap the telephone and fax numbers in the Customer table with a single UPDATE statement. You do not need to store the voice and fax numbers in temporary variables as you would if you were using a programming language to swap these columns.
SQL> update Customer 2 set 3 Home_Telephone_Number = Fax_Number, 4 Fax_Number = Home_Telephone_Number; 9 rows updated.
Another look at the Customer table shows that the swap of the two columns was successful.
SQL> select Customer_ID, Home_Telephone_Number, Fax_Number
2 from Customer
3 order by Customer_ID;
CUSTOMER_ID HOME_TELEP FAX_NUMBER
----------- ---------- ----------
1001 7145550123
1002 6025553811 6025553833
1003 5035551283
1004 8015558194
1005 3015556020 3015558331
1006 8085558183
1007 6025551384
1008 8105558356
1009 8105553535 8105554199
9 rows selected.
SQL> rollback;
Rollback complete.
The DELETE statement removes rows from a table. With Personal Oracle7, you don't need to know the physical ordering of the rows in the table to perform a DELETE. Oracle uses the criteria in the WHERE clause to determine which rows to delete; the Oracle database engine will determine the internal location of the rows.
The DELETE statement is the simplest of the four DML statements. The syntax is
DELETE FROM table_name [WHERE condition]
where table_name is the table to be updated and condition is a valid SQL condition.
If you think that the SQL syntax is inconsistent, you're correct. For example, the syntax for the UPDATE statement (UPDATE table_name) differs from the syntax of the DELETE statement (DELETE FROM table_name). You'll find many other idiosyncrasies in SQL, and they aren't going to go away soon. If you want to take advantage of the power in SQL, concentrate on learning the syntax and working through a lot of examples.
In designing an application, you may need to delete all the rows in a table. If the table has many rows, using a DELETE to accomplish this task can be quite inefficient. As an alternative, you should consider using the TRUNCATE TABLE statement. The TRUNCATE TABLE statement deletes all rows in a table without writing the deleted rows so that the deletion can be undone. Consequently, the TRUNCATE TABLE statement deletes rows much faster than the DELETE statement.
The TRUNCATE TABLE statement is typically used in the following way:
TRUNCATE TABLE table_name
One caveat: the TRUNCATE TABLE statement is not a DML statement. Therefore, if you issue a TRUNCATE TABLE statement, you cannot change your mind and perform a rollback to recover the lost rows; the TRUNCATE TABLE statement is a one-way trip.
If you're designing a database application, you want a system that will support multiple, concurrent users. To successfully implement such a system, however, two or more users should not be able to modify the same record at the same time. With Oracle, you can lock one or more rows via the SELECT FOR UPDATE statement.
The syntax for the SELECT FOR UPDATE statement is almost identical to that of the SELECT statement except for the keywords FOR UPDATE that are appended to the SELECT statement.
TipThe best time to incorporate multiuser support is when you're building the prototype, rather than waiting until your application is "upsized" to a multiuser platform.
A significant characteristic of an information system is the capability to provide concurrent access to multiple users. Of course, you would also want to be sure that one user could not step on another user's changes. For example, if Jean Smith is making changes to repair number 2013, Max Gilroy should not be able to change the status of repair number 2013 until Jean Smith has committed her changes.
By the same token, the fact that Smith is changing repair number 2013 should not prevent anyone from changing a different repair number.
In a multiuser Oracle environment, Oracle provides "read consistency" at the SQL statement level. Read consistency means that a single SQL statement cannot return results that are contradictory or inconsistent. The following example illustrates this concept.
If you want to know the initial retail value, the current used value, and the difference between the two values for all products, you would issue the following query:
select Initial_Retail_Value, Current_Used_Value,
Initial_Retail_Value - Current_Used_Value
from Product;
Assume that Jean Smith is modifying the current used value for a product while the previous SELECT statement is executing. Statement-level consistency means that the previous query will never return a row in which the actual difference for Initial_Retail_Value and Current_Used_Value does not equal the returned difference. Depending on when Jean commits her changes, you will either see the change or you won't, but you will never see a partial change manifested in a single SQL statement.
However, even though Oracle provides consistency within a single SQL statement, its default behavior doesn't guarantee read consistency during more than one statement. If you query a table twice, you may obtain different results the second time if another Oracle user has changed the table in between your first and second queries. Of course, read consistency is only applicable to an Oracle database that is supporting multiple users, such as the Oracle Enterprise Server or the Oracle Workgroup Server.
You may encounter a situation in which you need more than single-statement read consistency. The following statement provides read consistency across a particular transaction:
set transaction read only;
This topic is discussed in Chapter 17, "Defining Transactions."
A column's value is set to NULL in the following ways:
An INSERT statement can explicitly set a column's value to NULL, for example:
insert into Customer ( Customer_ID, Last_Name, First_Name, Fax_Number) values (2002, 'Smith', 'Wendy', NULL);
An UPDATE statement can set a column's value to NULL, for example:
update Product set Current_Used_Value = NULL where Manufacturer_ID = 'TES801';
After you have set the current used value to NULL for the specified rows, you can verify that Personal Oracle7 has indeed made the change.
select Product_ID, Manufacturer_ID, Description from Product where Manufacturer_ID = 'TES801' and Current_Used_Value is NULL;
When you create a table, you 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, the following INSERT statement attempts to add a row to the Product table without specifying a value for the Product_ID column, which is defined as NOT NULL.
SQL> insert into Product
2 (Manufacturer_ID, Description)
3 values
4 ('SEN101','GPS Receiver');
insert into Product
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
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'll have to compare the column list in the INSERT statement with the table definition.
To explicitly set a column in an existing row to NULL, you must use the UPDATE statement. For example, you can set all customer's fax numbers to NULL with the following statement:
SQL> update Customer 2 set Fax_Number = NULL; 9 rows updated.
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, instead of using an equal sign, you use the word IS (or IS NOT) to test for a NULL value for a column. Therefore, you can wind up with UPDATE statements that look like this:
SQL> update Customer 2 set Fax_Number = NULL 3 where 4 Fax_Number is not NULL; 3 rows updated.
You can manipulate the contents of Personal Oracle7 tables by using these three statements:
You can also use a subquery in conjunction with these statements.
A database transaction is a set of changes to one or more database tables that constitutes a logical unit of work. You use the COMMIT statement to make the transaction permanent. Alternatively, you use the ROLLBACK statement to rescind the transaction.