exception handling
1Q> attendance 75 percent (exception from sql)
roll no enterd by user
table - stud23(roll_no,att,status)
PL/SQL Code:
declare
mroll number;
matt number;
mstatus char;
begin
mroll:=&mroll;
select att into matt from stud33 where roll_no=mroll;
if(matt<75) then
dbms_output.put_line('Term not granted');
update stud33 set status='D' where roll_no=mroll;
else dbms_output.put_line('Term is granted');
update stud33 set status='ND' where roll_no=mroll;
end if;
end;
/
----------------------------------------------------------------------------------
2Q> exception user defined low balence
account_master24(name,balance,acc_no);
Declare
mbal number(10);
macc number(8);
trans_amt number(10);
No_sufficient_bal exception;
Begin
macc:=&acc_no;
trans_amt:=&trans_amt;
select balance into mbal from account_33
where acc_no = macc;
if (trans_amt<=mbal) then
update account_33 set balance=(balance-trans_amt)
where acc_no=macc;
else
raise No_sufficient_bal;
end if;
Exception
When No_sufficient_bal then
Dbms_output.put_line('Sufficint balance is not available in account');
End;
/
-----------------------------------------------------------------------------------
-
3Q>user deffined exception
for library book days between 15 to 30
Borrower(Roll_no, Name, Date_of_Issue, Name_of_Book, Status)
Fine(Roll_no, Date, Amt)
declare
mroll number(5);
mbook varchar(10);
mdate date;
mfine number(10);
datediff number(10);
-- User-define_18d exceptions
invalid_input EXCEPTION;
begin
mroll:=&mroll;
mbook:='&mbook';
select dateofissue into mdate from borrower_33 where
roll_no=mroll and nameofbook=mbook;
datediff:=to_date(sysdate)-to_date(mdate);
-- Check for overdue
IF datediff < 0 THEN
RAISE invalid_input; -- Negative date difference indicates invalid input
end if;
if(datediff<15) then
dbms_output.put_line('No Fine');
update borrower_33 set status='NF'where
roll_no=mroll and nameofbook=mbook;
elsif(datediff >15 and datediff <30) then
mfine:=(datediff*5);
dbms_output.put_line('Fine is '||mfine);
update borrower_33 set status='F' where roll_no=mroll and nameofbook=mbook;
insert into fine33 values(mroll,sysdate,mfine);
elsif(datediff>30) then
mfine:=((datediff-30)*50+(30*5));
dbms_output.put_line('Fine is'||mfine);
update borrower_33 set status='F' where roll_no=mroll and nameofbook=mbook;
insert into fine33 values(mroll,sysdate,mfine);
end if;
Exception
WHEN invalid_input THEN
dbms_output.put_line('Invalid input: Book return date is after current date.');
commit;
end;
/
-----------------------------------------------------------------------------------
------------------------
