Table maintenance revisited (again)
This presentation will apply variable concepts to file maintenance.
Data Manipulation Language (DML) is the name given to the code that is used to maintain a table by adding, changing and deleting.
CREATE TABLE ...
This command allows you to create a new table with the same format as an existing table. Depending on the selection of columns and rows you can either have exactly the same data or change the data. I did a SELECT * which means I took all of the columns, however in the WHERE clause, I excluded some of the rows.
Remember the sysdate takes today's date from the system and since we are currently in June of 2000 the 00 has the 20 as part of the stored date.
Null puts a null value in the Dept and Cost. Remember that null is not the same as spaces and zero. When you need to have null appear as a value you can use the NVL function.
Making an INSERT that takes user entry means that it far more usable. It can be saved and reused whenever data entry is required. This saves on recoding time.
This allows me to save the file for future use so that I can simply execute the file and respond to the prompts for data.
The @ means execute or run the saved sql file. In this example, I saved it on the A drive.
Note: This information is not covered on the accompanying handout. I have included it in response to questions from other classes.
In this example, I am creating a new table called movemain00 with the structure shown. The datatypes and size match the corresponding datatypes and size in maintain00 for the columns that are being created.
In this slide, I am taking selected rows from the maintain00 table and inserting them into the new movemain00 table.
Clearly I can only move information for the four columns. I can also select specific records using the WHERE.
Note that in the example on the notes I did not reverse price and cost so the cost went in as the price and the price went in as the cost. I might go back to the notes and fix it, but then again, the error makes a point. If it looks right in the notes, I went back and fixed it!!!
INSERT INTO can take data from previously defined tables as shown here. This INSERT deals with specified columns and only those columns for a particular row.
In this UPDATE, the inner select is executed first. It finds the row in maintain00 where the idno = 123 and retrieves the dept which is TY.
Then the UPDATE which is outer, sets the itemdept = TY where the itemnum on the movemain00 table (the table that is being updated) is equal to 234.
I am largely following the notes on this slide show because there are some areas where previous classes had questions and I am using the slide show to clarify.
The main difference between this update and the update on the previous slide is that this one works with only one table and the previous one used two tables.
Note that insertmain00 worked. The .sql is not required since it is the default.
DELETE FROM is the command to delete records/rows from a table.
The commit statement commits what you have done in the database. After doing a commit all add, changes and deletes have been committed to the table.
Rollback can be used to reverse activities such as adds, changes and deletes.
In this example, rollback goes back to the commit that I made on the previous slide. Everything done since the commit is rollbacked. Everything done prior to the commit was made permanent by the commit command.
Savepoints are given names because multiple savepoints can be created.
In issuing a rollback, you can rollback to a specific named savepoint.
The rollback with a savepoint rolls back to that point and reverses the adds, changes and deletions from the table taht occured after the named savepoint and prior to the rollback. Note that this is assuming no commit command was issued.
When you do not use a name with the rollback, the default Oracle rules are in effect. In this example, the rollback went back to the commit that I issued just prior to the update.