Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 17:00pm
shawa00251

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

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,142
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 18 Nov 2012
Copyright © CodeProject, 1999-2014
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