Write a package that contains a procedure and a function. The procedure is passed a room number. If the room number exists, the procedure gets the capacity of the room and the building name from the LOCATION table. If the room number does not exist, the procedure performs the appropriate exception-handling routine. The function is passed a csid and returns maximum number of seats available in the course section.

Question

What will be an ideal response?

Answer 1

```
SQL> CREATE OR REPLACE PACKAGE college_package
2 AS
3 PROCEDURE find_room
4 (i_roomno IN location.RoomNo%TYPE,
5 o_capacity OUT location.Capacity%TYPE,
6 o_bldg OUT location.Building%TYPE);
7 FUNCTION section_count
8 (i_csid IN crssection.csid%TYPE)
9 RETURN NUMBER;
10 END college_package;
11 /

Package created.


SQL> CREATE OR REPLACE PACKAGE BODY college_package AS
2 PROCEDURE find_room
3 (i_roomno IN location.RoomNo%TYPE,
4 o_capacity OUT location.Capacity%TYPE,
5 o_bldg OUT location.Building%TYPE) IS
6 BEGIN
7 SELECT Capacity, Building INTO o_capacity, o_bldg
8 FROM location WHERE RoomNo = i_roomno;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE(i_roomno || ' does not exist.');
12 END find_room;
13 ---------------------------------------------------------
14 FUNCTION section_count
15 (i_csid IN crssection.CsId%TYPE) RETURN NUMBER IS
16 v_count NUMBER(2);
17 BEGIN
18 SELECT MaxCount INTO v_count
19 FROM crssection WHERE CsId = i_csid;
20 Return v_count;
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 RETURN -1;
24 END section_count;
25 ---------------------------------------------------------
26 END college_package;
27 /

Package body created.
```