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 |
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.gifDescription the the illustration other_boolean_form.gifDescription that the illustration numeric_subexpression.gifDescription that the illustration date_expression.gifKeyword and Parameter Description
BETWEENThis 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_nameA 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_expressionAn expression that returns the Boolean worth TRUE, FALSE, or NULL.
boolean_function_callAny duty call the returns a Boolean value.
boolean_literalThe 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_nameA 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_ROWCOUNTDesigned 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_nameA 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_expressionAn expression that returns a character or character string.
character_function_callA role call the returns a character value or a worth implicitly convertible to a personality value.
character_literalA literal the represents a character value or a value implicitly convertible to a character value.
character_variable_nameA previously claimed variable the stores a character value.
collection_nameA repertoire (nested table, index-by table, or varray) previously claimed within the existing scope.
cursor_nameAn clearly cursor previously asserted within the existing scope.
cursor_variable_nameA PL/SQL cursor change previously claimed within the current scope.
date_constant_nameA 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_expressionAn expression that returns a date/time value.
date_function_callA role call the returns a date value or a worth implicitly convertible to a day value.
date_literalA literal meaning representing a day value or a value implicitly convertible to a date value.
date_variable_nameA previously declared variable that stores a date value.
EXISTS, COUNT, FIRST, LAST, LIMIT, NEXT, PRIORCollection 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".
exponentAn expression that have to return a numeric value.
%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNTCursor 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_nameA 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_nameA 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.
INComparison 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.
indexA numeric expression that have to return a worth of form BINARY_INTEGER or a value implicitly convertible to that datatype.
indicator_nameAn 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 NULLComparison operator the returns the Boolean worth TRUE if that operand is null, or FALSE if that operand is not null.
LIKEComparison 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, ORLogical 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".
NULLKeyword 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_nameA 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_expressionAn expression the returns an creature or genuine value.
numeric_function_callA function call that returns a numeric value or a value implicitly convertible come a numeric value.
numeric_literalA literal that represents a number or a value implicitly convertible to a number.
numeric_variable_nameA previously claimed variable the stores a numeric value.
patternA 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_operatorOperator that compares expressions. Because that the meaning of every operator, see "Comparison Operators".
SQLA 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 concatenationPL/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