I am trying to use 'if' condition inside a 'if' condition to create a function. I know the syntax looks somewhat like below, but I am unable to do it in my code.
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE
IF sales > quota THEN
bonus := 50;
ELSE
bonus := 0;
END IF;
END IF;
Below is the program I am trying to complete. Please help me with it. Its a function to find phone bill amount wrt given number of calls and plan Type
CREATE OR REPLACE FUNCTION BILL(NUM_OF_CALLS NUMBER, PLAN_TYPE NUMBER) RETURN NUMBER IS
BILL_AMT NUMBER;
MIN_1 NUMBER :=150;
MIN_2 NUMBER :=1000;
BEGIN
IF PLAN_TYPE:=150 THEN
IF NUM_OF_CALLS<150 THEN
BILL_AMT:=MIN_1;
ELSIF NUM_OF_CALLS BETWEEN 151 AND 250 THEN
BILL_AMT:=MIN_1+(NUM_OF_CALLS-150);
ELSIF NUM_OF_CALLS BETWEEN 251 AND 400 THEN
BILL_AMT:=MIN_1+(100*1)+(NUM_OF_CALLS-250)*0.5;
ELSIF NUM_OF_CALLS>400 THEN
BILL_AMT:=MIN_1+(100*1)+(150*0.5)+(NUM_OF_CALLS-400)*0.3;
END IF;
ELSE PLAN_TYPE:=500 THEN
IF NUM_OF_CALLS<1000 THEN
BILL_AMT:=MIN_2;
ELSIF NUM_OF_CALLS BETWEEN 1001 AND 1500 THEN
BILL_AMT:=MIN_2+(NUM_OF_CALLS-1000)*0.50;
ELSIF NUM_OF_CALLS>1500 THEN
BILL_AMT:=MIN_2+(500*0.50)+(NUM_OF_CALLS-1500)*0.25;
END IF;
END IF;
RETURN BILL_AMT;
END;
Thanks
Aucun commentaire:
Enregistrer un commentaire