PL/SQL

What is PL-SQL

• 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.
 What is PL-SQL

• 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.
 Introduction of PL/SQL

The PL/SQL programming language was developed by Oracle Corporation in the late 1980s
as procedural extension language for SQL and the Oracle relational database. Following
are certain notable facts about PL/SQL −
PL/SQL is a completely portable, high-performance transaction-processing language.
PL/SQL provides a built-in, interpreted and OS independent programming environment.
PL/SQL can also directly be called from the command-line SQL*Plus interface.
Direct call can also be made from external programming language calls to database.
PL/SQL's general syntax is based on that of ADA and Pascal programming language.
Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.
 What is PL-SQL

• Disadvantages of SQL:
• SQL doesn’t provide the programmers with a 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 - Environment Setup

PL/SQL is not a standalone programming language; it is a tool within the

Oracle programming environment. SQL* Plus is an interactive tool that
allows you to type SQL and PL/SQL statements at the command prompt.
These commands are then sent to the database for processing. Once the
statements are processed, the results are sent back and displayed on
screen.
To run PL/SQL programs, you should have the Oracle RDBMS Server
installed in your machine. This will take care of the execution of the SQL
commands. You can download a trial version of Oracle from the following
link −
https://www.oracle.com/database/technologies/oracle-database-software-
downloads.html
 Starting PL SQL

After downloading the above two files, you will need to unzip them in a single
directory database and under that you will find the following sub-directories and a setup
file.
Finish the setup
It is now time to verify your installation. At the command prompt, use the following command if
you are using Windows −
sqlplus "/ as sysdba"

You should have the SQL prompt where you will write your PL/SQL commands and scripts −
 What is PL-SQL

• Differences between SQL and PL/SQL:

SQL PL/SQL

PL/SQL is a block of codes that used to

SQL is a single query that is used to
write the entire program blocks/
perform DML and DDL operations.
procedure/ function, etc.

It is declarative, that defines what

PL/SQL is procedural that defines how the
needs to be done, rather than how
things needs to be done.
things need to be done.

Execute as a single statement. Execute as a whole block.

Mainly used to manipulate data. Mainly used to create an application.

It is an extension of SQL, so it can contain

Cannot contain PL/SQL code in it.
SQL inside it.
 PL-SQL program structure

• PL/SQL extends SQL by

adding constructs found in
procedural languages,
resulting in a structural
language that is 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
 PL-SQL program structure

A block has the following structure:

DECLARE
declaration statements;
BEGIN
executable statements
EXCEPTIONS
exception handling statements
END;
 PL-SQL program structure

• 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 commands, DDL commands and
SQL*PLUS built-in functions as well.
 PL-SQL program structure

• Exception section starts with EXCEPTION keyword. This

section is optional which contains statements that are
executed when a run-time error occurs. Any exceptions
can be handled in this section.
 Hello World Program

DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
 PL/SQL Program Units

A PL/SQL unit is any one of the following −

PL/SQL block
Function
Package
Package body
Procedure
Trigger
Type
Type body
 PL/SQL Datatype

• Same Datatype are supported in PL/SQL which are

supported in SQL
Data type Syntax

Integer INTEGER

Smallint SMALLINT

Numeric NUMERIC(P,S)

Real REAL

Decimal DECIMAL(P,S)

Float FLOAT(P)

Character CHAR(X)

Character varying VARCHAR2(X)

Date Date

Time TIME
 PL/SQL Datatpye

• Note: Data Type varies from database to database. For

example, MySQL supports INT but Oracle supports NUMBER
for integer values.
 PL/SQL Variable

A Variable is an identifier which holds data or another one

variable is an identifier whose value can be changed at the
execution time of program.
Variable declaration Syntax
variable_name datatype;
Example
a number;
Variable Initialization Syntax
variable_name datatype:=value;
Example
a number:=10;
 PL/SQL Variable

• Rules for variable declaration

• Variable name start with alphabet.
• Variable name contains minimum one character and
maximum 30 character.
• It should not allow any space and any special character.
• Keywords are not allow to declare as a variable name.
 PL/SQL Variable

DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;
BEGIN
null;
END;
 PL/SQL Variable

• Explanation:
• SET SERVEROUTPUT ON : It is used to display the buffer
used by the dbms_output.
• var1 INTEGER : It is the declaration of variable, named var1
which is of integer type. There are many other data types
that can be used like float, int, real, smallint, long etc. It also
supports variables used in SQL as well like NUMBER(prec,
scale), varchar, varchar2 etc.
• PL/SQL procedure successfully completed. : It is
displayed when the code is compiled and executed
successfully.
• Slash (/) after END; : The slash (/) tells the SQL*Plus to
execute the block.
 Variable Scope in PL/SQL

PL/SQL allows the nesting of blocks, i.e., each program block may contain
another inner block. If a variable is declared within an inner block, it is not
accessible to the outer block. However, if a variable is declared and
accessible to an outer block, it is also accessible to all nested inner blocks.
There are two types of variable scope −
Local variables − Variables declared in an inner block and not accessible to
outer blocks.
Global variables − Variables declared in the outermost block or a package.
 Global and Local variables
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num1 number := 195;
num2 number := 185;
 PL/SQL Variable

Example of variable declaration and initialization.

DECLARE
num1 number;
num2 number;
c number;
BEGIN
num1 := 10;
num2 := 20;
c := num1 + num2;
dbms_output.put_line(c);
END;
 PL/SQL Constant

A Constant is an identifier which holds data whose value cannot be

changed at the execution time of program.

Constant declaration

Syntax
constant_name Constant datatype := value;

constant_name is the name of the constant it means similar to a

variable name.

The word Constant is a reserved word and that value does not change.
value: value is assigned to constant when it is declared.
 PL/SQL Constant

Example
declare
salary_bonus constant number(4):=2000;
begin
salary_bonus := 4000;
dbms_output.put_line (salary_bonus);
end;
 PL SQL Constant

DECLARE

-- constant declaration

pi constant number := 3.141592654;

-- other declarations

radius number(5,2);

dia number(5,2);

circumference number(7, 2);

area number (10, 2);

BEGIN

-- processing

radius := 9.5;

dia := radius * 2;
 PL/SQL Operator

Same Operators are supported in PL/SQL which are supported

in SQL, except assignment operator.
In place of assignment operator := is acting as an assignment
operator in PL/SQL.

Arithmetic Operators + - * / %
Relational Operators < > <= >=
Logical Operators AND OR NOT
Miscellaneous Operators
 PL/SQL Operator

PLSQL : || Operator
The string in PL/SQL is actually a sequence of characters with an optional size
specification.
The characters could be numeric, letters, blank, special characters or a combination of
all.
The || Operator in PLSQL is used to concatenate 2 or more strings together.
The result of concatenating two-character strings is another character string.
The result has datatype CHAR and is limited to 2000 characters if both character
strings are of datatype CHAR whereas if either string is of datatype VARCHAR2, the
result has datatype VARCHAR2 and is limited to 4000 characters.
 PL/SQL Operator
The CONCAT character function can also be used as an alternative to
the vertical bar operator in PLSQL for concatenation of strings.

Syntax:
string1 || string2 [ || string_n ]

Parameters Used:

string1 –It is used to specify the first string to concatenate.

string2 –It is used to specify the second string to concatenate.
string_n –It is used to specify the nth string to concatenate.

Return Type:The || operator returns a string value.

 PL/SQL Operator

DECLARE
Test_String string(10) := 'Hello ';
Test_String2 string(10) := 'world!';
BEGIN
dbms_output.put_line((Test_String || Test_String2));
END;
 PL/SQL Operator

Operator Name Example (int a=8, b=3)

+ Addition a+b=11

- Subtraction a-b=5

* Multiplication a*b=24

/ Division a/b=2

% Modulas (Remainder) a%4=0

 PL/SQL Operator

• Relational Operators
Operators Description Example: a=10,
b=20

!= Check the left operands value are not equal to right operands, if yes return true. a!=b=True

> Check the left operand value is greater than right Operand, if yes condition becomes a>b=False
true

< Check the left operand value is less than right Operand, if yes condition becomes a<b=True
true

<= Check the left operand value is less than or equal to right Operand, if yes condition a<=b=True
becomes true

>= Check the left operand value is greater than or equal to right Operand, if yes a>b=False
condition becomes true
 PL/SQL Operator

• Logical Operator
Operator Description

1 AND And are use to combined two or more than two condition together. If both the
condition is true then return true.

2 OR OR are use to combined two or more than two condition together, In this case you
need at least one condition is true then return result.

3 Not NOT operator reverse the meaning of any logical operator

 PL/SQL Operator

• Miscellaneous Operator

Between Not between In

Not in Like Exists

Not exists Is null Is not null

Any All Some

Union Union all Intersect

minus
 PL/SQL Comment

• Generally Comments are used to provide the description

about the Logic written in program. Comments are not
displayed on output screen. When we use the comments,
then that specific part will be ignored by compiler.

• Single line comment

• In PL/SQL you can write single line comment by using --
symbol
• Syntax
• -- single line comment
 PL/SQL Comment

• Multi line comment

• In PL/SQL you can write multi line comment by using /* */
symbol
• Syntax
• /* multi line

• comment */
 PL/SQL Input Output
Statements
Example to see how to display a message using
PL/SQL :
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var varchar2(40) := 'I love Computers' ;
BEGIN
dbms_output.put_line(var);
END;
/
PL/SQL Conditional Statements

Conditional Statements are depending on the condition

block need to be executed or not which is decided by
condition.
If the condition is "true" statement block will be executed, if
condition is "false" then statement block will not be
executed.
if....then Syntax
if (condition) then
statements;
end if;
PL/SQL Conditional Statements

Example
DECLARE n_sales NUMBER :=
2000000;
BEGIN
IF n_sales > 100000 THEN
DBMS_OUTPUT.PUT_LINE( 'Sales
revenue is greater than 100K ' );
END IF;
END;
 PL/SQL Conditional Statements

Avoid evaluating Boolean variables

A Boolean variable is always TRUE, FALSE or NULL.
Therefore the following comparison is unnecessary:
IF b_profitable = TRUE THEN
DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;

Instead, use:
IF b_profitable THEN
DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
PL/SQL Conditional Statements
set serveroutput on;
if...else
DECLARE
if (condition) then x int;
statements; BEGIN
else x := 20;
statements; if mod(x,2) = 0 then
end if; dbms_output.put_line('Even
Number');
else
dbms_output.put_line('Odd
Number');
end if;
END;
 If..else example

declare age int:=20;

begin
if age>18 then
dbms_output.put_line('Vote');
else
dbms_output.put_line('Can not vote');
end if;
end;
PL/SQL Conditional Statements

IF THEN ELSIF statement example

DECLARE
a int; b int;
BEGIN
a := 20; b := 20;
if(a>b) then
dbms_output.put_line('a is greater than b');
elsif(b>a) then
dbms_output.put_line('b is greater than a');
else
dbms_output.put_line('Both a and b are equal');
end if;
END;
 PL/SQL Basic Loop
Basic loop structure encloses sequence of statements in between the LOOP and END
LOOP statements. With each iteration, the sequence of statements is executed and then
control resumes at the top of the loop.
Syntax
The syntax of a basic loop in PL/SQL programming language is −
LOOP
Sequence of statements;
END LOOP;

Here, the sequence of statement(s) may be a single statement or a block of statements.

An EXIT statement or an EXIT WHEN statement is required to break the loop.
 Example of Loop
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
 Exit when in loop

DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
 WHILE LOOP
While statement in PL/SQL programming language repeatedly executes a target statement as long as a
given condition is true.
Syntax
WHILE condition LOOP
sequence_of_statements
END LOOP;

Example
DECLARE
a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
 For Loop in PL/SQL

A FOR LOOP is a repetition control structure that allows you to efficiently

write a loop that needs to execute a specific number of times.
Syntax
FOR counter IN initial_value .. final_value LOOP
sequence_of_statements;
END LOOP;
 Example

DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
 Nested Loops
PL/SQL allows using one loop inside another loop. Following section shows a few examples to illustrate the concept.
The syntax for a nested basic LOOP statement in PL/SQL is as follows −
LOOP
Sequence of statements1
LOOP
Sequence of statements2
END LOOP;
END LOOP;

The syntax for a nested FOR LOOP statement in PL/SQL is as follows −

FOR counter1 IN initial_value1 .. final_value1 LOOP
sequence_of_statements1
FOR counter2 IN initial_value2 .. final_value2 LOOP
sequence_of_statements2
 PL/SQL - CASE Statement

• Like the IF statement, the CASE statement selects one

sequence of statements to execute. However, to select the
sequence, the CASE statement uses a selector rather than
multiple Boolean expressions. A selector is an expression,
the value of which is used to select one of several
alternatives.
• The syntax for the case statement in PL/SQL is −
• CASE selector
• WHEN 'value1' THEN S1;
• WHEN 'value2' THEN S2;
• WHEN 'value3' THEN S3;
• ...
• ELSE Sn; -- default case
PL/SQL - CASE Statement
 PL/SQL - CASE Statement

• Example
• DECLARE
• grade char(1) := 'A';
• BEGIN
• CASE grade
• when 'A' then dbms_output.put_line('Excellent');
• when 'B' then dbms_output.put_line('Very good');
• when 'C' then dbms_output.put_line('Well done');
• when 'D' then dbms_output.put_line('You passed');
• when 'F' then dbms_output.put_line('Better try again');
• else dbms_output.put_line('No such grade');
• END CASE;
• END; --enter grade: ‘A’, enter character in single quotes