In this blog, we’ll discuss conditions in PL/SQL. Decision-making structures require that the programmer specify one or more conditions to be evaluated or tested by the program, alongside a press release or statements to be executed if the condition is decided to be true, and optionally, other statements to be executed if the condition is decided to be false.
Following is that the general sort of a typical conditional (i.e., decision making) structure found in most of the programming languages −

PL/SQL programing language provides following sorts of decision-making statements. Click the subsequent links to see their detail.
1 IF – THEN statement
The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is true, the statements get executed and if the condition is fake or NULL then the IF statement does nothing.
2 IF-THEN-ELSE statement
IF statement adds the keyword ELSE followed by an alternate sequence of statement. If the condition is fake or NULL, then only the choice sequence of statements get executed. It ensures that either of the sequence of statements is executed.
3 IF-THEN-ELSIF statement
It allows you to settle on between several alternatives.
4 Case statement
Like the IF statement, the CASE statement selects one sequence of statements to execute.
However, to pick the sequence, the CASE statement uses a selector instead of multiple Boolean expressions. A selector is an expression whose value is employed to pick one among several alternatives.
5 Searched CASE statement
The searched CASE statement has no selector, and it’s WHEN clauses contain search conditions that yield Boolean values.
6 nested IF-THEN-ELSE
You can use one IF-THEN or IF-THEN-ELSIF statement inside another IF-THEN or IF-THEN-ELSIF statement(s).
PL/SQL – Loops
In this blog, we’ll discuss Loops in PL/SQL. There could also be a situation once you got to execute a block of code several number of times. generally, statements are executed sequentially: the primary statement during a function is executed first, followed by the second, and so on.
Programming languages provide various control structures that leave more complicated execution paths.
A loop statement allows us to execute a press release or group of statements multiple times and following is that the general sort of a loop statement in most of the programming languages −

PL/SQL provides the subsequent sorts of loop to handle the looping requirements. Click the subsequent links to see their detail.
S.No Loop Type & Description
1 PL/SQL Basic LOOP
In this loop structure, sequence of statements is enclosed between the LOOP and therefore the END LOOP statements. At each iteration, the sequence of statements is executed then control resumes at the highest of the loop.
2 PL/SQL WHILE LOOP
Repeats a press release or group of statements while a given condition is true. It tests the condition before executing the loop body.
3 PL/SQL FOR LOOP
Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable.
4 Nested loops in PL/SQL
You can use one or more loop inside any another basic loop, while, or for loop.
Labeling a PL/SQL Loop
PL/SQL loops are often labeled. The label should be enclosed by double angle brackets (>) and appear at the start of the LOOP statement. The label name also can appear at the top of the LOOP statement. you’ll use the label within the EXIT statement to exit from the loop.
The following program illustrates the concept −

DECLARE 
i number(1); 
j number(1); 
BEGIN 
> 
FOR i IN 1..3 LOOP 
> 
FOR j IN 1..3 LOOP 
dbms_output.put_line('i is: '|| i || ' and j is: ' || j); 
END loop inner_loop; 
END loop outer_loop; 
END; 
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
i is: 1 and j is: 1 
i is: 1 and j is: 2 
i is: 1 and j is: 3 
i is: 2 and j is: 1 
i is: 2 and j is: 2 
i is: 2 and j is: 3 
i is: 3 and j is: 1 
i is: 3 and j is: 2 
i is: 3 and j is: 3

PL/SQL procedure successfully completed.

The Loop Control Statements

Loop control statements change execution from its normal sequence. When execution leaves a scope, all automatic objects that were created therein scope are destroyed.
PL/SQL supports the subsequent control statements. Labeling loops also help in taking the control outside a loop. Click the subsequent links to see their details.
S.No Control Statement & Description
1 EXIT statement
The Exit statement completes the loop and control passes to the statement immediately after the top LOOP.
2 CONTINUE statement
Causes the loop to skip the rest of its body and immediately retest its condition before reiterating.
3 GOTO statement
Transfers control to the labelled statement. Though it’s not advised to use the GOTO statement in your program.
So, this brings us to the end of blog. This Tecklearn ‘Conditions and Loops in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

416