Guide to Oracle SQL (Buffalo State University Version)
The Data Manipulation Language (DML) statements allow you to use SQL to modify (UPDATE), create (INSERT), combine (MERGE), remove (DELETE), and remove all (TRUNCATE) records in your database tables. I introduced these statements in a previous chapter so I will go into more detail in this chapter.
The INSERT Statement
The INSERT statement is the most common way to popuate a database table. Oracle also provides a SQL*Loader utility which can be used to populate tables with data that is in various type of formats (fixed, variable, etc.). Table have constraints (rules) that govern what type of data can be stored in a record. I will cover constraints in a future chapter.
The DESCRIBE Statement
The INSERT statement uses the fields it is given to place the values in the record. You need to be aware of the data types for each field. Use the DESCRIBE statement to identify the fields and their data types.
This might be a good time to mention one of my guiding normalization principles. Never store unnecessary data. In the faculty table the officephone field is VARCHAR2(20) and is storing hyphens (-). This is what I mean by "unnecessary data". I woud have made that field NUMBER(10) and not required the hyphens to be there duing data entry and the insert process.
The UPDATE Statement
The UPDATE statement uses the criteria on the WHERE clause to modify field values in the record.
The DELETE Statement
The DELETE statement can be used to remove an existing record from a table.
SQL Statement Errors
There are many reasons why SQL statements fail and cause result in a SQL error code being displayed.
Using an INSERT With a SELECT Statement
In a previous chapter I mentioned a scenerio where you need to copy an existing employee record from the employees table to the employee_history table. The next figure illustrates how to copy all the records from on table (countries)into another existing table (countries_copy).
When a DML statement is executed the reqult is a transactoin. Oracle provides a few additional statements that allow you to work with transactions. The COMMIT statement tells SQL to apply any transactions since the last COMMIT. The ROLLBACK tells SQL to undo any transactions since the last COMMIT. You can also use the SAVEPOINT to create a point in the time ine of your transactions to then be able to ROLLBACK to.