Introduction To PL/SQL

PL/SQL stands for Procedural Language/Structured Query Language.

PL/SQL is Extension of SQL Language. It's a language developed by the Oracle corporation and the full name is procedural language/structured query language. PL/SQL is a high performance transaction processing language and also totally portable. It provides a built-in interpreted and operating system independent programming language.

PL/SQL Syntax

{DECLARE}
declaration ststements;
begin
executable ststements;
{EXCEPTION}
exception handling statements;
end;

Advantages of PL/SQL

PL/SQL is a Procedural language/Structured query language in which these are following advantages

Error exceptional handling - It handles the error or exceptions and giving user friendly error message.
Reduces Network Traffic - PL/SQL nature is Entire block of SQL statement execute to the oracle database engine at once so it's benefit to reduce the Network Traffic.
Allowance SQL - It is flexible, robust and simply to learn by the support of PL/SQL. it is very easy and conceptual .
Best performance - It engine procedure multiple sql statement simultaneously.
Higher productivity - PL/SQL great productivity through enabling using of better tools.
Completely portability - PL/SQL are portable to all operating system and platform on which Oracle database runs.
Tight integration with PL/SQL - work properly within oracle database.
Security - PL/SQL can obtain data abstraction, access control.
Intermediate Calculation - PL/SQL Enumeration done fast and oracle engines increase the transaction program.

Procedures in PL/SQL

PL/SQL performs one or more specific task and it is same as in other procedure language. Procedure is also called PL/SQL.
A procedure language includes a header and a body. The header structure of the name of the procedure and the variables or parameters passed through the procedure.
The body structure or declaration statements, execution statement and exception statement same as a general PL/SQL Block.
A procedure is same as an anonymous Procedure Block but it is named for repeated usage.

Example

create or replace procedure add_new
is
x1 num:=$x1;
x2 num:=$x2;
x3=num;
begin
x3=x1+x2;
dbms_output.put_line(x3);
end add_new;

Types of Loops in PL/SQL

There are three types of loop in the Pl/Sql.

Basic Loop

A Basic Loop is utilized when an arrangement of segments is to be executed at any rate once before the loop ends. An EXIT condition must be determined in the loop, generally the loop will get into an unbounded number of emphasis.

Example

DECLARE
x num := 8;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 8;
IF x > 39 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
/

While Loop

The while loop is enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression returns true loop runs otherwise exit from loop.

The while loop is enclosed by the keywords LOOP and END LOOP, prior to every iteration of the loop, the expression is evaluated. On the off chance that the expression returns true LOOP runs generally exit from LOOP.

Example

DECLARE
a num(3) := 10;
BEGIN
WHILE a < 30 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/

For Loop

Numeric for loop loops repeat over a predetermined scope of whole numbers. The extent is a piece of a loop plan, which is enclosed by the keywords for and loop.

Example