Nested FOR loops

Nested FOR loops:

SQL>DECLARE
    n NUMBER:=&no;
    v VARCHAR2(100);
    BEGIN
    FOR i IN 1..n
    LOOP
    FOR k IN 1..i
    LOOP
    v:=v||’ ‘||K;
    END LOOP;
    display(v);
    v:=null;
    END LOOP;
    END;

WHILE Loops

WHILE Loops:

• The WHILE LOOP statement includes a condition associates with a sequence of statement.
• If the condition evaluates to true, then the sequence of statements will be executed, and again control resumes at the beginning of the loop.
• IF the condition evaluates to false or NULL, then the loop is bypassed and the control passes to the next statement.

Syntax:
    WHILE condition LOOP
    Statement1;            condition is
    Statement2;            evaluated at the beginning of
    …                each iteration.
    END LOOP;

SQL>DECLARE
    n NUMBER(3):=1;
    v VARCHAR2(100);
    BEGIN
        WHILE n<=10
        LOOP
            v:=v||’ ‘||N;
            n:=n+1;
        END LOOP;
    display(v);
    END;

SQL>DECLARE
    n NUMBER(5):=&N;
    s NUBMER(5):=0;
    r NUMBER(2):=0;
    BEGIN
    WHILE n!=0
    LOOP
    r:=mod(n,10);
    s:=s+r;
    n:=trunc(n/10);
    END LOOP;
    display(‘ The sum of digit of given number is’||s);
    END;

FOR LOOP

FOR LOOP:
 

• It has the same general structure as the basic loop.
• “FOR LOOP” contains a control statement at the front of the LOOP keyword, to determine the number of iterations that PL/SQL performs.

Syntax:

FOR loop_counter IN [REVERSE] Lowerbound.Upperbound LOOP
Statement1;
Statement2;
END LOOP;

Counter:

It is an implicitly declared INTEGER whose value is automatically increased or decreased by 1 on each iteration of the LOOP until the upper bound or lower bound is reached.

Reverse:

• It is a keyword, and causes the counter to decrement with each iteration from the upper bound to the lower bound.
• The loop_counter need not be declared, as it is implicitly declared as an integer.
• The bounds of the loop are evaluated once.
• This determines the total number of iterations that loop_counter will take on the values ranging from low_bound to high_bound, incrementing by 1 each time until the loop is complete.

SQL>DECLARE
    v_FactNum NUMBER:=&No;
    v_Factorial NUMBER:=1;
BEGIN
    FOR v_Counter IN REVERSE 1..v_FactNum
    LOOP
        v_Factorial:=v_Factorial*v_Counter;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(‘The Factorial of’||v_FactNum||’is:’||v_Factorial);
END;

SQL>DECLARE
    j NUMBER(2):=&J;
    v VARCHAR2(100);
    k NUMBER(3);
    BEGIN
    FOR  i IN 1..10
    loop
    k:=j*i;
    v:=v||j||’*’||i||’=‘||k||’ ‘;
    end loop;
    display(v);
    END;

Restrictions on GOTO

Restrictions on GOTO:

• It is illegal to branch into an inner block, loop, or IF statement.
• “GOTO” cannot navigate from the EXCEPTION selection to any other section of the PL/SQL block.
• “GOTO” cannot reference a LABLE in a nested block.
• “GOTO” cannot be executed outside an “IF” clause to LABLE inside “IF” clause.
• It is better to have a limited usage of “GOTO” in programming block.
• “GOTO” cannot navigate from the EXCEPTION selection to any other section of the PL/SQL block.

SQL>DECLARE
    n NUMBER:=10;
    BEGIN
        GOTO l_innerblock;
        IF n>20 THEN
        <<l_innerblock>>
        INSERT INTO dept VALUES(50,’SW’,’HYD’);
        END IF;
    END;

SQL>BEGIN
        GOTO l_innerblock;
    BEGIN
    <<l_innerblock>>
    INSERT INTO dept VALUES(50,’SW’,’HYD’);
    END;
    END;

GOTO and Labels

GOTO and Labels:
 

• The “GOTO” statements allows us to branch to a label unconditionally.
• The label, which is enclosed within double angle brackets must precede an executable SQL statement or a PL/SQL block.
• When executed, the GOTO statements transfers control to the labelled statement or block.

Syntax:
    GOTO LabelName;

Scenario: 
SQL>CREATE TABLE product_master
    (product_no varchar2(6) constraint pk_product_pk primary key,
    Description varchar2(20),
    Unit_measure varchar2(20),
    Qty_on_hand number(8),
    Reorder_lvl number(8),
    Cost_price number(10,2),
    Selling_price number(8,2));

SQL>DECLARE
    v_Qtyhand product_master.qty_on_hand%type;
    v_Relevel product_master.reorder_lvl%type;
    v_product_no product_master.product_no%type;
    BEGIN
        v_product_no:=‘&prodno’;
        SELECT qty_no_hand,reorder_lvl INTO
        v_Qtyhand,v_Relevel
        FROM product_master
        WHERE product_no=v_product_no;
        IF v_Qtyhand<v_Relevel> THEN
            GOTO updation;
        ELSE
            GOTO noupdation;
        END IF;
        <<updation>>
        UPDATE product_master SET
        qty_on_hand=qty_on_hand+reorder_lvl
        WHERE  product_no=v_product_no;
        RETURN;
        <<noupdation>>
        display(‘There are enogh product’);
        RETURN;
        END;
 
Note:

• Hence to keeps proper meaning within the sequence “RETURN” should be used…

SQL>DECLARE
        v_Counter NUMBER:=1;
    BEGIN
    LOOP
        INSERT INTO temp_table
        VALUES(v_Counter,’Loop Count’);
        v_Counter:=v_Counter+1;
        IF v_Counter>=50 THEN
        GOTO EndOfLoop;
        END IF;
        END LOOP;
        <<EndOfLoop>>
        INSERT INTO temp_table(Ind) VALUES(‘Done!’);
    END;

Nested loops and labels

Nested loops and labels:
 

• Loops can be nested to multiple levels.
  All the loops can be nested into one another.
• Loops can be labelled as per the requirements.
• Label loops by placing the label before the word loop within the label delimiters.
• When the loop is labelled, the label name can be optionally included after the END LOOP statement for clarity.

SQL>DECLARE
    v_num NUMBER:=1;
    BEGIN
    <<outerloop>> LOOP
    <<innerloop>> LOOP
    EXIT WHEN v_num>5;
    Display(‘Inner loop:’||v_num);
    v_num:=v_num+1;
    END LOOP innerloop;
    Display(‘Outer loop:’||v_num);
    v_num:=v_num+1;
    EXIT WHEN v_num>10;
    END LOOP outerloop;
    END;

Nested Loops

Nested Loops:
 

• It is a situation where one loop is embedded into the other.
• The outer loop and the inner loop get associated with one another and execute simultaneously.
• The overall loop terminates is dictated  by the outer loop’s “EXIT WHEN” condition or “EXIT” condition.
• In nested loop’s the outer loops condition evaluated as TRUE, always makes the inner loop to resume its process ad the inner loop’s termination actually makes the outer loop to update its process.

SQL>DECLARE
    v_num NUMBER:=1;
    BEGIN
    LOOP
    EXIT WHEN v_num>10;
       LOOP
    EXIT WHEN v_num>5;
    display(‘Inner loop:’||v_num);
    v_num:=v_num+1;
    END LOOP;
     display(‘Outer loop:’||v_num);
    v_num:=v_num+1;
    END LOOP;
    END;

EXIT Statement

EXIT Statement:

• “EXIT” Statement is used to terminate a LOOP.
• Once the loop is terminated, the control passes to the next statement after the “END LOOP”.
• The “EXIT” statement should always be placed inside the loop only.
• “EXIT” can be associate with a “WHEN” clause to allow conditional termination of the loop.
• The “EXIT” condition can be at the top of the loop or at the end of the loop as per logical convenience.
• Depending upon the circumstances we can make use of this LOOP as Pre-Tested loop or Post-Tested loop construct.
• The loop terminates its process when the conditional state is “TRUE”.
• The statement EXIT WHEN condition is equivalent to

IF condition THEN
    EXIT;
END IF;
 
SQL>DECLARE
    v_counter Number:=1;
    BEGIN
        LOOP
    INSERT INTO temp_table
    VALUES(v_Counter,’Loop index’);
    v_Counter:=v_Counter+1;
    EXIT WHEN v_Counter>50;
        END LOOP;
    END;

SQL>DECLARE
    a  NUMBER:=100;
    BEGIN
    LOOP
        IF a=250 THEN
        EXIT;
        END IF;
    a:=a+25;
    display(a);
    END LOOP;
    END;

Simple Loop

Simple Loop:

• The most basic kind of loop, simple loops, have the syntax.

LOOP
    Sequence_of_statements;

• The sequence_of_statements will be executed between the keywords “LOOP” and “END LOOP”.
• To keep the loop in finite state the “EXIT” statement is used.
• We can add one with the EXIT statement which has the following syntax:
• EXIT [WHEN Condition];

Loops

Loops:

• Loops repeat a statement or sequence of statements multiple times.
• There are three loop types:

• Basic loop or Simple Loop.
• FOR loop.
• WHILE loop.

Labeled CASE Statements

Labeled  CASE Statements:

• A CASE statement can optionally be labelled, like a PL/SQL block.
• If a CASE statement is labelled, then the label can also appear after the END CASE clause.

SQL>DECLARE
        v_TestVar NUMBER:=10;
    BEGIN
        <<Mycase>>
        CASE v_TestVar
        WHEN 10 THEN
        DBMS_OUTPUT.PUT_LINK(‘ACCOUNTING’);    
WHEN 20 THEN
        DBMS_OUTPUT.PUT_LINK(‘RESEARCH’);
        WHEN 30 THEN
        DBMS_OUTPUT.PUT_LINK(‘SALES’);
WHEN 40 THEN
        DBMS_OUTPUT.PUT_LINK(‘OPERATIONS’);
        END CASE Mycase;
    END;

SQL>Create or replace procedure display(s varchar2)
    IS
    DBMS_OUTPUT.PUT_LINE(S);
    END;

CASE Expressions

CASE Expressions:

• It is similar to a switch statement in C.
• When a particular search condition evaluates to TRUE, the group of statements associated with this condition are executed.
• We can evaluate conditions using the normal conditional operators or SQL*Plus operators.

CASE expression Syntax:
CASE test_var
    WHEN value_1 THEN
        Sequence_of_Statements1;
    WHEN value_2 THEN
        Sequence_of_Statements_2;
……..
    WHEN value_n THEN
        Sequence_of_Statements_n;
[ELSE
        else_sequence;]
END CASE;

• The reserved word “CASE” marks the beginning of the CASE statement.
• The selector is a value that determines, which “WHEN” clause should be executed.
• Each when clause contains as expression and or more executable statements associated with it.
• The “ELSE” clause is optional.
• Each “CASE” statement is marked with “END CASE”.
• Where test_var is the variable or expression to be tested, value_1 through value_n are the comparison values.
• If none of the values are equal, then else_sequence will be executed.

SQL>DECLARE
    v_Dname varchar2(20);
        v_Deptno number;
    BEGIN
        SELECT deptno into v_Deptno
        FROM emp
        WHERE empno=&eno;
        CASE v_Deptno
        WHERE 10 THEN
        v_Dname:=‘ACCOUNTING’;
        WHERE 20 THEN
        v_Dname:=‘RESEARCH’;
        WHERE 30 THEN
        v_Dname:=‘SALES’;
WHERE 40 THEN
        v_Dname:=‘OPERATIONS’;
        ELSE
        v_Dname:=‘UNKNOW’;
        END CASE;
        DBMS_OUTPUT.PUT_LINE(‘Emp dept name is:’||v_Dname);
    END;

ELSIF Statements

ELSIF Statements:
Syntax:
IF Condition1 THEN
    Statement1;
ELSIF Condition2 THEN
    Statement2;
ELSIF Condition3 THEN
    Statement3;
ELSE
    Statementn;
END IF;

SQL>DECLARE
     v_TotalEmps NUMBER;
    BEGIN
        SELECT COUTN(*)
        INTO v_TotalEmps
        FROM emp;
        IF v_TotalEmps=0 THEN
        INSERT INTO temp_table values(‘There are no emps’);
        ELSIF v_TotalEmps <5 THEN
        INSERT INTO temp_table  values(‘There are few emps joined’);
            ELSIF v_TotalEmps <10 THEN
        INSERT INTO temp_table values(‘There are little more emps joined’);
        ELSE
        INSERT INTO temp_table values(‘There are many emps joined’);
        END IF;
    END;

Branching with Logical connectivity’s

Branching with Logical connectivity’s:

• In this situation one “IF” is associated with a collection of conditions using either logical either logical “AND” or logical “OR” operator.

Syntax1:
If condition AND condition2 THEN
    Statement1;
    Statement2;
ELSE
    Statement1;
    Statement2;
END IF:

Syntax2:
IF condition1 AND condition2 THEN
    Statement1;
    Statement2;
ELSE
    Statement1;
    Statement2;
END IF;

Syntax3:
IF condition1 AND condition2 OR condition3 xTHEN
Statement1;
    Statement2;
ELSE
    Statement1;
    Statement2;
END IF;

Behaviour of NULL’s

 
Behaviour of NULL’s:

• In simple “IF” when a condition is evaluated to NULL, then the statements in “TRUE” state will not be executed, instead the control will be passed to the first executable statement after the “END IF”.
• In IF…THEN…ELSE construct the FALSE block is executed whenever the condition evaluates to NULL.
• Hence when ever a conditional process is executed it is better to cross verify the NULL status of any variable or value before execution.

SQL> DECLARE
        v_Firstnum NUMBER:=&Fnum;
        v_Secondnum NUMBER:=&Snum;
    BEGIN
    IF v_Firstnum=v_Secondnum THEN
    DBMS_OUTPUT.PUT_LINE(‘Given numbers are equal’);
        END IF;
    DBMS_OUTPUT.PUT_LINE(‘Did you watch the NULL effect.’);
    END;

Note: Supply NULL at runtime to check the effect of NULL

SQL> DECLARE
        v_num NUMBER:=&Enternumber;
    BEGIN
    IF MOD(v_num,2)=0 THEN
    DBMS_OUTPUT.PUT_LINE(‘v_num||’is an Even number.’);
    ELSE
    DBMS_OUTPUT.PUT_LINE(‘v_num||’is an Odd number.’);
        END IF;
    DBMS_OUTPUT.PUT_LINE(‘Did you watch the Difference….’);
    END;