Operators and Operands

Some of the operations we will perform on the command line should display their result. To make this possible, first type SET SERVEROUTPUT ON and press Enter:

To start one or a group of statements in PL/SQL, type BEGIN and press Enter. To end the statement or a group of statements, type END;. To end the whole thing, on the next line, type /. An example of a whole section would be:

BEGIN
        
-- Do something here

END;
/

To manage many of its internal objects, Oracle uses some settings or internal arrangements named packages (you may be familiar with namespaces in C++ or C# and packages in Java). Like everything in a computer application, every package has a name. One of the packages in Oracle is named DBMS_OUTPUT.

One of the characteristics of a package, in fact its most important aspect, is its content. A package contains code and objects, etc. The objects in a package have behaviors. One of the behaviors of DBMS_OUTPUT is to receive some values. Another behavior is to display them.

To display information, DBMS_OUTPUT is equipped with an action (called a method) named PUT_LINE. To use it, type DBMS_OUTPUT.PUT_LINE(). In the parentheses, type what you want to display. An example of a whole section would be:

BEGIN
DBMS_OUTPUT.PUT_LINE(Put something here);
END;
/

To make code easy to read, you should indent code that does not belong to the same category:

BEGIN
    DBMS_OUTPUT.PUT_LINE(Put something here);
END;
/

Of course, there are rules you must follow about what to put in the parentheses of DBMS_OUTPUT.PUT_LINE(). We will see which ones.

A string is one or a group of symbols (or characters) used as an entity. To create a string, include the content between single quotes. An example would be 'Australia'.

If you have a string you would like to display, you can write it in the parentheses of DBMS_OUTPUT.PUT_LINE(). Here is an example:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Australia');
END;
/

Of course, a string can contain space. Here is an example:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Nouvelle Calédonie');
END;
/

You may have more than one string or statement you want to use at the same time. One option is to write one followed by the other and separate them with ||. Here is an example:

BEGIN
    DBMS_OUTPUT.PUT_LINE('South ' || 'Carolina');
END;
/

In the same way, you can add as many strings as you judge necessairy. Here is an example:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Emirats' || ' ' || 'Arabes' || ' '  || 'Unis');
END;
/

A unary operator is an operator that performs its operation on only one operand.

An operator is referred to as binary if it operates on two operands.

Consider the following illustration of a ruler:

The negative numbers are on the left side of 0. The positive numbers are on the right side.

The positive unary operator, when used, must be positioned on the left side of its operand. Here is an example:

+1250

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate. 

To add two numeric values, you can use the + operator. Here is an example:

125 + 4088

In the same way, you can add as many values as necessary.

To subtract two numeric values, you can use the - operator. Here is an example:

1240 - 608

In the same way, you can subtract various numbers from one another, as in a - b - c - d.

To multiply one number to another, you use the * operator. Here is an example:

128 * 42

This would produce 5376

To divide one number from another, use the forward slash operator "/". Here is an example:

128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0). Make sure that you know the relationship(s) between the numbers involved in the operation.

In the above division, 128/42, the result is 3. When you multiply 42 by 3, as in 42*3, you get 126. In some cases, you may be interested in knowing the amount that was left out after the operation. The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the MOD operator. Here is an example:

128 MOD 3

This would produce 2.

Like most computer languages, PL/SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, subtraction is not associative and can lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:

154 - 12) + 8
154 - (12 + 8)

This would produce:

150
134

As you can see, using the parentheses controls how the whole operation would proceed.