UPDATE_CUSTOMER_BANK_DETAIL.sql

CREATE OR REPLACE PROCEDURE UPDATE_CUSTOMER_BANK_DETAIL(DET_ID IN NUMBER,
BNAME VARCHAR,
ACC_NBR IN NUMBER,
BNK_RNUM IN NUMBER
)

IS
D_ID NUMBER;
ACCNBR_EXCEPTION EXCEPTION;
BRNUM_EXCEPTION EXCEPTION;
ACCNBR_LEN_EXCEPTION EXCEPTION;
BRNUM_LEN_EXCEPTION EXCEPTION;

BEGIN
    Select count(*) into D_ID from CUSTOMER_BANK_DETAIL where DET_ID=CUSTOMER_BANK_DETAIL.DETAIL_ID;
    IF D_ID =0 THEN
        RAISE NO_DATA_FOUND;
    ELSIF is_number(ACC_NBR)=0 THEN
        RAISE ACCNBR_EXCEPTION;
    ELSIF is_number(BNK_RNUM)=0 THEN
        RAISE BRNUM_EXCEPTION;
    ELSIF LENGTH(ACC_NBR)<>10 THEN
        RAISE ACCNBR_LEN_EXCEPTION;
    ELSIF LENGTH(BNK_RNUM)<>8 THEN
        RAISE BRNUM_LEN_EXCEPTION;
    ELSE
        UPDATE CUSTOMER_BANK_DETAIL SET BANKNAME = BNAME, ACCOUNT_NUMBER = ACC_NBR, BANK_ROUTINGNUMBER = BNK_RNUM, LAST_UPDATED = SYSDATE  WHERE CUSTOMER_BANK_DETAIL.DETAIL_ID = DET_ID;
        DBMS_OUTPUT.PUT_LINE('Customer Bank Details - updated successfully');
    COMMIT;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Customer Bank - record not found ');
    WHEN ACCNBR_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('ACCOUNT NUMBER SHOULD BE A NUMBER');
    WHEN BRNUM_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('BANK ROUTING NUMBER SHOULD BE A NUMBER');
    WHEN ACCNBR_LEN_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('ACCOUNT NUMBER SHOULD BE OF 10 DIGITS');
    WHEN BRNUM_LEN_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('BANK ROUTING NUMBER SHOULD BE OF 8 DIGITS');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;