3) ITERATIONS IN PL/SQL:
a) LOOPs facilitates to repeat a statement or sequence of statements multiple times.
b) The different types of PL/SQL Loops are
i) Basic or SImple Loop
ii) For Loops
iii) WHILE Loops
i) BASIC OR SIMPLE LOOP:
--------------------
a) It is the simplest form of the LOOP construct in PL/SQL.
b) It encloses a sequence o statements between the keywords LOOP and END LOOP.
c) It allows execution of its statements at least once.
d) If we keep the LOOP in finite state the EXIT statement is used.
Syntax:
LOOP
Statement1;
:
:
EXIT[WHEN Condition];
END LOOP;
-> EXIT Statement:
i) EXIT statement is used to terminate a LOOP.
ii) Once the LOOP is terminated, the Control passes to the next statement after the END LOOP.
iii) EXIT can be issued either as an action within an IF statement or as stand alone statement with LOOP.
iv) The EXIT statement should always be placed inside a LOOP.
v) EXIT ca be associated with in a WHEN clause to allow conditional termination of the LOOP.
vi) A Basic Loop can contain multiple EXIT statements.
vii) The EXIT condition can be at the top of the LOOP or at the end of the LOOP as per Logical Convenience.
Example: \SimpleLoop\ SimpleExit1.sql
Sql> DECLARE
V_Num NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The Line'||V_Num||'Output is'||V_Num);
V_Num:=V_Num+1;
IF V_Num>5 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The Total Lines of Outputs are'|| TO_CHAR(V_Num-1));
END;
Example: \SimpleLoop\ SimpleLoopExitWhen2.sql
Sql> DECLARE
V_Num NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The Line'||V_Num||'Output is'||V_Num);
V_Num:=V_Num+1;
EXIT WHEN V_Num>5;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The Total Lines of Outputs are'|| TO_CHAR(V_Num-1));
END;
->NESTED LOOPS:
i) It is a situation where one Loop is embedded into the other.
ii) The Outer Loop and the Inner Loop get associated with one another and executed simultaniously.
iii) The overall Loop terminates in declared by the Outer Loop EXIT WHEN condition or EXIT cindition.
Example: \SimpleLoop\NestedLoops3.sql
NestedLoops4.sql
Sql> DECLARE
V_Num NUMBER:=1;
BEGIN
LOOP
LOOP
EXIT WHEN V-Num>5;
DBMS_OUTPUT.PUT_LINE('Inner Loop:'||V_Num);
V_Num:=V_Num+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Outer Loop:'||V_Num);
V_Num:=V_Num+1;
EXIT WHEN V_Num>10;
END LOOP;
END;
->WHILE LOOP:
i) It can be used to repeat a sequence of statements untill the controlling condition is no longer TRUE.
ii) The condition is evaluated at the start of each iteration.
iii) The Loop terminates when the condition is FALSE.
Syntax:
WHILE Condition LOOP
Statement1;
Statement2;
:
END LOOP;
iv) If the condition yields NULL, the LOOP is bypassed and control passed to the next statement.
-> NESTED LOOPS AND LABLES:
i) LOOPs can be nested to multiple levels.
ii) All the LOOPs can be Nested into one another.
iii) LOOPs can be Labeled as per the requirements.
iv) A Label is placed before the statement, eithe on the same line or on a separate line.
v) Label Loops by placing the label before the word Loop within the Label delimiters.
vi) When the LOOP is labled, the Label Name can be optionally included after the END LOOP statement for clarity.
Example:
SQL> DECLARE
V_Number NUMBER(2):=1;
V_Output VARCHAR2(100);
BEGIN
WHILE V_Number<=10
LOOP
V_Output||''||V_Number;
V_Number:=V-Number+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Output);
END;
SQL> DECLARE
V_Number NUMBER;
V_Position NUMBER;
V_Result NUMBER:=0;
BEGIN
V_Num:=&NumbertoReverse;
WHILE V_Num>0
LOOP
V_Position:=MOD(V_Num,10);
V_Result:=(V_Result*10)+V_Position;
V_Num:=TRUNC(V_Num/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Result);
END;
->FOR LOOP:
i) It has the same general structure as the basic loop.
ii) 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 Counter IN[REVERSE]
LowerBound..UpperBound LOOP
Statement1;
Statement2;
:
:
END LOOP;
Counter:
It is an implicitly declared integer whose value automatically increased or decreased by 1 on each iteration of the LOOP untill the Upper Bound or Lower Bound is reached.
REVERSE:
It is a keyword, and causes the counter to decreament with each iteration from the UpperBound to the Lower Bound.
iii) The Counter need not be declared, as it implicitly declared as an Integer.
iv) The Lower Bound and the Upper Bound of the loop can be Literals, Variables or Expressions, but they should be evaluated to Integer.
v) The Lower and Upper bounds of a LOOP statement need not be numeric literals, they can be expressions that convert to numeric values.
Example:
Sql> DECLARE
V_StartRange NUMBER:=&StartRange;
V_EndRange NUMBER:=&EndRange;
V_Result VARCHAR2(500):=NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('Illustration of FOR LOOP......!');
FOR MyIndex IN V_StartRange.V-EndRange
LOOP
V_Result"=V_Result||''||V_StartRange;
V_StartRange:=V_StartRange+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Result);
END;
Sql> DECLARE
V_NumFact NUMBER:=&GiveNumber;
V_Factorial NUMBER:=1;
BEGIN
FOR Index1 IN V_NumFact
LOOP
V_Factorial:=V-Factorial*Index1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Factorial);
END;
Sql> DECLARE
V_NumFact NUMBER:=&GiveNumber;
V_Factorial NUMBER:=1;
BEGIN
FOR Index1 IN REVERSE 1..V_NumFact
LOOP
V_Factorial:=V-Factorial*Index1;
DBMS_OUTPUT.PUT_LINE(Index1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Factorial);
END;
Sql> DECLARE
V_Name VARCHAR2(30);
V_Position VARCHAR2(200):=NULL;
BEGIN
V_Name:='&EnterName';
FOR 1 IN 1..LENGTH(V_Name)
LOOP
V_Position:=V_Position||''||SUBSTR(V_Nmae,1,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_Position);
END;
Sql> DECLARE
V_MyChar Varchar2(20);
BEGIN
FOR MyIndex1 IN 1..15
LOOP
FOR MyIndex2 IN 1...MyIndex1
LOOP
V_MyChar:=V_MyChar||"*";
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_MyChar);
V_MyChar:=NULL;
END LOOP;
END;
Sql> DECLARE
V_OuterLoopRange NUMBER:=5;
V_InnerLoopRange NUMBER:=3;
BEGIN
<
FOR MyIndex IN 1..V_OuterLoopRange
LOOP
DBMS_OUTPUT.PUT_LINE('OuterLoop:'|| MyIndex);
<
FOR MyIndex2 IN 1..V_InnerLoopRange
LOOP
DBMS_OUTPUT.PUT_LINE('InnerLoop:'|| MyIndex2);
END LOOP;
END LOOP;
END;
AddThis
Posts
0 comments
Post a Comment
Do comment to make this blog better