Certify and Increase Opportunity.
Be
Govt. Certified PL SQL Developer
Conditionals
In this section, we’ll look at how to create conditional statements. A conditional statement executes a code segment based on a condition, such as an equality test (a = b), a comparison test (a > b), or a Boolean test. PL/SQL has three conditional structures: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF-THEN-…-ELSE.
The IF-THEN format executes a code block if the condition is TRUE. For example:
IF line_count > LINES_PER_PAGE
THEN
line_count := 0;
DBMS_SQL.PUT_LINE ('--------');
END IF;
The IF-THEN-ELSE format has two code blocks. If the condition is TRUE, the first block is executed; otherwise, the second block is executed. For example:
IF items_sold > get_employee_target (emp_id) THEN over_quota_count := over_quota_count + 1; give_raise (emp_id); ELSE give_talking_to (emp_id); END IF;
The IF-THEN-ELSIF-THEN-…-ELSE, PL/SQL’s equivalent of the CASE or SWITCH statement, can contain multiple conditions. The statement executes the code block associated with the first TRUE condition. Here’s an example:
IF is_number (current_char) OR is_letter (current_char) THEN new_char := current_char; ELSIF current_char = ' ' THEN new_char := '+'; ELSE new_char := convert_to_hex (current_char); END IF;
NOTE: Be careful with conditional syntax. Every PL/SQL programmer has made at least one of the following two mistakes: using END instead of END IF, or adding an “E” in the “ELSIF” keyword. In either case, the compiler gets confused and generates an error.
Loops
Looping , or iteration, causes the block between the keywords LOOP and END LOOP to be repeatedly executed. The loop ends, or terminates, when an exit condition is met. Once a loop terminates, program control is returned to the first line after the END LOOP keyword. There are three looping structures: simple, WHILE, and FOR.
In the simple loop, the exit condition is embedded inside the loop body. The EXIT command terminates the loop immediately, and is usually embedded inside an IF…THEN statement. EXIT WHEN combines EXIT with a conditional to form a more compact syntax. Here are two constructions of a simple loop. The first example uses EXIT:
LOOP
COUNT := COUNT + 1;
IF COUNT > 10
THEN
EXIT;
END IF;
END LOOP;
The second example uses EXIT WHEN:
LOOP COUNT := COUNT + 1; EXIT WHEN COUNT > 10; END LOOP;
In the second kind of loop, the WHILE loop, the exit condition is outside the body of the loop. The code within the body of the loop iterates while the loop condition is true. The loop terminates when the condition is false, for example:
WHILE (COUNT <= 10) LOOP COUNT := COUNT + 1; END LOOP;
The last kind of loop, the FOR loop, iterates a predetermined number of times. For example, the number of loops needed to process each month in the year does not depend on a complex condition; it always requires 12 passes through the loop. A FOR loop is controlled by an index variable that ranges from a lower bound to an upper bound. The index variable begins at the lower bound. Each pass through the loop increments it. The loop terminates when the index reaches the upper bound, for example:
FOR month_index IN 1 .. 12 LOOP process_month_sales (month_index); END LOOP;
There are a few things to be aware of when using FOR loops:
- The lower bound and upper bound are evaluated only once, on the first pass through the loop. Changes made to the bounds inside the body, assuming they are local variables, are ignored.
- It’s generally considered bad practice to use the EXIT command to short circuit the fixed nature of the FOR loop. If the number of loops depends on a condition, then a simple loop or WHILE loop is a clearer construct than a FOR loop.
Apply for PL SQL Certification Now!!
http://www.vskills.in/certification/Certified-PL/SQL-Developer
