Skip Headers

PL/SQL User"s Guide and also Reference10g relax 1 (10.1) component Number B10807-01
*
Home
*
Book List
*
Contents
*
Index
*
MasterIndex
*
Feedback
*
Previous
*
Next
View PDF

Expressions

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

You are watching: You can use parentheses to override the default order of operations.

The PL/SQL compiler determines the datatype of one expression indigenous the types of the variables, constants, literals, and operators that consist of the expression. Every time the expression is evaluated, a solitary value the that form results. For much more information, watch "PL/SQL Expressions and Comparisons".

Syntax

*
Description of the illustration expression.gif

*
Description the the illustration other_boolean_form.gif

*
Description that the illustration numeric_subexpression.gif

*
Description that the illustration date_expression.gif

Keyword and Parameter Description

BETWEEN

This compare operator tests whether a worth lies in a stated range. It way "greater than or equal to low value and less 보다 or equal to high value."

boolean_constant_name

A continuous of form BOOLEAN, which have to be initialized to the worth TRUE, FALSE, or NULL. Arithmetic to work on Boolean constants room not allowed.

boolean_expression

An expression that returns the Boolean worth TRUE, FALSE, or NULL.

boolean_function_call

Any duty call the returns a Boolean value.

boolean_literal

The predefined worths TRUE, FALSE, or NULL (which means a missing, unknown, or inapplicable value). You can not insert the worth TRUE or FALSE into a database column.

boolean_variable_name

A change of type BOOLEAN. Only the values TRUE, FALSE, and also NULL can be assigned come a BOOLEAN variable. Girlfriend cannot pick or fetch column values right into a BOOLEAN variable. Also, arithmetic work on BOOLEAN variables are not allowed.

%BULK_ROWCOUNT

Designed for use with the FORALL statement, this is a composite attribute that the latent cursor SQL. For much more information, view "SQL Cursor".

character_constant_name

A formerly declared constant that stores a character value. It must be initialized come a character worth or a value implicitly convertible to a personality value.

character_expression

An expression that returns a character or character string.

character_function_call

A role call the returns a character value or a worth implicitly convertible to a personality value.

character_literal

A literal the represents a character value or a value implicitly convertible to a character value.

character_variable_name

A previously claimed variable the stores a character value.

collection_name

A repertoire (nested table, index-by table, or varray) previously claimed within the existing scope.

cursor_name

An clearly cursor previously asserted within the existing scope.

cursor_variable_name

A PL/SQL cursor change previously claimed within the current scope.

date_constant_name

A formerly declared consistent that stores a date value. It have to be initialized to a date value or a value implicitly convertible come a date value.

date_expression

An expression that returns a date/time value.

date_function_call

A role call the returns a date value or a worth implicitly convertible to a day value.

date_literal

A literal meaning representing a day value or a value implicitly convertible to a date value.

date_variable_name

A previously declared variable that stores a date value.

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

Collection methods. Once appended come the name of a collection, these approaches return beneficial information. For example, EXISTS(n) return TRUE if the nth aspect of a collection exists. Otherwise, EXISTS(n) returns FALSE. For an ext information, watch "Collection Methods".

exponent

An expression that have to return a numeric value.

%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT

Cursor attributes. Once appended to the name of a cursor or cursor variable, these attributes return valuable information about the execution the a multi-row query. Friend can additionally append them to the latent cursor SQL.

host_cursor_variable_name

A cursor variable declared in a PL/SQL organize environment and also passed to PL/SQL together a tie variable. Hold cursor variables have to be prefixed through a colon.

host_variable_name

A variable claimed in a PL/SQL organize environment and also passed come PL/SQL together a tie variable. The datatype of the hold variable should be implicitly convertible come the ideal PL/SQL datatype. Also, hold variables should be prefixed through a colon.

IN

Comparison operator the tests collection membership. It method "equal to any kind of member of." The set can save on computer nulls, but they room ignored. Also, expression of the form

value no IN setreturn FALSE if the set contains a null.

index

A numeric expression that have to return a worth of form BINARY_INTEGER or a value implicitly convertible to that datatype.

indicator_name

An indicator variable claimed in a PL/SQL organize environment and also passed come PL/SQL. Indicator variables must be prefixed v a colon. An indicator variable "indicates" the value or condition of its linked host variable. For example, in the invernessgangshow.net Precompiler environment, indicator variables have the right to detect nulls or truncated values in output host variables.

IS NULL

Comparison operator the returns the Boolean worth TRUE if that operand is null, or FALSE if that operand is not null.

LIKE

Comparison operator that compares a character worth to a pattern. Situation is significant. Choose returns the Boolean worth TRUE if the character fads match, or FALSE if they carry out not match.

NOT, AND, OR

Logical operators, which monitor the tri-state logic of Table 2-2. And returns the value TRUE just if both that operands are true. OR returns the worth TRUE if either of that is operands is true. Not returns the opposite worth (logical negation) the its operand. For an ext information, check out "Logical Operators".

NULL

Keyword that represents a null. It stands for a missing, unknown, or inapplicable value. As soon as NULL is supplied in a numeric or day expression, the result is a null.

numeric_constant_name

A previously declared consistent that shop a numeric value. It must be initialized to a numeric value or a worth implicitly convertible to a numeric value.

numeric_expression

An expression the returns an creature or genuine value.

numeric_function_call

A function call that returns a numeric value or a value implicitly convertible come a numeric value.

numeric_literal

A literal that represents a number or a value implicitly convertible to a number.

numeric_variable_name

A previously claimed variable the stores a numeric value.

pattern

A personality string contrasted by the choose operator come a stated string value. The can incorporate two special-purpose characters called wildcards. An underscore (_) matches specifically one character; a percent sign (%) matches zero or much more characters. The pattern can be followed by to escape "character_literal", which turns off wildcard development wherever the escape character shows up in the string adhered to by a percent authorize or underscore.

relational_operator

Operator that compares expressions. Because that the meaning of every operator, see "Comparison Operators".

SQL

A cursor opened up implicitly by invernessgangshow.net to procedure a SQL data manipulation statement. The implicitly cursor SQL constantly refers to the many recently executed SQL statement.

+, -, /, *, **

Symbols because that the addition, subtraction, division, multiplication, and exponentiation operators.

||

The concatenation operator. Together the following example shows, the result of concatenating string1 through string2 is a character string that contains string1 adhered to by string2:

"Good" || " morning!" = "Good morning!"The next instance shows that nulls have no effect on the an outcome of a concatenation:

"suit" || NULL || "case" = "suitcase"A null cable (""), i beg your pardon is zero personalities in length, is treated favor a null.

Usage Notes

In a Boolean expression, you deserve to only compare values that have actually compatible datatypes. For much more information, view "Converting PL/SQL Datatypes".

In conditional regulate statements, if a Boolean expression returns TRUE, its associated sequence of declaration is executed. But, if the expression returns FALSE or NULL, its linked sequence of declaration is not executed.

The relational operators have the right to be used to operands of type BOOLEAN. By definition, TRUE is better than FALSE. Comparisons involving nulls constantly return a null. The worth of a Boolean expression can be assigned just to Boolean variables, not to organize variables or database columns. Also, datatype conversion come or from kind BOOLEAN is no supported.

You deserve to use the enhancement and subtraction operators to increment or decrement a day value, together the following instances show:

hire_date := "10-MAY-95";hire_date := hire_date + 1; -- renders hire_date "11-MAY-95"hire_date := hire_date - 5; -- provides hire_date "06-MAY-95"When PL/SQL evaluate a boolean expression, NOT has actually the highest precedence, and has the next-highest precedence, and also OR has actually the lowest precedence. However, you have the right to use parentheses come override the default operator precedence.

See more: What Is 1 Of 10 Billion Dollars ? What Is 1 Of 10 Billion Dollars

Within one expression, operations happen in their predefined bespeak of precedence. From very first to last (top to bottom), the default stimulate of to work is

parenthesesexponentsunary operatorsmultiplication and divisionaddition, subtraction, and also concatenation

PL/SQL evaluates operators of same precedence in no particular order. As soon as parentheses enclose an expression the is component of a bigger expression, PL/SQL evaluate the parenthesized expression first, then offers the an outcome in the larger expression. Once parenthesized expressions room nested, PL/SQL evaluates the innermost expression an initial and the outermost expression last.

Examples

Several examples of expressions follow:

(a + b) > c -- Boolean expressionNOT perfect -- Boolean expressionTO_CHAR(acct_no) -- personality expression"Fat " || "cats" -- character expression"15-NOV-95" -- date expressionMONTHS_BETWEEN(d1, d2) -- date expressionpi * r**2 -- numeric expressionemp_cv%ROWCOUNT -- numeric expressionRelated Topics

Assignment Statement, Constants and Variables, leave Statement, IF Statement, LOOP Statements