PL/SQL Operators

< PL/SQL Literals

PL/SQL Operator Precedence >

Operators are said to be symbols that tells the compiler for performing specific mathematical or logical manipulation. This language is rich in built-in operators and also provides the following types of operators:

Arithmetic operators

Let us see all the operators supported by PL/SQL. Assume two variables A that holds value 20 and variable B holds 10-

Addition (+): it adds two operands, like A+B that gives 30.
Subtraction (-): it subtracts two operands, like A-B that gives 10.
Multiplication (*): it multiplies two operands, and gives result as 200.
Divide (/): it divides two operands, and gives result as 2.
Exponentiation (**): this operator raises one operand to the power of other. Like 2 to the power of 5 gives 32.

Given below is the example in PL/SQL.

BEGIN
dbms_output.put_line  (20+10);
dbms_output.put_line  (20-10);
dbms_output.put_line  (20*10);
dbms_output.put_line  (20/10);
dbms_output.put_line  (2**5);
END;
/

When we execute the above code we get following output.

PL/SQL procedure successfully completed.

Relational operators

These operators compare two expressions or the values and then returns a Boolean result. Assume two variables A that holds 30 and variable B holds 20.

Equal to (=): This operator checks whether two operands are unequal or not, if yes then the condition becomes true. A=B is not true.
{ !=, <>, ~=}: they check whether the two operands or equal or not, if these values are not equal then the condition becomes true. Like A != B is true.
Greater than (>): they check whether if the left operand is greater than the value of right operand, if yes then the condition becomes true. A > B is not true.
Lesser than (<): they check whether if the left operand is lesser than the value of right operand, if yes then the condition becomes true. A < B is not true.
Greater than equal to (>=): they check whether if the left operand is greater than or equal to the value of right operand, if yes then the condition becomes true. A >= B is not true.
Lesser than equal to (<=): they check whether if the left operand is lesser than or equal to the value of right operand, if yes then the condition becomes true. A <= B is true.

Let us see the example:

DECLARE 
   a number (2) := 30; 
   b number (2) := 20; 
BEGIN 
   IF (a = b) then 
      dbms_output.put_line('a is equal to value of b'); 
   ELSE 
      dbms_output.put_line('a is not equal to value of b'); 
   END IF;  
   IF (a < b) then 
      dbms_output.put_line(‘a is less than value of  b'); 
   ELSE 
      dbms_output.put_line(' a is not less than value of b'); 
   END IF; 
   IF ( a > b ) THEN 
      dbms_output.put_line('a is greater than value of b'); 
   ELSE 
      dbms_output.put_line('a is not greater than value of b'); 
   END IF;  
   -- Lets change value of a and b 
   a := 5; 
   b := 10; 
   IF ( a <= b ) THEN 
      dbms_output.put_line(‘ a is either equal or less than value of b'); 
   END IF; 
   IF ( b >= a ) THEN 
      dbms_output.put_line(' b is either equal or greater than value of a'); 
   END IF;
   IF ( a <> b ) THEN 
      dbms_output.put_line('a is not equal to value of b'); 
   ELSE 
      dbms_output.put_line(' a is equal to value of b'); 
   END IF;  
END; 
/
When we execute the above code, we get the following output:
a is not equal to value of b 
 a is not less than value of b 
 a is greater than value of b 
 a is either equal or less than value of b 
 b is either equal or greater than value of a 
 a is not equal to value of b

When we execute the above code, we get the following output:

a is not equal to value of b
a is not less than value of b
a is greater than value of b
a is either equal or less than value of b
b is either equal or greater than value of a
a is not equal to value of b

PL/SQL procedure successfully completed

Comparison operators: These operators are used for comparing one expression to another expression. The results are always TRUE, FALSE or NULL.
LIKE: This operator compares a character, string or CLOB value to a pattern and then returns TRUE in case if the value matches the pattern and it returns if it does not.

Example:
DECLARE 
PROCEDURE compare (value  varchar2,  pattern varchar2 ) is 
BEGIN 
   IF value LIKE pattern THEN 
      dbms_output.put_line ('True'); 
   ELSE 
      dbms_output.put_line ('False'); 
   END IF; 
END;  
BEGIN 
   compare('Sai S', 'Z%A_i'); 
   compare('Sai S', 'Z%A_i'); 
END; 
/
Output:
True 
False

PL/SQL procedure successfully completed.

BETWEEN: This operator tests whether a value lies between the specified range. C BETWEEN a AND b means that x>=a and also x<= b.

Example:
DECLARE 
   a number(2) := 2; 
BEGIN 
   IF (a between 1 and 5) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF;   
   IF (a BETWEEN 1 AND 5) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF;   
   IF (x BETWEEN 6 AND 10) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
END; 
/
Output:
True 
True 
False

PL/SQL procedure successfully completed

IN: it tests the set membership x IN (set) means that the variable x is equal to any member of the set.
IS NULL: this operator returns the Boolean value whether it is true if the operand is NULL or then FALSE if it is not NULL. Comparisons those involving NULL values that always yields NULL.

Example:
DECLARE 
   letter varchar2(1) := 'a'; 
BEGIN 
   IF (letter in ('a', 'b', 'c')) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
  IF (letter in ('m', 'n', 'o')) THEN 
       dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
    IF (letter is null) THEN 
    dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
END; 
/
Output:
True
False
False

PL/SQL procedure successfully completed.

Logical operators: these operators work on the Boolean operands and also produce Boolean results. We can assume Variable A holds true and variable B holds false. Let us see the operators that comes under logical operators
and: this operator checks whether the both operands are true then condition becomes true. (A and B) is false.
Or: this operator checks whether the both operands are true then condition becomes true. (A or B) is false.
not: This is used for reversing the logical state of the operand. In case if the condition is true then the logical NOT operator will then be the false. (A not B) is false.

Let us see the example given below:

DECLARE 
   a boolean := true; 
   b boolean := false; 
BEGIN 
   IF (a AND b) THEN 
      dbms_output.put_line(' Given Condition is true'); 
   END IF; 
   IF (a OR b) THEN 
      dbms_output.put_line(' Given Condition is true'); 
   END IF; 
   IF (NOT a) THEN 
      dbms_output.put_line(' a is not true'); 
   ELSE 
      dbms_output.put_line(' a is true'); 
   END IF; 
   IF (NOT b) THEN 
      dbms_output.put_line(' b is not true'); 
   ELSE 
      dbms_output.put_line(' b is true'); 
   END IF; 
END; 
/

when we execute the above code, we get following result:
Given Condition is true
a is true
b is not true
PL/SQL procedure successfully completed.

String operators

< PL/SQL Literals

PL/SQL Operator Precedence >

Email address

Password

Email address

Enter a valid email address

Explore 1000+ Free Courses

PLSQL

PLSQL

PL/SQL Operators

PL/SQL procedure successfully completed.

Explore 1000+ Free Courses