|
SQL> CREATE TABLE myHotel(
2 room_id INTEGER,
3 resident_count INTEGER,
4 room_capacity INTEGER,
5 name VARCHAR2(20)
6 );
Table created.
SQL>
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(101, 20, 20, 'First Room');
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(102, 19, 20, 'Second Room');
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(103, 10, 20, 'Third Room');
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(104, 0, 20, 'Fourth Room');
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
2 IS
3 roomname VARCHAR2 (20);
4 residentcount PLS_INTEGER;
5 capacity PLS_INTEGER;
6 noroom EXCEPTION;
7 BEGIN
8 SELECT resident_count, room_capacity, name
9 INTO residentcount, capacity, roomname
10 FROM myHotel
11 WHERE room_id = roomin;
12
13 IF residentcount > capacity - 1
14 THEN
15 RAISE noroom;
16 ELSE
17 UPDATE myHotel
18 SET resident_count = residentcount + 1
19 WHERE room_id = roomin;
20 COMMIT;
21 DBMS_OUTPUT.put_line ('Student count:'||residentcount||' in '|| roomname);
22 END IF;
23 EXCEPTION
24 WHEN noroom
25 THEN
26 DBMS_OUTPUT.put_line ('There is no room in ' || roomname);
27 WHEN OTHERS
28 THEN
29 DBMS_OUTPUT.put_line ('Error ' || SQLERRM || ' occurred.');
30 END;
31 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myHotel;
Table dropped.
SQL>
SQL>
SQL>
SQL>
|