CONTROL STRUCTURE:
******************
-->Executes a set of statements only when given condition is true.
-->Wd can pass multiple conditions.
-->condition can be formed by using comparision,Logical and special operator which returns is bollean.
-->It is used to compare the values.
-->Based on condition result we can perform a task(Either TRUE or FALSE).
CONDITIONAL CONTROL STATEMENTS:
*******************************
1.IF STATEMENT(SIMPLE IF)
2.IF ELSE STATEMENT
3.IF ELSIF STATEMENT
4.NESTED IF STATEMENT
IF STATEMENT:
*************
SYNTAX:
******
IF <CONDITION>THEN
<STATEMENT1>
<STATEMENT2>
END IF;
EX:
***
DECLARE
ABC NUMBER :=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE IF');
IF ABC>20 THEN
DBMS_OUTPUT.PUT_LINE('Inside If-ABC VALUE IS :'||ABC);
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF');
END;
/
DECLARE
ABC NUMBER :=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE IF');
IF ABC>30 THEN
DBMS_OUTPUT.PUT_LINE('Inside If-ABC VALUES IS :'||ABC);
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF');
END;
/
DECLARE
ABC NUMBER :=10;
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE IF');
IF ABC>30 THEN
DBMS_OUTPUT.PUT_LINE('ABC VALUES IS :'||ABC);
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF');
END;
/
DECLARE
A NUMBER :=100;
B NUMBER :=200;
BEGIN
IF A=100 AND B=200 THEN
DBMS_OUTPUT.PUT_LINE('A VALUES IS :'||A);
DBMS_OUTPUT.PUT_LINE('B VALUES IS :'||B);
DBMS_OUTPUT.PUT_LINE(A+B);
END IF;
END;
/
DECLARE
A NUMBER :=100;
B NUMBER :=200;
BEGIN
IF A IN (100,200) THEN
DBMS_OUTPUT.PUT_LINE('A VALUES IS :'||A);
DBMS_OUTPUT.PUT_LINE('B VALUES IS :'||B);
DBMS_OUTPUT.PUT_LINE(A+B);
END IF;
END;
/
DECLARE
LV_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP
WHERE EMPNO=7782;
DBMS_OUTPUT.PUT_LINE('BEFORE IF');
IF LV_ENAME='CLARK' THEN
DBMS_OUTPUT.PUT_LINE('ENAME PRESENT');
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF');
END;
/
DECLARE
LV_DEPTNO NUMBER :=&NO;
LV_DNAME VARCHAR2(20);
BEGIN
SELECT DNAME
INTO LV_DNAME
FROM DEPT
WHERE DEPTNO=LV_DEPTNO;
IF LV_DNAME='ACCOUNTING' THEN
DBMS_OUTPUT.PUT_LINE('NEWYORK');
END IF;
END;
/
DECLARE
LV_ENAME EMP.ENAME%TYPE;
LV_SAL EMP.SAL%TYPE;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP
WHERE EMPNO=7782;
DBMS_OUTPUT.PUT_LINE('BEFORE IF');
IF LV_ENAME='CLARK' AND LV_SAL=2450 THEN
DBMS_OUTPUT.PUT_LINE('ENAME AND SAL PRESENT');
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF');
END;
/
DECLARE
LV_NAME VARCHAR2(20) :='ORACLEDATABSE';
LV_LENGTH NUMBER;
BEGIN
LV_LENGTH :=LENGTH(LV_NAME);
DBMS_OUTPUT.PUT_LINE('LENGTH OF ENAME IS --- '||LV_LENGTH);
DBMS_OUTPUT.PUT_LINE('BEFORE IF ');
IF LV_LENGTH>15 THEN
DBMS_OUTPUT.PUT_LINE('VALID NAME');
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF ');
END;
/
IF ELSE STATEMENT:
******************
SYNTAX:
*******
IF <CONDITION>THEN
<STATEMENT1>
ELSE
<STATEMENT2>
END IF;
EX:
***
DECLARE
ABC NUMBER :=10;
BEGIN
IF ABC>5 THEN
DBMS_OUTPUT.PUT_LINE('IF VALUE IS:'||ABC);
ELSE
DBMS_OUTPUT.PUT_LINE('ELSE VALUE IS:'||ABC);
END IF;
END;
/
DECLARE
ABC NUMBER :=1;
BEGIN
IF ABC>5 THEN
DBMS_OUTPUT.PUT_LINE('IF VALUE IS:'|| ABC);
ELSE
DBMS_OUTPUT.PUT_LINE('ELSE VALUE IS:'||ABC);
END IF;
END;
/
-->Writa a program to accept a year and check whether it is leap year or not
DECLARE
YEAR NUMBER:=&YEAR;
BEGIN
IF MOD(YEAR,4)=0 THEN
DBMS_OUTPUT.PUT_LINE(YEAR||' IS A LEAP YEAR');
ELSE
DBMS_OUTPUT.PUT_LINE(YEAR||' IS NOT A LEAP YEAR');
END IF;
END;
/
DECLARE
NUM NUMBER :=:NUM;
BEGIN
IF MOD(NUM,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(NUM ||' :IS A EVEN NUMBER');
ELSE
DBMS_OUTPUT.PUT_LINE(NUM ||' :IS A ODD NUMBER');
END IF;
END;
/
-->write a pl-sql program to find the greatest of 2 numbers
declare
a number;
b number;
begin
a:=&a;
b:=&b;
if (a >b) then
dbms_output.put_line('a is greater='||a);
else
dbms_output.put_line('b is greater='||b);
end if;
end;
/
--Find out the length of the string and if it >15 -- print it's valid else invalid
DECLARE
LV_NAME VARCHAR2(20) :='ORACLEDATABSE';
LV_LENGTH NUMBER;
BEGIN
LV_LENGTH :=LENGTH(LV_NAME);
DBMS_OUTPUT.PUT_LINE('LENGTH OF ENAME IS --- '||LV_LENGTH);
DBMS_OUTPUT.PUT_LINE('BEFORE IF ');
IF LV_LENGTH>15 THEN
DBMS_OUTPUT.PUT_LINE('VALID NAME');
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID NAME');
END IF;
DBMS_OUTPUT.PUT_LINE('AFTER IF ');
END;
/
--Get the emp name , if the length >10 then print the valid ename other wise invalid ename
DECLARE
LV_EMPNO NUMBER :=7839;
LV_ENAME VARCHAR2(10);
LV_LENGTH NUMBER;
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP
WHERE EMPNO=LV_EMPNO;
DBMS_OUTPUT.PUT_LINE('ENAME IS : '||LV_ENAME);
LV_LENGTH :=LENGTH(LV_ENAME);
DBMS_OUTPUT.PUT_LINE('LENGTH OF ENAME IS --- : '||LV_LENGTH);
IF LV_LENGTH>10 THEN
DBMS_OUTPUT.PUT_LINE('VALID ENAME');
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID ENAME');
END IF;
END;
/
DECLARE
LV_DEPTNO NUMBER;
BEGIN
SELECT DEPTNO
INTO LV_DEPTNO
FROM DEPT
WHERE DEPTNO=&NO;
IF LV_DEPTNO=10 THEN
DBMS_OUTPUT.PUT_LINE('TEN');
ELSE
DBMS_OUTPUT.PUT_LINE('OTHERS');
END IF;
END;
/
DECLARE
LV_DEPTNO NUMBER;
LV_DNAME VARCHAR2(10);
BEGIN
SELECT DEPTNO,DNAME
INTO LV_DEPTNO,LV_DNAME
FROM DEPT
WHERE DEPTNO=&NO;
IF LV_DEPTNO=10 AND LV_DNAME='ACCOUNTING' THEN
DBMS_OUTPUT.PUT_LINE('DNAME EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('DNAME NOT EXISTS');
END IF;
END;
/
SELECT INVOICE_ID,INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE='USD'
AND INVOICE_ID=10005;
/
DECLARE
LV_INVOICE_CURRENCY_CODE VARCHAR2(10) :=:CURRENCY;
LV_INVOICE_ID NUMBER :=:INVOICE_ID;
BEGIN
SELECT INVOICE_CURRENCY_CODE
INTO LV_INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE=LV_INVOICE_CURRENCY_CODE
AND INVOICE_ID=LV_INVOICE_ID;
IF LV_INVOICE_CURRENCY_CODE='USD' THEN
DBMS_OUTPUT.PUT_LINE('US DOLLAR');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT A US DOLLAR');
END IF;
END;
/
DECLARE
LV_INVOICE_CURRENCY_CODE VARCHAR2(10);
LV_INVOICE_ID NUMBER :=:INVOICE_ID;
BEGIN
SELECT INVOICE_CURRENCY_CODE
INTO LV_INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE=:CURRENCY
AND INVOICE_ID=LV_INVOICE_ID;
IF LV_INVOICE_CURRENCY_CODE='USD' THEN
DBMS_OUTPUT.PUT_LINE('US DOLLAR');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT A US DOLLAR');
END IF;
END;
/
DECLARE
LV_INVOICE_CURRENCY_CODE VARCHAR2(10);
LV_INVOICE_ID NUMBER;
BEGIN
SELECT INVOICE_CURRENCY_CODE
INTO LV_INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_CURRENCY_CODE=:CURRENCY
AND INVOICE_ID=:INVOICE;
IF LV_INVOICE_CURRENCY_CODE='USD' THEN
DBMS_OUTPUT.PUT_LINE('US DOLLAR');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT A US DOLLAR');
END IF;
END;
/
DECLARE
LV_ENAME VARCHAR2(20);
LV_EMPNO NUMBER;
LV_SAL NUMBER;
BEGIN
SELECT EMPNO,ENAME,SAL
INTO LV_EMPNO,LV_ENAME,LV_SAL
FROM EMP
WHERE EMPNO=7900;
DBMS_OUTPUT.PUT_LINE('ENAME IS : '||LV_ENAME);
DBMS_OUTPUT.PUT_LINE('ENAME IS : '||LV_SAL);
IF LV_ENAME ='ALLEN' AND LV_SAL=950 THEN
DBMS_OUTPUT.PUT_LINE('ENAME IS PRESENT');
ELSE
DBMS_OUTPUT.PUT_LINE('ENAME IS NOT PRESENT');
END IF;
END;
/
DECLARE
A NUMBER :=NULL;
BEGIN
IF A IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('A IS NOT NULL VALUE :'||A);
ELSE
DBMS_OUTPUT.PUT_LINE('A IS NULL VALUE :'||A);
END IF;
END;
/
IF ELSIF STATEMENT OR IF THEN ELSE STATEMENT:
*********************************************
SYNTAX:
*******
IF<CONDITION1>THEN
SEQUENCE OF STATEMENT1
ELSIF<CONDITION2>THEN
SEQUENCE OF STATEMENT2
ELSIF<CONDITION3>THEN
SEQUENCE OF STATEMENT3
ELSE
SEQUENCE OF STATEMENT4
END IF;
NOTE:
*****
-->If Condition1 is true statement1 is executed.
-->If condition1 if false then check the condition2.
-->If condition2 is true then statement2 is executed.
-->If condition2 is false then check the condition3.
-->If condition3 is true then statement3 is executed.
-->If condition3 is false then else statement executed.
EX:
***
DECLARE
ABC NUMBER :=100;
BEGIN
IF ABC=10 THEN
DBMS_OUTPUT.PUT_LINE('ABC VALUE IS :'||ABC);
ELSIF ABC=20 THEN
DBMS_OUTPUT.PUT_LINE('ABC VALUE IS :'||ABC);
ELSIF ABC=100 THEN
DBMS_OUTPUT.PUT_LINE('ABC VALUE IS :'||ABC);
ELSIF ABC=200 THEN
DBMS_OUTPUT.PUT_LINE('ABC VALUE IS :'||ABC);
ELSE
DBMS_OUTPUT.PUT_LINE('ABC VALUE IS :'||ABC);
END IF;
END;
/
-->Write a program to accept two strings and display the large one among those
DECLARE
STR1 VARCHAR2(100):=:STR1;
STR2 VARCHAR2(100):=:STR2;
BEGIN
IF LENGTH(STR1) > LENGTH(STR2) THEN
DBMS_OUTPUT.PUT_LINE(STR1 ||' IS GREATER');
ELSIF LENGTH(STR1) < LENGTH(STR2) THEN
DBMS_OUTPUT.PUT_LINE(STR2 ||' IS GREATER');
ELSE
DBMS_OUTPUT.PUT_LINE('BOTH STRINGS ARE EQUAL');
END IF;
END;
/
EX:
****
-->Write a program to accept the annual income of the emp and find the income tax
i) If the annsal>20000 and annsal <=50000 then tax is 10% of income
ii)If the annsal>50000 and annsal <=100000 then tax is Rs 800+16% of income
iii)If the annsal>100000 then tax is Rs 2500+25% of income
DECLARE
AI NUMBER(10,2):=:ANNUALINCOME;
TAX NUMBER(10,3):=0;
LV_TOTAL_SAL NUMBER;
BEGIN
IF AI BETWEEN 20000 AND 50000 THEN
TAX:=AI*10/100;
ELSIF AI BETWEEN 50001 AND 100000 THEN
TAX:=800+AI*16/100;
ELSIF AI > 100001 THEN
TAX:=2500+AI*25/100;
END IF;
LV_TOTAL_SAL :=AI-TAX;
DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME '||AI);
DBMS_OUTPUT.PUT_LINE('TAX '||TAX);
DBMS_OUTPUT.PUT_LINE('FINAL SALARY IS : '||LV_TOTAL_SAL);
END;
/
DECLARE
AI NUMBER(10,2):=:ANNUALINCOME;
TAX NUMBER(10,3):=0;
BEGIN
IF AI>=20000 AND AI<=50000 THEN
TAX:=AI*10/100;
ELSIF AI>50000 AND AI<=100000 THEN
TAX:=800+AI*16/100;
ELSIF AI > 100000 THEN
TAX:=2500+AI*25/100;
END IF;
DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME '||AI);
DBMS_OUTPUT.PUT_LINE('TAX '||TAX);
END;
/
-->write a plsql program to accept deptno as argument and return dept location */
DECLARE
LV_DEPTNO NUMBER;
BEGIN
SELECT DEPTNO
INTO LV_DEPTNO
FROM DEPT
WHERE DEPTNO=:DEPTNO;
IF LV_DEPTNO=10 THEN
DBMS_OUTPUT.PUT_LINE('LOCATION IS NEW YORK');
ELSIF LV_DEPTNO=20 THEN
DBMS_OUTPUT.PUT_LINE('LOCATION IS DALLAS');
ELSIF LV_DEPTNO=30 THEN
DBMS_OUTPUT.PUT_LINE('LOCATION IS CHICAGO');
ELSIF LV_DEPTNO=40 THEN
DBMS_OUTPUT.PUT_LINE('LOCATION IS BOSTON');
END IF;
END;
/
DECLARE
LV_DEPTNO NUMBER;
LV_LOC VARCHAR2(20);
VDNO NUMBER :=&NUMBER;
BEGIN
SELECT DEPTNO,LOC
INTO LV_DEPTNO,LV_LOC
FROM DEPT
WHERE DEPTNO=VDNO;
IF LV_DEPTNO=VDNO THEN
DBMS_OUTPUT.PUT_LINE('LOCATION IS :'||LV_LOC);
--ELSIF LV_DEPTNO=VDNO THEN
--DBMS_OUTPUT.PUT_LINE('LOCATION IS :'||LV_LOC);
--ELSIF LV_DEPTNO=VDNO THEN
--DBMS_OUTPUT.PUT_LINE('LOCATION IS :'||LV_LOC);
--ELSIF LV_DEPTNO=VDNO THEN
--DBMS_OUTPUT.PUT_LINE('LOCATION IS :'||LV_LOC);
--ELSE
--DBMS_OUTPUT.PUT_LINE('NO SUCH DEPARTMENT EXISTS');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO SUCH DEPARTMENT EXISTS');
END;
/
DROP TABLE STUDENT;
/
CREATE TABLE STUDENT
(
SNO NUMBER,
SNAME VARCHAR2(20),
M1 NUMBER,
M2 NUMBER,
M3 NUMBER,
M4 NUMBER,
M5 NUMBER,
M6 NUMBER,
HNO NUMBER
);
/
INSERT INTO STUDENT VALUES(1,'A',90,80,99,89,95,90,100);
/
INSERT INTO STUDENT VALUES(2,'C',90,70,50,60,80,60,101);
/
INSERT INTO STUDENT VALUES(3,'D',50,60,50,60,40,70,102);
/
INSERT INTO STUDENT VALUES(4,'E',40,50,40,55,50,60,103);
/
INSERT INTO STUDENT VALUES(5,'F',30,80,99,89,95,90,104);
/
INSERT INTO STUDENT VALUES(6,'G',100,30,100,25,100,99,105);
/
COMMIT;
/
DECLARE
I STUDENT%ROWTYPE;
LV_TOTAL NUMBER;
LV_AVG NUMBER;
LV_RESULT VARCHAR2(20);
BEGIN
SELECT *
INTO I
FROM STUDENT
WHERE HNO=:HNO;
LV_TOTAL :=I.M1+I.M2+I.M3+I.M4+I.M5+I.M6;
LV_AVG :=LV_TOTAL/6;
IF I.M1<35 OR I.M2<35 OR I.M3<35 OR I.M4<35 OR I.M5<35 OR I.M6<35 THEN
LV_RESULT :='FAIL';
ELSIF LV_AVG>=90 THEN
LV_RESULT :='GRADE A+';
ELSIF LV_AVG>=80 THEN
LV_RESULT :='GRADE A';
ELSIF LV_AVG>=75 THEN
LV_RESULT :='DISTINCTION';
ELSIF LV_AVG>=60 AND LV_AVG<75 THEN
LV_RESULT :='FIRST CALSS';
ELSIF LV_AVG>=50 AND LV_AVG<60 THEN
LV_RESULT :='SECOND CALSS';
ELSIF LV_AVG>=35 AND LV_AVG<50 THEN
LV_RESULT :='THIRD CALSS';
END IF;
DBMS_OUTPUT.PUT_LINE('*********STUDENT DETAILS*********');
DBMS_OUTPUT.PUT_LINE('STUDENT NUMBER : '||I.SNO);
DBMS_OUTPUT.PUT_LINE('STUDENT NAME IS : '||I.SNAME);
DBMS_OUTPUT.PUT_LINE('SUB1 MARKS IS : '||I.M1);
DBMS_OUTPUT.PUT_LINE('SUB2 MARKS IS : '||I.M2);
DBMS_OUTPUT.PUT_LINE('SUB3 MARKS IS : '||I.M3);
DBMS_OUTPUT.PUT_LINE('SUB4 MARKS IS : '||I.M4);
DBMS_OUTPUT.PUT_LINE('SUB5 MARKS IS : '||I.M5);
DBMS_OUTPUT.PUT_LINE('SUB6 MARKS IS : '||I.M6);
DBMS_OUTPUT.PUT_LINE('TOTAL MARKS IS : '||LV_TOTAL);
DBMS_OUTPUT.PUT_LINE('AVERAGE IS : '||LV_AVG);
DBMS_OUTPUT.PUT_LINE('RESULT IS : '||LV_RESULT);
END;
/
NESTED IF STATEMENT:(Compound IF)
**********************************
SYNTAX:
******
IF<CONDITION1>THEN
STATEMENT1
IF <CONDITION2>THEN
STATEMENT2
ELSE
STATEMENT3
END IF;
ELSE
STATEMENT4
END IF;
NOTE:
*****
-->If condition1 is true then statement1 executed and check condition2.
-->If condition2 is true then statement2 execute else statement3 executed.
-->If condition1 is false then statement4 executed.
/
EX:
***
-->Write a program to check whether a given number is odd or even only for +VE numbers?
DECLARE
n NUMBER:=:n;
BEGIN
IF n>=0 THEN
DBMS_OUTPUT.PUT_LINE(N||' IT IS POSITIVE NUMBER');
IF MOD(n,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N||' IT IS EVEN NUMBER');
ELSE
DBMS_OUTPUT.PUT_LINE(N||' IT IS ODD NUMBER');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(N||' IT IS NEGATIVE NUMBER');
END IF;
END;
/
DECLARE
LV_COUNTRY VARCHAR2(20) :=:LV_COUNTRY;
LV_CURRENCY VARCHAR2(20) :=:LV_CURRENCY;
BEGIN
IF LV_COUNTRY='US' THEN
DBMS_OUTPUT.PUT_LINE('COUNTRY IS :'||LV_COUNTRY);
IF LV_CURRENCY='USD' THEN
DBMS_OUTPUT.PUT_LINE('US DOLLAR');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT US DOLLAR');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('COUNTRY IS NOT US');
END IF;
END;
/
DROP TABLE XX_BANK_MASTER;
/
CREATE TABLE XX_BANK_MASTER
(
ACCTNO NUMBER PRIMARY KEY,
CUST_NAME VARCHAR2(20) NOT NULL,
ACCT_TYPE VARCHAR2(20) CHECK(ACCT_TYPE IN ('S','C','R')),
DATE_OF_OPERATION TIMESTAMP DEFAULT SYSDATE,
BAL NUMBER NOT NULL,
CONSTRAINT CHK_BAL CHECK((ACCT_TYPE='S' AND BAL>=5000) OR (ACCT_TYPE='C' AND BAL>=10000) OR (ACCT_TYPE='R' AND BAL>=5000))
);
/
INSERT INTO XX_BANK_MASTER VALUES (1,'A','S',SYSDATE,8000);
/
INSERT INTO XX_BANK_MASTER VALUES (2,'B','S',SYSDATE,6000);
/
INSERT INTO XX_BANK_MASTER VALUES (3,'C','S',SYSDATE,18000);
/
INSERT INTO XX_BANK_MASTER VALUES (4,'D','C',SYSDATE,28000);
/
INSERT INTO XX_BANK_MASTER VALUES (5,'E','C',SYSDATE,16000);
/
INSERT INTO XX_BANK_MASTER VALUES (6,'F','C',SYSDATE,12000);
/
INSERT INTO XX_BANK_MASTER VALUES (7,'G','S',SYSDATE,9000);
/
INSERT INTO XX_BANK_MASTER VALUES (8,'H','R',SYSDATE,7000);
/
INSERT INTO XX_BANK_MASTER VALUES (9,'I','R',SYSDATE,13000);
/
INSERT INTO XX_BANK_MASTER VALUES (10,'J','R',SYSDATE,6000);
/
COMMIT;
/
SELECT * FROM XX_BANK_MASTER;
/
DROP TABLE XX_BANK_TRANSACTION;
/
CREATE TABLE XX_BANK_TRANSACTION
(
TRANS_ID NUMBER PRIMARY KEY,
TRANS_TYPE VARCHAR2(20) CHECK(TRANS_TYPE IN ('D','W')),
DOT TIMESTAMP DEFAULT SYSDATE,
AMOUNT NUMBER NOT NULL,
ACCTNO NUMBER REFERENCES XX_BANK_MASTER(ACCTNO)
);
/
INSERT INTO XX_BANK_TRANSACTION VALUES (100,'D',SYSDATE,1000,1);
/
INSERT INTO XX_BANK_TRANSACTION VALUES (101,'D',SYSDATE,3000,2);
/
INSERT INTO XX_BANK_TRANSACTION VALUES (102,'D',SYSDATE,4000,3);
/
INSERT INTO XX_BANK_TRANSACTION VALUES (103,'W',SYSDATE,20000,3);
/
COMMIT;
/
SELECT * FROM XX_BANK_TRANSACTION;
/
DECLARE
LV_ACCT_NO XX_BANK_TRANSACTION.ACCTNO%TYPE :=:ACCTNO;
LV_TRANS_TYPE XX_BANK_TRANSACTION.TRANS_TYPE%TYPE :=:TRANS_TYPE;
LV_AMOUNT XX_BANK_TRANSACTION.AMOUNT%TYPE :=:AMOUNT;
LV_BAL XX_BANK_MASTER.BAL%TYPE;
LV_ACCT_TYPE XX_BANK_MASTER.ACCT_TYPE%TYPE;
BEGIN
SELECT BAL,ACCT_TYPE
INTO LV_BAL,LV_ACCT_TYPE
FROM XX_BANK_MASTER
WHERE ACCTNO=LV_ACCT_NO;
DBMS_OUTPUT.PUT_LINE('BALANCE IS : '||LV_BAL);
DBMS_OUTPUT.PUT_LINE('ACCT TYPE IS : '||LV_ACCT_TYPE);
IF UPPER(LV_TRANS_TYPE)='D' THEN
LV_BAL :=LV_BAL+LV_AMOUNT;
DBMS_OUTPUT.PUT_LINE('AFTER DEPOSIT THE AMOUNT : '||LV_BAL);
ELSIF UPPER(LV_TRANS_TYPE)='W' THEN
LV_BAL :=LV_BAL-LV_AMOUNT;
DBMS_OUTPUT.PUT_LINE('AFTER WITHDRAW THE AMOUNT : '||LV_BAL);
IF LV_ACCT_TYPE='S' AND LV_BAL<5000 THEN
RAISE_APPLICATION_ERROR(-20100,'BALANCE IS TOO LOW SO THERE IS NO TRANSACTION');
ELSIF LV_ACCT_TYPE='C' AND LV_BAL<10000 THEN
RAISE_APPLICATION_ERROR(-20100,'BALANCE IS TOO LOW SO THERE IS NO TRANSACTION');
ELSIF LV_ACCT_TYPE='R' AND LV_BAL<5000 THEN
RAISE_APPLICATION_ERROR(-20100,'BALANCE IS TOO LOW SO THERE IS NO TRANSACTION');
END IF;
END IF;
UPDATE XX_BANK_MASTER
SET BAL=LV_BAL
WHERE ACCTNO=LV_ACCT_NO;
COMMIT;
END;
/
No comments:
Post a Comment