Page 67
by Jonathan Gennick
Today's lesson is about expressions. Expressions enable you to manipulate data inside your PL/SQL routines. They combine values and operators and are used to perform calculations and compare data. Without expressions, you would get precious little done.
NEW TERM
PL/SQL expressions are composed of operands and
operators. Operands represent values. An operand is often a variable, but can also be a
literal, a constant, or a function call. Operators
specify actions, such as addition,
Page 68
multiplication, and so on, which can be performed using one or two operands. Here is a typical expression:
total_wages := hourly_rate * hours_worked
The three variables, total_wages, hourly_rate, and hours_worked, are all examples of operands. The * is the multiplication operator, and the := is the assignment operator.
Yesterday you learned about datatypes and variable declarations. Today you will read about the operators you can use to manipulate those variables. You'll see how to build simple expressions, such as the one just shown that multiplies two numbers together. You'll see how to build much more complex expressions that consist of function calls, operations using variables, and relational comparisons. You'll also learn how to control the order in which an expression is evaluated.
Expressions often contain operands of multiple datatypes. It's not unusual, for example, to want to subtract a number of years from a date. In cases like this, you must first convert the values being operated on to a compatible datatype. Only then can the necessary calculation be performed. In many cases, when the conversion is obvious, PL/SQL will handle this for you. You'll learn how and when PL/SQL does this. You'll also learn how you can explicitly specify the conversion, when you should do it, and why.
Operators are the glue that hold expressions together. A very simple expression could consist of just one variable or value, but to accomplish anything useful, you need more than that. Operators enable you to take one or two values and perform an operation that uses those values and returns a result. The operation could be as simple as adding two numbers together and returning the total, or it could be a complex logical expression used in an IF statement. PL/SQL operators can be divided into the following categories:
NEW TERM
PL/SQL operators are either unary or binary. Most are binary
operators, which means that they act on two values. An example is the addition operator, which
adds two numbers together. A few, such as the negation operator, are unary. Unary operators
only operate on one value.
Page 69
Each of these types of operators are described in the following sections. There is nothing unusual about the operators in PL/SQL, and if you have any other programming experience, you will see that the operators and the order of evaluation are not much different than for any other language.
Arithmetic operators are used for mathematical computations, such as addition and sub-traction. Table 4.1 shows the arithmetic functions supported by PL/SQL.
Table 4.1. Arithmetic operators.
| Operator | Example | Usage |
| ** | 10**5 | The exponentiation operator. It raises one number to the power of another. In the example shown, it raises 10 to the fifth power, resulting in a value of 100,000. |
| * | 2*3 | The multiplication operator. The example shown, 2 times 3, results in a value of 6. |
| / | 6/2 | The division operator. In the example, 6 divided by 2, the result would be 3. |
| + | 2+2 | The addition operator, which is used to add two values together. The example evaluates to 4. |
| - | 4-2 | The subtraction operator, which is used to subtract one number from another. The example subtracts 2 from 4 resulting in a value of 2. |
| - | -5 | The negation operator. Used by itself, the minus sign negates the operand. The expression shown evaluates to a negative 5. |
| + | +5 | Used by itself, the plus sign is the identity operator. It complements the negation operator, and the result of the expression is simply the value of the operand. In the example shown, the result is 5. |
Addition, Subtraction, Multiplication, and Division
The basic four arithmetic operators, addition, subtraction, multiplication, and
division, probably need no further explanation. Listing 4.1 shows some sample expressions, and
the output shows their resulting values.
Page 70
| NOTE |
Before executing the code shown in Listing 4.1 and most other listings in this chapter, make sure that you have first executed the following command at least once during the session:SET SERVEROUTPUT ONIf you omit this command, SQL*Plus won't display the output generated by the calls to DBMS_OUTPUT.PUT_LINE. You only need execute this command once each time you start SQL*Plus. For listings in which this is important, a comment is included at the beginning to remind you. |
INPUT
Listing 4.1. The basic four arithmetic operators in action.
1: --The basic arithmetic operators in action. 2: --Remember to execute: SET SERVEROUTPUT ON 3: BEGIN 4: DBMS_OUTPUT.PUT_LINE(4 * 2); --multiplication 5: DBMS_OUTPUT.PUT_LINE(24 / 3); --division 6: DBMS_OUTPUT.PUT_LINE(4 + 4); --addition 7: DBMS_OUTPUT.PUT_LINE(16 - 8); --subtraction 8: END; 9: /
8 8 8 8 PL/SQL procedure successfully completed.
ANALYSIS
As you can see, the DBMS_OUTPUT.PUT_LINE procedure was used to display the
values of four simple expressions, all of which evaluated to eight.
Exponentiation
Exponentiation, or raising a number to a power, is simply the act of multiplying a
number by itself a specified number of times. Table 4.2 shows a few examples of
exponentiation together with equivalent expressions using multiplication and the resulting values.
Table 4.2. Examples of exponentiation.
| Example | Equivalent to | Result |
| 10**5 | 10*10*10*10*10 | 100,000 |
| 2**3 | 2*2*2 | 8 |
| 6**2 | 6*6 | 36 |