Unnamed PL/SQL code block: Use of Control structure and Exception handling is mandatory.
Suggested Problem statement:
Consider Tables:
1. Borrower(Roll_no, Name, DateofIssue, NameofBook, Status)
2. Fine(Roll_no,Date,Amt)
Accept Roll_no and NameofBook from user.
Check the number of days (from date of issue).
If days are between 15 to 30 then fine amount will be Rs 5per day.
If no. of days>30, per day fine will be Rs 50 per day and 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.
Also handles the exception by named exception handler or user define exception handler.
Delimiter //
create procedure BookFine(roll_new integer,book_name varchar(30))
begin
declare X integer;
DECLARE CONTINUE HANDLER FOR NOT FOUND
begin
select CONCAT('ROLLNO', roll_new, ' NOT FOUND') as NoRecord;
end;
select datediff(curdate(),DateofIssue) into X from Borrower where Rollno=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='R' where Rollno=roll_new;
end;
//
Delimiter ;
Save above program with extension .sql([Link])
How to run:
1. Source [Link];
2. Call BookFine(1,”DBMS”);
3. Select * from Fine
