- 1.Arithmetic operators
- 2.Comparison operators
- 3.Logical operators
- 4.String operators
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
4 NOT BETWEEN 3 AND 4 Returns FALSE.
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.
(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.