Table maintenance revisited (again)
The variable concepts that we looked at can be applied to file maintenance. For this example,
I am going to use the maintain file that I created before. The code that is used to maintain
the table by adding, changing and removing rows from the table is called the Data Manipulation
Language (DML). A transaction is the DML statements that makeup a logical process.
SQL CODE:
SQL> select * from maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
SQL> desc maintain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(12)
PRICE NUMBER(6,2)
PURCHASED DATE
DEPT CHAR(2)
COST NUMBER(6,2)
Remember if the INSERT simply has the format: INSERT INTO table VALUES (data for an individual
record); then all columns must contain data. If the INSERT has the format: INSERT INTO table
(list of columns) VALUES(data for an individual record), then only the columns listed will
receive data.
When inserting data you can use the system date, and null to fill appropriate rows.
SQL CODE:
SQL> INSERT INTO maintain
2 VALUES('345', 'Baby Doll', 12, sysdate, NULL, NULL);
1 row created.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
The record that was inserted has the system date in the purchased field and dept and cost are
both null.
The following INSERT calls for user entry. This INSERT could be saved and executed when new
records need to be created. This makes the data entry far more direct without involvement
with recoding the INSERT each time you want to add a record.
SQL CODE:
1 INSERT INTO maintain
2* VALUES('&userid', '&username', &userprice, '&userpru', '&userdept', &usercost);
SQL>
Enter value for userid: 456
Enter value for username: Blocks
Enter value for userprice: 10
Enter value for userpru: 12-JUN-99
Enter value for userdept: TY
Enter value for usercost: 8
old 2: VALUES('&userid', '&username', &userprice, '&userpru', '&userdept', &usercost)
new 2: VALUES('456', 'Blocks', 10, '12-JUN-99', 'TY', 8)
1 row created.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
Taking rows from another table
The statement below creates a new table called movemain. It has four fields that match the
description of the table maintain.
SQL CODE:
SQL> CREATE TABLE movemain
2 ( itemnum VARCHAR2(3), itemname VARCHAR2(12), itemcost NUMBER(6,2), itemprice NUMBER(6,2));
Table created.
SQL> DESC maintain;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(3)
ITEMNAME VARCHAR2(12)
PRICE NUMBER(6,2)
PURCHASED DATE
DEPT CHAR(2)
COST NUMBER(6,2)
SQL> DESC movemain;
Name Null? Type
------------------------------- -------- ----
ITEMNUM VARCHAR2(3)
ITEMNAME VARCHAR2(12)
ITEMCOST NUMBER(6,2)
ITEMPRICE NUMBER(6,2)
In the example below, I want to take some of the information from the table maintain and move
it to the table movemain. For this move, I am selecting the four columns/fields that are the
structure for movemain from all rows/records where the price is > 15. The four columns are
moved for the two records that meet the criteria. In the second example, I only want to move
over the item number, the item name and the cost. I do not want to move the price. To
accomplish this, I need to specify the receiving fields in the INSERT.
SQL CODE:
SQL> INSERT INTO movemain
2 SELECT idno, itemname, price, cost
3 FROM maintain
4 WHERE price > 15;
2 rows created.
SQL> SELECT * FROM movemain;
ITE ITEMNAME ITEMCOST ITEMPRICE
--- ------------ --------- ---------
123 Teddy Bear 20 18
234 Dump Truck 15.95 14.36
In the second example, I only want to move over the item number, the item name and the cost.
I do not want to move the price. To accomplish this, I need to specify the receiving fields
in the INSERT. Note that the order of fields in the INSERT corresponds to the order in the
SELECT. The where clause in this example is selecting a record with a specific id number.
SQL CODE:
SQL> INSERT INTO movemain (itemnum, itemname, itemprice)
2 SELECT idno, itemname, price
3 FROM maintain
4 WHERE idno = '456';
1 row created.
SQL> SELECT * FROM movemain;
ITE ITEMNAME ITEMCOST ITEMPRICE
--- ------------ --------- ---------
123 Teddy Bear 20 18
234 Dump Truck 15.95 14.36
456 Blocks 10
Now I am altering the table movemain to have the column ITEMDEPT.
SQL CODE:
SQL> ALTER TABLE movemain
2 ADD (itemdept CHAR(2));
Table altered.
SQL> DESC movemain;
Name Null? Type
------------------------------- -------- ----
ITEMNUM VARCHAR2(3)
ITEMNAME VARCHAR2(12)
ITEMCOST NUMBER(6,2)
ITEMPRICE NUMBER(6,2)
ITEMDEPT CHAR(2)
Now I am going to put the department TY into the the record with id 234 on movemain. The way
I do this is to UPDATE movemain and set the itemdept = to the dept from the maintain file in
the row where idno = 123. I do this for the row/record in movemain where the itemnum = 234.
SQL CODE:
SQL> UPDATE movemain
2 SET itemdept = (SELECT dept
3 FROM maintain
4 WHERE idno = '123')
5 WHERE itemnum = '234';
1 row updated.
SQL> SELECT * FROM movemain;
ITE ITEMNAME ITEMCOST ITEMPRICE IT
--- ------------ --------- --------- --
123 Teddy Bear 20 18
234 Dump Truck 15.95 14.36 TY
456 Blocks 10
In this example, I am setting the itemdept in the row/record where itemnum is 456 to be equal
to the row/record in the same table where itemnum = 234 (that is the one I updated in the code
above).
SQL CODE:
SQL> UPDATE movemain
2 SET itemdept = (SELECT itemdept
3 FROM movemain
4 WHERE itemnum = '234')
5 WHERE itemnum = '456';
1 row updated.
SQL> SELECT * FROM movemain;
ITE ITEMNAME ITEMCOST ITEMPRICE IT
--- ------------ --------- --------- --
123 Teddy Bear 20 18
234 Dump Truck 15.95 14.36 TY
456 Blocks 10 TY
Deleting a row
Remember the DELETE FROM table statement allows you to delete rows from a table. The WHERE
condition attached to the DELETE allows you to remove a row or rows that meet specific
criteria. Omitting the where clause deletes all rows from the table but leaves the table
structure. I added a couple of more rows to the maintain table so it now looks like this.
SQL CODE:
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
567 Tea Set 9 09-JUN-99 TY 7.5
678 Warrior 15.99 09-JUN-99 TY 14
6 rows selected.
A delete statement to delete all records where cost is less than 8 and the results are shown
below:
SQL CODE:
SQL> DELETE FROM maintain
2 WHERE cost < 8;
1 row deleted.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
COMMIT, ROLLBACK, and SAVEPOINT
The commit statement commits what you have done in the database. Prior to doing a commit you
can rollback activities, after doing a commit the add, change or delete has been committed to
the file. This code commits the delete that I performed above. As you can see, the rollback
has no impact.
SQL CODE:
SQL> commit;
Commit complete.
SQL> rollback;
Rollback complete.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
Now I am going to delete a record and then do a rollback to get the record to return. The
delete that I am doing is based on another table. I am saying delete where the idno in the
maintain table matches the itemnum in the movemain table that I got when I selected the row
with the itemname = Blocks. Since the item numbers (idno in maintain and itemnum in movemain)
have the same id for the same records, the Blocks record will be deleted from the maintain
table. I then decided that I really didn't want to do that, so I did a rollback which
restored the row to the table maintain.
SQL CODE:
SQL> DELETE FROM maintain
2 WHERE idno =
3 (SELECT itemnum
4 FROM movemain
5 WHERE itemname = 'Blocks');
1 row deleted.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
678 Warrior 15.99 09-JUN-99 TY 14
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
The SAVEPOINT allows you to insert savepoints that can be points you roll back to using the
rollback command (Note you are now in SQL*PLUS not just SQL). In this example, I am showing
you the maintain file before I start. Then I am updating the file putting date in the record
with idno = 123. Then I show the result of this update. Next I set a savepoint after the
completion of this update called date_update. Next I delete a record and then change my mind.
I want to do a rollback that removes the delete but does not effect the update of the date so
I do a rollback to the savepoint date_update. If you look at the table after the rollback the
deleted record is restored and the updated record 123 still has a date. Finally I do another
rollback with no savepoint and this rollback goes back and undoes the update. Record 123 now
has no date.
SQL CODE:
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
SQL> UPDATE maintain
2 SET purchased = '12-MAY-99'
3 WHERE idno = '123';
1 row updated.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 12-MAY-99 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
SQL> SAVEPOINT date_update;
Savepoint created.
SQL> DELETE FROM maintain
2 WHERE idno = '345';
1 row deleted.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 12-MAY-99 TY 18
234 Dump Truck 15.95 TY 14.36
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
SQL> ROLLBACK to date_update;
Rollback complete.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 12-MAY-99 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM maintain;
IDN ITEMNAME PRICE PURCHASED DE COST
--- ------------ --------- --------- -- ---------
123 Teddy Bear 20 TY 18
234 Dump Truck 15.95 TY 14.36
345 Baby Doll 12 23-JUN-99
456 Blocks 10 12-JUN-99 TY 8
678 Warrior 15.99 09-JUN-99 TY 14
There are rules about transactions dealing with rollbacks and commits involving how far they
go that will be discussed later.