PL SQL Operators
A PL SQL operator directs the compiler to do operations with the help of a symbol.
These operators are called symbols.
By default, PL/SQL has the following operator types:
Arithmetic
Comparison
Logical
String
Relational
1. Arithmetic Operators
Symbol Description
+ Performs addition of two operands.
* Performs multiplication of two operands.
/ Performs division of two operands.
- Performs subtraction of two operands.
** Performs exponential operation.
Example:
declare
v_num1 number := 20;
v_num2 number := 5;
res number;
begin
res:= v_num1 + v_num2;
dbms_output.put_line('sum: ' || res);
res:= v_num1 - v_num2;
dbms_output.put_line('difference: ' || res);
res:= v_num1 * v_num2;
dbms_output.put_line('product: ' || res);
res:= v_num1 / v_num2;
dbms_output.put_line('quotient: ' || res);
res:= v_num1 ** 2;
dbms_output.put_line('exponentiation (20^2): ' || res);
end;
/
Output:
sum: 25
difference: 15
product: 100
quotient: 4
exponentiation (20^2): 400
PL/SQL procedure successfully completed.
2. Relational Operators
This operator performs the comparison and return values in Boolean.
Symbol Description
> Verifies if the value of the left operand is greater than right.
< Verifies if the value of the right operand is greater than left.
>= Verifies if the value of the left operand is greater than equal to right.
<= Verifies if the value of the right operand is greater than equal to left.
= Verifies if two operands are equal.
!=,~= ,<> Verifies if two operands are not equal.
Example:
declare
a number:=5;
b number:=12;
begin
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;
/
Output:
a is not equal to b
PL/SQL procedure successfully completed.
3. Comparison Operators
This operator gives the output as either true, false, or null value based on the result of
comparing one statement with the other.
Symbol Description
BETWEEN Verifies if a value lies in a range.
IN Verifies if a value is equal to a member set.
LIKE Verifies if a string or character is similar to a pattern.
IS NULL Verifies if an operand is equal to the value Null.
Example:
declare
v_number1 number := 10;
v_text1 varchar2(20) := 'appreciate';
begin
if v_text1 like 'app%' then
dbms_output.put_line('v_text1 starts with app');
end if;
if v_number1 between 5 and 15 then
dbms_output.put_line('v_number1 is between 5 and 15 (inclusive)');
end if;
if v_number1 in (5, 10, 15) then
dbms_output.put_line('v_number1 is in the set (5, 10, 15)');
end if;
if v_number1 is not null then
dbms_output.put_line('v_number1 is not null');
end if;
end;
/
Output:
v_text1 starts with APP
v_number1 is between 5 and 15 (inclusive)
v_number1 is in the set (5, 10, 15)
v_number1 is not null
PL/SQL procedure successfully completed.
4. Logical Operators
Logical operators are used to combine or negate conditions, and they evaluate to a
Boolean value. These operators are often used in IF, CASE, and LOOP statements.
Symbol Description
NOT Known as logical NOT. If the result is true, then NOT makes it
false.
AND Known as logical AND. If all the operands are true, the result is
true.
OR Known as logical OR. If anyone of the operands is true, the
result is true.
Example:
declare
score1 number := 80;
score2 number := 70;
begin
if score1 >= 75 and score2 >= 75 then
dbms_output.put_line('both scores are above average.');
elsif score1 >= 75 or score2 >= 75 then
dbms_output.put_line('at least one score is above average.');
else
dbms_output.put_line('both scores are below average.');
end if;
end;
output:
at least one score is above average.
PL/SQL procedure successfully completed.
5. string operator
concatenation operator (||)
The || operator concatenates two or more strings.
Example:
declare
firstname varchar2(20) := 'hello';
lastname varchar2(20) := 'world';
fullname varchar2(50);
begin
fullname := firstname || ' ' || lastname;
dbms_output.put_line('full name: ' || fullname);
end;
/
output:
full name: hello world
PL/SQL procedure successfully completed.
Operator Precedence
PL SQL operator precedence is set to define how an operation involving a single or multiple
operator or more than one operand shall be done. The operators with higher precedence are
calculated first than the others.
The below table lists down the operators from the high to low precedence.
Symbol Description
** exponential operation
+,- addition, subtraction
*,/ multiplication, division
+,-,|| addition, subtraction, concatenation
comparison operators
NOT negation
AND conjunction
OR inclusion