RelationalDBDesign

Programming PL/SQL
«Prev
Next»

## Operators in PL/SQL

### Logical Operators

### Comparison Operators

### Relational Operators

### IS NULL operator

### LIKE operator

### BETWEEN operator

### IN operator

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’);

```
```### Concatenation operators

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

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

### Boolean Expressions

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`

.
### Order of Operations

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

### Operators in PL/SQL - Exercise

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

Operators in PL/SQL - Exercise

```
```

```
```