BETWEEN : BETWEEN « PL SQL Operators « Oracle PL/SQL Tutorial






The BETWEEN operator tests to see if a value falls within a given range of values.

The Syntax for BETWEEN:

the_value [NOT] BETWEEN low_end AND high_end
  1. the_value is the value you are testing,
  2. low_end represents the low end of the range,
  3. high_end represents the high end of the range.

True is returned if the_value is greater than or equal to the low end of the range and less than or equal to the high end of the range.

The equivalent expression would look like this:

(the_value >= low_end) AND (the_value <= high_end)

SQL> DECLARE
  2    salary NUMBER := 20000;
  3    employee_id NUMBER := 36325;
  4
  5    PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
  6    BEGIN
  7      DBMS_OUTPUT.PUT_LINE(emp_id);
  8      DBMS_OUTPUT.PUT_LINE(bonus_amt);
  9    END;
 10
 11  BEGIN
 12  IF salary BETWEEN 10000 AND 20000
 13  THEN
 14     give_bonus(employee_id, 1500);
 15  ELSIF salary BETWEEN 20000 AND 40000
 16  THEN
 17     give_bonus(employee_id, 1000);
 18  ELSIF salary > 40000
 19  THEN
 20     give_bonus(employee_id, 500);
 21  ELSE
 22     give_bonus(employee_id, 0);
 23  END IF;
 24  END;
 25  /

PL/SQL procedure successfully completed.

SQL>








23.3.BETWEEN
23.3.1.BETWEEN
23.3.2.use < and > rather than BETWEEN in order to eliminate any overlap between conditions