Mega Code Archive

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>