Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E17126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Expression

An expression is an arbitrarily complex combination of operands (variables, constants, literals, operators, function invocations, 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

expression ::=

expression
Description of the illustration expression.gif

See:

boolean_expression ::=

boolean_expression
Description of the illustration boolean_expression.gif

boolean_literal ::=

boolean_literal
Description of the illustration boolean_literal.gif

See "function_call ::=".

conditional_predicate ::=

boolean_expression
Description of the illustration conditional_predicate.gif

other_boolean_form ::=

other_boolean_form
Description of the illustration other_boolean_form.gif

See:

character_expression ::=

character_expression
Description of the illustration character_expression.gif

See:

date_expression ::=

date_expression
Description of the illustration date_expression.gif

See:

numeric_expression ::=

numeric_expression
Description of the illustration numeric_expression.gif

numeric_subexpression ::=

numeric_subexpression
Description of the illustration numeric_subexpression.gif

See:

function_call ::=

numeric_expression
Description of the illustration function_call.gif

simple_case_expression ::=

simple_case_expression
Description of the illustration simple_case_expression.gif

searched_case_expression ::=

searched_case_expression
Description of the illustration searched_case_expression.gif

See "boolean_expression ::=".

collection_constructor ::=

simple_case_expression
Description of the illustration collection_constructor.gif

Semantics

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:

NOT, AND, OR

See "Logical Operators".

boolean_constant_name

The name of a constant of type BOOLEAN.

boolean_function_call

An invocation of a previously defined function that returns a BOOLEAN value. For more semantic information, see "function_call".

boolean_variable_name

The name of a variable of type BOOLEAN.

conditional_predicate

See "Conditional Predicates for Detecting Triggering DML Statement".

other_boolean_form

collection_name

The name of a collection.

index

An index for the collection collection_name—a numeric expression whose value is of type PLS_INTEGER or a value that can be implicitly converted to PLS_INTEGER (see "PLS_INTEGER and BINARY_INTEGER Data Types").

EXISTS

A collection method (function) that returns TRUE if the indexth element of the collection exists and FALSE otherwise. For more information, see "EXISTS Collection Method".

Restriction on EXISTS You cannot use EXISTS if collection_name identifies an associative array.

SQL

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

%FOUND, %ISOPEN, %NOTFOUND

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

relational_operator

See "Relational Operators".

IS [NOT] NULL

See "IS [NOT] NULL Operator".

LIKE pattern

See "LIKE Operator".

BETWEEN expression AND expression

See "BETWEEN Operator".

IN expression [, expression ]...

See "IN Operator".

character_expression

An expression that returns a character value.

character_constant_name

The name of a constant that stores a character value.

character_function_call

An invocation of a previously defined function that returns either a character value or a value that can be implicitly converted to a character value. For more semantic information, see "function_call".

character_literal

A literal of a character data type.

character_variable_name

The name of a variable that stores a character value.

||

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

date_expression

An expression that returns a date value.

date_constant_name

The name of a constant that stores a date value.

date_function_call

An invocation of a previously defined function that returns either a date value or a value that can be implicitly converted to a date value. For more semantic information, see "function_call".

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.

+, -

Addition and subtraction operators.

numeric_expression

An expression that returns a numeric value.

+, -, /, *, **

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

numeric_subexpression

SQL

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

%ROWCOUNT

A cursor attribute explained in "Implicit Cursor Attribute" and "Named Cursor Attribute".

%BULK_ROWCOUNT]

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

numeric_constant_name

The name of a constant that stores a numeric value.

numeric_function_call

An invocation of a previously defined function that returns either a numeric value or a value that can be implicitly converted to a numeric value. For more semantic information, see "function_call".

numeric_literal

A literal of a numeric data type.

numeric_variable_name

The name of variable that stores a numeric value.

collection_name

The name of a collection.

COUNT, FIRST, LAST, LIMIT, NEXT, PRIOR

Collection methods explained in "Collection Method Invocation".

exponent

An expression whose value is numeric.

function_call

function_name

The name of a previously defined function.

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. The mode of the formal parameter determines what the actual parameter can be:

Formal Parameter Mode Actual Parameter
IN Constant, initialized variable, literal, or expression
OUT Variable whose data type is not defined as NOT NULL
IN OUT Variable (typically, it is a string buffer or numeric accumulator)

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

simple_case_expression

selector

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

WHEN selector_value THEN result

The selector_values are evaluated sequentially. If a selector_value is the value of selector, then the result associated with that selector_value is returned. Subsequent selector_values 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

The result is returned if and only if no selector_value has the same value as selector.

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

searched_case_expression

WHEN boolean_expression THEN result

The boolean_expressions are evaluated sequentially. If a boolean_expression has the value TRUE, then the result associated with that boolean_expression is returned. Subsequent boolean_expressions are not evaluated.

ELSE result

The result is returned if and only if no boolean_expression has the value TRUE.

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

collection_constructor

Constructs a collection of the specified type with elements that have the specified values. For more information, see "Collection Constructors".

collection_type

The name of a previously declared nested table type or VARRAY type (not an associative array type).

value

A valid value for an element of the collection.

If the collection is a varray, it has a maximum size, which the number of values cannot exceed. If the collection is a nested table, it has no maximum size.

If you specify no values, the constructed collection is empty but not null (for the difference between empty and null, see "Collection Types").

Examples

Related Topics

In this chapter:

In other chapters: