Oracle PL/SQL Tutorial/PL SQL Operators/Operators

Arithmetic Operators

Arithmetic operators are used for mathematical computations.

Operator Example Usage

10**5 The exponentiation operator. 10**5 = 100,000.

2*3 The multiplication operator. 2 * 3 = 6. / 6/2 The division operator. 6/2 = 3. + 2+2 The addition operator. 2+2 = 4. - 4-2 The subtraction operator. 4 -2 = 2. - -5 The negation operator. + +5 It complements the negation operator.

The basic arithmetic operators in action.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(4 * 2);  --multiplication
  3    DBMS_OUTPUT.PUT_LINE(24 / 3); --division
  4    DBMS_OUTPUT.PUT_LINE(4 + 4);  --addition
  5    DBMS_OUTPUT.PUT_LINE(16 - 8); --subtraction
  6  END;
  7  /
8
8
8
8
PL/SQL procedure successfully completed.
SQL>

Comparison Operators

Comparison operators are used to compare one value or expression to another.

All comparison operators return a boolean result.

Operator Example Usage = IF A = B THEN The equality operator. <> IF A <> B THEN The inequality operator. != IF A != B THEN Another inequality operator, synonymous with <>. ~= IF A ~= B THEN Another inequality operator, synonymous with <>. < IF A < B THEN The less than operator. > IF A > B THEN The greater than operator. <= IF A <= B THEN The less than or equal to operator. >= IF A >= B THEN The greater than or equal to operator. LIKE IF A LIKE B THEN The pattern-matching operator. BETWEEN IF A BETWEEN B AND C THEN Checks to see if a value lies within a specified range of values. IN IF A IN (B,C,D) THEN Checks to see if a value lies within a specified list of values. IS NULL IF A IS NULL THEN Checks to see if a value is null.

Exponentiation

SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(4 ** 2);
  3  END;
  4  /
16
PL/SQL procedure successfully completed.
SQL>
SQL>

Logical Operators

PL/SQL has three logical operators: AND, OR, and NOT.

The NOT operator is typically used to negate the result of a comparison expression.

The AND and OR operators are typically used to link together multiple comparisons.

The Syntax for the NOT Operator:

NOT boolean_expression

Logical Operators in PL/SQL

x y x AND y x OR y NOT x True True True True False True False False True False False True False True True False False False False True

Operators

PL/SQL operators are either unary or binary.

Binary operators act on two values.

An example of binary operators is the addition operator, which adds two numbers together.

Unary operators only operate on one value.

The negation operator is unary.

PL/SQL operators can be divided into the following categories:

1. Arithmetic operators
2. Comparison operators
3. Logical operators
4. String operators

23. 1. Operators 23. 1. 1. Operators 23. 1. 2. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thebasicarithmeticoperatorsinaction.htm">The basic arithmetic operators in action</a> 23. 1. 3. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperatorsinPLSQL.htm">Logical Operators in PL/SQL</a> 23. 1. 4. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/RunningAnonymousBlocksofCode.htm">Running Anonymous Blocks of Code</a> 23. 1. 5. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ArithmeticOperators.htm">Arithmetic Operators</a> 23. 1. 6. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Exponentiation.htm">Exponentiation</a> 23. 1. 7. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thenegationandidentityoperatorsinaction.htm">The negation and identity operators in action.</a> 23. 1. 8. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ComparisonOperators.htm">Comparison Operators</a> 23. 1. 9. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/TheRelationalOperators.htm">The Relational Operators: =, <>, !=, ~=, <, >, <=, >=</a> 23. 1. 10. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperators.htm">Logical Operators</a> 23. 1. 11. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/StringOperators.htm">String Operators</a> 23. 1. 12. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/UseofComparisonOperatorswithStrings.htm">Use of Comparison Operators with Strings</a>

Running Anonymous Blocks of Code

SQL>
SQL>  set serveroutput on
SQL>  declare
  2       v_length NUMBER :=5.5;
  3       v_width  NUMBER :=3.5;
  4       v_area   NUMBER;
  5   begin
  6       v_area:=v_length*v_width;
  7       DBMS_OUTPUT.put_line("Area:"||v_area);
  8   end;
  9  /
Area:19.25
PL/SQL procedure successfully completed.
SQL>

String Operators

PL/SQL has two operators specifically designed to operate only on character string data.

These are the LIKE operator and the concatenation (||) operator.

The Syntax for the Concatenation Operator:

string_1 || string_2

The basic arithmetic operators in action

SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(4 * 2);  --multiplication
  3    DBMS_OUTPUT.PUT_LINE(24 / 3); --division
  4    DBMS_OUTPUT.PUT_LINE(4 + 4);  --addition
  5    DBMS_OUTPUT.PUT_LINE(16 - 8); --subtraction
  6  END;
  7  /
8
8
8
8
PL/SQL procedure successfully completed.
SQL>
SQL>

The negation and identity operators in action.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    x   NUMBER;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE(-242.24);
  5    x := 5;
  6    DBMS_OUTPUT.PUT_LINE(-x);
  7     x := -5;
  8     DBMS_OUTPUT.PUT_LINE(-x);
  9     DBMS_OUTPUT.PUT_LINE(+10);
 10     DBMS_OUTPUT.PUT_LINE(+x);
 11  END;
 12  /
-242.24
-5
5
10
-5
PL/SQL procedure successfully completed.
SQL>

The Relational Operators: =, <>, !=, ~=, <, >, <=, >=

String comparisons are case-sensitive.

String comparisons are dependent on the character set being used.

String comparisons are affected by the underlying datatype.

Comparing two values as CHAR strings might yield different results than the same values compared as VARCHAR2 strings.

It"s important to remember that Oracle dates contain a time component

True Expressions False Expressions 5 = 5 5 = 3 "AAAA" = "AAAA" "AAAA " = "AAAA" 5 != 3 5 <> 5 "AAAA " ~= "AAAA" "AAAA" ~= "AAAA" 10 < 200 10.1 < 10.05 "Jeff" < "Jenny" "jeff" < "Jeff" TO_DATE("15-Nov-61" < "15-Nov-97") TO_DATE("1-Jan-97" < "1-Jan-96") 10.1 <= 10.1 10 <= 20 "A" <= "B" "B" <= "A" TO_DATE("1-Jan-97") <= TO_DATE("1-Jan-97) TO_DATE("15-Nov-61") <= TO_DATE("15-Nov-60)

Use of Comparison Operators with Strings

When using character strings in comparison expressions, the result depends on several things:

1. Character set
2. Datatype
3. Case (upper versus lower)

In the typical ASCII environment,

1. all lowercase letters are actually greater than all uppercase letters,
2. digits are less than all letters, and
3. the other characters fall in various places depending on their corresponding ASCII codes.

23. 1. Operators 23. 1. 1. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Operators.htm">Operators</a> 23. 1. 2. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thebasicarithmeticoperatorsinaction.htm">The basic arithmetic operators in action</a> 23. 1. 3. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperatorsinPLSQL.htm">Logical Operators in PL/SQL</a> 23. 1. 4. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/RunningAnonymousBlocksofCode.htm">Running Anonymous Blocks of Code</a> 23. 1. 5. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ArithmeticOperators.htm">Arithmetic Operators</a> 23. 1. 6. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Exponentiation.htm">Exponentiation</a> 23. 1. 7. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/Thenegationandidentityoperatorsinaction.htm">The negation and identity operators in action.</a> 23. 1. 8. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/ComparisonOperators.htm">Comparison Operators</a> 23. 1. 9. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/TheRelationalOperators.htm">The Relational Operators: =, <>, !=, ~=, <, >, <=, >=</a> 23. 1. 10. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/LogicalOperators.htm">Logical Operators</a> 23. 1. 11. <A href="/Tutorial/Oracle/0460__PL-SQL-Operators/StringOperators.htm">String Operators</a> 23. 1. 12. Use of Comparison Operators with Strings