Transcript Table maintenance revisited (again)

Table maintenance revisited
(again)
Please use speaker notes for
additional information!
SQL> SELECT * FROM maintain;
IDN
--123
234
345
456
789
678
890
Create copy
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 23-JUN-99
Blocks
10 12-JUN-99 TY
8
Radio
24.99 06-JUL-99 TY
20
Warrior
15.99 09-JUN-99 TY
14
Calculator
20.75 07-JUL-99 TY
17
7 rows selected.
SQL>
2
3
4
To do that, I first created a table with
the same format and data as the
previous table except that I excluded
all records with an indo that was not <
300. This got me the two records for
Teddy Bear and Dump Truck.
CREATE TABLE maintain00
AS
SELECT * FROM maintain
WHERE idno < 300;
Table created.
SQL> SELECT * from maintain00;
IDN
--123
234
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
SQL> DESC maintain00;
Name
Null?
------------------------------- -------IDNO
ITEMNAME
PRICE
PURCHASED
DEPT
COST
The notes use a table called maintain.
In working with it, I had added
multiple records. I decided to create a
new table with the same format and the
first two records with similar data for
this presentation.
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
DATE
CHAR(2)
NUMBER(6,2)
Next I described the table. It has the
same description as maintain since it
was created from maintain.
Insert
SQL> DESC maintain00;
Name
Null?
------------------------------- -------IDNO
ITEMNAME
PRICE
PURCHASED
DEPT
COST
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
DATE
CHAR(2)
NUMBER(6,2)
SQL> INSERT INTO maintain00
2 VALUES ('345','Baby Doll', 12, sysdate, NULL, NULL);
1 row created.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
ITEMNAME
PRICE PURCHASED
------------ --------- --------Teddy Bear
20
Dump Truck
15.95
Baby Doll
12 19-JUN-00
DE
COST
-- --------TY
20
TY
14.36
This is an example of
the INSERT we have
already seen. Note that
all 6 columns receive
data even though two
are set up as null.
Insert with user entry
In this example, all of the values have been set up as
variables for user input. When the statement is executed,
the user is prompted to enter the information. The
information is used to create the record that is entered into
the table named maintain00.
SQL> INSERT INTO maintain00
2 VALUES ('&userid', '&username', &userprice, '&userpurchase', '&userdept', &usercost);
Enter value for userid: 456
Enter value for username: Blocks
Enter value for userprice: 10
Enter value for userpurchase: 12-JUN-00
Enter value for userdept: TY
Enter value for usercost: 8
old
2: VALUES ('&userid', '&username', &userprice, '&userpurchase', '&userdept', &usercost)
new
2: VALUES ('456', 'Blocks', 10, '12-JUN-00', 'TY', 8)
1 row created.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Insert
Edit takes you into the editor. The
code was then saved on my floppy
disk as
SQL> edit
insertmain00.sql
Insert
The saved file insertmain00.sql
was executed twice using @.
The user was prompted for data
and the rows/records were added
to the table.
SQL> @a:\insertmain00.sql
Enter value for userid: 478
Enter value for username: Football
Enter value for userprice: 14.98
Enter value for userpurchase: 10-JUN-00
Enter value for userdept: TY
Enter value for usercost: 11
old
2: VALUES ('&userid', '&username', &userprice, '&userpurchase', '&userdept', &usercost)
new
2: VALUES ('478', 'Football', 14.98, '10-JUN-00', 'TY', 11)
1 row created.
SQL> @ a:\insertmain00.sql
Enter value for userid: 488
Enter value for username: Jump Rope
Enter value for userprice: 5.99
Enter value for userpurchase: 12-JUN-00
Enter value for userdept: TY
Enter value for usercost: 5
old
2: VALUES ('&userid', '&username', &userprice, '&userpurchase', '&userdept', &usercost)
new
2: VALUES ('488', 'Jump Rope', 5.99, '12-JUN-00', 'TY', 5)
1 row created.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Create table
SQL> CREATE TABLE movemain00
2 (itemnum VARCHAR2(3),
3
itemname VARCHAR2(12),
4
itemcost NUMBER(6,2),
5
itemprice NUMBER(6,2));
NOTE: maintain00 has 6 columns while the
new table movemain00 only has 4. The
types are similar for idno and itemnum,
itemname and itemname, price and
itemprice, cost and itemcost. There is no
equivalent for purchased or dept and the
order of price and cost have been reversed.
Table created.
SQL> DESC maintain00;
Name
Null?
------------------------------- -------IDNO
ITEMNAME
PRICE
PURCHASED
DEPT
COST
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
DATE
CHAR(2)
NUMBER(6,2)
SQL> DESC movemain00;
Name
Null?
------------------------------- -------ITEMNUM
ITEMNAME
ITEMCOST
ITEMPRICE
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
NUMBER(6,2)
Insert
Note that the
information is
inserted into
movemain00
in the order
that the
columns are
listed in the
SELECT.
SQL> DESC maintain00;
Name
Null?
------------------------------- -------#1
IDNO
#2
ITEMNAME
#4
PRICE
PURCHASED
DEPT
#3
COST
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
DATE
CHAR(2)
NUMBER(6,2)
SQL> DESC movemain00;
Name
Null?
------------------------------- -------ITEMNUM
ITEMNAME
ITEMCOST
ITEMPRICE
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
NUMBER(6,2)
SQL> INSERT INTO movemain00
2
SELECT idno, itemname, cost, price
3
FROM maintain00
4
WHERE price > 15;
2 rows created.
SQL> SELECT * FROM movemain00;
ITE
--123
234
ITEMNAME
ITEMCOST ITEMPRICE
------------ --------- --------Teddy Bear
20
20
Dump Truck
14.36
15.95
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Insert
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
SQL> SELECT * FROM movemain00;
ITE
--123
234
ITEMNAME
ITEMCOST ITEMPRICE
------------ --------- --------Teddy Bear
20
20
Dump Truck
14.36
15.95
SQL> INSERT INTO movemain00 (itemnum, itemname, itemprice)
2
SELECT idno, itemname, price
3
FROM maintain00
Three columns on movemain00
4
WHERE idno = '456';
are listed to be filled. The select
1 row created.
SQL> SELECT * FROM movemain00;
ITE
--123
234
456
ITEMNAME
ITEMCOST ITEMPRICE
------------ --------- --------Teddy Bear
20
20
Dump Truck
14.36
15.95
Blocks
10
lists the three fields from
maintain00 that contains the data
that will be used to create the
row/record. The where specifies
which row/record from
maintain00 contains the three
columns that will be used.
Alter
SQL> ALTER TABLE movemain00
2 ADD (itemdept CHAR(2));
First I added a column to movemain00. The
new column is itemdept. The results are
shown below.
Table altered.
SQL> DESC movemain00;
Name
Null?
------------------------------- -------ITEMNUM
ITEMNAME
ITEMCOST
ITEMPRICE
ITEMDEPT
SQL> UPDATE movemain00
2 SET itemdept = (SELECT dept
3
FROM maintain00
4
WHERE idno = '123')
5 WHERE itemnum = '234';
Type
---VARCHAR2(3)
VARCHAR2(12)
NUMBER(6,2)
NUMBER(6,2)
CHAR(2)
I want to update the table
movemain00 by setting the new
column itemdept equal to the
dept that is assigned to the
record with idno 123 in the table
maintain00. The inner select
locates record 123 in maintain00
and selects the dept which is TY.
It then sets itemdept = TY for
the row with itemnum = 234.
SQL> SELECT idno, dept
2 FROM maintain00;
1 row updated.
SQL> SELECT * FROM movemain00;
ITE
--123
234
456
ITEMNAME
ITEMCOST ITEMPRICE IT
------------ --------- --------- -Teddy Bear
20
20
Dump Truck
14.36
15.95 TY
Blocks
10
IDN
--123
234
345
456
478
488
DE
-TY
TY
TY
TY
TY
SQL> SELECT * FROM movemain00;
Update
The inner select finds row
234 and returns the itemdept
which is TY.
ITE
--123
234
456
ITEMNAME
ITEMCOST ITEMPRICE IT
------------ --------- --------- -Teddy Bear
20
20
Dump Truck
14.36
15.95 TY
Blocks
10
SQL> UPDATE movemain00
2 SET itemdept = (SELECT itemdept
3
FROM movemain00
4
WHERE itemnum = '234')
5 WHERE itemnum = '456';
1 row updated.
SQL> SELECT * FROM movemain00;
ITE
--123
234
456
ITEMNAME
ITEMCOST ITEMPRICE IT
------------ --------- --------- -Teddy Bear
20
20
Dump Truck
14.36
15.95 TY
Blocks
10 TY
The outer UPDATE
assigns the selected
TY to itemnum
456.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
6 rows selected.
Insert
I decided to add another record with a cost
less than 8 to the table. I used my
insertmain00 to do this.
SQL> @ a:\insertmain00
Enter value for userid: 567
Enter value for username: Tea Set
Enter value for userprice: 9
Enter value for userpurchase: 09-JUN-00
Enter value for userdept: TY
Enter value for usercost: 7.5
old
2: VALUES ('&userid', '&username', &userprice, '&userpurchase', '&userdept', &usercost)
new
2: VALUES ('567', 'Tea Set', 9, '09-JUN-00', 'TY', 7.5)
1 row created.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
Delete
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
SQL> DELETE FROM maintain00
2 WHERE cost < 8;
2 rows deleted.
Note that 2 rows were deleted.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Commit,
rollback
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
SQL> commit;
I added the Jump Rope and Tea Set back into the file and
then did a commit to make these changes permanent.
Commit complete.
SQL> rollback;
Rollback complete.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
Note that the rollback
after the commit has no
impact. The commit
has made the changes
permanent so there is
nothing to rollback
Commit,
rollback
SQL> DELETE FROM maintain00
2 WHERE cost < 8;
2 rows deleted.
The DELETE FROM
statement removed two
records from the table.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
SQL> rollback;
Rollback complete.
Rollback, rolled back the delete and
restored the two records to the table.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
Savepoint
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
SQL> UPDATE maintain00
2 SET purchased = '12-MAY-00'
3 WHERE idno = '123';
The first record in the table was updated
to include a purchase date.
1 row updated.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20 12-MAY-00 TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
SQL> SAVEPOINT date_update;
Savepoint created.
A savepoint named
date_update was created.
Savepoint
SQL> DELETE FROM maintain00
2 WHERE idno = '345';
1 row deleted.
SQL> SELECT * FROM maintain00;
SQL> SAVEPOINT date_update;
Savepoint created.
This is the savepoint shown on the
previous slide.
After the savepoint was created, I
deleted a record. The accompanying
select shows that the record for Baby
Doll has been deleted.
I then did a rollback to the savepoint
named date_update. Since this
savepoint was prior to the delete, the
record was restored.
IDN
--123
234
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20 12-MAY-00 TY
20
Dump Truck
15.95
TY
14.36
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
6 rows selected.
SQL> ROLLBACK TO date_update;
Rollback complete.
SQL> SELECT * FROM maintain00;
IDN
--123
234
345
456
478
488
567
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20 12-MAY-00 TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 19-JUN-00
Blocks
10 12-JUN-00 TY
8
Football
14.98 10-JUN-00 TY
11
Jump Rope
5.99 12-JUN-00 TY
5
Tea Set
9 09-JUN-00 TY
7.5
7 rows selected.
Rollback
SQL> Rollback;
This is not a named rollback
so it rollback to the commit
statement which means the
update of the purchase date
is no longer in place.
Rollback complete.
SQL> SELECT * FROM maintain;
IDN
--123
234
345
456
789
678
890
ITEMNAME
PRICE PURCHASED DE
COST
------------ --------- --------- -- --------Teddy Bear
20
TY
20
Dump Truck
15.95
TY
14.36
Baby Doll
12 23-JUN-99
Blocks
10 12-JUN-99 TY
8
Radio
24.99 06-JUL-99 TY
20
Warrior
15.99 09-JUN-99 TY
14
Calculator
20.75 07-JUL-99 TY
17
7 rows selected.