Quick Tour
2.4.3Manage
This step provides data management functionality for the applica- tion. In this example we will add records to the itemmast and cust- mast tables, intended as static data. Then, sales orders will be processed as a transaction and "commited" or "rolled back" depending on the validity of the data. The final step will be to dis- play the result of the transaction processing by dumping the con- tents of the orderlist and orderitems tables.
•INSERT INTO - This will add a record by inserting it into the table
•SELECT - Fetch records according to select criteria
•DELETE FROM - Delete records from a table.
•COMMIT WORK - Make changes permanent.
The following excerpt from iSQL_Tutorial4 shows the SQL syntax that would be used to implement the entry of an order and related items as a transaction. The items and the order are inserted, then a select statement is used to verify the existence of the item and customer num- ber. iSQL is a tool that requires the user to interact. This script automatically implements the appropriate commit or rollback statement based on the results of the verification.
INSERT INTO orderitems VALUES ('1', 1, 2, '1');
INSERT INTO orderitems VALUES ('1', 2, 1, '2');
INSERT INTO orderlist VALUES ('9/1/2002', '9/5/2002', '1', '1001');
SELECT orderitems.oi_itemnum, itemmast.im_itemnum
FROM orderitems, itemmast
WHERE orderitems.oi_itemnum = itemmast.im_itemnum;
SELECT orderlist.ol_custnum, custmast.cm_custnum
FROM orderlist, custmast
WHERE orderlist.ol_custnum = custmast.cm_custnum;
COMMIT WORK;
INSERT INTO orderitems VALUES ('2', 1, 1, '3');
INSERT INTO orderitems VALUES ('2', 2, 3, '4');
INSERT INTO orderlist VALUES ('9/2/2002', '9/6/2002', '2', '9999');
SELECT orderitems.oi_itemnum, itemmast.im_itemnum
FROM orderitems, itemmast
WHERE orderitems.oi_itemnum = itemmast.im_itemnum;
SELECT orderlist.ol_custnum, custmast.cm_custnum
FROM orderlist, custmast
WHERE orderlist.ol_custnum = custmast.cm_custnum;
ROLLBACK WORK;
INSERT INTO orderitems VALUES ('3', 1, 2, '3'); INSERT INTO orderitems VALUES ('3', 2, 3, '99');
INSERT INTO orderlist VALUES ('9/22/2002', '9/26/2002', '3', '1002'); SELECT orderitems.oi_itemnum, itemmast.im_itemnum
FROM orderitems, itemmast
WHERE orderitems.oi_itemnum = itemmast.im_itemnum;
FairCom Corporation |
