PL/SQL - Operators

Hello, aspiring programmers! Today, we're going to embark on an exciting journey into the world of PL/SQL operators. Don't worry if you've never written a line of code before – we'll start from the very basics and work our way up. By the end of this tutorial, you'll be manipulating data like a pro!

Arithmetic Operators

Let's start with something familiar: math! PL/SQL arithmetic operators work just like the ones you learned in school. They allow us to perform basic mathematical operations on numbers.

Here's a table of arithmetic operators in PL/SQL:

Operator	Description	Example
+	Addition	5 + 3 = 8
-	Subtraction	10 - 4 = 6
*	Multiplication	3 * 4 = 12
/	Division	15 / 3 = 5
**	Exponentiation	2 ** 3 = 8

Let's see these operators in action with a simple PL/SQL code example:

DECLARE
  num1 NUMBER := 10;
  num2 NUMBER := 5;
  result NUMBER;
BEGIN
  result := num1 + num2;
  DBMS_OUTPUT.PUT_LINE('Addition: ' || result);

  result := num1 - num2;
  DBMS_OUTPUT.PUT_LINE('Subtraction: ' || result);

  result := num1 * num2;
  DBMS_OUTPUT.PUT_LINE('Multiplication: ' || result);

  result := num1 / num2;
  DBMS_OUTPUT.PUT_LINE('Division: ' || result);

  result := num1 ** 2;
  DBMS_OUTPUT.PUT_LINE('Exponentiation: ' || result);
END;

In this example, we declare two variables num1 and num2, and then perform various arithmetic operations on them. The results are then printed to the output.

When you run this code, you'll see:

Addition: 15
Subtraction: 5
Multiplication: 50
Division: 2
Exponentiation: 100

Relational Operators

Now, let's move on to relational operators. These are used to compare two values and determine the relationship between them. They're like the questions you ask when comparing things: "Is this bigger than that?" or "Are these two the same?"

Here's a table of relational operators in PL/SQL:

Operator	Description	Example
=	Equal to	5 = 5 is true
<>	Not equal to	5 <> 3 is true
<	Less than	3 < 5 is true
>	Greater than	5 > 3 is true
<=	Less than or equal to	3 <= 3 is true
>=	Greater than or equal to	5 >= 5 is true

Let's see these in action:

DECLARE
  a NUMBER := 10;
  b NUMBER := 20;
BEGIN
  IF a = b THEN
    DBMS_OUTPUT.PUT_LINE('a is equal to b');
  ELSIF a < b THEN
    DBMS_OUTPUT.PUT_LINE('a is less than b');
  ELSE
    DBMS_OUTPUT.PUT_LINE('a is greater than b');
  END IF;

  IF a <> b THEN
    DBMS_OUTPUT.PUT_LINE('a is not equal to b');
  END IF;
END;

This code compares a and b using relational operators. When you run it, you'll see:

a is less than b
a is not equal to b

Comparison Operators

Comparison operators in PL/SQL are quite similar to relational operators, but they include some additional functionality, particularly for working with NULL values and comparing across different data types.

Here's a table of comparison operators:

Operator	Description
IS NULL	Checks if a value is NULL
IS NOT NULL	Checks if a value is not NULL
LIKE	Pattern matching
BETWEEN	Checks if a value is within a range
IN	Checks if a value matches any value in a list

Let's see an example:

DECLARE
  name VARCHAR2(20) := 'John Doe';
  age NUMBER := 25;
BEGIN
  IF name IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('Name is: ' || name);
  END IF;

  IF name LIKE 'J%' THEN
    DBMS_OUTPUT.PUT_LINE('Name starts with J');
  END IF;

  IF age BETWEEN 20 AND 30 THEN
    DBMS_OUTPUT.PUT_LINE('Age is between 20 and 30');
  END IF;

  IF age IN (25, 30, 35) THEN
    DBMS_OUTPUT.PUT_LINE('Age is 25, 30, or 35');
  END IF;
END;

This code demonstrates various comparison operators. When you run it, you'll see:

Name is: John Doe
Name starts with J
Age is between 20 and 30
Age is 25, 30, or 35

Logical Operators

Logical operators allow us to combine multiple conditions. They're like the "and" and "or" in everyday language when we're describing complex situations.

Here's a table of logical operators in PL/SQL:

Operator	Description
AND	True if both conditions are true
OR	True if either condition is true
NOT	Reverses the result of a condition

Let's see these in action:

DECLARE
  age NUMBER := 25;
  has_license BOOLEAN := TRUE;
BEGIN
  IF age >= 18 AND has_license THEN
    DBMS_OUTPUT.PUT_LINE('You can drive a car');
  END IF;

  IF age < 18 OR NOT has_license THEN
    DBMS_OUTPUT.PUT_LINE('You cannot drive a car');
  ELSE
    DBMS_OUTPUT.PUT_LINE('You can drive a car');
  END IF;
END;

This code checks if a person can drive based on their age and whether they have a license. When you run it, you'll see:

You can drive a car
You can drive a car

PL/SQL Operator Precedence

Just like in mathematics, PL/SQL operators have a certain order in which they are evaluated. This is called operator precedence. Understanding this is crucial to writing correct and predictable code.

Here's the precedence of operators in PL/SQL, from highest to lowest:

1. Exponentiation (**)
2. Multiplication (*), Division (/)
3. Addition (+), Subtraction (-)
4. Comparison operators (=, <>, <, >, <=, >=)
5. IS NULL, LIKE, BETWEEN, IN
6. NOT
7. AND
8. OR

Let's see an example of how this works:

DECLARE
  result BOOLEAN;
BEGIN
  result := 5 > 3 AND 10 / 2 = 5 OR NOT (2 + 2 = 4);

  IF result THEN
    DBMS_OUTPUT.PUT_LINE('The result is TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The result is FALSE');
  END IF;
END;

In this example, the expression is evaluated as follows:

1. 10 / 2 = 5 (division, then comparison)
2. 5 > 3 (comparison)
3. 2 + 2 = 4 (addition, then comparison)
4. NOT (2 + 2 = 4) (logical NOT)
5. 5 > 3 AND 10 / 2 = 5 (logical AND)
6. Result of step 5 OR result of step 4 (logical OR)

When you run this code, you'll see:

The result is TRUE

And there you have it! We've covered the main types of operators in PL/SQL. Remember, practice makes perfect. Try writing your own code examples and experiment with different combinations of operators. Before you know it, you'll be writing complex PL/SQL statements with ease!

Credits: Image by storyset