ASSIGNMENT 4
Practical 4: Unnamed PL/SQL code block: Use of Control structure and Exception
handling is mandatory.
Write a PL/SQL block of code for the following requirements:
Schema: 1. Borrower (Rollin, Name, Dateofissue, NameofBook, Status)
2. Fine(Roll_no, Date, Amt)
• Accept roll_no & name of book from user.
• Check the number of days (from date of issue), if days are between 15 to 30 then
fine amount will be Rs Sper day.
• If no. of days>30, per day fine will be Rs 50 per day & for days less than 30,
Rs. 5 per day.
• After submitting the book, status will change from I to R.
• If condition of fine is true, then details will be stored into fine table.
Frame the problem statement for writing PL/SQL block inline with above statement.
..............................................................................
mysql> use liberey;
Database changed
mysql> select*from Borrower;
Roll_no Name DOI Name_Book Status
34 Jyoti 2024-09-19 DBMS i
35 Sakshi 2024-04-10 TOC r
63 Swati 2024-03-24 CNS i
79 Priti 2024-08-28 SPM r
80 Siddhi 2024-07-31 SPOS i
85 Pratiksha 2024-10-09 IOT r
6 rows in set (0.00 sec)
mysql> desc fine;(OPTIONAL)
Field Type Null Key Default Extra
Roll_no int YES NULL
Date date YES NULL
Amount int YES NULL
3 rows in set (0.01 sec)
mysql> delimiter //
mysql> create procedure Borro(Roll_new int, name_book varchar(20))
-> begin
-> declare X integer;
-> declare continue handler for not found
-> begin
-> select 'NOT FOUND';
-> end;
-> select datediff(curdate(), DOI) into X from Borrower where Roll_no = Roll_new;
-> if (X>15&&X<30)
-> then
-> insert into fine values(Roll_new,curdate(), (X*5));
-> end if;
-> if(X>30)
-> then
-> insert into fine values(Roll_new,curdate(),(X*50));
end if;
update Borrower set status = 'returned' where Roll_no = Roll_new;
-> end;
>//
Query OK, 0 rows affected, I warning (0.08 sec)
=====
mysql> call Borro (63,'cns');
-> call Borro (23,'xyz');
-> call Borro (63,'cns')//
Query OK, 1 row affected (0.02 sec)
| NOT FOUND |
| NOT FOUND |
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> call Borro(35,'toc')//
Query OK, 1 row affected (0.01 sec)
mysql> call Borro(80,'spos')//
Query OK, 1 row affected (0.01 sec)
mysql> select * from fine//
Roll_no Date Amount
63 2024-08-06 6750
63 2024-08-06 6750
35 2024-08-06 5900
3 rows in set (0.00 sec)
mysql> select from Borrower;
-> select * from Borrower//
Roll_no Name DOI Name_Book Status
34 Jyoti 2024-09-19 DBMS i
35 Sakshi 2024-04-10 TOC Returned
63 Swati 2024-03-24 CNS Returned
79 Priti 2024-08-28 SPM r
80 Siddhi 2024-07-31 SPOS Returned
85 Pratiksha2024-10-09 IOT r
6 rows in set (0.00 sec)
rows in set (0.01 sec)
