The IF statement contains more than one statement per condition.

     
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> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table 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      INSERT INTO MyTable (char_col) VALUES ('Nice and cozy');
 12    ELSIF v_NumberSeats < 100 THEN
 13      v_Comment := 'A little bigger';
 14      INSERT INTO MyTable (char_col) VALUES ('Some breathing room');
 15    ELSE
 16      v_Comment := 'Lots of room';
 17    END IF;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from mytable;

 NUM_COL CHAR_COL
-------- ------------------------------------------------------------
         Some breathing room

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> drop table mytable;

Table dropped.

SQL>
SQL> drop table place;

Table dropped.

SQL>

Related examples in the same category

1.	IF...ELSIF...ELSE... END IF
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

The IF statement contains more than one statement per condition. : IF « PL SQL « Oracle PL / SQL

Related examples in the same category