-
Notifications
You must be signed in to change notification settings - Fork 0
/
mysql_create_procedures.txt
61 lines (44 loc) · 1.52 KB
/
mysql_create_procedures.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- Procedure: GetMaxQuantity
DELIMITER //
CREATE PROCEDURE GetMaxQuantity()
BEGIN
SELECT MAX(Quantity) AS MaxQuantity FROM OrderDetails;
END //
DELIMITER ;
-- Procedure: ManageBooking
DELIMITER //
CREATE PROCEDURE ManageBooking(IN orderID INT, IN deliveryDate DATE)
BEGIN
UPDATE Orders SET DeliveryDate = deliveryDate WHERE OrderID = orderID;
SELECT CONCAT('Order ', orderID, ' updated with new delivery date: ', deliveryDate) AS Message;
END //
DELIMITER ;
-- Procedure: UpdateBooking
DELIMITER //
CREATE PROCEDURE UpdateBooking(IN orderID INT, IN customerID INT, IN orderDate DATE, IN deliveryDate DATE)
BEGIN
UPDATE Orders
SET CustomerID = customerID,
OrderDate = orderDate,
DeliveryDate = deliveryDate
WHERE OrderID = orderID;
SELECT CONCAT('Order ', orderID, ' updated successfully.') AS Message;
END //
DELIMITER ;
-- Procedure: AddBooking
DELIMITER //
CREATE PROCEDURE AddBooking(IN orderID INT, IN orderDate DATE, IN deliveryDate DATE, IN customerID INT)
BEGIN
INSERT INTO Orders (OrderID, OrderDate, DeliveryDate, CustomerID)
VALUES (orderID, orderDate, deliveryDate, customerID);
SELECT CONCAT('Order ', orderID, ' added successfully.') AS Message;
END //
DELIMITER ;
-- Procedure: CancelBooking
DELIMITER //
CREATE PROCEDURE CancelBooking(IN orderID INT)
BEGIN
DELETE FROM Orders WHERE OrderID = orderID;
SELECT CONCAT('Order ', orderID, ' canceled successfully.') AS Message;
END //
DELIMITER ;