pl/sql loops. building logical conditions all logical conditions must yield a boolean condition. you...

PL/SQL Loops

Building Logical ConditionsBuilding Logical Conditions

• All logical conditions must yield a boolean condition.

• You can build a simple Boolean condition by combining number, character or date expressions with a comparison operator

Examples:v_name1 = v_name2v_name1 = v_name2 v_name != ‘Ali’v_name != ‘Ali’

v_cgpa between 2.00 and 4.00v_cgpa between 2.00 and 4.00v_cgpa > 2.50v_cgpa > 2.50

v_name=‘Ali’ and v_deptno=35 and v_cgpa between 2.00 and 4.00v_name=‘Ali’ and v_deptno=35 and v_cgpa between 2.00 and 4.00

Using NULL values inUsing NULL values in Logical Logical ConditionsConditions

Using NULL values inUsing NULL values in Logical Logical ConditionsConditions

•You MUSTMUST handle null values with the IS NULL or IS NOT NULL operator.•Any arithmetic expression containing a null value evaluates to NULL.•Concatenated expressions with null values treat null values as an empty string.

Logic TablesLogic TablesLogic TablesLogic Tables

NOT

TRUE

FALSE

NULL

OR

TRUE

FALSE

NULL

TRUE FALSE NULL

FALSE

TRUE

NULL

AND

TRUE

FALSE

NULL

TRUE FALSE NULL

TRUE

NULL NULL

NULL

FALSE FALSE

FALSE

FALSE

FALSE

TRUE

TRUE

TRUE

TRUETRUE

FALSE

NULL NULL

NULL

Boolean conditionBoolean conditions with Logical s with Logical operatoroperatorss

Boolean ConditionsBoolean ConditionsBoolean ConditionsBoolean Conditions

V_REORDER_FLAG V_AVAILABLE_FLAG V_FLAG

TRUE TRUE

TRUE FALSE

NULL TRUE

NULL FALSE

v_flag := v_reorder_flag AND v_available_flag; v_flag := v_reorder_flag AND v_available_flag;

TRUETRUE

FALSEFALSE

NULLNULL

FALSEFALSE

What is the value of V_FLAG in each case?

Iterative Control: LOOP Iterative Control: LOOP StatementsStatements

Iterative Control: LOOP Iterative Control: LOOP StatementsStatements

• Loops repeat a statement or sequence of statements multiple times.

• There are three loop types:– Basic loop– FOR loop– WHILE loop

Main uses of Loop Types

Use• Basic loop: to provide repetitive actions

without overall conditions• FOR loops: to provide iterative control of

actions based on a count• WHILE: loops to provide iterative control of

actions based on a condition• EXIT statement : to terminate loops

depending on your programming logic

Basic LoopBasic LoopBasic LoopBasic Loop

LOOP statement1; . . . EXIT [WHEN condition];END LOOP;

LOOP statement1; . . . EXIT [WHEN condition];END LOOP;

where: condition is a Boolean variable or expression (TRUE, FALSE, or NULL);

where: condition is a Boolean variable or expression (TRUE, FALSE, or NULL);

-- delimiter-- delimiter showing beginning of the loop showing beginning of the loop

-- statements-- statements

-- EXIT statement-- EXIT statement

-- delimiter-- delimiter showing end of the loop showing end of the loop

Syntax

If the exit statement of a basic loop is missing, you have an infinite loop

The EXIT Statement• You can terminate a loop using the EXIT statement.• Control passes to the next statement after the END

LOOP statement. • You can issue EXIT either as an action within an IF

statement or as a standalone statement within the loop.

• The EXIT statement must be placed inside a loop. – You can attach a WHEN clause to allow conditional

termination of the loop. • When the EXIT statement is encountered, the

condition in the WHEN clause is evaluated. – If the condition yields TRUE, the loop ends and control

passes to the next statement after the loop.

• A basic loop can contain multiple EXIT statements.

Basic LoopBasic LoopBasic LoopBasic Loop

DECLARE v_counter NUMBER(2) := 0;BEGIN LOOP DBMS_OUTPUT.PUT_LINE(‘Current Value is ‘||v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;END;

DECLARE v_counter NUMBER(2) := 0;BEGIN LOOP DBMS_OUTPUT.PUT_LINE(‘Current Value is ‘||v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP;END;

Example:

Common mistake: failing to increment v_counter will result in an infinite loop!

FOR LoopFOR LoopFOR LoopFOR Loop

FOR counter in [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . .END LOOP;

FOR counter in [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . .END LOOP;

Syntax

• Use a FOR loop to shortcut the test for the number of iterations.

• You do not have to declare the index; it is declared implicitly.

• Counter : an implicitly declared integer whose value automatically increases or decreases by 1 on each iteration of the loop until the upper or lower bound is reached

• REVERSE : causes the index to decrement with each iteration from the upper bound to the lower bound – The lower bound is still referenced first.– If the lower bound of the loop range evaluates

to a larger integer than the upper bound, the sequence of statements will not be executed.



• Guidelines– Reference the counter within the loop only; it

is undefined outside the loop.– Use an expression to reference the existing

value of a counter.– Do not reference the counter as the target of

an assignment.


BEGIN FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(‘Current value is ‘|| i); END LOOP;END;

BEGIN FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(‘Current value is ‘|| i); END LOOP;END;

• Print numbers 1 to 10 on the screen

• Example

WHILE LoopWHILE LoopWHILE LoopWHILE Loop

WHILE condition LOOP statement1; statement2; . . .END LOOP;

WHILE condition LOOP statement1; statement2; . . .END LOOP;

Condition isCondition isevaluated at the evaluated at the beginning ofbeginning ofeach iteration.each iteration.

Syntax

• Use the WHILE loop to repeat statements while a condition is TRUE.

• You can use the WHILE loop to repeat a sequence of statements until the controlling condition is no longer TRUE. – The condition is evaluated at the start of each iteration. – The loop terminates when the condition is FALSE.

• If the condition is FALSE at the start of the loop, then no further iterations are performed.

• If the condition is NULL, the loop is bypassed and control passes to the next statement.

• If the variables involved in the conditions do not change during the body of the loop, then the condition remains TRUE and the loop does not terminate—infinite loop.



DECLAREv_counter NUMBER(2) := 1;BEGIN WHILE v_counter <= 10 LOOP DBMS_OUTPUT.PUT_LINE(‘Current Value is’ || v_counter); v_counter := v_counter + 1; END LOOP;END;

DECLAREv_counter NUMBER(2) := 1;BEGIN WHILE v_counter <= 10 LOOP DBMS_OUTPUT.PUT_LINE(‘Current Value is’ || v_counter); v_counter := v_counter + 1; END LOOP;END;

• Print numbers 1 to 10 on the screen

• Example

Nested Loops and LabelsNested Loops and LabelsNested Loops and LabelsNested Loops and Labels

• Nest loops to multiple levels.– You can nest FOR, WHILE, and basic loops within

one another– The termination of a nested loop does not terminate

the enclosing loop unless an exception was raised

• Use labels to distinguish between blocks and loops.

• Exit the outer loop with the EXIT statement referencing the label.

• A label is placed before a statement, either on the same line or on a separate line.

• Label loops by placing the label before the word LOOP within label delimiters (<<label>>).

• If the loop is labeled, the label name can optionally be included after the END LOOP statement for clarity.



...BEGIN <<Outer_loop>> LOOP v_counter := v_counter+1; EXIT WHEN v_counter>10; <<Inner_loop>> LOOP ... EXIT Outer_loop WHEN total_done = 'YES'; -- Leave both loops EXIT WHEN inner_done = 'YES'; -- Leave inner loop only ... END LOOP Inner_loop; ... END LOOP Outer_loop;END;

...BEGIN <<Outer_loop>> LOOP v_counter := v_counter+1; EXIT WHEN v_counter>10; <<Inner_loop>> LOOP ... EXIT Outer_loop WHEN total_done = 'YES'; -- Leave both loops EXIT WHEN inner_done = 'YES'; -- Leave inner loop only ... END LOOP Inner_loop; ... END LOOP Outer_loop;END;

Using the label of the outer loop in the exit statement terminates the outer loop as well as the inner loop.

SummarySummarySummarySummary

• This week we learned about Loops– Basic loop– FOR loop– WHILE loop– EXIT statement

• Next Lesson we will learn how to use SELECT and DML statements inside PL/SQL blocks.

pl/sql loops. building logical conditions all logical conditions must yield a boolean condition. you...

Documents