An expression is an arbitrarily complex combination of operands (variables, constants, literals, operators, function calls, and placeholders) and operators. The simplest expression is a single variable.
The PL/SQL compiler determines the data type of an expression from the types of the operands and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results.
Keyword and Parameter Descriptions
This comparison operator tests whether a value lies in a specified range. It means: greater than or equal to low value and less than or equal to high value.
A constant of type BOOLEAN
, which must be initialized to the value TRUE
, FALSE
, or NULL
. Arithmetic operations on Boolean constants are not allowed.
An expression whose value is Boolean (TRUE
, FALSE
, or NULL
).
A call to a function that returns a Boolean value.
The predefined values TRUE
, FALSE
, or NULL
(which stands for a missing, unknown, or inapplicable value). You cannot insert the value TRUE
or FALSE
into a database column.
A variable of type BOOLEAN
. Only the values TRUE
, FALSE
, and NULL
can be assigned to a BOOLEAN
variable. You cannot select or fetch column values into a BOOLEAN
variable. Also, arithmetic operations on BOOLEAN
variables are not allowed.
Designed for use with the FORALL
statement, this is a composite attribute of the implicit cursor SQL
. For more information, see SQL (Implicit) Cursor Attribute.
A previously declared constant that stores a character value. It must be initialized to a character value or a value implicitly convertible to a character value.
An expression that returns a character or character string.
A function call that returns a character value or a value implicitly convertible to a character value.
A literal that represents a character value or a value implicitly convertible to a character value.
A previously declared variable that stores a character value.
A collection (nested table, index-by table, or varray) previously declared within the current scope.
An explicit cursor previously declared within the current scope.
A PL/SQL cursor variable previously declared within the current scope.
A previously declared constant that stores a date value. It must be initialized to a date value or a value implicitly convertible to a date value.
An expression that returns a date/time value.
A function call that returns a date value or a value implicitly convertible to a date value.
A literal representing a date value or a value implicitly convertible to a date value.
A previously declared variable that stores a date value.
EXISTS, COUNT, FIRST, LAST, LIMIT, NEXT, PRIOR
Collection methods. When appended to the name of a collection, these methods return useful information. For example, EXISTS(
n
)
returns TRUE
if the n
th element of a collection exists. Otherwise, EXISTS(
n
)
returns FALSE
. For more information, see Collection Method Call.
An expression that must return a numeric value.
%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT
Cursor attributes. When appended to the name of a cursor or cursor variable, these attributes return useful information about the execution of a multiple-row query. You can also append them to the implicit cursor SQL
.
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. Host cursor variables must be prefixed with a colon.
A variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The data type of the host variable must be implicitly convertible to the appropriate PL/SQL data type. Also, host variables must be prefixed with a colon.
Comparison operator that tests set membership. It means: equal to any member of. The set can contain nulls, but they are ignored. Also, expressions of the form
value NOT IN set
return FALSE
if the set contains a null.
A numeric expression that must return a value of type BINARY_INTEGER,
PLS_INTEGER
, or a value implicitly convertible to that data type.
An indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable indicates the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables can detect nulls or truncated values in output host variables.
Comparison operator that returns the Boolean value TRUE
if its operand is null, or FALSE
if its operand is not null.
Comparison operator that compares a character value to a pattern. Case is significant. LIKE
returns the Boolean value TRUE
if the character patterns match, or FALSE
if they do not match.
Logical operators, which follow the tri-state logic of Table 2-3. AND
returns the value TRUE
only if both its operands are true. OR
returns the value TRUE
if either of its operands is true. NOT
returns the opposite value (logical negation) of its operand. For more information, see Logical Operators.
Keyword that represents a null. It stands for a missing, unknown, or inapplicable value. When NULL
is used in a numeric or date expression, the result is a null.
A previously declared constant that stores a numeric value. It must be initialized to a numeric value or a value implicitly convertible to a numeric value.
An expression that returns an integer or real value.
A function call that returns a numeric value or a value implicitly convertible to a numeric value.
A literal that represents a number or a value implicitly convertible to a number.
A previously declared variable that stores a numeric value.
A character string compared by the LIKE
operator to a specified string value. It can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. The pattern can be followed by ESCAPE
'character_literal'
, which turns off wildcard expansion wherever the escape character appears in the string followed by a percent sign or underscore.
Operator that compares expressions. For the meaning of each operator, see Comparison Operators.
A cursor opened implicitly by the database to process a SQL data manipulation statement. The implicit cursor SQL
always refers to the most recently executed SQL statement.
Symbols for the addition, subtraction, division, multiplication, and exponentiation operators.
The concatenation operator. As the following example shows, the result of concatenating string1 with string2 is a character string that contains string1 followed by string2:
'Good' || ' morning!' = 'Good morning!'
The next example shows that nulls have no effect on the result of a concatenation:
'suit' || NULL || 'case' = 'suitcase'
A null string (''
), which is zero characters in length, is treated like a null.
An expression whose value is used to select one of several alternative result values. The value of case_operand
can be of any PL/SQL type except BLOB
, BFILE
, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.
WHEN { case_operand_value | boolean_expression } THEN result_value
The case_operand_value
s or boolean_expression
s are evaluated sequentially. If a case_operand_value
is the value of case_operand
, or if the value of a boolean_expression
is TRUE
, the result_value
associated with that case_operand_value
or boolean_expression
is returned. Subsequent case_operand_value
s or boolean_expression
s are not evaluated.
A case_operand_value
can be of any PL/SQL type other than BLOB
, BFILE
, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.
In the simple CASE
expression, the result_value
is returned if and only if no case_operand_value
has the same value as case_operand
.
In the searched CASE
statement, the result_value
is returned if and only if no boolean_expression
has the value TRUE
.
If you omit the ELSE
clause, the case expression returns NULL
.
In a Boolean expression, you can only compare values that have compatible data types. For more information, see PL/SQL Data Type Conversion.
In conditional control statements, if a Boolean expression returns TRUE
, its associated sequence of statements is executed. But, if the expression returns FALSE
or NULL
, its associated sequence of statements is not executed.
The relational operators can be applied to operands of type BOOLEAN
. By definition, TRUE
is greater than FALSE
. Comparisons involving nulls always return a null. The value of a Boolean expression can be assigned only to Boolean variables, not to host variables or database columns. Also, data type conversion to or from type BOOLEAN
is not supported.
You can use the addition and subtraction operators to increment or decrement a date value, as the following examples show:
hire_date := '10-MAY-95'; hire_date := hire_date + 1; -- makes hire_date '11-MAY-95' hire_date := hire_date - 5; -- makes hire_date '06-MAY-95'
When PL/SQL evaluates a boolean expression, NOT
has the highest precedence, AND
has the next-highest precedence, and OR
has the lowest precedence. However, you can use parentheses to override the default operator precedence.
Within an expression, operations occur in the following order (first to last):
Parentheses
Exponents
Unary operators
Multiplication and division
Addition, subtraction, and concatenation
PL/SQL evaluates operators of equal precedence in no particular order. When parentheses enclose an expression that is part of a larger expression, PL/SQL evaluates the parenthesized expression first, then uses the result in the larger expression. When parenthesized expressions are nested, PL/SQL evaluates the innermost expression first and the outermost expression last.