******************************************************************************
*Unnamed PL/SQLcode block: Use of Control structure and Exception handling *
*is mandatory. Suggested Problem statement: Consider Tables: *
*A. Borrower (Roll_no, Name, Date_of_Issue, Name_of_Book, Status) *
*B. Fine (Roll_no, Date, Amt) *
*1. Accept Roll_no and Name_of_Book from user. *
*2. Check the number of days (from Date_of_Issue). *
*3. If days are between 15 to 30 then fine amount will be Rs 5per day. *
*4. If no. of days>30, per day fine will be Rs 50 per day and for days less *
* than 30, Rs. 5 per day. *
*5. After submitting the book, status will change from I to R. *
*6. If condition of fine is true, then details will be stored into fine table*
*7. Also handles the exception by named exception handler or user define *
* exception handler. *
******************************************************************************
SQL> CREATE TABLE borrower(roll_no NUMBER , name VARCHAR2(25), dateofissue
DATE,name_of_book VARCHAR2(30), status VARCHAR2(20));
Table created.
SQL> CREATE TABLE fine(roll_no NUMBER,date_of_return DATE,amt NUMBER);
Table created.
SQL> INSERT INTO borrower
VALUES(11,'ANJALI',TO_DATE('01-08-2023','DD-MM-YYYY'),'OPERATING
SYSTEM','ISSUED');
1 row created.
SQL> INSERT INTO borrower
VALUES(12,'HARSHADA',TO_DATE('15-10-2023','DD-MM-YYYY'),'DATA
STRUCTURE','ISSUED');
1 row created.
SQL> INSERT INTO borrower
VALUES(13,'AJAY',TO_DATE('24-08-2023','DD-MM-YYYY'),'DATABASE MANAGEMENT
SYSTEM','ISSUED');
1 row created.
SQL> INSERT INTO borrower
VALUES(14,'ABHISHEK',TO_DATE('26-08-2023','DD-MM-YYYY'),'COMPUTER
NETWORK','ISSUED');
1 row created.
SQL> INSERT INTO borrower
VALUES(15,'JECITA',TO_DATE('09-09-2023','DD-MM-YYYY'),'DISCRETE
MATHEMATICS','ISSUED');
1 row created.
SQL> SET SERVEROUT ON
SQL> SET VERIFY OFF
SQL> DECLARE
2 i_roll_no NUMBER;
3 name_of_book VARCHAR2(25);
4 no_of_days NUMBER;
5 return_date DATE := TO_DATE(SYSDATE,'DD-MM-YYYY');
6 temp NUMBER;
7 doi DATE;
8 fine NUMBER;
9 BEGIN
10 i_roll_no := &i_roll_no;
11 name_of_book := '&nameofbook';
12 SELECT to_date([Link],'DD-MM-YYYY') INTO doi FROM borrower
WHERE borrower.roll_no = i_roll_no AND borrower.name_of_book = name_of_book;
13 no_of_days := return_date-doi;
14 dbms_output.put_line(no_of_days);
15 IF (no_of_days >15 AND no_of_days <=30) THEN
16 fine := 5*no_of_days;
17 ELSIF (no_of_days>30 ) THEN
18 temp := no_of_days-30;
19 fine := 150 + temp*50;
20 END IF;
21 dbms_output.put_line(fine);
22 INSERT INTO fine VALUES(i_roll_no,return_date,fine);
23 UPDATE borrower SET status = 'RETURNED' WHERE borrower.roll_no = i_roll_no;
24 END;
25 /
Enter value for i_roll_no: 12
Enter value for nameofbook: DATA STRUCTURE
62
1750
PL/SQL procedure successfully completed.
SQL> select * from borrower;
ROLL_NO NAME DATEOFISSUE NAME_OF_BOOK
STATUS
---------- ------------------------- -------------------------------------------
--------------------
11 ANJALI 01-AUG-23 OPERATING SYSTEM
ISSUED
12 HARSHADA 15-AUG-23 DATA STRUCTURE
RETURNED
13 AJAY 24-AUG-23 DATABASE MANAGEMENT
SYSTEMISSUED
14 ABHISHEK 26-AUG-23 COMPUTER NETWORK
ISSUED
15 JECITA 09-SEP-23 DISCRETE MATHEMATICS
ISSUED
SQL> select * from fine;
ROLL_NO DATE_OF_RETURN AMT
---------- ------------------ ----------
12 16-OCT-23 1750