CS422 Lab2

Lab 2. PL/SQL and JDBC
CS422, Summer 2006

Please upload your solutions to CSNS. Note that file uploading will be disabled automatically after 7:50PM, so please turn in your work on time.

[Schema] Create the following schema in your Oracle database, and populate it with enough data to test the programs and procedures you develop in the rest of the lab:

products( product_id, category, description, price, quantity )
customers( customer_id, first_name, last_name, address )
orders( order_id, customer_id, date_ordered, date_shipped )
order_details( order_id, product_id, quantity )

[JDBC] (5pt) For this exercise, write a Java program for canceling an order. The program takes an order id as input and performs the following operations:

Check if the order has been shipped (i.e. date_shipped is not null). The order cannot be canceled if it is already shipped.
If the order is not shipped yet,

add the quantity of each ordered product back to the products table,
remove corresponding entries from the order_details table, and
remove the order from the orders table.

All the operations should be performed as part of a transaction, and if any error occurs during the transaction, all operations should be properly rolled back, and the program outputs an error message.

One of the purposes of this exercise is to make sure that you can compile and run Java programs using Oracle JDBC driver on the classroom computers. So please complete this exercise before you proceed to the next section.

[Stored Procedures] (5pt) Implement the order cancellation using a PL/SQL stored procedure (a function, actually). The function takes an order id as parameter, and performs the order cancellation as described in the previous exercise. The function return -1 if the order is already shipped, or 0 if the order is canceled successfully.

[Embedded SQL] (extra credit 5pt) Implement the order cancellation using SQLJ. Like in the JDBC exercise, all the operations must be implemented on the application side, i.e. you cannot rely on the stored procedure you developed in the previous exercise.