A PL/SQL package with two methods
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>
SQL> CREATE OR REPLACE PACKAGE RoomsPkg AS
2 PROCEDURE NewRoom(p_Building place.building%TYPE,
3 p_RoomNum place.room_number%TYPE,
4 p_NumSeats place.number_seats%TYPE,
5 p_Description place.description%TYPE);
6
7 PROCEDURE DeleteRoom(p_RoomID IN place.room_id%TYPE);
8 END RoomsPkg;
9 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY RoomsPkg AS
2 PROCEDURE NewRoom(p_Building place.building%TYPE,
3 p_RoomNum place.room_number%TYPE,
4 p_NumSeats place.number_seats%TYPE,
5 p_Description place.description%TYPE) IS
6 BEGIN
7 INSERT INTO place
8 (room_id, building, room_number, number_seats, description)
9 VALUES
10 (1100, p_Building, p_RoomNum, p_NumSeats,
11 p_Description);
12 END NewRoom;
13
14 PROCEDURE DeleteRoom(p_RoomID IN place.room_id%TYPE) IS
15 BEGIN
16 DELETE FROM place
17 WHERE room_id = p_RoomID;
18 END DeleteRoom;
19 END RoomsPkg;
20 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table place;
Table dropped.
SQL>
SQL>
Related examples in the same category