|
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>
|