PL/SQL operators

PL/SQL operators

PL/SQL operators are the glue that holds expressions together. PL/SQL operators can be divided into the following categories.
  • 1.Arithmetic operators
  • 2.Comparison operators
  • 3.Logical operators
  • 4.String operators
 PL/SQL operators are either unary (i.e. they act on one value/variable) or binary (they act on two value/variables)

1.Arithmetic operators in PL/SQL

Arithmetic operators are used for mathematical computations. They are:
a) +      Addition
b)Subtraction or Negation( Ex: -5)
c)     *      Multiplication
d) /      Division
e) **       Exponentiation operator (example 10**5=10^5)

2.Comparison operators in PL/SQL

Comparison operators return a BOOLEAN result, either TRUE or FALSE. They are
• (=)  Equality operator, Example:   5=3.
• (!=)  Inequality operator, Example:   a! = b.
• (< >)  Inequality operator, Example:   5 < > 3.
• (-=)  Inequality operator, Example:   ‘john’ -= ‘johny’.
• (<)  Less than operator, Example:   a < b.
• (>)  Greater than operator, Example:   a > b.
• (<=)  Less than or equal to operator, Example:   a < = b.
• (>=)  Greater than or equal to operator, Example:   a >= b.

In addition to this PL/SQL also provides some other comparison operators like LIKE, IN, BETWEEN, IS NULL etc.
LIKE: Pattern-matching operator.
It is used to compare a character string against a pattern. Two wild card characters are defined for use with LIKE, the % (percentage sign) and ( _ ) underscore. The % sign matches any number of characters in a string and ( _ ) matches exactly one.
Example1: new%matches with newyork, newjersey etc (i.e. any string beginning with  the word new).
Example2: ‘— day’ matches with Sunday, Monday and Friday and It will not match with other days like ‘Tuesday’, ‘Wednesday’, ‘Thursday’ and ‘Saturday’.
IN: Checks to see if a value lies within a specified list of values. The syntax is
Syntax: The_value[NOT] IN (value1, value2,value3….)
Ex: 3 IN (4, 8,7,5,3, and 2) Returns TRUE.
Sun NOT IN (‘sat’, ‘mon’, ‘tue’, ‘wed’, ‘sun’) Returns TRUE.
BETWEEN: Checks to see if a value lies within a specified range of value.
Syntax: the_value[ NOT]    BETWEEN low_end AND high_end.
Ex:   5 BETWEEN -5  AND  10 Returns  TRUE
IS NULL:  Checks to see if a value is NULL.
Syntax: the_value IS[not]NULL
Ex: If balance IS NULL then
If acc_id IS NOT NULL then

3.Logical Operators in PL/SQL

PL/SQL implements 3 logical operations AND, OR and NOT. The NOT operator is unary operator and is typically used to negate the result of s comparison expression, where as the AND and OR operators are typically used to link together multiple comparisons.

  • A SND B is true only if A returns TRUE and B returns TRUE else it is FALSE.
  • A OR B is TRUE if either of A or B is TRUE. And it is PALSE if both A and B are PALSE.
  • NOT A returns TRUE if A is TRUE.
Example- (5=5) AND (4<20) AND (2>=2) Returns TRUE
(5=5) Or (5!=4) Return TRUE
‘mon’ IN (‘sun’,’sat’) OR (2=2) Return TRUE.

4. String operators in PL/SQL

PL/SQL has two operators specially designed to operate only on character string type data. These are LIKE and (||) Concatenation operator. LIKE is a comparison operator and is used to compare strings. Concatenation operator has the following syntax.
Syntax:  String_1 || string_2:
String_1and string_2 both are string and can be string constants, string variables or string expressions. The concatenation operator returns a resultant string consisting of all the characters in String_1 followed by all the characters in string_2.
Example: ‘Chandra’ || ‘shekhar’ Returns  ‘Chandrashekhar’
A=’Engineering’  B=’College’  C=VARCHAR2(50)
C=A|| ‘’ ||B           Returns a value to variable C as ‘Engineering College’.
Note-1: PL/SQL string comparisons are always case sensitive, i.e. ‘aaa’ not equal to ‘AAA’.
Note-2: ORACLE has some built in functions that are designed to convert from one data type to another data type.
To_date: Converts a character string into date.
T_number: Converts a character string to a number.

To_char: Converts either a number or date to character string.

Leave a Reply

Your email address will not be published.