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 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
0 mhegazy94 460
1 Sergey Alexandrovich Kryukov 460
2 Ravi Bhavnani 190
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,135
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,619
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web03 | 2.8.140922.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