Scribd
Upload
2 views2 pages

Bank PL SQL Program

The document outlines the creation of a BANK200 table with specific columns and constraints, including a primary key and a balance check. It also presents a PL/SQL program for debiting an account, which includes input prompts for account number and amount, validation checks, and balance updates. The program ensures that the debit amount is within a specified range and that the resulting balance remains above a minimum threshold.

Uploaded by

p1218446
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views2 pages

Bank PL SQL Program

The document outlines the creation of a BANK200 table with specific columns and constraints, including a primary key and a balance check. It also presents a PL/SQL program for debiting an account, which includes input prompts for account number and amount, validation checks, and balance updates. The program ensures that the debit amount is within a specified range and that the resulting balance remains above a minimum threshold.

Uploaded by

p1218446
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Bank Table and PL/SQL Debit Program

1. Create Table

CREATE TABLE BANK200 (


ACNO NUMBER(10) PRIMARY KEY,
ACT_NAME VARCHAR2(20),
ACT_TYPE VARCHAR2(10),
BAL NUMBER(10,2) CHECK (BAL > 500)
);

2. PL/SQL Program for Debit Operation

SET SERVEROUTPUT ON;


ACCEPT accountno PROMPT 'Enter Account Number: ';
ACCEPT amount PROMPT 'Enter Amount: ';

DECLARE
vacno NUMBER := &accountno;
vamount NUMBER := &amount;
vbal NUMBER(10,2);
vcount NUMBER := 0;
min_bal_constraint EXCEPTION;
BEGIN
SELECT COUNT(*) INTO vcount FROM BANK200 WHERE ACNO = vacno;

IF vcount <> 0 THEN


IF vamount > 100 AND vamount < 20000 THEN
SELECT BAL INTO vbal FROM BANK200 WHERE ACNO = vacno;
vbal := vbal - vamount;

IF vbal > 500 THEN


UPDATE BANK200 SET BAL = vbal WHERE ACNO = vacno;
DBMS_OUTPUT.PUT_LINE('Balance Updated');
ELSE
DBMS_OUTPUT.PUT_LINE('No sufficient balance');
RAISE min_bal_constraint;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Amount should be >100 and <20000');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('No such account');
END IF;

EXCEPTION
WHEN min_bal_constraint THEN
DBMS_OUTPUT.PUT_LINE('Balance should be greater than 500');
END;
/

You might also like