Oracle Database 10g or later

I write the following incomplete block:

DECLARE
   l_value   PLS_INTEGER := 1;
BEGIN
   LOOP
      /*EXIT*/

      IF l_value = 10
      THEN
         l_value := NULL;
      ELSE
         l_value := l_value + 1;
      END IF;
   END LOOP;
   DBMS_OUTPUT.put_line (NVL (l_value, 0));
END;
/

Which of the choices, when used in place of the /*EXIT*/ comment, results in a PL/SQL block that will display "0" after execution?

Choice 1

CASE
         WHEN l_value IS NULL
         THEN
            EXIT;
         WHEN l_value > 10
         THEN
            EXIT;
         ELSE
            NULL;
      END CASE;

Choice 2

EXIT WHEN l_value IS NULL OR l_value > 10;

Choice 3

EXIT IF l_value IS NULL OR l_value > 10;

Choice 4

EXIT WHEN l_value IS NULL;
EXIT WHEN l_value > 10;

Choice 5

IF l_value IS NULL OR l_value > 10
THEN
         EXIT;
 END IF;

Answer

PL/SQL has three categories of control statements:

• Conditional selection statements, which run different statements for different data values. The conditional selection statements are IF and CASE.

• Loop statements, which run the same statements with a series of different data values. The loop statements are the basic LOOP, FOR LOOP, and WHILE LOOP.

  The EXIT statement transfers control to the end of a loop. The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. Both EXIT and CONTINUE have an optional WHEN clause, where you can specify a condition.

• Sequential control statements, which are not crucial to PL/SQL programming. The sequential control statements are GOTO, which goes to a specified statement, and NULL, which does nothing.

PL/SQL supports three kinds of loops:

Simple (a.k.a., basic) loop

LOOP
   statements
END LOOP;

Note: one of your statements in a simple loop should be EXIT or EXIT WHEN to ensure that you don't end up with an infinite loop.

WHILE loop

WHILE condition
LOOP
   statements
END LOOP;

FOR loop

FOR iterator IN low_value .. high_value
LOOP
   statements
END LOOP;

FOR record IN [cursor | (SELECT statement) ]
LOOP
   statements
END LOOP;

Use the EXIT WHEN statement to exit from a loop (and our recommendation would be to use this only with a simple or basic loop to conform. to the "one way in, one way out" principle) when the conditional expression evaluates to TRUE.

Verification

DECLARE
   l_value   PLS_INTEGER := 1;
BEGIN
   LOOP
      CASE
         WHEN l_value IS NULL
         THEN
            EXIT;
         WHEN l_value > 10
         THEN
            EXIT;
         ELSE
            NULL;
      END CASE;

      IF l_value = 10
      THEN
         l_value := NULL;
      ELSE
         l_value := l_value + 1;
      END IF;
   END LOOP;
   DBMS_OUTPUT.put_line (NVL (l_value, 0));
END;
/

DECLARE
   l_value   PLS_INTEGER := 1;
BEGIN
   LOOP
      EXIT WHEN l_value IS NULL OR l_value > 10;

      IF l_value = 10
      THEN
         l_value := NULL;
      ELSE
         l_value := l_value + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (NVL (l_value, 0));
END;
/

DECLARE
   l_value   PLS_INTEGER := 1;
BEGIN
   LOOP
      EXIT IF l_value IS NULL OR l_value > 10;

      IF l_value = 10
      THEN
         l_value := NULL;
      ELSE
         l_value := l_value + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (NVL (l_value, 0));
END;
/

DECLARE
   l_value   PLS_INTEGER := 1;
BEGIN
   LOOP
      EXIT WHEN l_value IS NULL;
      EXIT WHEN l_value > 10;

      IF l_value = 10
      THEN
         l_value := NULL;
      ELSE
         l_value := l_value + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (NVL (l_value, 0));
END;
/

DECLARE
   l_value   PLS_INTEGER := 1;
BEGIN
   LOOP
      IF l_value IS NULL OR l_value > 10
      THEN
         EXIT;
      END IF;

      IF l_value = 10
      THEN
         l_value := NULL;
      ELSE
         l_value := l_value + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (NVL (l_value, 0));
END;
/

Resources

Link to Quiz

http://plsqlchallenge.com/pls/apex/f?p=10000:659:::NO:659:P659_COMP_EVENT_ID,P659_QUIZ_ID,P659_QUESTION_ID:51189,305831,&cs=149832DFE71FE2F92C5195E7C23D528BA