## 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

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.