Click here to Skip to main content
12,952,398 members (40,623 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
i have been asked to create a procedure to make discounts for customers

for every 3rd visit a 10% discount must be applied for the customer
for every 6th visit a 20% discount must be applied for the customer
and for every 9th and subsequent visit a 30% discount must be applied for the customer

we have booking table:
BK_ID = booking id
CUST_ID = customer id
SP_ID = specialisim id
SH_ID = shop id
NUM_BK = number of bookings/per customer

we have payment table:
P_ID = payment id
P_AMOUNT = payment amount for treatment
CUST_ID = customer id
BK_ID = booking id


this is what i have so far, and it creates with compilation errors:

CREATE OR REPLACE PROCEDURE TOTNUMOFBOOKINGS
AFTER INSERT ON BOOKING
FOR EACH ROW
DECLARE
V_CUST_ID BOOKING.CUST_ID%TYPE:
V_BK_ID BOOKING.BK_ID%TYPE:
V_NUM_BK BOOKING.NUM_BK%TYPE:
BEGIN
SELECT COUNT(*) INTO V_NUM_BK
FROM BOOKING
WHERE CUST_ID := :OLD.CUST_ID;
UPDATE BOOKING;
SET NUM_BK = V_NUM_BK;

BEGIN LOOP
FOR TOTNUMOFBOOKINGS
IF NUM_BK COUNT(*) IS >=3 THEN
UPDATE PAYMENT
SET P_AMOUNT = P_AMOUNT - P_AMOUNT *0.10
WHERE CUST_ID = 'CO1';
DBMS_OUTPUT.PUT_LINE ('APPLY 10% DISCOUNT')||


ELSE IF NUM_BK COUNT (*) IS >=6 THEN
UPDATE PAYMENT
SET P_AMOUNT = P_AMOUNT - P_AMOUNT *0.10
WHERE CUST_ID = 'CO3';
DBMS_OUTPUT.PUT_LINE ('APPLY 20% DISCOUNT')||

ELSE IF NUM_BK COUNT (*) IS >=9 THEN
UPDATE PAYMENT
SET P_AMOUNT = P_AMOUNT - P_AMOUNT *0.10
WHERE CUST_ID = 'CO3';
DBMS_OUTPUT.PUT_LINE ('APPLY 30% DISCOUNT')||

END IF;
END IF;
END IF;
END;
Posted 18-Nov-12 16:00pm
shawa00251

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,289
CHill60 3,490
Maciej Los 3,103
Jochen Arndt 1,975
ppolymorphe 1,900


Advertise | Privacy | Mobile
Web01 | 2.8.170525.1 | Last Updated 18 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100