unnamed-code-block.sql

SET SERVEROUTPUT ON;
DECLARE
NAME_OF_BOOK_ VARCHAR(50);
ROLL_NUMBER_ NUMBER;
CURRENT_DATE DATE;
DATE_OF_ISSUE_ DATE;
NUMBER_OF_DAY NUMBER;
STATUS_ VARCHAR2(1);
FINE_AMOUNT NUMBER := 0;
STATUS_CHECKUP EXCEPTION;
BEGIN
NAME_OF_BOOK_ := &NAME_OF_BOOK;
ROLL_NUMBER_ := &ROLL_NUMBER;
SELECT DATE_OF_ISSUE INTO DATE_OF_ISSUE_ FROM BORROWER WHERE ROLL_NUMBER = ROLL_NUMBER_ AND NAME_OF_BOOK = NAME_OF_BOOK_;
SELECT STATUS INTO STATUS_ FROM BORROWER WHERE ROLL_NUMBER = ROLL_NUMBER_;
CURRENT_DATE := TRUNC(SYSDATE);
SELECT CURRENT_DATE - DATE_OF_ISSUE_ INTO NUMBER_OF_DAY FROM DUAL;

IF(STATUS_ = 'R') THEN
    RAISE STATUS_CHECKUP;
ELSE
    IF(NUMBER_OF_DAY >= 30)THEN
        FINE_AMOUNT := 50 * NUMBER_OF_DAY;
    ELSIF(NUMBER_OF_DAY > 15 AND NUMBER_OF_DAY < 30)THEN
        FINE_AMOUNT := NUMBER_OF_DAY * 5;
        END IF;
    INSERT INTO FINE VALUES(ROLL_NUMBER_, CURRENT_DATE, FINE_AMOUNT);
    
UPDATE BORROWER SET STATUS = 'R' WHERE ROLL_NUMBER = ROLL_NUMBER_;
END IF;

EXCEPTION
    WHEN STATUS_CHECKUP THEN
         DBMS_OUTPUT.PUT_LINE('NO NEED TO PAY FINE');
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO SUCH STUDENT');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR! SOMETHING WENT WRONG');
END;