Control Statement using tables

****************************
Control statement1 (if)
****************************

DECLARE
NAME VARCHAR2(30);
BEGIN
SELECT ENAME
INTO NAME
FROM EMP WHERE SAL=5000;
IF NAME='KING' THEN
DBMS_OUTPUT.PUT_LINE('THE FIRST TWO LETTER OF THE MAN IS -----> ' ||SUBSTR(NAME,1,2));
END IF;
END;

--------------------------------------------------------------

****************************
Control statement2 (if-then-else-elsif)
****************************

DECLARE
NAME VARCHAR2(30);
SALARY NUMBER(5);
BEGIN
SELECT ENAME,SAL
INTO NAME,SALARY
FROM EMP WHERE SAL=5000;
IF NAME='KING' THEN
DBMS_OUTPUT.PUT_LINE('THE FIRST TWO LETTER OF THE MAN IS -----> ' ||SUBSTR(NAME,1,2));
ELSIF NAME='FORD' THEN
DBMS_OUTPUT.PUT_LINE('THE SALARY OF THE MAN IS -----> ' ||SALARY);
ELSE
DBMS_OUTPUT.PUT_LINE('SORRY NO RESULT FOUND');
END IF;
END;
--------------------------------------------------------------

****************************
CONTROL STATEMENT3(case-when-then)
****************************

DECLARE
GRD CHAR:=UPPER('&GRADE');
STD VARCHAR2(30);
BEGIN
STD:=
CASE GRD
WHEN 'A' THEN 'EXCELLENT'
WHEN 'B' THEN 'VERY GOOD'
WHEN 'C' THEN 'GOOD'
ELSE 'NO SUCH GRADE'
END;
DBMS_OUTPUT.PUT_LINE('GRADE::'||GRD||'---'||'STANDERD>>'||STD);
END;
--------------------------------------------------------------

****************************
HANDLING NULL VALUE
****************************

DECLARE
X NUMBER(3):=12;
Y NUMBER(3):=0;
BEGIN
X:=12*NULL;
IF X IS NULL THEN
DBMS_OUTPUT.PUT_LINE('THE VALUE OF X IS NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('THE VALUE OF Y IS::'||Y);
END IF;
END;
--------------------------------------------------------------

****************************
USING NORMAL "LOOP-EXIT WHEN"
****************************

DECLARE
B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
LOOP
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
CNTR:=CNTR+1;
EXIT WHEN CNTR>3;
END LOOP;
END;
-------------------------------------------------------------

****************************
USING NORMAL "WHILE LOOP"
****************************

DECLARE

B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
WHILE CNTR<=3
LOOP
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
CNTR:=CNTR+1;
END LOOP;
END;
-------------------------------------------------------------

****************************

USING NORMAL "FOR LOOP"
****************************

DECLARE
B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
FOR CNTR IN 1..3
LOOP
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
END LOOP;
END;
-------------------------------------------------------------

****************************
EXAMPLE OF NESTED LOOPS
****************************

DECLARE
B NUMBER(5);
CNTR NUMBER(10):=1;
BEGIN
SELECT EMPNO INTO B FROM EMP WHERE SAL=5000;
<>
LOOP
<>
LOOP
EXIT WHEN CNTR>3;
DBMS_OUTPUT.PUT_LINE('YOUR DATA WILL BE INSERTED'||CNTR);
CNTR:=CNTR+1;
END LOOP INNER_LOOP;
INSERT INTO STUD(ROLL) VALUES(B+CNTR);
CNTR:=CNTR+1;
EXIT WHEN CNTR>7;
END LOOP OUTER_LOOP;
END;