RelationalDBDesign
RelationalDBDesign

PL/SQL Datatypes
«Prev
Next»

## Operators in PL/SQL

### Logical Operators

### Comparison Operators

### Relational Operators

### IS NULL operator

### LIKE operator

### BETWEEN operator

### IN operator

### Concatenation operators

### Boolean Expressions

### Order of Operations

### Operators in PL/SQL - Exercise

Lesson 7 | Operators in PL/SQL |

Objective | Use operators within PL/SQL |

The *operators* n PL/SQL are similar to those used in SQL. Expressions are constructed by using operands and operators.
An operand is a variable, constant, or literal. PL/SQL evaluates an expression by combining the values of the operands in ways specified by the operators.

The logical operators *truth table* .

`AND`

, `OR`

, and `NOT`

operate according to the value returned by the `AND`

and `OR`

are binary operators; `NOT`

is a unary operator.
These operators let you compare one expression to another. They are used in conditional control statements and SQL data manipulation statements. The result is always

`TRUE`

, `FALSE`

, or `NULL`

.
Relational operators are

`=`

, `!=`

, `<`

, `>`

, `<=`

, and `>=`

. They allow for arbitrary comparisons of complex expressions.
The

`IS NULL`

operator returns a boolean value `TRUE`

if its operand is null or `FALSE`

if it is not null.
Comparisons involving nulls always yield `NULL`

. For example,
IF value IS NULL THEN value := 0; END IF;

You can use the

`LIKE`

operator to compare a character value to a pattern. Case is significant.
For example,
SELECT * FROM CUSTOMER WHERE FIRSTNAME LIKE ‘Am%’;

This operator tests whether a value lies within a specified range. For example,

SELECT * FROM PRODUCT WHERE SALE_PRICE BETWEEN 40 AND 45;

The

`IN`

operator tests the existence of a value within a set of value. For example,
DELETE FROM CUSTOMER WHERE STATE IN ('FL', ‘NY’);

These operators let you manipulate strings by appending one string to another. For example,

SELECT * FROM PRODUCT WHERE PRODUCT_NAME = ‘F’ || ‘ish’;

PL/SQL supports the comparison of variables and constants in SQL and PL/SQL statements. These comparisons, called boolean expressions, generally consist of simple expressions
separated by relational operators. Boolean expressions are often connected by logical operators

`NOT`

, `AND,`

and `OR`

.
In PL/SQL, a boolean expression always evaluates to `TRUE`

, `FALSE`

, or `NULL`

.
The operations within an expression are executed in a particular order depending on their precedence. Parentheses control the order of evaluation. The following table lists the order of operations.

Operator | Operation |

`**` , `NOT` | Exponentiation, logical negation |

`+` , `-` | Identity, negation |

`*` , `/` | Multiplication, division |

`+` , `-` , `||` | Addition, subtraction, concatenation |

`=` , `!=` , `<` , `>` , `<=` , `>=` , `IS NULL` , `LIKE` , `BETWEEN` , `IN` | Comparison |

`AND` | Conjunction |

`OR` | Inclusion |

Click the Exercise link below to create a PL/SQL block that uses identifiers, literals, operators, and conversion functions.

Operators in PL/SQL - Exercise

Operators in PL/SQL - Exercise