PL/SQL User's Guide and Reference

Expressions

Description

An expression is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable.

The PL/SQL compiler determines the datatype of an expression from the types of the variables, constants, literals, and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results. For more information, see "Expressions and Comparisons" .

Syntax

expression ::=

[(]{  boolean_expression
    | character_expression
    | date_expression
    | numeric_expression}[)]

boolean_expression ::=

[NOT] {  boolean_constant_name
       | boolean_function_call
       | boolean_literal
       | boolean_variable_name
       | other_boolean_form} 
[{AND | OR} [NOT] {  boolean_constant_name
                   | boolean_function_call
                   | boolean_literal
                   | boolean_variable_name
                   | other_boolean_form}] ...

other_boolean_form ::=

expression 
   {  relational_operator expression
    | IS [NOT] NULL
    | [NOT] LIKE pattern
    | [NOT] BETWEEN expression AND expression
    | [NOT] IN (expression[, expression]...)
    | {  cursor_name
       | cursor_variable_name
       | :host_cursor_variable_name
       | SQL}{%FOUND | %ISOPEN | %NOTFOUND}
    | plsql_table_name.EXISTS(index)}

numeric_expression ::=

{  {  cursor_name
    | cursor_variable_name
    | :host_cursor_variable_name
    | SQL}%ROWCOUNT
 | :host_variable_name[:indicator_name]
 | numeric_constant_name
 | numeric_function_call
 | numeric_literal
 | numeric_variable_name
 | plsql_table_name{  .COUNT
                    | .FIRST
                    | .LAST
                    | .NEXT(index)
                    | .PRIOR(index)}}[**exponent]
[ {+ | - | * | /}
 {  {  cursor_name
     | cursor_variable_name
     | :host_cursor_variable_name
     | SQL}%ROWCOUNT
  | :host_variable_name[:indicator_name]
  | numeric_constant_name
  | numeric_function_call
  | numeric_literal
  | numeric_variable_name
  | plsql_table_name{  .COUNT
                     | .FIRST
                     | .LAST
                     | .NEXT(index)
                     | .PRIOR(index)}}[**exponent]]...

character_expression ::=

{  character_constant_name
 | character_function_call
 | character_literal
 | character_variable_name
 | :host_variable_name[:indicator_name]}
[ || {  character_constant_name
     | character_function_call
     | character_literal
     | character_variable_name
     | :host_variable_name[:indicator_name]}]...

date_expression ::=

{  date_constant_name
 | date_function_call
 | date_literal
 | date_variable_name
 | :host_variable_name[:indicator_name]}
[{+ | -} numeric_expression]...

Keyword and Parameter Description

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL.

character_expression

This is an expression that yields a character or character string.

date_expression

This is an expression that yields a date/time value.

numeric_expression

This is an expression that yields an integer or real value.

NOT, AND, OR

These are logical operators, which follow the tri-state logic of the truth tables

. 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. NOT NULL returns NULL because nulls are indeterminate. For more information, see "Logical Operators"

boolean_constant_name

This identifies a constant of type BOOLEAN, which must be initialized to the value TRUE or FALSE or the non-value NULL. Arithmetic operations on Boolean constants are illegal.

boolean_function_call

This is any function call that returns a Boolean value.

boolean_literal

This is the predefined value TRUE or FALSE or the non-value NULL, which stands for a missing, unknown, or inapplicable value. You cannot insert the value TRUE or FALSE into a database column.

boolean_variable_name

This identifies a variable of type BOOLEAN. Only the values TRUE and FALSE and the non-value 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 illegal.

relational_operator

This operator allows you to compare expressions. For the meaning of each operator, see "Comparison Operators"

IS [NOT] NULL

This comparison operator returns the Boolean value TRUE if its operand is null, or FALSE if its operand is not null.

[NOT] LIKE

This comparison operator 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.

pattern

This is 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.

[NOT] BETWEEN

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."

[NOT] IN

This comparison operator 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

yield FALSE if the set contains a null.

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

host_cursor_variable_ name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host cursor variables must be prefixed with a colon.

SQL

This identifies a cursor opened implicitly by Oracle to process a SQL data manipulation statement. The implicit SQL cursor always refers to the most recently executed SQL statement.

%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT

These are cursor attributes. When appended to the name of a cursor or cursor variable, these attributes return useful information about the execution of a multi-row query. You can also append them to the implicit SQL cursor. For more information, see "Using Cursor Attributes"

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

EXISTS, COUNT, FIRST, LAST, NEXT, PRIOR

These are PL/SQL table attributes. When appended to the name of a PL/SQL table, these attributes return useful information. For example, EXISTS(n) returns TRUE if the nth element of a PL/SQL table exists. Otherwise, EXISTS(n) returns FALSE. For more information, see "Using PL/SQL Table Attributes"

index

This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype.

host_variable_name

This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host variable must be implicitly convertible to the appropriate PL/SQL datatype. Also, host variables must be prefixed with a colon. For more information, see "Using Host Variables"

indicator_name

This identifies 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. For more information, see "Using Indicator Variables"

numeric_constant_name

This identifies 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.

numeric_function_call

This is a function call that returns a numeric value or a value implicitly convertible to a numeric value.

numeric_literal

This is a literal that represents a numeric value or a value implicitly convertible to a numeric value.

numeric_variable_name

This identifies a previously declared variable that stores a numeric value.

NULL

This keyword 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 always a null.

exponent

This is an expression that must yield a numeric value.

+, -, /, *, **

These symbols are the addition, subtraction, division, multiplication, and exponentiation operators, respectively.

character_constant_name

This identifies 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.

character_function_call

This is a function call that returns a character value or a value implicitly convertible to a character value.

character_literal

This is a literal that represents a character value or a value implicitly convertible to a character value.

character_variable_name

This identifies a previously declared variable that stores a character value.

||

This is 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!'  yields  'Good morning!'

The next example shows that nulls have no effect on the result of a concatenation:

'suit' || NULL || 'case'  yields  'suitcase'

A string zero characters in length ('') is called a null string and is treated like a null.

date_constant_name

This identifies 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.

date_function_call

This is a function call that returns a date value or a value implicitly convertible to a date value.

date_literal

This is a literal that represents a date value or a value implicitly convertible to a date value.

date_variable_name

This identifies a previously declared variable that stores a date value.

Usage Notes

In a Boolean expression, you can only compare values that have compatible datatypes. For more information, see "Datatype Conversion"

In conditional control statements, if a Boolean expression yields TRUE, its associated sequence of statements is executed. But, if the expression yields FALSE or NULL, its associated sequence of statements is not executed.

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.

The relational operators can be applied to operands of type BOOLEAN. By definition, TRUE is greater than FALSE. Comparisons involving nulls always yield a null.

The value of a Boolean expression can be assigned only to Boolean variables, not to host variables or database columns. Also, datatype 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'

Within an expression, operations occur in their predefined order of precedence. From first to last (top to bottom), the default order of operations is

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 value in the larger expression. When parenthesized expressions are nested, PL/SQL evaluates the innermost expression first and the outermost expression last.

Examples

Several examples of expressions follow:

(a + b) > c             -- Boolean expression
NOT finished            -- Boolean expression
TO_CHAR(acct_no)        -- character expression
'Fat ' || 'cats'        -- character expression
'15-NOV-95'             -- date expression
MONTHS_BETWEEN(d1, d2)  -- date expression
pi * r**2               -- numeric expression
emp_cv%ROWCOUNT         -- numeric expression