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
expressionWHENcomparison_expressionTHEN RETURNreturn_expression[WHENcomparison_expressionTHEN RETURNreturn_expression. . .] ENDor
CASE WHEN
conditionTHEN RETURNreturn_expression[WHENconditionTHEN RETURNreturn_expression. . .] ENDCASE expressions are new with Oracle9i. Both of the forms shown here can take an ELSE clause in the form:
ELSE
else_expressionYou 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, ...