Database

Chapter 5



Data Manipulation and Transaction Control



At a Glance



□ Chapter Overview

□ Chapter Objectives

□ Chapter Notes

□ Key Terms





Chapter Overview


This chapter introduces data management by presenting the INSERT, UPDATE, and DELETE data manipulation language (DML) commands available in Oracle 11g to enter, change, and delete data.The chapter concludes with a discussion of transaction control.



Chapter Objectives


After completing this chapter, you should be able to do the following:

□ Use the INSERT command to add a record to an existing table
□ Manage virtual columns in data manipulations
□ Use quotes in data values
□ Use a subquery to copy records from an existing table
□ Use the UPDATEcommand to modify a table’s existing rows
□ Use substitution variables with an UPDATE command
□ Delete records
□ Manage transactions with the transaction control commands COMMIT, ROLLBACK, and SAVEPOINT
□ Differentiate between a shared lock and an exclusive lock
□ Use the SELECT . . . FOR UPDATE command to create a shared lock



Chapter Notes


Inserting New RowsNew rows can be added to a table using the INSERT command. If the actual data values to be entered are provided, the data are listed in a VALUES clause. Unless data for every column is provided and the data is listed in the same order as the data is stored in the database table (use DESC to verify), a column list must be specified in the INSERT INTO clause. In addition, a column must be ignoredin the column list or the keyword DEFAULT used for the column value if a DEFAULT value assignment is desired.

[pic]

If the data is to be copied from an existing table, the VALUES clause is omitted and the subquery is inserted after the name of the destination table. In addition, the subquery is not required to be enclosed in parentheses.

Modifying Existing Rows

The UPDATE command isused to change data in existing rows. This can include changing existing values or replacing a NULL value with an actual data value. The SET clause is used to specify the column to be changed and the new value to be assigned to the column. If more than one column within a row needs to be changed, each column and its new value can be listed in the SET clause, separated by commas. The WHERE clausecan be included with the UPDATE command to specify which rows should be changed. If the WHERE clause is omitted, every row in the table will be updated with the change.

[pic]

Substitution Variables

Rather than a user having to constantly re-enter the INSERT or UPDATE command to make changes to rows, a script can be created and substitution variables can be entered for the data value(s).When a command includes a substitution variable, the command (or script) becomes dynamic (i.e., allows it to be changed with each execution). A substitution variable is identified by an ampersand (&) in front of the name of the variable. When the ampersand is encountered, the Oracle server will either prompt the user for the value to be assigned to the variable or will receive the necessary datavalue via an application program. Substitution variables are used in application program interfaces with the database.


Deleting Rows

The DELETE command is used to remove rows from database tables. The WHERE clause is used to specify which row(s) should be removed. If the WHERE clause is omitted, all rows will be deleted from the specified table.


Transaction Control Statements

DMLoperations are not permanently updated to the tables until the data has been committed. Once the data has been committed, it becomes viewable by other users. This provides a consistent view of the database to all database users. In addition, it makes it easier to “undo” undesired changes because the user also has the option of entering the ROLLBACK command to erase any uncommitted changes. In...