08:2

Database Systems
(5COM2005)

Database Systems
08:2
0

PL/SQL - Programming

Databases Systems
What we cover in this session
PL/SQL Programming Language

PL/SQL Block

Variables

Control Statement

Loop

Database Systems
PL/SQL – Procedural Language/SQL
 PL/SQL is an oracle’s procedural extension of SQL

 PL/SQL has similarity to modern procedure languages

 Variable and constraints
 Control structures
 Loop
 Exception handling
 Modularization (block)

Database Systems
PL/SQL – Block
[DECLARE Optional
-- declarations]
BEGIN
Mandatory
-- executable statements
[EXCEPION Optional
-- exception handlers]
END;
Mandatory

Database Systems
PL/SQL – Print Command
;

 For screen output use dbms_output.put_line()

Action Example:
set serveroutput on
BEGIN
dbms_output.put_line(‘Hello, World’);
END;

Note: set serveroutput on throws the output in screen.

Try with set serveroutput off

Database Systems
PL/SQL – Print Command
;

Output:

Database Systems
PL/SQL Variables
PL/SQL Programming Language

Variables are used as storage to hold value of

certain data

Through the program, value of the variable may

keep changing

Variable must be declared in declaration section,

before they are used.

Database Systems
PL/SQL Naming Convention
All PL/SQL variables need to have a valid name and
data type

PL/SQL variables name cannot be greater than 30

characters

Variable names must begin with letter or

underscore(_) or dollar symbol ($)

PL/SQL is case-insensitive, so for example name or

Name are same variable

Database Systems
PL/SQL Variables - Declaration
PL/SQL variables must need to declare before use.

Syntax:

<variableName> datatype[NOT NULL] [:=initialValue];

Note:- [] means optional.

Database Systems
PL/SQL - Declarations
 Example
vEmployeeGN VARCHAR2(5);

vSalary NUMBER(6,2) NOT NULL :=100;

MAX_HOLIDAY CONSTANT NUMBER :=20

Database Systems
PL/SQL - Declarations

Example in Action 1
(variable declared and initialized)
set serveroutput on

DECLARE
message varchar2(20):= 'Hello World';
BEGIN
dbms_output.put_line(message);
END;

Database Systems
PL/SQL – Example in Action 1

output

Database Systems
PL/SQL - Declarations

Example in Action 2
(variable declared only not initialization)
set serveroutput on

DECLARE
message varchar2(20);
BEGIN
dbms_output.put_line('My Message '|| message);
END;

Database Systems
PL/SQL - Declarations

Output

Database Systems
PL/SQL - Declarations
PL/SQL also allows variable to declare of type as columns
or entire row

 Columns type declaration

vGivenName Employee.GIVENNAME%TYPE;

 Row type declaration

vEmployee Employee%ROWTYPE;

Note:- These are not standard SQL declaration

Employee is a table with GIVENNAME attribute

Database Systems
PL/SQL Variables- Initialization
 By default, when a variable is declared, its value is
NULL

 However, a variable can be initialized as non-NULL

value during declaration.

 Assignment operator (:=) or the DEFAULT keyword is

used to assign a default value to a variable.

Database Systems
PL/SQL Variables- Initialization
Example :

DECLARE
name VARCHAR2(100) DEFAULT 'Deepak';
BEGIN
…
END;
OR
DECLARE
name VARCHAR2(100) := 'Deepak';
BEGIN
...
END;

Database Systems
PL/SQL Variables - Scope
The scope of the PL/SQL variables can be

Local – variables defined inside an inner block is

only available within the inner block.

Global – variables defined in the outer block or in

package are available to themselves and other inner
blocks within them.

Database Systems
PL/SQL - Comments
are non-executable part of code

helps to increase code readability

similar to SQL, there are two type of comments:

• single line comment

• multi-line comment

Database Systems
PL/SQL – Single-line comment
 Single line comment starts with a double
hyphen (--)
 The comment text is only a single line
Example:
-- this is single line comment starting with a double hyphen.

Database Systems
PL/SQL – Single line comment
set serveroutput on
DECLARE
message varchar2(20):= 'Hello, World';
BEGIN
dbms_output.put_line('Example Single line
comment’);
-- dbms_output.put_line(message);
END;

Guess what happen to message ??

Database Systems
PL/SQL – Multi-line comment
 starts with a slash and asterisk (/*)
 ends with an asterisk and slash (*/)
 comments text can span multiple line
 Example:
/* writing a multiline comments PL/SQL
……..
…….
Ending multi-line comment*/

Database Systems
PL/SQL - Assignment
In PL/SQL executable block, variables can be assigned in
two ways:-

 Using normal assignment operator (:=)

vEmployeeID := ‘DP15AAD’

 Using SQL SELECT or FETCH statement

SELECT count(*) INTO vTotalEmployee FROM employee;

Note:-
These are not standard SQL. In standard SQL, SET keyword and =
are used for variable assignment e.g. SET name=‘Deepak’

Database Systems
PL/SQL - Assignment
Example in Action:
Write a PL/SQL code to get total number of Employee.
set serveroutput on
DECLARE
vEmployeeNumber number(2,0);
BEGIN
SELECT count(*) INTO vEmployeeNumber FROM employee;
dbms_output.put_line('Total Number of Employee '||vEmployeeNumber);
END;

Database Systems
PL/SQL - Assignment
Output:

Database Systems
PL/SQL – Control Statements
PL/SQL supports three types control flow mechanism
 Sequential

 Conditional (branch)

 Iterative (loop)

Database Systems
PL/SQL – Conditional Statement
We going to cover syntax and example of two types of
conditional statement
 IF Statement

 CASE Statement

Database Systems
Conditional – IF Statement
Syntax

IF (condition) THEN Mandatory

< statement list>
[ELSIF (condition) THEN <statement list>] Optional
.
.
[ELSE<statement list>] Optional
END IF;
Mandatory

Database Systems
IF Statement- Example In Action
An employee is given bonus of 5% of their salary, if there salary is
greater than 5000. Write a simple PL/SQL block which calculate the
bonus of an employee whose employeeNo is 296431.

DECLARE
vEmployee Employee%ROWTYPE;
vEmployeeBonus number(10,2):=0;
BEGIN
SELECT * INTO vEmployee FROM employee where employeeNo = 296431;
dbms_output.put_line('Employee Salary is :'||vEmployee.salary);
IF ( vEmployee.salary > 5000) THEN
vEmployeeBonus:= .05*(vEmployee.salary);
END IF;
dbms_output.put_line('Bonus :'||vEmployeeBonus);
END;

Database Systems
Conditional – IF Statement
Output:

Database Systems
IF ELSE Example In Action
An employee is given bonus of 10% of their salary if there salary is
greater than 5000 and 5% of their salary otherwise. Write a simple
PL/SQL block which calculate the bonus of an employee whose
employeeNo is 296431.

DECLARE
vEmployee Employee%ROWTYPE;
vEmployeeBonus number(10,2):=0;
BEGIN
SELECT * INTO vEmployee FROM employee where employeeNo = 296431;
dbms_output.put_line('Employee Salary is :'||vEmployee.salary);
IF ( vEmployee.salary > 5000) THEN
vEmployeeBonus:= .10*(vEmployee.salary);
ELSE
vEmployeeBonus:= .05*(vEmployee.salary);
END IF;
dbms_output.put_line('Bonus :'||vEmployeeBonus);
END;
Database Systems
Conditional – IF ELSE Statement
Output:

Database Systems
Conditional – CASE Statement
Syntax

CASE (operand)
[WHEN (whenOperandList) | WHEN(searchCondition)]
THEN <SQL statement list>]
[ELSE <SQL statement list>]
END CASE;

Note:
ELSE statement is optional if all WHEN conditions are defined.

Database Systems
CASE - Example In Action
An employee is given bonus of 15% if their jobType is STRMAN,
10% if there job type is ASDMAN and 5% for any other jobType.
Write a simple PL/SQL block which calculate the bonus of an
employee whose employee number is 296431 using CASE
statement.
DECLARE
vEmployee Employee%ROWTYPE;
vEmployeeBonus number(10,2):=0;
BEGIN
SELECT * INTO vEmployee FROM employee where employeeNo = 296431;
dbms_output.put_line('JobType is :'||vEmployee.JobType);
dbms_output.put_line('Salary is :'||vEmployee.salary);
CASE (vEmployee.JOBTYPE)
WHEN 'STRMAN' THEN vEmployeeBonus := vEmployee.Salary*0.15;
WHEN 'ASDMAN' THEN vEmployeeBonus := vEmployee.Salary*0.10;
ELSE vEmployeeBonus := vEmployee.Salary*0.05;
END CASE;
dbms_output.put_line('Bonus :'||vEmployeeBonus);
END;

Database Systems
CASE - Example
Output:

Database Systems
PL/SQL – Iterative statement (LOOP)
Loops in PL/SQL helps to repeat the execution of block of
statements multiple time
Here we cover three main type of loops
 BASIC Loop

 WHILE Loop

 For Loop

Database Systems
Loop – Basic
Syntax

LOOP
<SQL statement list>
[increment/decrement statement]
EXIT condition;
END LOOP;

Database Systems
Loop – Basic
 Basic loop will execute for a certain number of times until
the exit condition is not satisfied

 EXIT statement need to be true at some point, otherwise

loop will run indefinitely

 Increment/Decrement statement changes the value for

EXIT statement, such that the loop meet the exit
statement at some point.

Database Systems
Basic Loop – Example in Action
DECLARE
i NUMBER(3) := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(i);
i:=i+1;
EXIT WHEN (i>10);
END LOOP;
END;

Database Systems
Basic Loop – Example in Action
Output:

Database Systems
Loop – WHILE
Syntax

WHILE (condition) LOOP

<statement list>
[increment/decrement statement]
END LOOP;

Database Systems
Loop – WHILE
 WHILE loop check the entry condition first

 If the entry condition is true, only then loop is executed

 Note:
The basic LOOP executed at least once, whereas the WHILE
loop will first check the condition and if the condition is
false then the control will not enter the loop at all.

Database Systems
WHILE Loop – Example in Action

DECLARE
i NUMBER(3) := 1;
BEGIN
WHILE (i <=100) LOOP
DBMS_OUTPUT.PUT_LINE(i);
i:=i+11;
END LOOP;
END;

Database Systems
While Loop – Example in Action
Output:

Database Systems
Loop – FOR
 Like WHILE loop, FOR loop check the entry condition first

 If the entry condition is true, only then loop is executed

Database Systems
For Loop – Example in Action

Syntax

BEGIN
FOR i in 1..10 LOOP
dbms_output.put_line(i);
END LOOP;
END;

Database Systems
For Loop – Example in Action
Output

Database Systems
Some Keywords in Loop
We can use some keywords to change how (for) loop
works:

 REVERSE

 CONTINUE

 EXIT

Database Systems
For Loop with Reverse
Reverse keyword is used to reverse the order of the Range

BEGIN
FOR i IN Reverse 1..10 LOOP
dbms_output.put_line(i);
END LOOP;
END;

Database Systems
For Loop with Reverse
Output

Database Systems
For Loop with Continue
Continue keyword is used skip for statement for run
where the continue condition is satisfied.
BEGIN
FOR i in 1..10 LOOP
if i = 5 THEN
CONTINUE;
END IF;
dbms_output.put_line(i);
END LOOP;
END;

Database Systems
For Loop with Continue
Output

Database Systems
Reading Materials
 Oracle PL/SQL by Example, Sixth Edition
by BenJamin and Elena

 Oracle PL/SQL for Dummies, by Michele

and Paul

 Oracle PL/SQL Programming by Steven

and Bill

Database Systems
Next Week
 Exception handling in PL/SQL

 Cursors in PL/SQL

 Functions

 Tiggers

Database Systems
Database Systems
(5COM2005)

Database Systems