PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.
A PL/SQL Block consists of three sections:
- The Declaration section (optional).
- The Execution section (mandatory).
- The Exception (or Error) Handling section (optional).
Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.
Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
This is how a sample PL/SQL Block looks.
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
Conditional Statements in PL/SQL
As the name implies, PL/SQL supports programming language features like conditional statements, iterative statements.
The programming constructs are similar to how you use in programming languages like Java and C++. In this section I will provide you syntax of how to use conditional statements in PL/SQL programming.
IF THEN ELSE STATEMENT
1)
IF condition
THEN
statement 1;
ELSE
statement 2;
END IF;
2)
IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF
3)
IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;
4)
IF condition1 THEN
ELSE
IF condition2 THEN
statement1;
END IF;
ELSIF condition3 THEN
statement2;
END IF;
Iterative Statements in PL/SQL
An iterative control Statements are used when we want to repeat the execution of one or more statements for specified number of times. These are similar to those in
There are three types of loops in PL/SQL:
• Simple Loop
• While Loop
• For Loop
1) Simple Loop
A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.
The General Syntax to write a Simple Loop is:
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
These are the important steps to be followed while using Simple Loop.
1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN condition, the statements in the loop is executed only once.
2) While Loop
A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.
The General Syntax to write a WHILE LOOP is:
WHILE
LOOP statements;
END LOOP;
Important steps to follow when executing a while loop:
1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) EXIT WHEN statement and EXIT statements can be used in while loops but it's not done oftenly.
3) FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reachs the value of the end integer.
The General Syntax to write a FOR LOOP is:
FOR counter IN val1..val2
LOOP statements;
END LOOP;
- val1 - Start integer value.
- val2 - End integer value.
Important steps to follow when executing a while loop:
1) The counter variable is implicitly declared in the declaration section, so it's not necessary to declare it explicity.
2) The counter variable is incremented by 1 and does not need to be incremented explicitly.
3) EXIT WHEN statement and EXIT statements can be used in FOR loops but it's not done oftenly.
NOTE: The above Loops are explained with a example when dealing with Explicit Cursors.
No comments:
Post a Comment