-
Notifications
You must be signed in to change notification settings - Fork 2
/
Phase3_PROCEDURE.sql
109 lines (95 loc) · 2.51 KB
/
Phase3_PROCEDURE.sql
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
USE FlowerShop
Go
select * from FlowersInOrder
select * from Flower;
--function to trigger
CREATE PROCEDURE Price_NumberOK
-- Add the parameters for the stored procedure here
@OrderID int
AS
BEGIN
SET NOCOUNT ON;
with T (p,o,f)
as
(select Flower.Price ,FlowersInOrder.Order_ID,FlowersInOrder.Flower_ID
from Flower,FlowersInOrder
where Flower.ID=FlowersInOrder.Flower_ID and FlowersInOrder.Order_ID=@OrderID)
update FlowersInOrder
set Price = Number * t.p
from T,FlowersInOrder
where FlowersInOrder.Order_ID=T.o and FlowersInOrder.Flower_ID=T.f and FlowersInOrder.Order_ID=@OrderID;
with Ta(o,p) as
(
select Order_ID,sum(Price)
from FlowersInOrder
group by Order_ID
having Order_ID=@OrderID
)
UPdate [Order]
set Total_Cost=Ta.p+[Order].Wrapping_price
from [Order],Ta
where [Order].ID=Ta.o and [Order].ID=@OrderID;
UPdate [Order]
set Final_Cost= Total_Cost-Discount
from [Order]
where[Order].ID=@OrderID;
update Flower
set Flower.Number=Flower.Number-FlowersInOrder.Number
from Flower,FlowersInOrder
where Flower.ID=FlowersInOrder.Flower_ID and FlowersInOrder.Order_ID= @OrderID;
update Customer
set total_purchase=[Order].Final_Cost
from Customer,[Order]
where Customer.ID=[Order].Customer_ID and [Order].ID=@OrderID
END
GO
--exec Price_NumberOK
--exec PriceToFlowersInOrder(4)
select * from [Order]
select * from FlowersInOrder
select * from Flower
select * from BoughtFlower
exec Price_NumberOK 20
CREATE PROCEDURE AddFlower
-- Add the parameters for the stored procedure here
@Bdate date
AS
BEGIN
SET NOCOUNT ON;
with T(i) as
(select Buy.ID from Buy where Buy.Buy_date=@Bdate),
T1(fi,n) as
(select BoughtFlower.FlowerID, BoughtFlower.Number from BoughtFlower,T where BoughtFlower.BuyID=T.i )
update Flower
set Number=Number+T1.n
from Flower,T1
where Flower.ID=T1.fi
END
GO
select * from Buy;
CREATE PROCEDURE BirthdateDis
@OrderID int,
@CustomerID int
AS
BEGIN
SET NOCOUNT ON;
declare @sdate date;
declare @Bdate date;
set @sdate = (select [Order].Shop_date from [Order] where [Order].ID=@OrderID and [Order].Customer_ID=@CustomerID);
set @Bdate = (select Customer.birthdate from Customer where Customer.ID=@CustomerID)
if MONTH(@sdate)=MONTH(@Bdate) and DAY(@sdate)=DAY(@Bdate)
begin
update [Order]
set Discount = Total_Cost * 0.3, Final_Cost=Total_Cost * 0.7
from [Order]
where ID=@OrderID
end
END
GO
CREATE PROCEDURE lastOrderID( @lastID int output)
AS
BEGIN
SET NOCOUNT ON;
set @lastID =(SELECT TOP 1 [Order].ID FROM [Order] ORDER BY ID DESC )
END
GO