Oracle in a Nutshell

Expressions

An expression is the basic unit of information in an SQL statement or function. Oracle supports several different types of expressions:

Simple expression

May be any of the following:

• Text within quotes

• A number

• A ROWID for a row specified as part of a table, a schema and table, or a query

• A column value for a row specified as part of a table, a schema and table, or a query

• ROWNUM

• A sequence followed by .NEXTVAL or .CURRVAL

• NULL

Compound expression

Consists of multiple expressions, joined by a standard operator, such as +, -, *, /, or || where applicable. Compound expressions can also be the result of a function.

CASE expression

Returns a single value, based on the result of a logical comparison. A CASE expression takes one of the following two forms:

CASE expression WHEN comparison_expression THEN RETURN return_expression
        [WHEN comparison_expression THEN RETURN return_expression . . .]
END

or

CASE WHEN condition THEN RETURN return_expression
        [WHEN condition THEN RETURN return_expression . . .]
END

CASE expressions are new with Oracle9i. Both of the forms shown here can take an ELSE clause in the form:

ELSE else_expression

You specify this clause before the END keyword, where else_expression is returned if none of the comparisons or conditions return a value.

CURSOR expression

Returns a nested cursor, the equivalent of a PL/SQL REF CURSOR (see Chapter 9). The form of this expression is:

CURSOR(subquery)

CURSOR expressions are new with Oracle9i.

DATETIME expression

Returns a datetime value, ...