Expression

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.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

expression ::=

Description of the illustration expression.gif

See:

• boolean_expression ::=

• character_expression ::=

• date_expression ::=

• numeric_expression ::=

• searched_case_expression ::=

• simple_case_expression ::=

boolean_expression ::=

Description of the illustration boolean_expression.gif

See function_call ::=.

conditional_predicate ::=

Description of the illustration conditional_predicate.gif

other_boolean_form ::=

Description of the illustration other_boolean_form.gif

character_expression ::=

Description of the illustration character_expression.gif

See function_call ::=.

date_expression ::=

Description of the illustration date_expression.gif

See function_call ::=.

numeric_expression ::=

Description of the illustration numeric_expression.gif

numeric_subexpression ::=

Description of the illustration numeric_subexpression.gif

See:

• function_call ::=

• named_cursor ::=

function_call ::=

Description of the illustration function_call.gif

simple_case_expression ::=

Description of the illustration simple_case_expression.gif

searched_case_expression ::=

Description of the illustration searched_case_expression.gif

See boolean_expression ::=.

Semantics

BETWEEN

See "BETWEEN Operator".

boolean_constant_name

The name of a constant of type BOOLEAN.

boolean_expression

An expression whose value is TRUE, FALSE, or NULL. For more information, see "BOOLEAN Expressions".

Restriction on boolean_expression Because SQL has no data type equivalent to BOOLEAN, you cannot:

• Assign a BOOLEAN expression to a database table column

• Select or fetch the value of a database table column into a BOOLEAN variable

• Use a BOOLEAN expression in a SQL statement, built-in SQL function, or PL/SQL function invoked from a SQL statement

boolean_function_call

A call to a function that returns a BOOLEAN value.

boolean_literal

The predefined value TRUE, FALSE, or NULL.

boolean_variable_name

The name of a variable of type BOOLEAN.

%BULK_ROWCOUNT

An attribute of the implicit cursor SQL for use with the FORALL statement. See SQL%BULK_ROWCOUNT.

character_constant_name

The name of a constant that stores a character value.

character_expression

An expression whose value is a character or character string.

character_function_call

A call to a function that returns either a character value or a value that can be implicitly converted to a character value.

character_literal

A literal whose value is either a character value or a value that can be implicitly converted to a character value.

character_variable_name

The name of a variable that stores a character value.

collection_name

The name of a collection.

date_constant_name

The name of a constant that stores a date value.

date_expression

An expression that returns a datetime value.

date_function_call

A function call that returns either a date value or a value that can be implicitly converted to a date value.

date_literal

A literal whose value is either a date value or a value that can be implicitly converted to a date value.

date_variable_name

The name of a variable that stores a date value.

EXISTS, COUNT, FIRST, LAST, LIMIT, NEXT, PRIOR

Collection methods described in "Collection Method Call".

exponent

An expression whose value is numeric.

%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT

Cursor attributes described in "Implicit Cursor Attribute" and "Named Cursor Attribute".

host_variable_name

The name of a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. Do not put space between the colon (:) and host_variable_name.

IN

See "IN Operator".

index

A numeric expression whose value is of type PLS_INTEGER or a value that can be implicitly converted to PLS_INTEGER (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions").

indicator_name

The name of an indicator variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. An indicator variable indicates the value or condition of its associated host variable (for example, in the Oracle Precompiler environment, an indicator variable can a detect null or truncated value in an output host variable). Do not put space between host_variable_name and the colon (:) or between the colon and indicator_name. This is correct:

:host_variable_name:indicator_name

INSERTING, UPDATING, DELETING

See "Conditional Predicates for Detecting the DML Operation that Fired a Trigger".

IS [NOT] NULL

See "IS [NOT] NULL Operator".

LIKE pattern

See "LIKE Operator".

NOT, AND, OR

See "Logical Operators".

numeric_constant_name

The name of a constant that stores a numeric value.

numeric_expression

An expression that returns a numeric value.

numeric_function_call

A call to a function that returns either a numeric value or a value that can be implicitly converted to a numeric value.

numeric_literal

A literal whose value is either a numeric value or a value that can be implicitly converted to a numeric value.

numeric_variable_name

The name of variable that stores a numeric value.

parameter [, parameter ]...

List of actual parameters for the function being called. The data type of each actual parameter must be compatible with the data type of the corresponding formal parameter (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions"). If the function specifies an initial value for a parameter, you can omit that parameter from the parameter list.

If the function has no parameters, or specifies an initial value for every parameter, you can either omit the parameter list or specify an empty parameter list.

relational_operator

See "Relational Operators".

SQL

The implicit cursor associated with the most recently run SELECT or DML statement. For more information, see "Implicit Cursors".

+, -, /, *, **

Addition, subtraction, division, multiplication, and exponentiation operators.

||

The concatenation operator, which appends one string operand to another. For more information, see "Concatenation Operator".

selector

An expression of any PL/SQL type except BLOB, BFILE, or a user-defined type. The selector is evaluated once.

WHEN { selector_value | boolean_expression } THEN result

The selector_values or boolean_expressions are evaluated sequentially. If a selector_value is the value of selector, or if the value of a boolean_expression is TRUE, the result associated with that selector_value or boolean_expression is returned. Subsequent selector_values or boolean_expressions are not evaluated.

A selector_value can be of any PL/SQL type except BLOB, BFILE, an ADT, a PL/SQL record, an associative array, a varray, or a nested table.

ELSE result

In the simple CASE expression, result is returned if and only if no selector_value has the same value as selector.

In the searched CASE statement, result is returned if and only if no boolean_expression has the value TRUE.

If you omit the ELSE clause, the case expression returns NULL.

Examples

• Example 2-29, "Concatenation Operator"

• Example 2-30, "Concatenation Operator with NULL Operands"

• Example 2-31, "Controlling Evaluation Order with Parentheses"

• Example 2-32, "Expression with Nested Parentheses"

• Example 2-33, "Improving Readability with Parentheses"

• Example 2-34, "Operator Precedence"

• Example 2-43, "Relational Operators in Expressions"

• Example 2-44, "LIKE Operator in Expression"

• Example 2-46, "BETWEEN Operator in Expressions"

• Example 2-47, "IN Operator in Expressions"

• Example 2-50, "Simple CASE Expression"

• Example 2-52, "Searched CASE Expression"

Related Topics

In this chapter:

• "Constant"

• "Literal"

• "Variable"

In other chapters:

• "Expressions"

• "Operator Precedence"

• "Predefined PL/SQL Scalar Data Types and Subtypes"

• "Implicit Conversion"