You must have already learned something about the Structured Query language, but in this article, we will discuss the Procedural language extensions over the Structured Query language. Particularly about the conditional statements used for loops in PL/SQL.
We will discuss the following three statements in this article.
It is a loop control statement. When we use ‘continue’ in PL/SQL code, it simply skips the following code in the loop's body, immediately retests the loop condition, and reiterates. In other words, the code written in the loop's body becomes ineffective after the ‘continue’ statement.
The syntax to use the ‘Continue’ statement is very simple. It is given below.
Syntax
Continue;
Anything in the PL/SQL is complicated without seeing an example so let us see the 'Continue statement.'
Example
DECLARE
val NUMBER :=5;
BEGIN
WHILE val<11
LOOP
val := val +1;
IF num =8 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE(num);
END LOOP;
END;
When the above code is executed in the SQL prompt, it will produce the following output.
Output
5
6
7
9
10
As we can see in the example, when the value of Val becomes eight, we do not want to print that, and we used the 'continue' statement to skip that part.
Now we will learn about the ‘exit’ statement in PL/SQL.
Exit
The 'exit' statement is also a loop control statement. It transfers the control directly to the end of the blog. In simple words, we can end the loop without using any conditions with the help of the 'exit' statement.
We can use it in two ways
Exit(generally used with the IF statement)
Exit when
The syntax to use the exit statement is given below.
Syntax
Exit
LOOP
//body of the LOOP
Exit;
END LOOP;
Exit when
LOOP
//body of the LOOP
Exit when CONDITION;
END LOOP;
Let us now see one example where the 'exit' statement is used.
Example
DECLARE
val NUMBER :=10;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(val);
IF val =20 THEN
EXIT;
END IF;
val := val+1;
END LOOP;
END;
When we execute the above PL/SQL query, it will give the following output
Output
10
11
12
13
14
15
16
17
18
19
20
We can also write the above query with the help of the 'exit when' statement like the below.
DECLARE
val NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(val);
EXIT WHEN Val =10;
Val:= val+1;
END LOOP;
END;
It will also produce the same output as the previous query.
1
2
3
4
5
6
7
9
10
Now let us move on and discuss the ‘Goto’ statement in PL/SQL.
Goto
The 'goto' statement in the PL/SQL, is a sequential control statement. As the name suggests it jumps from the goto statement to the labelled statement present in the query.
So before using the 'goto' statement, we need to label the desired statement with the help of 'label.'
The syntax for labelling the statement in PL/SQL is given below.
<<label>>
We need to name the label before the desired statement above.
Let us now look at the syntax for the 'goto' statement in Pl/SQL.
Syntax
GOTO label_name;
//Other statements
<<label_name>>
Statement;
We will now see one example of the goto statement to understand this completely.
Example
DECLARE
val number :=3;
BEGIN
<<statement1>>
--whileloop execution
WHILE val <=12 LOOP
dbms_output.put_line(val);
val := val +1;
IF val =7 THEN
val := val +1;
GOTO statement1;
END IF;
END LOOP;
END;
When we execute the above query in the PL/SQL prompt, it will produce the following output.
Output-
3
4
5
6
8
9
10
11
12
The 'goto' statement disturbs the flow of the program, and thus it is not recommended to use.
With this, we come to the end of the blog. Let us now see some of the frequently asked questions related to this topic.
How many types of control statements are there in PL/SQL? The PL/SQL has three types of control statements, and they are listed below. Loop statements Sequential control statements Conditional selection statements
Is it possible to rewrite any program by omitting the ‘goto’ statement? Yes! We can rewrite every program in PQ/SQL without using the 'goto' statement.
Why is the use of the 'goto' statement not recommended? The use of the 'goto' statement makes the program difficult to read and understand quickly. It also disturbs the flow of the program. Thus it is not recommended to be used.
Let us now end this article with the overall summary of it.
Key takeaways
This article taught us about the PL/SQL control statements, particularly the Continue, exit, and the goto statements. We saw their syntax and discussed a suitable example of each of these statements. In the end, we explored some of the frequently asked questions related to the PL/SQL control statements.
8+ registered 