PL/SQL - Part 6

PL/SQL - Part 6

USER DEFINED EXCEPTIONS:

DECLARE
/*declare the exception*/
exceptionname EXCEPTION;

BEGIN
/*detect the problem*/
IF condition THEN
/*inform the ORACLE engine that the exception has occurred*/
RAISE exceptionname;
END IF;
EXCEPTION
WHEN exceptionname THEN
action
END;
===============================================================

example:
--------
table1:

CREATE TABLE BANKACCOUNTS
(
ACNO NUMBER(5),
ACNAME VARCHAR2(10),
ACBAL NUMBER(10,2),
CONSTRAINT PK_BANKACCOUNTS_ACNO PRIMARY KEY(ACNO),
CONSTRAINT CK_BANKACCOUNTS_ACBAL CHECK(ACBAL > 0)
);

INSERT INTO BANKACCOUNTS
(ACNO, ACNAME, ACBAL)
VALUES(10111, 'Amit', 89000);

INSERT INTO BANKACCOUNTS
(ACNO, ACNAME, ACBAL)
VALUES(10112, 'Amrita', 9000);

INSERT INTO BANKACCOUNTS
(ACNO, ACNAME, ACBAL)
VALUES(10113, 'Amjad', 18000);


table 2:

CREATE TABLE TRANSACTIONS
(
ACNO NUMBER(5),
TRTYPE VARCHAR2(10),
TRAMT NUMBER(10,2),
TRDT DATE,
CONSTRAINT FK_TRANSACTIONS_ACNO FOREIGN KEY(ACNO) REFERENCES BANKACCOUNTS(ACNO)
);



TASK:
1.1.
UPDATE BANKACCOUNTS
SET ACBAL = ACBAL + 1000
WHERE ACNO = 10111;

1.2.
INSERT INTO TRANSACTIONS
(ACNO, TRTYPE, TRAMT, TRDT)
VALUES(10111, 'DEPOSIT' , 1000, SYSDATE);


=========================
CREATE A PL/ SQL CODE BLOCK AND INPUT THE FOLLOWING:
->ACNO
->TRTYPE
->TRAMT
->UPDATE THE BANKACCOUNTS TABLE AND INSERT IN THE TRANSACTIONS:
->CREATE A USER DEFINED EXCEPTION IF THE ACBAL GOES -VE AFTER WITHDRAWL
->CREATE A USER DEFINED EXCEPTION - THE MAX. DEPOSIT THAT IS ALLOWED IN A DAY IS 20000
->CREATE A USER DEFINED EXCEPTION - TRTY SHOULD ONLY ALLOW 'DEPOSIT' OR 'WITHDRAW'
-------------------------------------------------------------------

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*VARIABLE DECLARATIONS*/
AC NUMBER(5);
TRTY VARCHAR2(10);
TRAM NUMBER(10,2);
ac_balance NUMBER(10,2);
/*declare the exception*/
INSUFFICIENT_FUNDS EXCEPTION;
BEGIN
/*USER INPUT*/
/*DBMS_OUTPUT.PUT_LINE('ENTER ACNO, TYPE OF TRANSACTION AND TRANSACTION AMOUNT ');*/
AC:=∾
TRTY:=&TRTY;
TRAM:=&TRAM;
/*GET THE ACBAL OF THE ACNO ENTERED BY THE USER*/
SELECT ACBAL INTO ac_balance
FROM BANKACCOUNTS
WHERE ACNO=AC;
/*DETECT THE PROBLEM OF INSUFFICIENT FUNDS*/
IF TRAM > ac_balance THEN
/*PROBLEM*/
/*inform the ORACLE engine that the exception has occurred*/
RAISE INSUFFICIENT_FUNDS;
END IF;
/*CHECK TYPE OF TRANSACTION*/
IF TRTY='DEPOSIT' THEN
/*DEPOSIT*/
/*ADD THE TRANS AMOUNT TO THE ACBAL*/
ac_balance:=ac_balance + TRAM;
ELSIF TRTY='WITHDRAW' THEN
/*WITHDRAW*/
/*SUBTRACT THE TRANS AMOUNT TO THE ACBAL*/
ac_balance:=ac_balance - TRAM;
END IF;
/*UPDATE THE BANKACCOUNTS TABLE*/
UPDATE BANKACCOUNTS
SET ACBAL = ac_balance
WHERE ACNO = AC;
/*CHECK IF THE UPDATE COMMAND HAS BEEN EXECUTED OR NOT*/
IF SQL%FOUND = TRUE THEN
DBMS_OUTPUT.PUT_LINE('UPDATION SUCCESSFUL IN THE BANKACCOUNTS TABLE!');
ELSE
DBMS_OUTPUT.PUT_LINE('UPDATION FAILED IN THE BANKACCOUNTS TABLE!');
END IF;
/*INSERT THE TRANSACTIONS TABLE*/
INSERT INTO TRANSACTIONS
(ACNO, TRTYPE, TRAMT, TRDT)
VALUES(AC, TRTY , TRAM, SYSDATE);
/*CHECK IF THE INSERT COMMAND HAS BEEN EXECUTED OR NOT*/
IF SQL%FOUND = TRUE THEN
DBMS_OUTPUT.PUT_LINE('INSERTION SUCCESSFUL IN THE TRANSACTIONS TABLE!');
ELSE
DBMS_OUTPUT.PUT_LINE('INSERTION FAILED IN THE TRANSACTIONS TABLE!');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ACNO ' || AC || ' ENTERED IS INCORRECT!');
WHEN INSUFFICIENT_FUNDS THEN
DBMS_OUTPUT.PUT_LINE('INSUFFICIENT FUNDS!');
END;
/