PL/SQL Datatypes  «Prev  Next»

Lesson 7 Operators in PL/SQL
Objective Use operators within PL/SQL

Operators in 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.

Logical Operators

The logical operators AND, OR, and NOT operate according to the value returned by the truth table. AND and OR are binary operators; NOT is a unary operator.

Comparison Operators

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

Relational operators are =, !=, <, >, <=, and >=. They allow for arbitrary comparisons of complex expressions.

IS NULL operator

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;


LIKE operator

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

BETWEEN operator

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

SELECT * FROM PRODUCT 
WHERE SALE_PRICE BETWEEN 40 AND 45;

IN operator

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

SEMrush Software