PL / SQL Basics

Introduction
 PL/SQL, in simple terms, is a programming
language used for managing data in databases. It
combines SQL for data manipulation and procedural
features for building applications.
 PL/SQL (Procedural Language/SQL) is Oracle’s
extension of SQL that adds procedural features like
loops, conditions, and error handling. It allows
developers to write powerful programs that combine
SQL queries with logic to control how data is
processed.

2
 PL/SQL allows developers to:
 Execute SQL queries and DML commands
inside procedural blocks.
 Define variables and perform complex
calculations.
 Create reusable program units, such as
procedures, functions, and triggers.
 Handle exceptions, ensuring the program
runs smoothly even when errors occur.

3
 Key Features of PL/SQL
 PL/SQL brings the benefits of procedural programming to the relational
database world. Some of the most important features of PL/SQL include:
 Block Structure: PL/SQL can execute a number of queries in one block
using single command.
 Procedural Constructs: 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.
 Error Handling: PL/SQL provides a feature to handle the exception
which occurs in PL/SQL block known as exception handling block.
 Reusable Code: Create stored procedures, functions, triggers, and
packages, which can be executed repeatedly.
 Performance: Reduces network traffic by executing multiple SQL
statements within a single block

4
PL/SQL execution

Oracle Tutorials: PL/SQL

PL / SQL Basics
 PL / SQL block
 Lexical units
 Variable declarations
 PL / SQL types
 Expressions and operators
 PL / SQL control structures
 PL / SQL style guide
6
 Structure of PL/SQL
Block
 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.

7
PL / SQL Block
Basic Block Structure
 All blocks have three (3) sections
 Declarative

Where all variables, cursors, types, local
procedures and functions are declared
 Executable

Where the work of the block is performed

Contains SQL and procedural statements
 Exception

Errors are handled

Code here is not run unless an error occurs

8
Lexical Units
 Identifiers
 Delimiters
 Literals
 Comments

9
Lexical Units
 A lexical unit is a sequence of
characters, the character set
includes:
 Upper and lowercase letters: A-Z, a-z
 White space: tabs, spaces, and carriage
returns
 Math symbols: +-*/<>=
 Punctuation symbols: () {} [] ? ~ ; : . ‘ “
@#%$^&_|
10
Lexical Units
Identifiers
 Used to name PL/SQL objects
 Consists of a letter followed by any
sequence of characters such as:
 Letters, numbers, $, underscore, #
 Maximum length is thirty (30)
characters
 Other characters are not permitted

11
Lexical Units
Identifiers ~ Examples
 Some proper identifiers:
 y
 v_StudentID
 TempName
 t1
 t2_
 social_security_#
 YTD

12
Lexical Units
Identifiers ~ Examples
 Some improper identifiers:
 m+n
 _temp_
 First Name
 OhBoyThisIsReallyALongIdentifierNam
e
 1CannotStartWithADigit

13
Lexical Units
Identifiers ~ Reserved Words
 There are many identifiers known
as reserved words (keywords)
 They have special meaning to
PL/SQL
 You cannot use them as identifiers
 For example the word BEGIN is used
to start a PL/SQL block and cannot be
used as a variable name
14
Lexical Units
Delimiters
 Symbols used to separate
identifiers
 either a single character
 sequence of characters
 Examples of delimiters are:
 + * > <> <= ; || (and others…)

15
Lexical Units
Literals
 A literal is a value that is not an
identifier
 There are three (3) types of literals:
 Character

‘This is a character literal’
 Numeric

123 -9 +17 0 -6.9 3 9.87e-3 –95.12e7
 Boolean

True False Null

16
Lexical Units
Comments
 It is important to document your
code with comments to make it
clear to others and yourself
 They are ignored by the PL/SQL
compiler
 There are two kinds of comments
 Single-line
 Multiline

17
Variable Declarations
 Declaration syntax
 Variable initialization

18
Variable Declarations
Declaration Syntax
 Variables are declared in the
declarative section of a block
 Each variable has a specific type of
data that it can hold
 General syntax is:
 variable_name type [CONSTANT] [NOT NULL]
[:=value];

19
Variable Declarations
Variable Initialization
 Variables are initialized in their
declaration statement
 v_Counter NUMBER := 100;
 v_Fname VARCHAR2(10) DEFAULT ‘Scott’;
 If a variable is not initialized it is set to
NULL
 NULL simply means missing or unknown
value

20
PL / SQL Types
 Scalar types
 Composite types
 Reference types
 LOB types
 Using %TYPE
 User-defined subtypes
 Converting between Datatypes
 Variable scope and visibility
21
PL / SQL Types
Scalar Types
 Valid scalar types consist of the same as
the types for database columns
 Scalar types can be divided into seven
families:
 numeric
 character
 raw
 date
 rowid
 boolean

22
 PL/SQL Scalar Data Types
and Subtypes
 PL/SQL Scalar Data Types and Subtypes come under the
following categories
[Link] Date Type & Description
Numeric
1 Numeric values on which arithmetic operations are
performed.

Character
2 Alphanumeric values that represent single
characters or strings of characters.

Boolean
3 Logical values on which logical operations are
performed.
Datetime
4
Dates and times.
23
PL / SQL Types
Composite Types
 A composite type is one that has
components within it
 The three (3) composite types
available in PL/SQL are:
 Records
 Tables
 varrays

24
PL / SQL Types
Reference Types
 A variable that is declared as a
reference type can point (refer to)
different storage locations over the
life of the program
 REF CURSOR
 REF OBJECT

25
PL / SQL Types
LOB Types
 This type is used to store large
objects
 A large object can be either binary
or character value up to 4
gigabytes
 Can contain unstructured data

26
PL / SQL Types
Using %TYPE
 Often a PL/SQL variable will be
used to manipulate data stored in
a database table
 Variable needs to have the same type
as the table column
 Variable needs to be changed as the
table column is altered
 Time consuming and error prone

27
PL / SQL Types
Using %TYPE
 By using %TYPE when declaring a
variable
 Variable will be defined with the same
type as the associated table column
 Type is determined each time a block
is run or
 When procedures and functions are
compiled

28
%Type Example
 DECLARE
 v_id ORDERS.ORDER_ID%TYPE

ORDERS is table, ORDER_ID is a

column in the ORDERS table

29
PL / SQL Types
User-Defined Subtypes
 A subtype is a PL/SQL type that is
based on an existing type
 Used to give an alternative name
which describes its intended use
 PL/SQL defines several subtypes
 DECIMAL and INTEGER are
predefined subtypes of NUMBER

30
PL / SQL Types
Converting Between Datatypes
 Explicit Scalar Datatype Conversion
 Built-in conversion functions in SQL are
also available in PL/SQL
 Converts explicitly between variables
using formats
 Examples are:

TO_CHAR – converts numeric and date

TO_DATE – converts character

TO_NUMBER – converts character

31
PL / SQL Types
Converting Between Datatypes
 Implicit Scalar Datatype Conversion
 PL/SQL will convert between families
when possible
 Good programming practice suggests
using explicit conversions
 Formats are not used and does not
clearly show the intent of the program

32
PL / SQL Types
Variable Scope and Visibility
 The scope of a variable is the
portion of the program in which it
can be accessed
 The visibility of a variable is the
portion of the program where the
variable can be accessed without
having to qualify the reference

33
Expressions and Operators
 Assignment
 Expressions

34
Expressions and Operators
Assignment
 The syntax for an assignment is:

variable := expression;
 Performed in the executable and
exception handling sections
 lvalues must refer to actual storage
locations
 rvalues can be storage locations or literals
 An rvalue is read from while an lvalue is
written to
35
Expressions and Operators
Expressions
 PL/SQL expressions are rvalues
 These expressions have two
components
 An operand is the argument to an operator
 An operator is what operates on the
operands
 Precedence of operators determines the
order of evaluation of the expression
 Please Excuse My Dear Aunt Sally

36
PEMDAS: Please Excuse My Dear Aunt
Sally

P Parentheses first
Exponents (ie Powers and Square Roots,
E
etc.)
MD Multiplication and Division (left-to-right)

AS Addition and Subtraction (left-to-right)

37
Expressions and Operators
Expressions
 Numeric expressions
 Evaluated from left to right applying the
precedence order of operators
 Character expressions
 Only operator is concatenation operator ||
 Joins one or more strings (or arguments that
can be implicitly converted)
 Boolean expressions
 A boolean expression is any expression that
evaluates to a boolean value(True, False, Null)

38
PL / SQL Control Structures
 IF-THEN-ELSE
 Loops
 GOTOs and Labels
 PRAGMAs

39
PL / SQL Control Structures
IF-THEN-ELSE
The syntax for an IF-THEN-ELSE
statement is:
IF boolean_expression1 THEN
sequence_of_statements1;
[ELSIF boolean_expression2 THEN
sequence_of_statements2;]
…
[ELSE
sequence_of_statements3;]
END IF;

40
PL / SQL Control Structures
Loops
 Loops are divided into four (4)
categories
 Simple loops
 WHILE loops
 Numeric FOR loops
 Cursor FOR loops

41
PL / SQL Control Structures
Loops ~ Simple Loops
 The basic kind of loop has the syntax:
LOOP
sequence_of_statements;
END LOOP;
 These loops will execute forever unless
you include an EXIT statement
 Checks a condition for ending the loop since
it has no stopping condition, it syntax is:
EXIT [WHEN condition];

42
PL / SQL Control Structures
Loops ~ WHILE Loops
 The syntax for a WHILE loop is:
WHILE condition LOOP
sequence_of_statements;
END LOOP;
 Condition is checked before each
cycle of the loop
 If True, loop body is executed
 Otherwise loop is passed over

43
WHILE LOOP Example
DECLARE v_a number(2) := 1;
BEGIN
WHILE v_a < 10 LOOP
dbms_output.put_line('value of v_a: ' ||
v_a);
v_a := v_a + 1;
END LOOP;
END; /
44
PL / SQL Control Structures
Loops ~ Numeric FOR Loops
 The syntax for a numeric FOR loop is:
FOR loop_counter IN [REVERSE] lo_bound .. hi_bound LOOP
sequence_of_statements;
END LOOP;
 Numeric FOR loops have a defined number
of cycles
 Do not declare the loop index
 Loop increment (decrement) is always one
 In order to have a different increment you
would need to include additional code in the
loop body

45
PL / SQL Control Structures
Loops ~ Cursor FOR Loops
 A cursor FOR loop is a loop that is associated:
 With an explicit cursor
 A SELECT statement incorporated directly within
the loop boundary
 Used when you need to fetch and process
records from a cursor
 Replaces the cursor operations such as:
 Open
 Fetch
 End of records
 Close

46
File [Link]
Illustrates a stored procedure
CREATE OR REPLACE PROCEDURE PrintStudents(
p_Major IN [Link]%TYPE) AS
CURSOR c_Students IS
SELECT first_name, last_name
FROM students
WHERE major = p_Major;
BEGIN
FOR v_StudentRec IN c_Students LOOP
DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name || ' ' ||
v_StudentRec.last_name);
END LOOP;
END;
/

47
PL / SQL Control Structures
PRAGMAs
 The PRAGMA keyword signifies a
preprocessor statement
 PRAGMAs are processed at compile
time
 They do not execute during runtime
 Passes information to the compiler

48
Example: Types of
Pragmas

 Until Oracle 11g, PL/SQL programming language could make use of

five types of pragma(s) which are listed as below.
 AUTONOMOUS_TRANSACTION – Compiler allows schema objects
like subprograms, PL/SQL blocks, or triggers to commit their
transaction within their scope.
 RESTRICT_REFERENCES – Allows the compiler to scan the database
purity during package compilation. It can be used in packages only.
 EXCEPTION_INIT – Compiler associates a user defined exception
name with an exception number.
 SERIALLY_REUSABLE – Compiler deduces the package state from
session level to just a server call. It can be used in Package spec
only.
 PRAGMA INLINE – New member of oracle pragma family. It is used
to explicitly inline a program unit.

49
PL / SQL Style Guide
 Good style means that:
 It will take less time to understand what the program
is doing
 Modifications can be made easier and will be less
error prone
 Uniformity of code increases productivity
 Areas of where style can be applied are:
 Comments
 Variable Names
 Capitalization
 Indentation
 General Guidelines

50
PL / SQL Style Guide
Comments
 At the start of each block or procedure
 With each variable declaration
 Before each major section of a block
 Comments should be meaningful and not
restate what the code obviously shows
 It’s possible to have too many comments

51
PL / SQL Style Guide
Variable Names
 Make the variable names
descriptive
t NUMBER;
v_StudentID NUMBER(5);
 A variable name can tell us its use:
v_VariableName Program variable
e_ExceptionName User-defined exception
t_TypeName User-defined type
p_ParameterName Parameter to a procedure or
function

52
PL / SQL Style Guide
Capitalization
 PL/SQL is not case sensitive
 Proper use of case will improve program
readability
 reserved words in uppercase
 built-in functions in uppercase
 predefined types in uppercase
 sql keywords in uppercase
 database objects in lowercase
 variable names are in mixed case, with capital letter
for each word in the name

53
PL / SQL Style Guide
Indentation
 Use of white space (carriage
returns, spaces, and tabs) is
necessary to provide readability of
your code
 Indent:
 inside blocks
 SQL continuation statements
 IF-THEN-ELSE blocks
54
PL / SQL Style Guide
General Guidelines
 As you write more code you will
develop a pronounced level of
style
 It’s a good idea to show your code
to another programmer and have it
reviewed
 Be consistent in making your code
understandable
55
In Conclusion
 We have covered the basic syntax and
structure of PL/SQL
 Block
 Variables
 Datatypes (scalar, composite, reference)
 Expressions and operators
 Datatype conversions
 Control structures
 Programming style

56
 Example1
 SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var varchar2(40) := 'I love GeeksForGeeks' ;
BEGIN
dbms_output.put_line(var);
END;
/
Output:
I love GeeksForGeeks
PL/SQL procedure successfully completed.

57
 Example 2
 SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
-- taking input for variable a
a integer := &a ;
-- taking input for variable b
b integer := &b ;
c integer ;

BEGIN
c := a + b ;
dbms_output.put_line('Sum of '||a||' and '||b||' is = '||c);

END;

58
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' ||
c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' ||
f);
END; 59
Example shows the usage of Local and Global
variables

60
 pl/sql program to illustrate If statement

declare
num1 := 10;
num2 := 20;
begin
if num1 > num2
then dbms_output.put_line('num1 small');
end if;
dbms_output.put_line('I am Not in if');
;
61
 Sum of first 10 natural numbers

DECLARE
i NUMBER := 1;
sum1 NUMBER := 0;
BEGIN
WHILE i <= 10 LOOP
sum1 := sum1 + i;
i := i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum of first 10 numbers = ' ||
sum1);
END; 62
 Factorial of a number

DECLARE
num NUMBER := &num;
fact NUMBER := 1;
BEGIN
FOR i IN 1..num LOOP
fact := fact * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is:
' || fact);
END;

63
 Assignment

1. Write a PL/SQL program to check whether a

given number is prime or not
2. Write a PL/SQL program to find sum of digits
of a given number
3. Write a PL/SQL program to Reverse a given
number
4. Write a PL/SQL program to check whether a
number is even or odd
5. Write a PL/SQL program calculate square and
cube of a given number
6. Write a PL/SQL program calculate simple
interest
64