pVR3mUXm

1#Deletes old database if it is exists
2DROP PROCEDURE IF EXISTS placeOrderFromCart;
3
4#Declare delimiter (default is ;)
5delimiter $$
6CREATE PROCEDURE placeOrderFromCart(custNo INT)
7BEGIN
8	#Declare variables
9	DECLARE nItems INT;
10	DECLARE old INT;
11    
12    #Handle exception if something goes wrong
13	DECLARE EXIT HANDLER FOR SQLEXCEPTION
14    BEGIN
15		ROLLBACK;
16        RESIGNAL;
17			SELECT 'An error has occured';
18	END;
19    
20    #Sanity check for checking if cart is empty. 
21    #Get the number of items in cart
22	SELECT COUNT(*) INTO nItems FROM shoppingCart
23		WHERE customerNumber=custNo;
24        
25	#Attempt to place order from empty cart...
26	IF nItems = 0 THEN
27		#Throw an exception is we try to place an order from an empty cart.
28		SIGNAL SQLSTATE '45000'
29			SET MESSAGE_TEXT = 'Attempted to place order from empty cart';
30	END IF;
31    
32    #Start the transaction
33	START TRANSACTION;
34    
35    #SOMETHING WRONG WITH THIS
36    #does not do auto_increment
37    #does not generate primary key
38    SELECT MAX(orderNumber) INTO old FROM orders;
39    SET old = old + 1;
40	#SOMETHING WRONG WITH THIS
41
42    
43    #Create an order
44	INSERT INTO orders VALUES (NULL, SYSDATE(), DATE_ADD(SYSDATE(), 
45		INTERVAL 7 DAY), NULL, 'In Process', NULL, custNo);
46    
47    #populate orderdetails table
48	INSERT INTO orderdetails 
49		SELECT (old, c.productCode, quantity, MSVP, 0)
50        #'c' is an alias for shopping cart
51		FROM ShoppingCart c JOIN products USING(productCode)
52		WHERE (customerNumber = custNo);
53		#After placing order, delete the shopping cart
54	DELETE FROM shoppingCart WHERE customerNumber = custNo;
55    
56	#Commit the changes
57	COMMIT;
58END$$