Mega Code Archive

 

Categories / Oracle PLSQL / PL SQL

 

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