bing
Flat 10% & upto 50% off + Free additional Courses. Hurry up!

PL/SQL Control Structures

 

PL/SQL Control Structures

Procedural computer programs use the basic control structures.

 

control structures

 

  • The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or false. A condition is any variable or expression that returns a BOOLEAN value (TRUE or FALSE).
  • The iteration structure executes a sequence of statements repeatedly as long as a condition holds true.
  • The sequence structure simply executes a sequence of statements in the order in which they occur.

 

 Testing Conditions: IF and CASE Statements

The IF statement executes a sequence of statements depending on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

The CASE statement is a compact way to evaluate a single condition and choose between many alternative actions. It makes sense to use CASE when there are three or more alternatives to choose from.

 

  • Using the IF-THEN Statement

The simplest form of IF statement associates a condition with a sequence ofstatements enclosed by the keywords THEN and END IF (not ENDIF)

The sequence of statements is executed only if the condition is TRUE. If the condition is FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement.

Example: Using a Simple IF-THEN Statement

 DECLARE

sales NUMBER(8,2) := 10100;

quota NUMBER(8,2) := 10000;

bonus NUMBER(6,2);

emp_id NUMBER(6) := 120;

BEGIN

IF sales > (quota + 200) THEN

bonus := (sales - quota)/4;

UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;

END IF;

END;

/

 

  • Using CASE Statements

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.

Example: Using the CASE-WHEN Statement

 DECLARE

grade CHAR(1);

BEGIN

grade := 'B';

CASE grade

WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');

WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');

WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');

WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');

WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');

ELSE DBMS_OUTPUT.PUT_LINE('No such grade');

END CASE;

END;

/

 

Controlling Loop Iterations: LOOP and EXIT Statements

LOOP statements execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.

 

  • Using the LOOP Statement

The simplest form of LOOP statement is the basic loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows:

LOOP

sequence_of_statements

END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. You use an EXIT statement to stop looping and prevent an infinite loop. You can place one or more EXIT statements anywhere inside a loop, but not outside a loop. There are two forms of EXIT statements: EXIT and

EXIT-WHEN.

 

  • Using the EXIT Statement

The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement.

 

  • Using the EXIT-WHEN Statement

The EXIT-WHEN statement lets a loop complete conditionally. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement after the loop.

 

  • Labeling a PL/SQL Loop

Like PL/SQL blocks, loops can be labeled. The optional label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement. The label name can also appear at the end of the LOOP statement. When you nest labeled loops, use ending label names to improve readability.

 

  • Using the WHILE-LOOP Statement

The WHILE-LOOP statement executes the statements in the loop body as long as a condition is true:

WHILE condition LOOP

sequence_of_statements

END LOOP;

 

Using the FOR-LOOP Statement

Simple FOR loops iterate over a specified range of integers. The number of iterations is known before the loop is entered. A double dot (..) serves as the range operator. The range is evaluated when the FOR loop is first entered and is never re-evaluated. If the lower bound equals the higher bound, the loop body is executed once.

Example: Using a Simple FOR..LOOP Statement

 DECLARE

p NUMBER := 0;

BEGIN

FOR k IN 1..500 LOOP -- calculate pi with 500 terms

p := p + ( ( (-1) ** (k + 1) ) / ((2 * k) - 1) );

END LOOP;

p := 4 * p;

DBMS_OUTPUT.PUT_LINE( 'pi is approximately : ' || p ); -- print result

END;

/

 

Sequential Control: GOTO and NULL Statements

The GOTO statement is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The NULL statement can improve readability by making the meaning and action of conditional statements clear.

Overuse of GOTO statements can result in code that is hard to understand and maintain. Use GOTO statements sparingly. For example, to branch from a deeply nested structure to an error-handling routine, raise an exception rather than use a GOTO statement.

 

  • Using the GOTO Statement

The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. The labeled statement or block can be down or up in the sequence of statements.

Example : Using a Simple GOTO Statement

 DECLARE

p VARCHAR2(30);

n PLS_INTEGER := 37; -- test any integer > 2 for prime

BEGIN

FOR j in 2..ROUND(SQRT(n)) LOOP

IF n MOD j = 0 THEN -- test for prime

p := ' is not a prime number'; -- not a prime number

GOTO print_now;

END IF;

END LOOP;

p := ' is a prime number';

<<print_now>>

DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);

END;

/

 

  • Using the NULL Statement

The NULL statement does nothing, and passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).

Example: Using the NULL Statement to Show No Action

 DECLARE

v_job_id VARCHAR2(10);

v_emp_id NUMBER(6) := 110;

BEGIN

SELECT job_id INTO v_job_id FROM employees WHERE employee_id = v_emp_id;

IF v_job_id = 'SA_REP' THEN

UPDATE employees SET commission_pct = commission_pct * 1.2;

ELSE

NULL; -- do nothing if not a sales representative

END IF;

END;

/

"0 Responses on PL/SQL Control Structures"

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

top

Sales Offer

  • To avail this offer, enroll before 05th December 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
offer-june

Sign Up or Login to view the Free PL/SQL Control Structures.