PL/SQL tutorial 11: PL/SQL Simple Loop in Oracle Database by Manish Sharma

PL/SQL tutorial 11: PL/SQL Simple Loop in Oracle Database by Manish Sharma

What’s up Internet? Welcome back once again
I am Manish from Today with this tutorial I will be starting a new
series on Iterative statements also known as Loops in Oracle PL/SQL.
As we know that loops execute block of statements or a part of a program several times. In PL/SQl
we have 4 types of loops. 1. Simple Loop
2. While Loop 3. Numeric For Loop and
4. Cursor For loop In this series we will focus over the first
3 types of loops. The last type which is “Cursor For Loop” will be discussed with Cursor
in future videos. Having said that let’s start today’s tutorial with Simple Loop.
Simple loop is the most basic loop in Oracle PL/SQL. Let’s take a look at the syntax.
This will help you in better understanding as to why we call it a simple loop.
LOOP Statement 1;
Statement 2; …
Statement 3; END LOOP;
Here keyword LOOP marks the beginning and phrase END LOOP marks the ending of the loop.
In between we have a sequence of executable statements.
As you can see in this syntax that unlike conventional loops here we do not have update
statements or for that matter exist conditions which will terminate the loop. May be that
is why we call this a simple loop. But wait, it doesn’t have an exit condition!
Does that mean that it is an infinity loop? Hmmm yeah, you can say so.
To find out the answer of this question let’s jump over to our SQL Developer and do some
examples. I think nothing is better than a multiplication table for the demonstration
of this loop. So let’s write a program to print a Multiplication
table of 19. Why 19? Because I am still struggling with it. God why is it so hard.
DECLARE v_counter NUMBER :=0;
v_result NUMBER; BEGIN
LOOP v_counter :=v_counter+1;
v_result :=19*v_counter; DBMS_OUTPUT.PUT_LINE(’19’||’ x ‘||v_counter||’
=’|| v_result); END LOOP;
END; So here we are. In the declaration section
we declared 2 variables of Number data type. Variable v_counter will serve as a counter
and variable v_result will hold the result of multiplication. Down in the execution section
we have our simple loop. In this loop we have 3 statements first statement will work as
our update statement. This will update our counter and increment it by 1 with every iteration.
Statement 2 is an arithmetic expression which will perform the multiplication of our table
and will store the result in v_result variable. Statement 3 is an output statement which will
print the result of the multiplication in a formatted manner.
But here we do not have any exit statement to terminate the loop. This means that if
we execute this program then the execution will keep on printing till we halt it manually.
In this case Oracle PL/SQL gives us two clauses to terminate the loop
1. Exit 2. Exit When
Exit clause will terminate the loop when Exit condition is evaluated to be true. The exit
condition is evaluated with the help of Simple IF THEN condition which we discussed in PL/SQL
Tutorial 8. So let’s see how you can use this exit statement in this example.
IF v_counter>=10 THEN EXIT;
END IF; You simply have to add this IF THEN block
either right above the phrase END LOOP or immediately below the keyword loop. What this
IF THEN block will do; you ask? This block will keep an eye on your counter and will
tell the control to exit the loop when counter either becomes greater than or equal to 10.
Which means loop will execute for 10 times. Let’s execute and see.
And here is our result. You know what? Writing extra LOC was never
my style I always search for the option which can help me in reducing line of codes. For
terminating this loop we have that option which is the 2nd clause EXIT WHEN. Using this
clause you can replace this whole IF THEN block with a simple single statement. Yes
You heard it right just a single Line of code in place of this whole block. Let me show
you how. EXIT WHEN i_counter>=10;
That’s all you have to write at the place of the IF THEN block. Let’s execute and
see the result, Here we are as you can see the result is the
same as that of the previous one. Hope this example helped you in learning the
concept of this simple loop. If you like this video then give it a thumbs up and help your
friends by sharing this video with them or on your social media. More importantly don’t
forget to subscribe as many more interesting videos are yet to come.
That’s it for today, Thanks for watching will catch you soon till then take care. This
is Manish from RebellionRider

Danny Hutson

31 thoughts on “PL/SQL tutorial 11: PL/SQL Simple Loop in Oracle Database by Manish Sharma

  1. its my informatic test tomorrow and after watching 11 videos in a row i am damn sure i'll get good marks in PL/SQL portion . thank you so much.

  2. can Mr.Manish or someone tell me is there any online execution tools available for oracle 11g execution just like we have online compilers for C

  3. Hi Manish, hope you are doing great! Very nice Job! Allow me to ask you a question, I am developing an application using Oracle APEX and am struggling with the PL/SQL to write a code with the capability to send mails with the results of a SQL query like a table with some columns and rows by using HTML formatting. Any Idea how to cross html and PL/sql? I've a script which get really close work but it isn't =( … I'd appreciate your support! Best regards from Brazil!

Leave a Reply

Your email address will not be published. Required fields are marked *