PL/SQL Introduction
PL/SQL is an extension of structured query language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in a procedural format. Full form of PL/SQL is "Procedural Language/Structured Query Language". PL/SQL is a superset of SQL.
It combines the data manipulation power of SQL with the processing power of procedural language to create superpower SQL queries.
Architecture of PL/SQL
The PL/SQL architecture mainly consists of following three components:
- PL/SQL block
- PL/SQL engine
- Database server
Definition- PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements.
All the statements of a block are passed to Oracle engine all at once. Which increases processing speed and decreases the traffic.
Features of PL/SQL
- PL/SQL is basically a procedural language which provides the functionality of decision making, iteration and many more features of procedural programming languages.
- PL/SQL can execute a number of queries in one block using single command.
- One can create a PL/SQL unit such as procedures, functions, packages, triggers and types. Which are stored in the database for reuse by applications.
- PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception handling block.
- Applications written in PL/SQL are portable to computer hardware or operating system where Oracle is operational.
- PL/SQL offers extensive error checking.
Advantages of using PL/SQL
- Better performance, as SQL is executed in bulk rather than a single statement.
- High productivity
- Tight integration with SQL.
- Full portability
- Tight security
- Support object oriented programming concepts.
Disadvantages of SQL
- SQL does not provide the programmers with the technique of condition checking, looping and branching.
- SQL statements are passed to Oracle engine one at a time which increases traffic and decreases speed.
- SQL has no facility of error checking during manipulation of data.
PL/SQL basics
Like other programming languages PL/SQL has a character set, reserved words, punctuation, data types and fixed syntax rules.
Character sets and lexical units
PL/SQL programs are written in lines of text using a specific set of characters:
- Upper and lowercase letters A to Z and a to z
- Numerals 0 to 9
- Symbols ( ) + - * / <> = ! ~ ^ ; : , ' @ % . " # & _ | { } ? [ ]
- Tabs, spaces and carriage returns
PL/SQL keywords are not cases sensitive, so lowercase letters are equivalent to corresponding uppercase letters except within string and character literals.
PL/SQL block structure
PL/SQL extends SQL by adding constructs found in procedural languages, that make it more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks which can be nested within each other.
Typically each block performs a logical action in the program. A block has the following structure:
DECLARE
declaration statement ;
BEGIN
executable statement ;EXCEPTION
exception handling statements ;END;
Declare section- starts with DECLARE keyword in which variables, constants, records as cursors can be declared which stores data temporarily. It basically consists definition of PL/SQL identifiers. This part of the code is optional.
Execution section- starts with BEGIN and ends with END keyword. This is a mandatory section and here the program logic is written to perform any task like loops and conditional statements. It supports all DML(Data Manipulation Language) commands DDL(Data definition Language) commands and SQL*PLUS built in functions as well.
Exception section- starts with EXCEPTION keyboard. This section is optional which contains statements that are executed when a runtime error occurs. Any exceptions can be handled in this section.
SQL VS PL/SQL
| SR. NO. | SQL | PL/SQL |
|---|---|---|
| 1 | No procedural capabilities- SQL does not have procedural capabilities i.e. SQL does not provide the programming techniques of conditional checking, looping and branching | procedural capabilities- PL/SQL is a procedural extension of SQL. PL/SQL supports programming techniques of conditional checking, looping and branching |
| 2 | Timing consuming processing- SQL statements are passed to Oracle engine one at a time. Each time a SQL statement is executed, a call is made to engine's resources. This adds traffic on the network and decrease the speed of processing | PL/SQL statements sends an entire block of statements at one time. This reduces traffic on the network and increase the speed of processing |
| 3 | No error handling procedures- While processing an SQL statement, if an error occurs, the Oracle engine displays its own error messages. SQL has no facility for programmed handling of errors that arise during manipulation of data | Error handling procedures- PL/SQL supports error handing routines |
| 4 | No facilities sharing- SQL does not allow sharing of facilities | Facilities sharing- PL/SQL sharing of facilities |
There are several PL/SQL identifiers such as variables, constants, procedures, cursors, triggers etc.
Variables- like several other programming languages variables in PL/SQL must be declared prior to its use. They should have a valid name and data type as well.
We can declare variables using following syntax:
Syntax:
VARIABLE_NAME DATATYPE [NOT NULL := VALUE];
E.g.
msg VARCHAR2(50);
Complete E.g.
DECLARE
msg VARCHAR2(50);
BEGIN
msg := 'Hello PL/SQL';
DBMS_OUTPUT.PUT_LINE(msg);
END;
/
Control structure in PL/SQL
A condition is any variable or expression that returns a boolean value true or false. The iteration(loop) structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply execute a sequence of statements in the order which they occur.
PL/SQL supports following control statements:
- IF statements- conditionally executes a block of statements.
- LOOP statements- repeatedly executes a block of statements.
IF statements
The IF statement executes a sequence of statements depending on the value of a condition.
There are 3 forms of IF statements:
- IF-THEN
- IF-THEN-ELSE
- IF-THEN-ELSIF
IF-THEN
In this type of control structure/statement, if the condition is true then written statement will get executed(start) otherwise nothing will happen.
Syntax:
IF <CONDITION TEST> THEN
STATEMENTS
END IF;
E.g.
DECLARE
age NUMBER(10);
BEGIN
age := 18;
IF(age>17) THEN
DBMS_OUTPUT.PUT_LINE('You are qualified for voting');
END IF;
END;
/
Output: You are qualified for voting
IF-THEN-ELSE
In this type of control structure/statement, if the condition is true then first written statement will get executed(start) otherwise statement written after else will get executed.
Syntax:
IF <CONDITION TEST> THEN
STATEMENTS
ELSE
STATEMENTS
END IF;
E.g.
DECLARE
age NUMBER(10);
BEGIN
age := 16;
IF(age>17) THEN
DBMS_OUTPUT.PUT_LINE('You are qualified for voting');
ELSE
DBMS_OUTPUT.PUT_LINE('You are not qualified for voting');
END IF;
END;
/
Output: You are not qualified for voting
IF-THEN-ELSIF
In this type of control structure/statement it executes the statement code following a condition which evaluates to true if no condition evaluates true then statement code following else will get executed.
Syntax:
IF <CONDITION TEST> THEN
STATEMENTS
ELSIF<CONDITION TEST> THEN
STATEMENTS
ELSIF<CONDITION TEST> THEN
STATEMENTS
...
ELSE
STATEMENTS
END IF;
E.g.
DECLARE
marks NUMBER(10);
BEGIN
marks := 95;
IF(marks>80) THEN
DBMS_OUTPUT.PUT_LINE('A+');
ELSIF(marks>70) THEN
DBMS_OUTPUT.PUT_LINE('A');
ELSIF(marks>50) THEN
DBMS_OUTPUT.PUT_LINE('B+');
ELSIF(marks>40) THEN
DBMS_OUTPUT.PUT_LINE('B');
ELSIF(marks>30) THEN
DBMS_OUTPUT.PUT_LINE('C');
ELSIF(marks>=0) THEN
DBMS_OUTPUT.PUT_LINE('FAIL');
ELSE
DBMS_OUTPUT.PUT_LINE('Unknown Marks');
END IF;
END;
/
Output: A+
Looping statements
Looping or Iteration statements allow a set of instructions to be performed repeatedly until a certain condition is fulfilled.
PL/SQL provides 3 different types of looping statements:
- SIMPLE LOOP
- FOR LOOP
- WHILE LOOP
SIMPLE LOOP
It consists of LOOP keyword, body of EXECUTABLE CODE and END LOOP keyword.
Syntax:
LOOP
<EXECUTABLE CODE>
END LOOP;
E.g.
DECLARE
a NUMBER(10):=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(a);
a := a+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(a);
END;
/
Terminating a Simple Loop- By using Exit; and Exit when <Condition>; we can terminate Simple Loop.
E.g. 1
DECLARE
a NUMBER(10):=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(a);
a := a+1;
If a=5 Then
Exit;
End If;
END LOOP;
DBMS_OUTPUT.PUT_LINE(a);
END;
/
Output:
1
2
3
4
5
E.g. 2
DECLARE
a NUMBER(10):=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(a);
a := a+1;
Exit when a=5;
END LOOP;
DBMS_OUTPUT.PUT_LINE(a);
END;
/
Output:
1
2
3
4
5
FOR LOOP(Numeric For Loop)
For Loop check the condition/expression, if condition/expression is true then loop will continue. When the condition/expression evaluates false then loop will stop or terminate.
Syntax:
FOR<LOOP INDEX> IN [REVERSE] <LOWEST NO.>..<HIGHEST NO.>
LOOP
<EXECUTABLE CODE>
END LOOP;
E.g.
DECLARE
a NUMBER(10):=1;
BEGIN
FOR anum in 1..5
LOOP
a := anum+1;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END;
/
Output:
2
3
4
5
6
WHILE LOOP
While Loop execute a statement repeatedly as long as an expression/condition is true. Once the condition/expression becomes false the loop will terminate or stop.
Syntax:
WHILE<CONDITION>
LOOP
<EXECUTABLE CODE>
END LOOP;
E.g.
DECLARE
a NUMBER(10):=1;
BEGIN
While a<5
LOOP
a := a+1;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END;
/
Output:
2
3
4
5
PL/SQL Programs
1. Write a PL/SQL code to print the series 1 3 5 7 ... 25
Sol:
DECLARE
a NUMBER;
BEGIN
a:=1;
While a<25
LOOP
a := a+2;
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END;
/
Output:
3
5
7
9
11
13
15
17
19
21
23
25
2. Write a PL/SQL code to find the area of circle.
Sol:
DECLARE
radius FLOAT;
area FLOAT;
BEGIN
radius:=3;
area := 3.14*radius*radius;
DBMS_OUTPUT.PUT_LINE('The area of circle is: ' || area);
END;
/
Output:
The area of circle is: 28.26
3. Write a PL/SQL code to find the area of rectangle.
Sol:
DECLARE
length NUMBER(8,2);
breadth NUMBER(8,2);
area NUMBER(10,2);
BEGIN
length:=10;
breadth:=10;
area := length*breadth;
DBMS_OUTPUT.PUT_LINE('The area of rectangle is: ' || area);
END;
/
Output:
The area of rectangle is: 100
4. Write a PL/SQL code to find the area of triangle.
Sol:
DECLARE
base NUMBER(8,2);
height NUMBER(8,2);
area NUMBER(10,2);
BEGIN
base:=10;
height:=10;
area := base*height/2;
DBMS_OUTPUT.PUT_LINE('The area of triangle is: ' || area);
END;
/
Output:
The area of rectangle is: 50