IF...ELSIF...ELSE... END IF

     

SQL>
SQL> CREATE TABLE place (
  2    room_id          NUMBER(5) PRIMARY KEY,
  3    building         VARCHAR2(15),
  4    room_number      NUMBER(4),
  5    number_seats     NUMBER(4),
  6    description      VARCHAR2(50)
  7    );

Table created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20001, 'Building 7', 201, 1000, 'Large Lecture Hall');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20002, 'Building 6', 101, 500, 'Small Lecture Hall');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20003, 'Building 6', 150, 50, 'Discussion Room A');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20004, 'Building 6', 160, 50, 'Discussion Room B');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
  2             VALUES (20005, 'Building 6', 170, 50, 'Discussion Room C');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20006, 'Music Building', 100, 10, 'Music Practice Room');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20007, 'Music Building', 200, 1000, 'Concert Room');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20008, 'Building 7', 300, 75, 'Discussion Room D');

1 row created.

SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
  2             VALUES (20009, 'Building 7', 310, 50, 'Discussion Room E');

1 row created.

SQL>
SQL>
SQL> select * from place;

 ROOM_ID BUILDING        ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7           201.00      1000.00 Large Lecture Hall
######## Building 6           101.00       500.00 Small Lecture Hall
######## Building 6           150.00        50.00 Discussion Room A
######## Building 6           160.00        50.00 Discussion Room B
######## Building 6           170.00        50.00 Discussion Room C
######## Music Building       100.00        10.00 Music Practice Room
######## Music Building       200.00      1000.00 Concert Room
######## Building 7           300.00        75.00 Discussion Room D
######## Building 7           310.00        50.00 Discussion Room E

9 rows selected.

SQL>
SQL> DECLARE
  2    v_NumberSeats place.number_seats%TYPE;
  3    v_Comment VARCHAR2(35);
  4  BEGIN
  5    SELECT number_seats
  6      INTO v_NumberSeats
  7      FROM place
  8      WHERE room_id = 20008;
  9    IF v_NumberSeats < 50 THEN
 10      v_Comment := 'Fairly small';
 11    ELSIF v_NumberSeats < 100 THEN
 12      v_Comment := 'A little bigger';
 13    ELSE
 14      v_Comment := 'Lots of room';
 15    END IF;
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from place;

 ROOM_ID BUILDING        ROOM_NUMBER NUMBER_SEATS DESCRIPTION
-------- --------------- ----------- ------------ --------------------------------------------------
######## Building 7           201.00      1000.00 Large Lecture Hall
######## Building 6           101.00       500.00 Small Lecture Hall
######## Building 6           150.00        50.00 Discussion Room A
######## Building 6           160.00        50.00 Discussion Room B
######## Building 6           170.00        50.00 Discussion Room C
######## Music Building       100.00        10.00 Music Practice Room
######## Music Building       200.00      1000.00 Concert Room
######## Building 7           300.00        75.00 Discussion Room D
######## Building 7           310.00        50.00 Discussion Room E

9 rows selected.

SQL>
SQL>
SQL> drop table place;

Table dropped.

SQL>

Related examples in the same category

1.	The IF statement contains more than one statement per condition.
2.	IF THEN and END IF
3.	Adding ELSE to the IF block
4.	IF, ELSIF ELSE and END IF
5.	Using nested IF statements
6.	Using IF...ELSIF to determine a grade
7.	if then else
8.	A conditional statement
9.	Check number value in if statement
10.	Exit a loop with condition
11.	If ladder
12.	Compare three variables with if statement
13.	If count() is 0, insert data
14.	If condition meets, throw exception
15.	Nested if statement
16.	If...then...Else
17.	Using a Boolean variable instead of the comparison operation
18.	An if-then-elsif-then-else statement where the first two comparisons are true and the third false
19.	Create a function and call it in an if statement

IF...ELSIF...ELSE... END IF : IF « PL SQL « Oracle PL / SQL

Related examples in the same category