noroom exception : Handle Exception « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Handle Exception

noroom exception

    

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>

   
    
    
    
  

Related examples in the same category