Introduction to

PL/SQL
Unit 4

Prepared by Shimi 1
Biju
Introduction

 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.

Prepared by Shimi 2
Biju
Disadvantages of SQL

 SQL does not have any procedural capabilities

 SQL statements are passes to the oracle engine one at a time
 SQL has no facility for programmed handling of errors that arise
during the manipulation of data

Prepared by Shimi 3
Biju
Advantages of PL/SQL

 PL/SQL is development tool that not only supports sql data manipulation but also provides
facilities of conditional checking, branching and looping.

 Pl/SQL sends an entire block of sql statements to the oracle engine all in one go

 It also permits dealing with the errors as required, and facilitates displaying user friendly
messages

 It allows declaration and use of variables in blocks of code, which can store intermediate results
of query or to calculate values.

 All sorts of calculations can be done quickly and efficiently without the use of oracle engine.

 Application written in PL/SQL are portable to any computer hardware and operating system
where oracle is operational

Prepared by Shimi 4
Biju
PL/SQL Block

 PL/SQL permits the creation of structured logical blocks of code

that describe processes.
 A code block consists of set of sql statements and passed to the
oracle engine entirely
 A PL/SQL block has a definite structure, which can be divided into
sections

Prepared by Shimi 5
Biju
PL/SQL Block

 The Declare section

 The Begin and End Section which also (optionally) contains an
Exception section

Prepared by Shimi 6
Biju
Declare section

 Code block starts with declaration section, in which, memory

variables and other oracle objects can be declared and if required
initialized

Prepared by Shimi 7
Biju
Begin Section

 It consists of a set of SQL and PL/SQL statements, which describe

processes that have to be applied to table data
 Actual data manipulation, retrieval, looping and branching
constructs are specified in this section

Prepared by Shimi 8
Biju
The Exception
section
 This section deals with handling of errors that arise during
execution of the data manipulation statements which make up the
PL/SQL code block
 Errors can arise due to syntax, logic and/or validation rule violation

Prepared by Shimi 9
Biju
End Section

 This marks the end of a PL/SQL block

Prepared by Shimi 10
Biju
PL/SQL - Basic Syntax

DECLARE
Declarations of memory variables, constants, cursors etc in PL/SQL
BEGIN
SQL executable statements
PL/SQL executable statements
EXCEPTION
SQL or PL/SQL code to handle errors that may arise during the execution of
the code block between Begin and Exception section
END;

Prepared by Shimi 11
Biju
PL/SQL Environment

 The PL/SQL engine resides in the oracle engine

 Code blocks are sent to the PL/SQL engine where the procedural
statements are executed and SQL statements are sent to the SQL
executor in the Oracle engine
 The call to the oracle engine needs to be made only once to execute
any number of SQL statements if these SQL sentences are bundled
inside a PL/SQL block
 The speed of SQL statement execution is vastly enhanced.

Prepared by Shimi 12
Biju
PL/SQL Environment

Prepared by Shimi 13
Biju
PL/SQL The character set:

The basic character set includes the following:

 uppercase alphabets { A - Z } .
 lowercase alphabets {a-z }
 numerals { 0 - 9 } `
 symbols: () + - */ <> = !; : .‘ @ % ,“ # $ ^& _ \ { } ? [ ]
 Words used in a PL/SQL block are called Lexical Units. Blank
spaces can be freely insert between lexical units in a PL/SQL block.
The spaces have no effect on the PL/SQL block.

Prepared by Shimi 14
Biju
Literals
 A literal is a numeric value or a character string used to represent itself
 Numeric Literal
 These can be either integers or floats
 Eg. 25,6.34
 String Literal
 These are represented by one or more legal characters and must be enclosed
within single quotes.
 Eg, ‘Hello World’
 Character Literal
 These are string literals consisting of single characters
 Eg, ‘A’
 Logical Literal
 These are predetermined constants.
 Eg, TRUE, FALSE, NULL
Prepared by Shimi 15
Biju
PL/SQL Data Types

 The default data types that can be declared in PL/SQL are

 number (for storing numeric data},
 Char (for storing character data},
 date (for storing date and time data},
 boolean (for storing TRUE, FALSE or NULL),

number, char and date data types can have NULL values.

Prepared by Shimi 16
Biju
The %TYPE attribute

 PL/SQL can use the %TYPE attribute to declare variables based

on definitions of columns in a table.
 %TYPE declares a variable or constant to have the same data type
as that of a previously defined variable or of a clolumn in a table
or in a view

Prepared by Shimi 17
Biju
NOT NULL

 It causes the creation of a variable or a constant that cannot be

assigned a null value
 If an attempt is made to assign the value NULL to a variable or a
constant that has been assigned a NOT NULL constraint, an
internal error is returned

Prepared by Shimi 18
Biju
Variables:

 Variables in PL/SQL blocks are named variables.

 A variable name must begin with a character and can be followed
by a maximum of 29 other characters.
 Reserved words cannot be used as variable names unless enclosed
within double quotes.
 Variables must be separated from each other by at least one space or
by a punctuation mark.
 Case is insignificant when declaring variable names
 A space cannot be used in a variable name

Prepared by Shimi 19
Biju
Assigning values to
variables
 Using the assignment operator :=
 Selecting or fetching table data values into variables

Prepared by Shimi 20
Biju
Constants:

 Declaring a constant is similar to declaring a variable except that

you have to add the keyword 'constant' and immediately assign a
value to it.
 Thereafter, no further assignments to the constant are possible,
while the constant is within the scope of the PL/SQL block.

Prepared by Shimi 21
Biju
Displaying user Message on the Screen

BEGIN
dbms_output.put_line(‘Welcome to Pl/SQL');
END;

Prepared by Shimi 22
Biju
Displaying user Messages on the Screen:

 Programming tools require a method through which message can

be displayed to the user on the VDU screen.
 DBMS_OUTPUT is a package that includes a number of procedure
and functions that accumulate information in a buffer so that it can
be retrieved later. These functions can also be used to display
messages to the user.
 PUT LINE: Puts a piece of information in the package buffer
followed by an end-of-line marker, It can also be used to display
message to the user. Put line expects a single parameter of character
data type. if used to display a message, it is the message ‘string’.

Prepared by Shimi 23
Biju
Comments:

A comment can have two forms'

1. The comment line begins with a double hyphen (--).
 The entire line will be treated as a comment.
2. The comment line begins with a slash followed by an asterisk (/*)
till the occurrence of an asterisk followed by a slash (*/).
 All lines within are treated as comments.
 This form of specifying comments can be used to span across multiple
lines.
 technique can also be used to enclose a section of a PL/SQL block that
temporarily needs to be isolated and ignored.

Prepared by Shimi 24
Biju
PL/SQL Variables
 DECLARE
 a number := 10;
 b number;
 c char :='A';
 str char(10) :='welcome';
 pi constant number :=3.14;

 BEGIN
 dbms_output.put_line('Value of a is '||a);
 b := 20;
 dbms_output.put_line('Value of b is '||b);
 dbms_output.put_line('Value of c is '||c);
 dbms_output.put_line('Value of str is '||str);
 dbms_output.put_line('Value of constant pi is '||pi);
 END;

Prepared by Shimi 25
Biju
We can assign values to variables in the two ways

1) We can directly assign values to variables.

The General Syntax is:

variable_name:= value;
2)We can assign values to variables directly from the database columns by
using a SELECT.. INTO statement. The General Syntax is:
SELECT column_name
INTO variable_name
FROM table_name
[WHERE condition];

Prepared by Shimi 26
Biju
DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee ' || var_emp_id || ' has salary ' ||
var_salary);
END;

Prepared by Shimi 27
Biju
 Scope of PS/SQL Variables

 PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution
section of an outer block can contain inner blocks.
 Therefore, a variable which is accessible to an outer Block is also
accessible to all nested inner Blocks.
 The variables declared in the inner blocks are not accessible to outer
blocks.

 Based on their declaration we can classify variables into two types.

 Local variables - These are declared in a inner block and cannot be

referenced by outside Blocks.
 Global variables - These are declared in a outer block and can be
referenced by its itself and by its inner blocks.
Prepared by Shimi Biju
 For Example: In the below example we are creating two variables in the outer block and
assigning their product to the third variable created in the inner block. The variable 'var_mult' is
declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed
after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.

1> DECLARE
2> var_num1 number;
3> var_num2 number;
4>BEGIN
5> var_num1 := 100;
6> var_num2 := 200;

7> DECLARE
8> var_mult number;
9> BEGIN
10> var_mult :=
11> var_num1 *
var_num2;
12> END;
END;
Prepared by Shimi 29
Biju
 General Syntax to declare a constant is:
 constant_name CONSTANT datatype := VALUE;
 constant_name is the name of the constant i.e. similar to a variable
name.
 The word CONSTANT is a reserved word and ensures that the
value does not change.
 VALUE - It is a value which must be assigned to a constant when it
is declared. You cannot assign a value later.

Prepared by Shimi 30
Biju
 For example, to declare salary_increase, you can write code as follows:

 DECLARE
 salary_increase CONSTANT number (3) := 10;
 You must assign a value to a constant at the time you declare it. If you do not assign a value to a
constant while declaring it and try to assign a value in the execution section, you will get a
error. If you execute the below Pl/SQL block you will get error.

 DECLARE
 salary_increase CONSTANT number(3);
 BEGIN
 salary_increase:= 100;
 dbms_output.put_line (salary_increase);
 END;

Prepared by Shimi 31
Biju
PL/SQL Datatypes

 It defines how the data is stored, handled and treated by Oracle

during the data storage and processing.
 The main difference between PL/SQL and SQL data types is, SQL
data type are limited to table column while the PL/SQL data types
are used in the PL/SQL blocks.

Prepared by Shimi 32
Biju
Different Data Types in PL/SQL

Prepared by Shimi 33
Biju
Numeric Data Types and Subtypes

Prepared by Shimi 34
Biju
Prepared by Shimi 35
Biju
 NUMBER Data Type:
 This data type stores fixed or floating point numbers up to 38 digits of precision.
 This data type is used to work with fields which will contain only number data.
 The variable can be declared either with precision and decimal digit details or
without this information.
 Values need not enclose within quotes while assigning for this data type.
 A NUMBER(8,2);
 B NUMBER(8);
 C NUMBER;
 Syntax Explanation:
 In the above, the first declaration declares the variable 'A' is of number data type
with total precision 8 and decimal digits 2.
 The second declaration declares the variable 'B' is of number data type with total
precision 8 and no decimal digits.
 The third declaration is the most generic, declares variable 'C' is of number data
type with no restriction in precision or decimal places. It can take up to a
maximum of 38 digits.
Prepared by Shimi Biju
36
CHARACTER Data Type:

 This data type basically stores alphanumeric characters in string format.

 The literal values should always be enclosed in single quotes while
assigning them to CHARACTER data type.
 This character data type is further classified as follows:
 CHAR Data type (fixed string size)
 VARCHAR2 Data type (variable string size)
 VARCHAR Data type
 NCHAR (native fixed string size)
 NVARCHAR2 (native variable string size)
 LONG and LONG RAW

Prepared by Shimi 37
Biju
CHAR

Data Type Description Syntax

CHAR This data type stores the string grade CHAR;

value, and the size of the string is manager CHAR (10):= ‘ o r a c l e ;
fixed at the time of declaring the Syntax Explanation:
variable.
 The first declaration statement
declared the variable 'grade' of
 Oracle would be blank-padded CHAR data type with the
the variable if the variable maximum size of 1 byte
didn't occupy the entire size (default value).
that has been declared for it,
Hence Oracle will allocate the  The second declaration
memory for declared size even statement declared the variable
if the variable didn't occupy it 'manager' of CHAR data type
fully. with the maximum size of 10
and assigned the value ‘oracle'
 The size restriction for this data which is of 6 bytes. Oracle will
type is 1-2000 bytes. allocate the memory of 10
bytes rather than 6 bytes in this
 CHAR data type is more case.
appropriate to use where ever
fixed the size of data will be
handled.

Prepared by Shimi 38
Biju
VARCHAR
2
Data Type Description Syntax

VARCHAR2 This data type stores the string, but manager VARCHAR2(10) : = ‘ o r a c l e ' ;
the length of the string is not fixed. Syntax Explanation:

 The size restriction for this data  The above declaration

type is 1-4000 bytes for table statement declared the
column
variablesize and 1-32767 bytes 'manager' of VARCHAR2 data
for variables. type with the maximum size of
10 and assigned the value
 The size is defined for each ‘oracle' which is of 6 bytes.
variable at the time of variable Oracle will allocate memory of
declaration. only 6 bytes in this case.
 But Oracle will allocate memory
only after the variable is
defined, i.e., Oracle will
consider only the actual length
of the string that is stored in a
variable for memory allocation
rather than the size that has
been given for a variable in the
declaration part.
 It is always good to use
VARCHAR2 instead of CHAR
data type to optimize the
memory usage.
Prepared by Shimi 39
Biju
VARCHAR

Data Type Description Syntax

VARCHAR This is synonymous with the manager VARCHAR(10) : = ‘oracle';

VARCHAR2 data type. Syntax Explanation:

 It is always a good practice to  The above declaration

use VARCHAR2 instead of statement declared the variable
VARCHAR to avoid behavioral 'manager' of VARCHAR data
changes. type with the maximum size of
10 and assigned the value
‘oracle' which is of 6 bytes.
Oracle will allocate memory of
only 6 bytes in this case.
(Similar to VARCHAR2)

Prepared by Shimi 40
Biju
NCHAR
Data Type Description Syntax

NCHAR This data type is same as CHAR native NCHAR(10);

data type, but the character set will Syntax Explanation:
of the national character set.
 The above declaration
statement declares the variable
 This character set can be 'native' of NCHAR data
type
NLS_PARAMETERS.
defined for the session using 
with the maximum size of 10.
The length of this variable
 The character set can be either depends upon the (number of
UTF16 or UTF8. lengths) per byte as defined in
the character set.
 The size restriction is 1-2000
bytes.

Prepared by Shimi 41
Biju
NVARCHAR2
Data Type Description Syntax

NVARCHAR2 This data type is same as Native var NVARCHAR2(10):=‘oracle';

VARCHAR2 data type, but the Syntax Explanation:
character set will be of the national
character set.
 This character set can be  The above declaration
defined for the session using statement declares the variable
NLS_PARAMETERS. 'Native_var' of NVARCHAR2
 The character set can be either data type
size of 10.with the maximum
UTF16 or UTF8.
 The size restriction is 1-4000
bytes.

Prepared by Shimi 42
Biju
LONG and LONGRAW
Data Type Description Syntax

LONG and
This data type is used to store large Lar ge_text LONG;
LONGRAW
Large_raw LONG RAW;
text or raw data up to the maximum
Syntax Explanation:
size of 2GB.
 These are mainly used in the data
dictionary.  The above declaration
statement declares the
 LONG data type is used to store
variable 'Large_text' of
character set data, while LONG LONG data type and
RAW is used to store data in binary 'Large_raw' of LONG RAW

format. data type.

Note: Using LONG data type is

 LONG RAW data type accepts
not recommended by Oracle.
media objects, images, etc. Instead, LOB data type
43

should
Prepared by Shimi Biju
whereas LONG works only on data
BOOLEAN Data Type:

 This data type stores the logical values.

 It represents either TRUE or FALSE and mainly used in
conditional statements.
 Values need not enclose within quotes while assigning for this data
type.

 Var1 BOOLEAN;
 Syntax Explanation:
 In the above, variable 'Var1' is declared as BOOLEAN data type.
The output of the code will be either true or false based on the
condition set

Prepared by Shimi 44
Biju
 DATE Data Type:

 This data type stores the values in date format, as date, month, and year.
Whenever a variable is defined with DATE data type along with the date it can
hold time information and by default time information is set to 12:00:00 if not
specified. Values need to enclose within quotes while assigning for this data
type.
 The standard Oracle time format for input and output is 'DD-MON-YY' and it is
again set at NLS_PARAMETERS (NLS_DATE_FORMAT) at the session level.
 newyear DATE:='01-JAN-2015';
 current_date DATE:=SYSDATE;
 Syntax Explanation:
 In the above, variable 'newyear' is declared as DATE data type and assigned the
value of Jan 1st, 2015 date.
 The second declaration declares the variable current_date as DATE data type
and assigned the value with current system date.
 Both these variable holds the time information.
Prepared by Shimi Biju
LOB Data Type:

 This data type is mainly used to store and manipulate large blocks of unstructured
data's like images, multimedia files, etc.
 Oracle prefers LOB instead of the a LONG data type as it is more flexible than the
LONG data type.
 The below are the few main advantage of LOB over LONG data type.
 The number of column in a table with LONG data type is limited to 1, whereas
a table has no restriction on a number of columns with LOB data type.
 The data interface tool accepts LOB data type of the table during data
replication, but it omits LONG column of the table. These LONG columns need
to be replicated manually.
 The size of the LONG column is 2GB, whereas LOB can store up to 128 TB.
 Oracle is constantly improvising the LOB data type in each of their releases
according to the modern requirement, whereas LONG data type is constant and
not getting many updates.

Prepared by Shimi 46
Biju
Different LOB data types
Data Type Description Syntax

Binary_data BLOB;
BLOB This data type stores the LOB data in the binary file format up to Syntax Explanation:
the maximum size of 128 TB. This doesn't store data based on
the character set details, so that it can store the unstructured data  In the above, variable 'Binary_data' is
such as multimedia objects, images, etc. declared as a BLOB.

Charac_data CLOB;
CLOB and CLOB data type stores the LOB data into the character set, Syntax Explanation:
NCLOB whereas NCLOB stores the data in the native character set.  In the above, variable 'Charac_data' is
Since these data types use character set based storage, these declared as CLOB data type.
cannot store the data like multimedia, images, etc. that cannot be
put into a character string. The maximum size of these data
types is 128 TB.

BFILE  BFILE are the data types that stored the unstructured binary
format data outside the database as an operating-system
file.
 The size of BFILE is to a limited operating system, and they
are read-only files and can't be modified.

Prepared by Shimi Biju 47

PL/SQL - Operators

 An operator is a symbol that tells the compiler to perform specific

mathematical or logical manipulation. PL/SQL language is rich in
built-in operators and provides the following types of operators −
 Arithmetic operators
 Relational operators
 Comparison operators
 Logical operators
 String operators

Prepared by Shimi 48
Biju
 Arithmetic Operators
 Following table shows all the arithmetic operators supported by
PL/SQL. Let us assume variable A holds 10 and variable B holds
5, then −
Operator Description Example

+ Adds two operands A + B will give 15

- Subtracts second A - B will give 5

operand from the first

* Multiplies both operands A * B will give 50

/ Divides numerator by A / B will give 2

de- numerator

** Exponentiation operator, A ** B will

raises one operand to give 100000
the power of other
Prepared by Shimi 49
Biju
Relational Operators
 Relational operators compare two expressions or values and return a
Boolean result. Following table shows all the relational operators
supported by PL/SQL. Let us assume variable A holds 10 and
variable B holds 20, then
Operator Description Example

Checks if the values of two operands are equal or not, if (A = B) is not true.
=
yes then condition becomes true.
!=
Checks if the values of two operands are equal or not, if
<> (A != B) is true.
values
~= are not equal then condition becomes true.

Checks if the value of left operand is greater than the (A > B) is not true.
>
value of right operand, if yes then condition becomes
true.

Checks if the value of left operand is less than the (A < B) is true.
<
value of right operand, if yes then condition becomes
true.

Checks if the value of left operand is greater than or equal (A >= B) is not true.
>=
to the value of right operand, if yes then condition
becomes true.

Prepare Checks if the value of left operand is less than or equal to (A <= B) is true
d by Shimi<B =iju the value of right operand, if yes then condition 50
Exampl
DECLARE
e a number (2) := 21;


 b number (2) := 10;

 BEGIN
 IF (a = b) then
 dbms_output.put_line(‘a is equal to b');
 ELSE
 dbms_output.put_line('a is not equal to b');
 END IF;
 IF (a < b) then
 dbms_output.put_line(‘ a is less than b');
 ELSE
 dbms_output.put_line(' a is not less than b');
 END IF;
 IF ( a > b ) THEN
 dbms_output.put_line(' a is greater than b');
 ELSE
 dbms_output.put_line(' a is not greater than b');
 END IF;
Prepared by Shimi 51
Biju
 a := 5;
 b := 20;
 IF ( a <= b ) THEN
 dbms_output.put_line('a is either equal or less than b');
 END IF;
 IF ( b >= a ) THEN
 dbms_output.put_line('b is either equal or greater than a');
 END IF;
 IF ( a <> b ) THEN
 dbms_output.put_line('a is not equal to b');
 ELSE
 dbms_output.put_line('a is equal to b');
 END IF;
 END;

Prepared by Shimi 52
Biju
Comparison Operators
 Comparison operators are used for comparing one expression to
another. The result is always either TRUE, FALSE or NULL.
Operator Description Example

The LIKE operator compares a character, string, or If 'Zara Ali' like 'Z% A_i' returns a
LIKE CLOB value to a pattern and returns TRUE if the Boolean true, whereas, 'Nuha Ali'
value matches the pattern and FALSE if it does like 'Z% A_i' returns a Boolean
not. false.

If x = 10 then, x between 5 and

The BETWEEN operator tests whether a value lies
20 returns true, x between 5
BETWEEN in a specified range. x BETWEEN a AND b means
and 10 returns true, but x
that x >= a and x <= b.
between 11 and 20 returns
false.
If x = 'm' then, x in ('a', 'b', 'c')
The IN operator tests set membership. x IN (set)
IN returns Boolean false but x in
means that x is equal to any member of set.
('m', 'n', 'o') returns Boolean
true.

The IS NULL operator returns the BOOLEAN value

If x = 'm', then 'x is null'
IS NULL TRUE if its operand is NULL or FALSE if it is not
returns Boolean false.
NULL. Comparisons involving NULL values always
yield NULL.
Prepared by Shimi 53
Biju
LIKE
 The Oracle LIKE condition allows wildcards to be used in the WHERE clause
of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to
perform pattern matching.
 Syntax
 The syntax for the LIKE condition in Oracle/PLSQL is:

 expression LIKE pattern [ ESCAPE 'escape_character' ]

 expression
 A character expression such as a column or field.
 pattern
 A character expression that contains pattern matching. The patterns that you can
choose from are:

Prepared by Shimi 54
Biju
 Wildcard Explanation
Allows you to match any string of
%
any length (including zero length)
Allows you to match on a
_
single character

escape_character

• Optional.
• It allows you to test for literal instances of a wildcard character such as % or _.

Prepared by Shimi 55
Biju
Example - Using % wildcard (percent sign
wildcard)
to find all of the customers whose last_name begins with 'Ap'.
SELECT last_name
FROM customers
WHERE last_name
LIKE 'Ap%';

 You can also using

the % wildcard
multiple times within
the same string.
For example,

SELECT last_name
Pr epar wFROM
ed bhy Sohim si eBijulast_name contains the
customers 56
Example - Using _ wildcard (underscore wildcard)
 _ wildcard is looking for only one character.
 Example
SELECT supplier_name
FROM suppliers
WHERE supplier_name
LIKE 'Sm_th';
 This Oracle LIKE condition example would return all suppliers whose
supplier_name is 5 characters long, where the first two characters is 'Sm' and the last
two characters is 'th'. For example, it could return suppliers whose supplier_name is
'Smith', 'Smyth', 'Smath', 'Smeth', etc.

 Here is another example:

SELECT *
FROM suppliers
WHERE account_number
LIKE '92314_';
 You might find that you are looking for an account number, but you only have 5 of
Using NOT Operator

 Let's use the % wilcard with the NOT Operator.

 Find suppliers whose name does not start with 'T'.

 For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name
NOT LIKE ‘T%';
 By placing the NOT Operator in front of the Oracle LIKE condition, you
are able to retrieve all suppliers whose supplier_name does not start
with 'W'.
Prepared by Shimi 58
Biju
Using Escape Characters

 It is important to understand how to "Escape Characters" when

pattern matching. These examples deal specifically with escaping
characters in Oracle.

 Let's say you wanted to search for a % or a _ character in the Oracle

LIKE condition. You can do this using an Escape character.

 Please note that you can only define an escape character as a single
character (length of 1).

Prepared by Shimi 59
Biju
example:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'Water!%' ESCAPE '!';
 This Oracle LIKE condition example identifies the ! character as an escape
character. This statement will return all suppliers whose name is Water%.

 Here is another more complicated example using escape characters in the Oracle
LIKE condition.

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!%' ESCAPE '!';

 This Oracle LIKE condition example returns all suppliers whose name starts
with H and ends in %. For example, it would return a value such as 'Hello%'.
Prepared by Shimi 60
Biju
 The escape character with the _ character in the Oracle LIKE
condition.
 For example:

 SELECT *
 FROM suppliers
 WHERE supplier_name LIKE 'H%!_' ESCAPE '!';
 This Oracle LIKE condition example returns all suppliers whose
name starts with H and ends in _. For example, it would return a
value such as 'Hello_'.

Prepared by Shimi 61
Biju
BETWEEN Operator

 The following program shows the usage of the BETWEEN

operator −
DECLARE
x number(2) := 10;
BEGIN
IF (x between 5 and 20) THEN
dbms_output.put_line('True');
ELSE
dbms_output.put_line('False');
END IF;

Prepared by Shimi 62
Biju
IN and IS NULL Operators

 The following program shows the usage of IN and IS NULL operators −

 DECLARE
 letter varchar2(1) := 'm';
 BEGIN
 IF (letter in ('a', 'b', 'c')) THEN
 dbms_output.put_line('True');
 ELSE
 dbms_output.put_line('False');
 END IF;
 END

Prepared by Shimi 63
Biju
 DECLARE
 letter varchar2(1) := 'm';
 BEGIN
 IF (letter is null) THEN
 dbms_output.put_line('True');
 ELSE
 dbms_output.put_line('False');
 END IF;
 END;

Prepared by Shimi 64
Biju
Logical Operators
 Following table shows the Logical operators supported by PL/SQL.
All these operators work on Boolean operands and produce
Boolean results. Let us assume variable A holds true and variable
B holds false, then −
Operator Description Examples

Called the logical AND operator. If both the (A and B) is false.

and
operands are true then condition becomes
true.

Called the logical OR Operator. If any of the (A or B) is true.

or
two operands is true then condition
becomes true.

Called the logical NOT Operator. Used to not (A and B) is true.

reverse the logical state of its operand. If
not a
condition is true then Logical NOT
operator
Pr epar
will make it false. 65
ed by Shimi Biju
Exampl
e
 DECLARE
 a boolean := true;
 b boolean := false;
 BEGIN
 IF (a AND b) THEN
 dbms_output.put_line('Line 1 - Condition is true');
 END IF;
 IF (a OR b) THEN
 dbms_output.put_line('Line 2 - Condition is true');
 END IF;
 IF (NOT a) THEN
 dbms_output.put_line('Line 3 - a is not true');
 ELSE
 dbms_output.put_line('Line 3 - a is true');
 END IF;
 END;
Prepared by Shimi Biju
66
Control Structure

 The flow of control statements can be classified into the following

categories
 Conditional Control
 Iterative Control
 Sequential Control

Prepared by Shimi 67
Biju
Types of decision making statements

 IF-THEN
 IF-THEN-ELSE
 IF-THEN-ELSIF
 NESTED-IF
 CASE
 SEARCHED CASE

Prepared by Shimi 68
Biju
IF-THEN Statement

 The IF-THEN statement is mainly used to execute a particular

section of codes only when the condition is satisfied.
 The condition should yield Boolean (True/False). It is a basic
conditional statement which will allow the ORACLE to
execute/skip a particular piece of code based on the pre-defined
conditions

Prepared by Shimi 69
Biju
Syntax for IF THEN Statements:

IF <condition: returns Boolean>THEN

-executed only if the condition returns TRUE
<action_block>
END if;
In the above syntax, keyword 'IF' will be followed by a condition
which evaluates to 'TRUE'/'FALSE'.
The control will execute the <action_block> only if the condition
returns <TRUE>.
In the case of condition evaluates to <FALSE> then, SQL will
skip the <action_block>, and it will start executing the code next to
'END IF' block.

Prepared by Shimi 70
Biju
 Example 1: In this example, we are going to print a message when the number
is greater than 100. For that, we will execute the following code
 To print a message when a number has value more than 100, we execute the
following code.
 DECLARE
 a NUMBER :=10;
 BEGIN
 dbms_output.put_line(‘Program started.' );
 IF( a > 100 ) THEN
 dbms_output.put_line('a is greater than 100');
 END IF;
 dbms_output.put_line(‘Program completed.');
 END;

Prepared by Shimi 71
Biju
 Example 2: In this example, we are going to print a message if a given
alphabet is present in English vowels (A, E, I, O, U).
 To print a message when the given character is Vowel, we execute the
following code.
 DECLARE
 a CHAR(1) :=’u’;
 BEGIN
 IF UPPER(a) in ('A’,'E','I','0','U' ) THEN
 dbms_output.put_line(‘The character is in English Vowels');
 END IF;
 END;

Prepared by Shimi 72
Biju
IF-THEN-ELSE Statement

 The IF-THEN-ELSE statement is mainly used to select between two

alternatives based on the condition.
 Below is the syntax representation of IF-THEN-ELSE statement.
 Syntax for IF-THEN-ELSE Statements:
 IF <condition: returns Boolean>THEN
 -executed only if the condition returns TRUE
 <action_blockl>
 ELSE
 -execute if the condition failed (returns FALSE)
 <action_block2>
 END if;

Prepared by Shimi 73
Biju
 In the above syntax, keyword 'IF' will be followed by a condition
which evaluates to 'TRUE'/'FALSE'.
 The control will execute the <action_block1> only if the condition
returns <TRUE>.
 In case of condition evaluates to <FALSE> then, SQL will execute
<action_block2>.
 In any case, one of the two action blocks will be executed.

Prepared by Shimi 74
Biju
 Example 1: In this example, we are going to print message whether the given
number is odd or even.
DECLARE
a NUMBER:=11;
BEGIN
dbms_outpu
t.put_line
(‘Program
started');
IF( mod(a,2)
=0) THEN
dbms_output.put_line('a is even number' );
ELSE
dbms_output.put_line('a is odd
number1);
END IF;
Prepared by Shimi 75
Biju
IF-THEN-ELSIF Statement

 The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-
THEN statement can be followed by an optional ELSIF...ELSEstatement. The ELSIF clause
lets you add additional conditions.
 Syntax
 The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming language is −

IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN
S3; -- Executes when the boolean expression 3 is
true
ELSE
S4; -- executes when the none of the above condition is true
END IF;
Prepared by Shimi 76
Biju
Exampl
e
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 )
THEN
dbms_output.pu
t_line('Value of a
is 10' );
ELSIF ( a = 20 )
THEN
dbms_output.pu
t_line('Value of a
is 20' );
ELSIF ( a = 30 )
THEN
dbms_output.put_line('Value of a is 30' ); 77
Nested IF-THEN-ELSE Statements

One IF or ELSE IF statement inside another IF or ELSE

IFstatement(s). Syntax
IF( boolean_expression 1)THEN
-- executes when the boolean expression 1 is true
IF(boolean_expression 2) THEN
-- executes when the boolean expression 2 is true
sequence-of-statements;
END IF;
ELSE
-- executes when the boolean expression 1 is not
true else-statements;
END IF;

Prepared by Shimi 78
Biju
Exampl
e
DECLARE
a number(3) := 100;
b number(3) := 200;
BEGIN
-- check the boolean condition
IF( a = 100 ) THEN
-- if condition is true then check the following
IF( b = 200 ) THEN
-- if condition is true then print the following
dbms_output.put_line('Value of a is 100 and b is 200' );
END IF;
END IF;
dbms_output.put_line('Exact value of a is : ' || a );
dbms_output.put_line('Exact value of b is : ' || b );
END;

Prepared by Shimi 79
Biju
CASE 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.
 Syntax
 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
END CASE;
Prepared by Shimi 80
Biju
Exampl
e
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;
Prepared by Shimi 81
Biju
Searched CASE Statement

 The searched CASE statement has no selector and the WHEN clauses
of the statement contain search conditions that give Boolean values.
 Syntax
 The syntax for the searched case statement in PL/SQL is −

 CASE
 WHEN selector = 'value1' THEN S1;
 WHEN selector = 'value2' THEN S2;
 WHEN selector = 'value3' THEN S3;
 ...
 ELSE Sn; -- default case
 END CASE;

Prepared by Shimi 82
Biju
Exampl
e
DECLARE
grade char(1) := 'B';
BEGIN
case
when grade = 'A' then
dbms_output.put_line('Excellent'); when grade = 'B' then
dbms_output.put_line('Very good');
when grade = 'C' then dbms_output.put_line('Well
done'); when grade = 'D' then dbms_output.put_line('You
passed');
when grade = 'F' then dbms_output.put_line('Better try
again'); else dbms_output.put_line('No such grade');
end case;
END;
Prepared by Shimi 83
Biju
Loops
 A loop statement allows us to execute a statement or group of
statements multiple times
S.No Loop Type & Description
PL/SQL Basic LOOP

1 In this loop structure, sequence of statements is enclosed between the LOOP and
the END LOOP statements. At each iteration, the sequence of statements is
executed and then control resumes at the top of the loop.

PL/SQL WHILE LOOP

2
Repeats a statement or group of statements while a given condition is true. It tests
the condition before executing the loop body.
PL/SQL FOR LOOP
3 Execute a sequence of statements multiple times and abbreviates the code that
manages the loop variable.

Nested loops in PL/SQL

4 You can use one or more loop inside any another basic loop, while, or for loop.

Prepared by Shimi 84
Biju
Simple Loop

 A Simple Loop is used when a set of statements is to be executed at least

once before the loop terminates. An EXIT condition must be specified in
the loop, otherwise the loop will get into an infinite number of
iterations. When the EXIT condition is satisfied the process exits from
the loop.

 General Syntax to write a Simple Loop is:

LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;

Prepared by Shimi 85
Biju
 These are the important steps to be followed while using Simple
Loop.
1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) Use a EXIT WHEN statement to exit from the Loop.
If you use a EXIT statement without WHEN condition, the
statements in the loop is executed only once.

Prepared by Shimi 86
Biju
Example of PL/SQL EXIT Loop

Let's take a simple example to explain it well:

DECLARE
i NUMBER := 1;
BEGIN
LOOP
E
XI
T
W
H
E
N
i>
Prepared by Shimi 87
Biju
PL/SQL EXIT Loop Example 2

DECLARE
VAR1 NUMBER;
VAR2 NUMBER;
BEGIN
VAR1:=100;
VAR2:=1
; LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
IF (VAR2=10) THEN
EXIT;
END IF;
VAR2:=VAR2+
1;
END LOOP;
END; 88
 While Loop

 A WHILE LOOP is used when a set of statements has to be executed as long as

a condition is true. The condition is evaluated at the beginning of each iteration.
The iteration continues until the condition becomes false.

 The General Syntax to write a WHILE LOOP is:

WHILE <condition> LOOP
statements;
END LOOP;
 Important steps to follow
when executing a while
loop:
1) Initialise a variable before
the loop body.
2) Increment the variable in
Example of PL/SQL While Loop

DECLARE
i INTEGER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;

Prepared by Shimi 90
Biju
PL/SQL WHILE Loop Example 2

DECLARE
VAR1 NUMBER;
VAR2 NUMBER;
BEGIN
VAR1:=200;
VAR2:=1;
WHILE (VAR2<=10)
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
VAR2:=VAR2+1;
END LOOP;
END;

Prepared by Shimi 91
Biju
FOR Loop

 A FOR LOOP is used to execute a set of statements for a predetermined

number of times.
 Iteration occurs between the start and end integer values given.
 The counter is always incremented by 1.
 The loop exits when the counter reachs the value of the end integer.

 The General Syntax to write a FOR LOOP is:

FOR counter IN val1..val2 LOOP
statements;
END LOOP;
 val1 - Start integer value.
 val2 - End integer value.
Prepared by Shimi Biju 92
 Important steps to follow when executing a while loop:

1. The counter variable is implicitly declared in the declaration

section, so it's not necessary to declare it explicity.
2. The counter variable is incremented by 1 and does not need to be
incremented explicitly.
3. EXIT WHEN statement and EXIT statements can be used in FOR
loops but it's not done oftenly.

Prepared by Shimi 93
Biju
PL/SQL For Loop Example 1

 Let's see a simple example of PL/SQL FOR loop.

BEGIN
FOR k IN 1..10 LOOP
-- note that k was not declared
DBMS_OUTPUT.PUT_LINE(k)
; END LOOP;
END;

Prepared by Shimi 94
Biju
PL/SQL For Loop Example 2

DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
END LOOP;
END;

Prepared by Shimi 95
Biju
PL/SQL For Loop REVERSE Example 3

 Let's see an example of PL/SQL for loop where we are using REVERSE
keyword.
DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN
REVERSE 1..10
LOOP
DBMS_OUTPUT.
PUT_LINE
(VAR1*VAR2);
END LOOP;
Prepared by Shimi 96
END;
Biju
The Loop Control Statements

 Loop control statements change execution from its normal

sequence.
 When execution leaves a scope, all automatic objects that were
created in that scope are destroyed.
 PL/SQL supports the following control statements.
 Labeling loops also help in taking the control outside a loop.

Prepared by Shimi 97
Biju
Loop Control Statements
S.No Control Statement & Description
EXIT statement
1
The Exit statement completes the loop and control passes to
the statement immediately after the END LOOP.
CONTINUE statement

2 Causes the loop to skip the remainder of its body and

immediately retest its condition prior to reiterating.

GOTO statement

3 Transfers control to the labeled statement. Though it is not

advised to use the GOTO statement in your program.

Prepared by Shimi 98
Biju
Sequential control

 A GOTO statement in PL/SQL programming language provides an

unconditional jump from the GOTO to a labeled statement in the same
subprogram.
 Syntax
The syntax for a GOTO statement in PL/SQL is as follows −

GOTO label;
..
..
<< label >>
statement;

Prepared by Shimi 99
Biju
Exampl
e
DECLARE
a number(2) := 10;
BEGIN
<<loopstart>>
-- while loop execution
WHILE a < 20 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 15 THEN
a := a + 1;
GOTO loopstart;
END IF;
END LOOP;
END;

Prepared by Shimi 100

Biju