|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
DROP PROCEDURE Add_CustomerMaster_Record;
DROP PROCEDURE Add_CustomerOrders_Record;
DROP PROCEDURE Add_OrderItems_Record;
DROP PROCEDURE Add_ItemMaster_Record;
DROP PROCEDURE Delete_Records;
DROP PROCEDURE Display_Records;
CREATE PROCEDURE Add_CustomerMaster_Record (
IN cm_custnumb CHAR(4),
IN cm_custzipc CHAR(9),
IN cm_custstat CHAR(2),
IN cm_custrtng CHAR(1),
IN cm_custname VARCHAR(47),
IN cm_custaddr VARCHAR(47),
IN cm_custcity VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custmast VALUES (?,?,?,?,?,?,?)"
);
st.setParam (1, cm_custnumb);
st.setParam (2, cm_custzipc);
st.setParam (3, cm_custstat);
st.setParam (4, cm_custrtng);
st.setParam (5, cm_custname);
st.setParam (6, cm_custaddr);
st.setParam (7, cm_custcity);
st.execute();
END
CREATE PROCEDURE Add_CustomerOrders_Record (
IN co_ordrdate DATE,
IN co_promdate DATE,
IN co_ordrnumb CHAR(6),
IN co_custnumb CHAR(4)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custordr VALUES (?,?,?,?)"
);
st.setParam (1, co_ordrdate);
st.setParam (2, co_promdate);
st.setParam (3, co_ordrnumb);
st.setParam (4, co_custnumb);
st.execute();
END
CREATE PROCEDURE Add_OrderItems_Record (
IN oi_sequnumb SMALLINT,
IN oi_quantity SMALLINT,
IN oi_ordrnumb CHAR(6),
IN oi_itemnumb CHAR(5)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO ordritem VALUES (?,?,?,?)"
);
st.setParam (1, oi_sequnumb);
st.setParam (2, oi_quantity);
st.setParam (3, oi_ordrnumb);
st.setParam (4, oi_itemnumb);
st.execute();
END
CREATE PROCEDURE Add_ItemMaster_Record (
IN im_itemwght INTEGER,
IN im_itempric MONEY,
IN im_itemnumb CHAR(5),
IN im_itemdesc VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO itemmast VALUES (?,?,?,?)"
);
st.setParam (1, im_itemwght);
st.setParam (2, im_itempric);
st.setParam (3, im_itemnumb);
st.setParam (4, im_itemdesc);
st.execute();
END
CREATE PROCEDURE Delete_Records (
IN tablename CHAR(256)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"DELETE FROM " + tablename
);
st.execute();
END
CREATE PROCEDURE Display_Records ()
RESULT (
Name CHAR(47),
Total FLOAT
)
BEGIN
SQLCursor cur = new SQLCursor (
"SELECT cm_custname, SUM(im_itempric * oi_quantity) " +
"FROM custmast, custordr, ordritem, itemmast " +
"WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb " +
"GROUP BY cm_custnumb, cm_custname"
);
cur.open();
cur.fetch();
while (cur.found())
{
SQLResultSet.set(1, cur.getValue(1, CHAR));
SQLResultSet.set(2, cur.getValue(2, FLOAT));
SQLResultSet.insert();
cur.fetch();
}
cur.close();
END
-- Manage
ECHO MANAGE;
ECHO Delete records...;
CALL Delete_Records('custmast');
CALL Delete_Records('custordr');
CALL Delete_Records('ordritem');
CALL Delete_Records('itemmast');
ECHO Add records...;
CALL Add_CustomerMaster_Record('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange');
CALL Add_CustomerMaster_Record('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford');
CALL Add_CustomerMaster_Record('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta');
CALL Add_CustomerMaster_Record('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia');
CALL Add_CustomerOrders_Record('9/1/2002', '9/5/2002', '1', '1001');
CALL Add_CustomerOrders_Record('9/2/2002', '9/6/2002', '2', '1002');
CALL Add_OrderItems_Record(1, 2, '1', '1');
CALL Add_OrderItems_Record(2, 1, '1', '2');
CALL Add_OrderItems_Record(3, 1, '1', '3');
CALL Add_OrderItems_Record(1, 3, '2', '3');
CALL Add_ItemMaster_Record(10, 19.95, '1', 'Hammer');
CALL Add_ItemMaster_Record(3, 9.99, '2', 'Wrench');
CALL Add_ItemMaster_Record(4, 16.59, '3', 'Saw');
CALL Add_ItemMaster_Record(1, 3.98, '4', 'Pliers');
ECHO Display records...;
CALL Display_Records();
