RelationalDBDesign
prev next prev next
  Course navigation
 
Lesson 7
Objective
Operators in PL/SQL
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.
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
The next lesson concludes this module.
Operators in PL/SQL - Exercise
Click the Exercise button to create a PL/SQL block that uses identifiers, literals, operators, and conversion functions.
Operators in PL/SQL - Exercise
  Course navigation