jeudi 3 décembre 2015

How do I use 'if' condition inside a 'if' condition?

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