Oracle8
SQL Reference
Release 8.0 A58225-01 |
|
This chapter describes methods of manipulating individual data items. Standard arithmetic operators such as addition and subtraction are discussed, as well as less common functions such as absolute value and string length. Topics include:
Note: Functions, expressions, and descriptions preceded by |
An operator manipulates individual data items and returns
a result. The data items are called operands or arguments.
Operators are represented by special characters or by keywords. For example,
the multiplication operator is represented by an asterisk (*) and the operator
that tests for nulls is represented by the keywords IS NULL. Tables in
this section list SQL operators.
There are two general classes of operators:
Other operators with special formats accept more than two
operands. If an operator is given a null operand, the result is always
null. The only operator that does not follow this rule is concatenation
(||).
Precedence is the order in which Oracle evaluates
different operators in the same expression. When evaluating an expression
containing multiple operators, Oracle evaluates operators with higher precedence
before evaluating those with lower precedence. Oracle evaluates operators
with equal precedence from left to right within an expression.
Table 3-1 lists the levels of precedence among SQL operators from high to low. Operators listed on the same line have the same precedence.
In the following expression multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.
1+2*3
You can use parentheses in an expression to override operator
precedence. Oracle evaluates expressions inside parentheses before evaluating
those outside.
SQL also supports set operators (UNION, UNION ALL, INTERSECT,
and MINUS), which combine sets of rows returned by queries, rather than
individual data items. All set operators have equal precedence.
You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value. Some of these operators are also used in date arithmetic. Table 3-2 lists arithmetic operators.
Do not use two consecutive minus signs with no separation
(--) in arithmetic expressions to indicate double negation or the subtraction
of a negative value. The characters -- are used to begin comments within
SQL statements. You should separate consecutive minus signs with a space
or a parenthesis. For more information on comments within SQL statements,
see "Comments".
The concatenation operator manipulates character strings. Table 3-3 describes the concatenation operator.
Operator | Purpose | Example |
---|---|---|
|| |
Concatenates character strings. |
SELECT 'Name is ' || ename FROM emp; |
The result of concatenating two character strings is another
character string. If both character strings are of datatype CHAR, the result
has datatype CHAR and is limited to 2000 characters. If either string is
of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to
4000 characters. Trailing blanks in character strings are preserved by
concatenation, regardless of the strings' datatypes. For more information
on the differences between the CHAR and VARCHAR2 datatypes, see "Character
Datatypes".
On most platforms, the concatenation operator is two solid
vertical bars, as shown in Table 3-3. However,
some IBM platforms use broken vertical bars for this operator. When moving
SQL script files between systems having different character sets, such
as between ASCII and EBCDIC, vertical bars might not be translated into
the vertical bar required by the target Oracle environment. Oracle provides
the CONCAT character function as an alternative to the vertical bar operator
for cases when it is difficult or impossible to control translation performed
by operating system or network utilities. Use this function in applications
that will be moved between environments with differing character sets.
Although Oracle treats zero-length character strings as nulls,
concatenating a zero-length character string with another operand always
results in the other operand, so null can result only from the concatenation
of two null strings. However, this may not continue to be true in future
versions of Oracle. To concatenate an expression that might be null, use
the NVL function to explicitly convert the expression to a zero-length
string.
This example creates a table with both CHAR and VARCHAR2 columns, inserts values both with and without trailing blanks, and then selects these values, concatenating them. Note that for both CHAR and VARCHAR2 columns, the trailing blanks are preserved.
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6), col3 VARCHAR2(6), col4 CHAR(6) ); Table created. INSERT INTO tab1 (col1, col2, col3, col4) VALUES ('abc', 'def ', 'ghi ', 'jkl'); 1 row created. SELECT col1||col2||col3||col4 "Concatenation" FROM tab1; Concatenation ------------------------ abcdef ghi jkl
Comparison operators compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN. For information on conditions, see "Conditions". Table 3-4 lists comparison operators.
Operator | Purpose | Example |
---|---|---|
= |
Equality test. |
SELECT * FROM emp WHERE sal = 1500; |
!= ^= <> ¬= |
Inequality test. Some forms of the inequality operator may be unavailable on some platforms. |
SELECT * FROM emp WHERE sal != 1500; |
> < |
"Greater than" and "less than" tests. |
SELECT * FROM emp WHERE sal > 1500; SELECT * FROM emp WHERE sal < 1500; |
>= <= |
"Greater than or equal to" and "less than or equal to" tests. |
SELECT * FROM emp WHERE sal >= 1500; SELECT * FROM emp WHERE sal <= 1500; |
IN |
"Equal to any member of" test. Equivalent to "= ANY". |
SELECT * FROM emp WHERE job IN ('CLERK','ANALYST'); SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30); |
NOT IN |
Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL. |
SELECT * FROM emp WHERE sal NOT IN (SELECT sal FROM emp WHERE deptno = 30); SELECT * FROM emp WHERE job NOT IN ('CLERK', ANALYST'); |
ANY SOME |
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows. |
SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno = 30); |
ALL |
Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows. |
SELECT * FROM emp WHERE sal >= ALL ( 1400, 3000); |
[NOT] BETWEEN x AND y |
[Not] greater than or equal to x and less than or equal to y. |
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000; |
EXISTS |
TRUE if a subquery returns at least one row. |
SELECT ename, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno); |
x [NOT] LIKE y [ESCAPE 'z'] |
TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character, excepting percent (%) and underbar (_) may follow ESCAPE; a wildcard character is treated as a literal if preceded by the character designated as the escape character. |
See "LIKE Operator". SELECT * FROM tab1 WHERE col1 LIKE 'A_C/%E%' ESCAPE '/'; |
IS [NOT] NULL |
Tests for nulls. This is the only operator that you should use to test for nulls. See "Nulls". |
SELECT ename, deptno FROM emp WHERE comm IS NULL; |
Additional information on the NOT IN and LIKE operators appears
in the sections that follow.
If any item in the list following a NOT IN operation is null, all rows evaluate to UNKNOWN (and no rows are returned). For example, the following statement returns the string 'TRUE' for each row:
SELECT 'TRUE' FROM emp WHERE deptno NOT IN (5,15);
However, the following statement returns no rows:
SELECT 'TRUE' FROM emp WHERE deptno NOT IN (5,15,null);
The above example returns no rows because the WHERE clause condition evaluates to:
deptno != 5 AND deptno != 15 AND deptno != null
Because all conditions that compare a null result in a null,
the entire expression results in a null. This behavior can easily be overlooked,
especially when the NOT IN operator references a subquery.
The LIKE operator is used in character string comparisons
with pattern matching. The syntax for a condition using the LIKE operator
is shown in this diagram:
While the equal (=) operator exactly matches one character
value to another, the LIKE operator matches a portion of one character
value to another by searching the first value for the pattern specified
by the second. Note that blank padding is not used for LIKE comparisons.
With the LIKE operator, you can compare a value to a pattern rather than to a constant. The pattern can only appear after the LIKE keyword. For example, you can issue the following query to find the salaries of all employees with names beginning with 'SM':
SELECT sal FROM emp WHERE ename LIKE 'SM%';
The following query uses the = operator, rather than the LIKE operator, to find the salaries of all employees with the name 'SM%':
SELECT sal FROM emp WHERE ename = 'SM%';
The following query finds the salaries of all employees with the name 'SM%'. Oracle interprets 'SM%' as a text literal, rather than as a pattern, because it precedes the LIKE operator:
SELECT sal FROM emp WHERE 'SM%' LIKE ename;
Patterns usually use special characters that Oracle matches with different characters in the value:
Case is significant in all conditions comparing character expressions including the LIKE and equality (=) operators. You can use the UPPER() function to perform a case-insensitive match, as in this condition:
UPPER(ename) LIKE 'SM%'
When LIKE is used to search an indexed column for a pattern,
Oracle can use the index to improve the statement's performance if the
leading character in the pattern is not "%" or "_". In this case, Oracle
can scan the index by this leading character. If the first character in
the pattern is "%" or "_", the index cannot improve the query's performance
because Oracle cannot scan the index.
This condition is true for all ENAME values beginning with "MA":
ename LIKE 'MA%'
All of these ENAME values make the condition TRUE:
MARTIN, MA, MARK, MARY
Case is significant, so ENAME values beginning with "Ma,"
"ma," and "mA" make the condition FALSE.
Consider this condition:
ename LIKE 'SMITH_'
This condition is true for these ENAME values:
SMITHE, SMITHY, SMITHS
This condition is false for 'SMITH', since the special character
"_" must match exactly one character of the ENAME value.
You can include the actual characters "%" or "_" in the pattern
by using the ESCAPE option. The ESCAPE option identifies the escape character.
If the escape character appears in the pattern before the character "%"
or "_" then Oracle interprets this character literally in the pattern,
rather than as a special pattern matching character.
To search for any employees with the pattern 'A_B' in their name:
SELECT ename FROM emp WHERE ename LIKE '%A\_B%' ESCAPE '\';
The ESCAPE option identifies the backslash (\) as the escape
character. In the pattern, the escape character precedes the underscore
(_). This causes Oracle to interpret the underscore literally, rather than
as a special pattern matching character.
If a pattern does not contain the "%" character, the condition
can be TRUE only if both operands have the same length.
Consider the definition of this table and the values inserted into it:
CREATE TABLE freds (f CHAR(6), v VARCHAR2(6)); INSERT INTO freds VALUES ('FRED', 'FRED');
Because Oracle blank-pads CHAR values, the value of F is
blank-padded to 6 bytes. V is not blank-padded and has length 4.
A logical operator combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 3-5 lists logical operators.
For example, in the WHERE clause of the following SELECT statement, the AND logical operator is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:
SELECT * FROM emp WHERE hiredate < TO_DATE('01-JAN-1984', 'DD-MON-YYYY') AND sal > 1000;
Table 3-6 shows the result of applying the NOT operator to a condition.
NOT |
TRUE |
FALSE |
UNKNOWN |
---|---|---|---|
|
FALSE |
TRUE |
UNKNOWN |
Table 3-7 shows the results of combining two expressions with AND.
AND |
TRUE |
FALSE |
UNKNOWN |
TRUE |
TRUE |
FALSE |
UNKNOWN |
FALSE |
FALSE |
FALSE |
FALSE |
UNKNOWN |
UNKNOWN |
FALSE |
UNKNOWN |
Table 3-8 shows the results of combining two expressions with OR.
OR |
TRUE |
FALSE |
UNKNOWN |
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
FALSE |
UNKNOWN |
UNKNOWN |
TRUE |
UNKNOWN |
UNKNOWN |
Set operators combine the results of two component queries
into a single result. Queries containing set operators are called compound
queries. Table 3-9 lists SQL set operators.
All set operators have equal precedence. If a SQL statement
contains multiple set operators, Oracle evaluates them from the left to
right if no parentheses explicitly specify another order. To comply with
emerging SQL standards, a future release of Oracle will give the INTERSECT
operator greater precedence than the other set operators. Therefore, you
should use parentheses to specify order of evaluation in queries that use
the INTERSECT operator with other set operators.
The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:
Consider these two queries and their results:
SELECT part FROM orders_list1; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE SELECT part FROM orders_list2; PART ---------- CRANKSHAFT TAILPIPE TAILPIPE
The following examples combine the two query results with
each of the set operators.
The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows how datatype must match when columns do not exist in one or the other table:
SELECT part, partnum, to_date(null) date_in FROM orders_list1 UNION SELECT part, to_null(null), date_in FROM orders_list2; PART PARTNUM DATE_IN ---------- ------- -------- SPARKPLUG 3323165 SPARKPLUG 10/24/98 FUEL PUMP 3323162 FUEL PUMP 12/24/99 TAILPIPE 1332999 TAILPIPE 01/01/01 CRANKSHAFT 9394991 CRANKSHAFT 09/12/02 SELECT part FROM orders_list1 UNION SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP TAILPIPE CRANKSHAFT
The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:
SELECT part FROM orders_list1 UNION ALL SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP FUEL PUMP TAILPIPE CRANKSHAFT TAILPIPE TAILPIPE
Note that the UNION operator returns only distinct rows that
appear in either result, while the UNION ALL operator returns all rows.
A PART value that appears multiple times in either or both queries (such
as 'FUEL PUMP') is returned only once by the UNION operator, but multiple
times by the UNION ALL operator.
The following statement combines the results with the INTERSECT operator which returns only those rows returned by both queries:
SELECT part FROM orders_list1 INTERSECT SELECT part FROM orders_list2; PART ---------- TAILPIPE
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT part FROM orders_list1 MINUS SELECT part FROM orders_list2; PART ---------- SPARKPLUG FUEL PUMP
Table 3-10 lists other SQL operators.
Operator | Purpose | Example |
---|---|---|
(+) |
Indicates that the preceding column is the outer join column in a join. See "Outer Joins". |
SELECT ename, dname FROM emp, dept WHERE dept.deptno = emp.deptno(+); |
PRIOR |
Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured, query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between parent and child rows. You can also use this operator in other parts of a SELECT statement that performs a hierarchical query. The PRIOR operator is a unary operator and has the same precedence as the unary + and - arithmetic operators. See "Hierarchical Queries". |
SELECT empno, ename, mgr FROM emp CONNECT BY PRIOR empno = mgr; |
A SQL function is similar to an operator in that it manipulates data items and returns a result. SQL functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments:
function(argument, argument, ...)
If you call a SQL function with an argument of a datatype
other than the datatype expected by the SQL function, Oracle implicitly
converts the argument to the expected datatype before performing the SQL
function. See "Data Conversion".
If you call a SQL function with a null argument, the SQL
function automatically returns null. The only SQL functions that do not
follow this rule are CONCAT, DECODE, DUMP, NVL, and REPLACE.
SQL functions should not be confused with user functions
written in PL/SQL. User functions are described in "User
Functions".
In the syntax diagrams for SQL functions, arguments are indicated
with their datatypes following the conventions described in "Syntax
Diagrams and Notation" in the Preface of this reference.
SQL functions are of these general types:
The two types of SQL functions differ in the number of rows
upon which they act. A single-row function returns a single result row
for every row of a queried table or view; a group function returns a single
result row for a group of queried rows.
Single-row functions can appear in select lists (if the SELECT
statement does not contain a GROUP BY clause), WHERE clauses, START WITH
clauses, and CONNECT BY clauses.
Group functions can appear in select lists and HAVING clauses.
If you use the GROUP BY clause in a SELECT statement, Oracle divides the
rows of a queried table or view into groups. In a query containing a GROUP
BY clause, all elements of the select list must be expressions from the
GROUP BY clause, expressions containing group functions, or constants.
Oracle applies the group functions in the select list to each group of
rows and returns a single result row for each group.
If you omit the GROUP BY clause, Oracle applies group functions
in the select list to all the rows in the queried table or view. You use
group functions in the HAVING clause to eliminate groups from the output
based on the results of the group functions, rather than on the values
of the individual rows of the queried table or view. For more information
on the GROUP BY and HAVING clauses, see the GROUP
BY Clause and the HAVING Clause.
In the sections that follow, functions are grouped by the
datatypes of their arguments and return values.
Number functions accept numeric input and return numeric
values. This section lists the SQL number functions. Most of these functions
return values that are accurate to 38 decimal digits. The transcendental
functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate
to 36 decimal digits. The transcendental functions ACOS, ASIN, ATAN, and
ATAN2 are accurate to 30 decimal digits.
Purpose |
|
Example |
SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 |
Purpose |
|
Example |
SELECT ACOS(.3)"Arc_Cosine" FROM DUAL; Arc_Cosine ---------- 1.26610367 |
Purpose |
|
Example |
SELECT ASIN(.3) "Arc_Sine" FROM DUAL; Arc_Sine ---------- .304692654 |
Purpose |
|
Example |
SELECT ATAN(.3) "Arc_Tangent" FROM DUAL; Arc_Tangent ---------- .291456794 |
Purpose |
|
Example |
SELECT ATAN2(.3, .2) "Arc_Tangent2" FROM DUAL; Arc_Tangent2 ------------ .982793723 |
Purpose |
|
Example |
SELECT CEIL(15.7) "Ceiling" FROM DUAL; Ceiling ---------- 16 |
Purpose |
|
Example |
SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" FROM DUAL; Cosine of 180 degrees --------------------- -1 |
Purpose |
|
Example |
SELECT COSH(0) "Hyperbolic cosine of 0" FROM DUAL; Hyperbolic cosine of 0 ---------------------- 1 |
Purpose |
|
Example |
SELECT EXP(4) "e to the 4th power" FROM DUAL; e to the 4th power ------------------ 54.59815 |
Purpose |
|
Example |
SELECT FLOOR(15.7) "Floor" FROM DUAL; Floor ---------- 15 |
Purpose |
|
Example |
SELECT LN(95) "Natural log of 95" FROM DUAL; Natural log of 95 ----------------- 4.55387689 |
Purpose |
|
Example |
SELECT LOG(10,100) "Log base 10 of 100" FROM DUAL; Log base 10 of 100 ------------------ 2 |
Purpose |
|
Example |
SELECT POWER(3,2) "Raised" FROM DUAL; Raised ---------- 9 |
Syntax |
ROUND(n[,m]) |
Purpose |
|
SELECT ROUND(15.193,1) "Round" FROM DUAL; Round ---------- 15.2 |
|
|
SELECT ROUND(15.193,-1) "Round" FROM DUAL; Round ---------- 20 |
Syntax |
SIGN(n) |
Purpose |
|
Example |
SELECT SIGN(-15) "Sign" FROM DUAL; Sign ---------- -1 |
Purpose |
|
Example |
SELECT SIN(30 * 3.14159265359/180) "Sine of 30 degrees" FROM DUAL; Sine of 30 degrees ------------------ .5 |
Purpose |
|
Example |
SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL; Hyperbolic sine of 1 -------------------- 1.17520119 |
Purpose |
|
Example |
SELECT SQRT(26) "Square root" FROM DUAL; Square root ----------- 5.09901951 |
Purpose |
|
Example |
SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees" FROM DUAL; Tangent of 135 degrees ---------------------- - 1 |
Purpose |
|
Example |
SELECT TANH(.5) "Hyperbolic tangent of .5" FROM DUAL; Hyperbolic tangent of .5 ------------------------ .462117157 |
Purpose |
|
Examples |
SELECT TRUNC(15.79,1) "Truncate" FROM DUAL; Truncate ---------- 15.7 |
|
SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL; Truncate ---------- 10 |
Single-row character functions accept character input and
can return either character or number values.
This section lists character functions that return character
values. Unless otherwise noted, these functions all return values with
the datatype VARCHAR2 and are limited in length to 4000 bytes. Functions
that return values of datatype CHAR are limited in length to 2000 bytes.
If the length of the return value exceeds the limit, Oracle truncates it
and returns the result without an error message.
Syntax |
CONCAT(char1, char2) |
Purpose |
Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator". |
Example |
Purpose |
|
Example |
SELECT INITCAP('the soap') "Capitals" FROM DUAL; Capitals --------- The Soap |
Purpose |
|
Example |
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase" FROM DUAL; Lowercase -------------------- mr. scott mcmillan |
Purpose |
Returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric. The value of 'nlsparams' can have this form: 'NLS_SORT = sort' where sort is either a linguistic sort sequence or BINARY. The linguistic sort sequence handles special linguistic requirements for case conversions. Note that these requirements can result in a return value of a different length than the char. If you omit 'nlsparams', this function uses the default sort sequence for your session. For information on sort sequences, see Oracle8 Reference. |
Example |
SELECT NLS_INITCAP ('ijsland', 'NLS_SORT = XDutch') "Capitalized" FROM DUAL; Capital ------- IJsland |
Syntax |
NLS_LOWER(char [, 'nlsparams'] ) |
Purpose |
|
Example |
SELECT NLS_LOWER ('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL; Lower ----- cittá |
Syntax |
NLS_UPPER(char [, 'nlsparams'] ) |
Purpose |
|
Example |
SELECT NLS_UPPER ('gro |
Syntax |
RTRIM(char [,set] |
Purpose |
|
Example |
SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g." FROM DUAL; RTRIM e.g ------------- BROWNINGyxX |
Syntax |
SOUNDEX(char) |
|
Purpose |
||
|
|
|
|
|
b, f, p, v = 1 c, g, j, k, q, s, x, z = 2 d, t = 3 l = 4 m, n = 5 r = 6 |
|
|
|
|
|
|
Example |
SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE'); ENAME ---------- SMITH |
Syntax |
SUBSTR(char, m [,n]) |
Purpose |
|
Example |
Syntax |
UPPER(char) |
Purpose |
|
Example |
SELECT UPPER('Large') "Uppercase" FROM DUAL; Upper ----- LARGE |
This section lists character functions that return number
values.
Syntax |
INSTRB(char1,char2[,n[,m]]) |
Purpose |
|
Example |
Syntax |
LENGTH(char) |
Purpose |
|
Example |
SELECT LENGTH('CANDIDE') "Length in characters" FROM DUAL; Length in characters -------------------- 7 |
Syntax |
LENGTHB(char) |
Purpose |
|
Example |
Syntax |
NLSSORT(char [, 'nlsparams']) |
Purpose |
Returns the string of bytes used to sort char. The value of 'nlsparams' can have the form 'NLS_SORT = sort' where sort is a linguistic sort sequence or BINARY. If you omit 'nlsparams', this function uses the default sort sequence for your session. If you specify BINARY, this function returns char. For information on sort sequences, see the discussions of national language support in Oracle8 Reference. |
Example |
Date functions operate on values of the DATE datatype. All
date functions return a value of DATE datatype, except the MONTHS_BETWEEN
function, which returns a number.
Syntax |
ADD_MONTHS(d,n) |
Purpose |
|
Example |
SELECT TO_CHAR( ADD_MONTHS(hiredate,1), 'DD-MON-YYYY') "Next month" FROM emp WHERE ename = 'SMITH'; Next Month ----------- 17-JAN-1981 |
Syntax |
ROUND(d[,fmt]) |
Purpose |
Returns d rounded to the unit specified by the format model fmt. If you omit fmt, d is rounded to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt. |
Example |
SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-93 |
Syntax |
SYSDATE |
Purpose |
|
Example |
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW" FROM DUAL; NOW ------------------- 10-29-1993 20:27:11 |
Syntax |
1TRUNC(d,[fmt]) |
Purpose |
Returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day. See "ROUND and TRUNC" for the permitted format models to use in fmt. |
Example |
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL; New Year --------- 01-JAN-92 |
Table 3-11 lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.
The starting day of the week used by the format models DAY,
DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.
For information on this parameter, see Oracle8
Reference.
Conversion functions convert a value from one datatype to
another. Generally, the form of the function names follows the convention
datatype TO datatype. The first datatype is the input datatype;
the last datatype is the output datatype. This section lists the SQL conversion
functions.
Syntax |
CHARTOROWID(char) |
Purpose |
|
Example |
SELECT ename FROM emp WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO'); ENAME ---------- LEWIS |
Syntax |
HEXTORAW(char) |
Purpose |
|
Example |
INSERT INTO graphics (raw_column) SELECT HEXTORAW('7D') FROM DUAL; |
Syntax |
RAWTOHEX(raw) |
Purpose |
|
Example |
SELECT RAWTOHEX(raw_column) "Graphics" FROM graphics; Graphics -------- 7D |
Syntax |
ROWIDTOCHAR(rowid) |
Purpose |
|
Example |
SELECT ROWID FROM offices WHERE ROWIDTOCHAR(ROWID) LIKE '%Br1AAB%'; ROWID ------------------ AAAAZ6AABAAABr1AAB |
Syntax |
TO_CHAR(d [, fmt [, 'nlsparams'] ]) |
Purpose |
Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see "Format Models". |
|
|
Example |
SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY') "New date format" FROM emp WHERE ename = 'BLAKE'; New date format ------------------ May 01, 1981 |
Syntax |
TO_CHAR(n [, fmt [, 'nlsparams'] ]) |
|
Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see "Format Models". |
|
|
|
|
Example 1 |
|
Example 2 |
SELECT TO_CHAR(-10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''AusDollars'' ') "Amount" FROM DUAL; Amount ------------------- AusDollars10.000,00- |
Notes:
|
|
|
Syntax |
TO_DATE(char [, fmt [, 'nlsparams'] ]) |
Purpose |
Converts char of CHAR or VARCHAR2 datatype to a value
of DATE datatype. The fmt is a date format specifying the format
of char. If you omit fmt, char must be in the default
date format. If fmt is 'J', for Julian, then char must be
an integer. For information on date formats, see "Format
Models".
The 'nlsparams' has the same purpose in this function
as in the TO_CHAR function for date conversion.
Do not use the TO_DATE function with a DATE value for the
char argument. The returned DATE value can have a different century
value than the original char, depending on fmt or the default
date format.
For information on date formats, see "Date Format Models". |
Example |
INSERT INTO bonus (bonus_date) SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American') FROM DUAL; |
Syntax |
DUMP(expr[,return_format[,start_position[,length]] ] ) |
|
Purpose |
Returns a VARCHAR2 value containing the datatype code, length
in bytes, and internal representation of expr. The returned result
is always in the database character set. For the datatype corresponding
to each code, see Table 2-1.
The argument return_format specifies the format of
the return value and can have any of the values listed below.
By default, the return value contains no character set information. To retrieve the character set name of expr, specify any of the format values below, plus 1000. For example, a return_format of 1008 returns the result in octal, plus provides the character set name of expr. |
|
|
returns result in octal notation. |
|
|
returns result in decimal notation. |
|
|
returns result in hexadecimal notation. |
|
|
returns result as single characters. |
|
|
||
Example 1 |
SELECT DUMP('abc', 1016) FROM DUAL; DUMP('ABC',1016) ------------------------------------------ Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63 |
|
Example 2 |
SELECT DUMP(ename, 8, 3, 2) "OCTAL" FROM emp WHERE ename = 'SCOTT'; OCTAL ---------------------------- Type=1 Len=5: 117,124 |
|
Example 3 |
SELECT DUMP(ename, 10, 3, 2) "ASCII" FROM emp WHERE ename = 'SCOTT'; ASCII ---------------------------- Type=1 Len=5: 79,84 |
Syntax |
BFILENAME ('directory', 'filename') |
|
Purpose |
Returns a BFILE locator that is associated with a physical
LOB binary file on the server's file system. A directory is an alias for
a full pathname on the server's file system where the files are actually
located; 'filename' is the name of the file in the server's file system.
Neither 'directory' nor 'filename' need to point to an existing object on the file system at the time you specify BFILENAME. However, you must associate a BFILE value with a physical file before performing subsequent SQL, PL/SQL, DBMS_LOB package, or OCI operations. For more information, see CREATE DIRECTORY. |
|
|
Note: This function does not verify that either the
directory or file specified actually exists. Therefore, you can call the
CREATE DIRECTORY command after BFILENAME. However, the object must exist
by the time you actually use the BFILE locator (for example, as a parameter
to one of the OCILob or DBMS_LOB operations such as OCILobFileOpen() or
DBMS_LOB.FILEOPEN()).
For more information about LOBs, see Oracle8 Application Developer's Guide and Oracle Call Interface Programmer's Guide. |
|
Example |
INSERT INTO file_tbl VALUES (BFILENAME ('lob_dir1', 'image1.gif')); |
Syntax |
NLS_CHARSET_ID(text) |
Purpose |
Returns the NLS character set ID number corresponding to
NLS character set name, text. The text argument is a run-time
VARCHAR2 value. The text value 'CHAR_CS' returns the server's database
character set ID number. The text value 'NCHAR_CS' returns the server's
national character set ID number.
Invalid character set names return null.
For a list of character set names, see Oracle8 Reference. |
|
SELECT NLS_CHARSET_ID('ja16euc') FROM DUAL; NLS_CHARSET_ID('JA16EUC') ------------------------- 830 |
|
SELECT NLS_CHARSET_ID('char_cs') FROM DUAL; NLS_CHARSET_ID('CHAR_CS') ------------------------- 2 |
|
SELECT NLS_CHARSET_ID('nchar_cs') FROM DUAL; NLS_CHARSET_ID('NCHAR_CS') -------------------------- 2 |
Syntax |
NLS_CHARSET_NAME(n) |
Purpose |
Returns the name of the NLS character set corresponding
to ID number n. The character set name is returned as a VARCHAR2
value in the database character set.
If n is not recognized as a valid character set ID,
this function returns null.
For a list of character set IDs, see Oracle8 Reference. |
Example |
SELECT NLS_CHARSET_NAME(2) FROM DUAL; NLS_CH ------ WE8DEC |
Syntax |
UID |
Purpose |
Syntax |
VSIZE(expr) |
Purpose |
|
Example |
SELECT ename, VSIZE (ename) "BYTES" FROM emp WHERE deptno = 10; ENAME BYTES ---------- ---------- CLARK 5 KING 4 MILLER 6 |
Object reference functions manipulate REFs-references to
objects of specified object types. For more information about REFs, see
Oracle8 Concepts and Oracle8
Application Developer's Guide.
Syntax |
DEREF(e) |
Purpose |
|
Example |
CREATE TABLE tb1(c1 NUMBER, c2 REF t1); SELECT DEREF(c2) FROM tb1; |
Syntax |
REFTOHEX(r) |
Purpose |
|
Example |
CREATE TABLE tb1(c1 NUMBER, c2 REF t1); SELECT REFTOHEX(c2) FROM tb1; |
Syntax |
MAKE_REF(table, key [,key...]) |
Purpose |
Creates a REF to a row of an object view using key as the primary key. For more information about object views, see Oracle8 Application Developer's Guide. |
Example |
CREATE TYPE t1 AS OBJECT(a NUMBER, b NUMBER); CREATE TABLE tb1 (c1 NUMBER, c2 NUMBER, PRIMARY KEY(c1, c2)); CREATE VIEW v1 OF t1 WITH OBJECT OID(a, b) AS SELECT * FROM tb1; SELECT MAKE_REF(v1, 1, 3) FROM DUAL; |
Group functions return results based on groups of rows, rather
than on single rows. In this way, group functions are different from single-row
functions. For a discussion of the differences between group functions
and single-row functions, see "SQL Functions".
Many group functions accept these options:
DISTINCT |
This option causes a group function to consider only distinct values of the argument expression. |
ALL |
This option causes a group function to consider all values, including all duplicates. |
For example, the DISTINCT average of 1, 1, 1, and 3 is 2;
the ALL average is 1.5. If neither option is specified, the default is
ALL.
All group functions except COUNT(*) ignore nulls. You can
use the NVL in the argument to a group function to substitute a value for
a null.
If a query with a group function returns no rows or only
rows with nulls for the argument to the group function, the group function
returns null.
Syntax |
AVG([DISTINCT|ALL] n) |
Purpose |
|
Example |
SELECT AVG(sal) "Average" FROM emp; Average ---------- 2077.21429 |
Syntax |
MAX([DISTINCT|ALL] expr) |
Purpose |
|
Example |
SELECT MAX(sal) "Maximum" FROM emp; Maximum ---------- 5000 |
Syntax |
MIN([DISTINCT|ALL] expr) |
Purpose |
|
Example |
SELECT MIN(hiredate) "Earliest" FROM emp; Earliest --------- 17-DEC-80 |
Syntax |
STDDEV([DISTINCT|ALL] x) |
Purpose |
|
Example |
SELECT STDDEV(sal) "Deviation" FROM emp; Deviation ---------- 1182.50322 |
Syntax |
SUM([DISTINCT|ALL] n) |
Purpose |
|
Example |
SELECT SUM(sal) "Total" FROM emp; Total ---------- 29081 |
Syntax |
VARIANCE([DISTINCT|ALL]x) |
|
Purpose |
||
|
||
|
||
Example |
SELECT VARIANCE(sal) "Variance" FROM emp; Variance ---------- 1389313.87 |
You can write your own user functions in PL/SQL to provide
functionality that is not available in SQL or SQL functions. User functions
are used in a SQL statement anywhere SQL functions can be used; that is,
wherever expression can occur.
For example, user functions can be used in the following:
For a complete description on the creation and use of user
functions, see Oracle8 Application
Developer's Guide.
User functions must be created as top-level PL/SQL functions
or declared with a package specification before they can be named within
a SQL statement. Create user functions as top-level PL/SQL functions by
using the CREATE FUNCTION statement described in CREATE
FUNCTION. Specify packaged functions with a package with the CREATE
PACKAGE statement described in CREATE PACKAGE.
To call a packaged user function, you must declare the RESTRICT_REFERENCES
pragma in the package specification.
To use a user function in a SQL expression, you must own
or have EXECUTE privilege on the user function. To query a view defined
with a user function, you must have SELECT privileges on the view. No separate
EXECUTE privileges are needed to select from the view.
User functions cannot be used in situations that require an unchanging definition. Thus, a user function cannot:
With PL/SQL, the names of database columns take precedence over the names of functions with no parameters. For example, if user SCOTT creates the following two objects in his own schema:
CREATE TABLE emp(new_sal NUMBER, ...); CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;
then in the following two statements, the reference to NEW_SAL refers to the column EMP.NEW_SAL:
SELECT new_sal FROM emp; SELECT emp.new_sal FROM emp;
To access the function NEW_SAL, you would enter:
SELECT scott.new_sal FROM emp;
Here are some sample calls to user functions that are allowed in SQL expressions.
circle_area (radius) payroll.tax_rate (empno) scott.payroll.tax_rate (dependent, empno)@ny
For example, to call the TAX_RATE user function from schema SCOTT, execute it against the SS_NO and SAL columns in TAX_TABLE, and place the results in the variable INCOME_TAX, specify the following:
SELECT scott.tax_rate (ss_no, sal) INTO income_tax FROM tax_table WHERE ss_no = tax_id;
If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:
You can also refer to a stored top-level function using any
synonym that you have defined for it.
A format model is a character literal that describes the format of DATE or NUMBER data stored in a character string. You can use a format model as an argument of the TO_CHAR or TO_DATE function:
Note that a format model does not change the internal representation
of the value in the database.
This section describes how to use:
You can use a format model to specify the format for Oracle
to use to return values from the database to you.
The following statement selects the commission values of the employees in Department 30 and uses the TO_CHAR function to convert these commissions into character values with the format specified by the number format model '$9,990.99':
SELECT ename employee, TO_CHAR(comm, '$9,990.99') commission FROM emp WHERE deptno = 30; EMPLOYEE COMMISSION ---------- ---------- ALLEN $300.00 WARD $500.00 MARTIN $1,400.00 BLAKE TURNER $0.00 JAMES
Because of this format model, Oracle returns commissions
with leading dollar signs, commas every three digits, and two decimal places.
Note that TO_CHAR returns null for all employees with null in the COMM
column.
The following statement selects the date on which each employee from department 20 was hired and uses the TO_CHAR function to convert these dates to character strings with the format specified by the date format model 'fmMonth DD, YYYY':
SELECT ename, TO_CHAR(Hiredate,'fmMonth DD, YYYY') hiredate FROM emp WHERE deptno = 20; ENAME HIREDATE ---------- ------------------ SMITH December 17, 1980 JONES April 2, 1981 SCOTT April 19, 1987 ADAMS May 23, 1987 FORD December 3, 1981 LEWIS October 23, 1997
With this format model, Oracle returns the hire dates with
the month spelled out (as specified by "fm" and discussed in "Format
Model Modifiers"), two digits for the day, and the century included
in the year.
You can use format models to specify the format of a value
that you are converting from one datatype to another datatype required
for a column. When you insert or update a column value, the datatype of
the value that you specify must correspond to the column's datatype. For
example, a value that you insert into a DATE column must be a value of
the DATE datatype or a character string in the default date format (Oracle
implicitly converts character strings in the default date format to the
DATE datatype). If the value is in another format, you must use the TO_DATE
function to convert the value to the DATE datatype. You must also use a
format model to specify the format of the character string.
The following statement updates BAKER's hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the character string '1992 05 20' to a DATE value:
UPDATE emp SET hiredate = TO_DATE('1992 05 20','YYYY MM DD') WHERE ename = 'BLAKE';
You can use number format models
All number format models cause the number to be rounded to
the specified number of significant digits. If a value has more significant
digits to the left of the decimal place than are specified in the format,
pound signs (#) replace the value. If a positive value is extremely large
and cannot be represented in the specified format, then the infinity sign
(~) replaces the value. Likewise, if a negative value is extremely small
and cannot be represented by the specified format, then the negative infinity
sign replaces the value (-~).
A number format model is composed of one or more number format elements. Table 3-12 lists the elements of a number format model. Examples are shown in Table 3-13.
Table 3-13 shows the results of the following query for different values of number and 'fmt':
SELECT TO_CHAR(number, 'fmt') FROM DUAL
The MI and PR format elements can appear only in the last
position of a number format model. The S format element can appear only
in the first or last position of a number format model.
The characters returned by some of these format elements are specified by initialization parameters. Table 3-14 lists these elements and parameters.
You can specify the characters returned by these format elements
implicitly using the initialization parameter NLS_TERRITORY. For information
on these parameters, see Oracle8 Reference.
You can change the characters returned by these format elements
for your session with the ALTER SESSION command. You can also change the
default date format for your session with the ALTER SESSION command. For
information, see ALTER SESSION.
You can use date format models
The default date format is specified either explicitly with
the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization
parameter NLS_TERRITORY. For information on these parameters, see Oracle8
Reference.
You can change the default date format for your session with
the ALTER SESSION command. For information, see ALTER
SESSION.
The total length of a date format model cannot exceed 22
characters.
A date format model is composed of one or more date format
elements as listed in Table 3-15. For
input format models, format items cannot appear twice, and format items
that represent similar information cannot be combined. For example, you
cannot use 'SYYYY' and 'BC' in the same format string. Only some of the
date format elements can be used in the TO_DATE function as noted in Table
3-15.
Oracle returns an error if an alphanumeric character is found in the date string where punctuation character is found in the format string. For example:
TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')
The functionality of some date format elements depends on the country and language in which you are using Oracle. For example, these date format elements return spelled values:
The language in which these values are returned is specified
either explicitly with the initialization parameter NLS_DATE_LANGUAGE or
implicitly with the initialization parameter NLS_LANGUAGE. The values returned
by the YEAR and SYEAR date format elements are always in English.
The date format element D returns the number of the day of
the week (1-7). The day of the week that is numbered 1 is specified implicitly
by the initialization parameter NLS_TERRITORY.
For information on these initialization parameters, see Oracle8
Reference.
Oracle calculates the values returned by the date format
elements IYYY, IYY, IY, I, and IW according to the ISO standard. For information
on the differences between these values and those returned by the date
format elements YYYY, YYY, YY, Y, and WW, see the discussion of national
language support in Oracle8 Reference.
The RR date format element is similar to the YY date format
element, but it provides additional flexibility for storing date values
in other centuries. The RR date format element allows you to store 21st
century dates in the 20th century by specifying only the last two digits
of the year. It will also allow you to store 20th century dates in the
21st century in the same way if necessary.
If you use the TO_DATE function with the YY date format element, the date value returned is always in the current century. If you use the RR date format element instead, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Table 3-16 summarizes the behavior of the RR date format element.
The following example demonstrates the behavior of the RR
date format element.
Assume these queries are issued between 1950 and 1999:
SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL; Year ---- 1995 SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"; FROM DUAL; Year ---- 2017
Assume these queries are issued between 2000 and 2049:
SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year"; FROM DUAL; Year ---- 1995 SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"; FROM DUAL; Year ---- 2017
Note that the queries return the same values regardless of
whether they are issued before or after the year 2000. The RR date format
element allows you to write SQL statements that will return the same values
after the turn of the century.
Table 3-17 lists suffixes that can be added to date format elements:
Suffix | Meaning | Example Element | Example Value |
---|---|---|---|
TH |
Ordinal Number |
DDTH |
4TH |
SP |
Spelled Number |
DDSP |
FOUR |
SPTH or THSP |
Spelled, ordinal number |
DDSPTH |
FOURTH |
When you add one of these suffixes to a date format element,
the return value is always in English.
Note: Date suffixes are valid only on output and cannot be used
to insert a date into the database.
|
Capitalization in a spelled-out word, abbreviation, or Roman
numeral follows capitalization in the corresponding format element. For
example, the date format model 'DAY' produces capitalized words like 'MONDAY';
'Day' produces 'Monday'; and 'day' produces 'monday'.
You can also include these characters in a date format model:
These characters appear in the return value in the same location
as they appear in the format model.
You can use the FM and FX modifiers in format models for
the TO_CHAR function to control blank padding and exact format checking.
A modifier can appear in a format model more than once. In
such a case, each subsequent occurrence toggles the effects of the modifier.
Its effects are enabled for the portion of the model following its first
occurrence, and then disabled for the portion following its second, and
then reenabled for the portion following its third, and so on.
"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:
"Format exact". This modifier specifies exact matching for the character argument and date format model of a TO_DATE function:
When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.
If any portion of the character argument violates any of
these conditions, Oracle returns an error message.
The following statement uses a date format model to return a character expression:
SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR (SYSDATE, 'Month')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides" FROM DUAL; Ides ------------------ 3RD of April, 1995
Note that the statement above also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:
SELECT TO_CHAR(SYSDATE, 'DDTH')||' of '|| TO_CHAR(Month, YYYY') "Ides" FROM DUAL; Ides ----------------------- 03RD of April , 1995
The following statement places a single quotation mark in the return value by using a date format model that includes two consecutive single quotation marks:
SELECT TO_CHAR(SYSDATE, 'fmDay')||'''s Special') "Menu" FROM DUAL; Menu ----------------- Tuesday's Special
Two consecutive single quotation marks can be used for the
same purpose within a character literal in a format model.
Table 3-18 shows whether the following statement meets the matching conditions for different values of char and 'fmt' using FX:
UPDATE table SET date_column = TO_DATE(char, 'fmt');
The following additional formatting rules apply when converting string values to date values:
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' |
'MON' and 'MONTH' |
'MON |
'MONTH' |
'MONTH' |
'MON' |
'YY' |
'YYYY' |
'RR' |
'RRRR' |
An expression is a combination of one or more values,
operators, and SQL functions that evaluate to a value. An expression generally
assumes the datatype of its components.
This simple expression evaluates to 4 and has datatype NUMBER (the same datatype as its components):
2*2
The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:
TO_CHAR(TRUNC(SYSDATE+7))
You can use expressions in
For example, you could use an expression in place of the quoted string 'smith' in this UPDATE statement SET clause:
SET ename = 'smith';
This SET clause has the expression LOWER(ename) instead of
the quoted string 'smith':
Expressions have several forms. Oracle does not accept all
forms of expressions in all parts of all SQL commands. You must use appropriate
expression notation whenever expr appears in conditions, SQL functions,
or SQL commands in other parts of this reference. The description of each
command in Chapter 4, "Commands", documents
the restrictions on the expressions in the command. The sections that follow
describe and provide examples of the various forms of expressions.
A Form I expression specifies column, pseudocolumn, constant, sequence number, or NULL.
In addition to the schema of a user, schema can also
be "PUBLIC" (double quotation marks required), in which case it must qualify
a public synonym for a table, view, or snapshot. Qualifying a public synonym
with "PUBLIC" is supported only in data manipulation language (DML) commands,
not data definition language (DDL) commands.
The pseudocolumn can be either LEVEL, ROWID, or ROWNUM.
You can use a pseudocolumn only with a table, not with a view or snapshot.
NCHAR and NVARCHAR2 are not valid pseudocolumn or ROWLABEL datatypes. For
more information on pseudocolumns, see "Pseudocolumns".
If you are not using Trusted Oracle, the expression ROWLABEL
always returns NULL. For information on using labels and ROWLABEL, see
your Trusted Oracle documentation.
Some valid Form I expressions are:
emp.ename 'this is a text string' 10 N'this is an NCHAR string'
A Form II expression specifies a host variable with an optional
indicator variable. Note that this form of expression can only appear in
embedded SQL statements or SQL statements processed in an Oracle Call Interface
(OCI) program.
Some valid Form II expressions are:
:employee_name INDICATOR :employee_name_indicator_var :department_location
A Form III expression specifies a call to a SQL function
operating on a single row.
Some valid Form III expressions are:
LENGTH('BLAKE') ROUND(1234.567*43) SYSDATE
For information on SQL functions, see "SQL
Functions".
A Form IV expression specifies a call to a user function
Some valid Form IV expressions are:
circle_area(radius) payroll.tax_rate(empno) scott.payrol.tax_rate(dependents, empno)@ny
For information on user functions, see "User
Functions".
A Form V expression specifies a combination of other expressions.
Note that some combinations of functions are inappropriate
and are rejected. For example, the LENGTH function is inappropriate within
a group function.
Some valid Form V expressions are:
('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY')
A Form VI expression specifies a call to a type constructor.
If type_name is an object type, then the type argument
list must be an ordered list of arguments, where the first argument is
a value whose type matches the first attribute of the object type, the
second argument is a value whose type matches the second attribute of the
object type, and so on. The total number of arguments to the constructor
must match the total number of attributes of the object type; the maximum
number of arguments is 999.
If type_name is a VARRAY or nested table type, then
the argument list can contain zero or more arguments. Zero arguments imply
construction of an empty collection. Otherwise, each argument corresponds
to an element value whose type is the element type of the collection type.
Whether type_name is an object type, a VARRAY, or
a nested table type, the maximum number of arguments it can contain is
999.
CREATE TYPE address_t AS OBJECT (no NUMBER, street CHAR(31), city CHAR(21), state CHAR(3), zip NUMBER); CREATE TYPE address_book_t AS TABLE OF address_t; DECLARE /* Object Type variable initialized via Object Type Constructor */ myaddr address_t = address_t(500, 'Oracle Parkway', 'Redwood Shores', 'CA', 94065); /* nested table variable initialized to an empty table via a constructor*/ alladdr address_book_t = address_book_t(); BEGIN /* below is an example of a nested table constructor with two elements specified, where each element is specified as an object type constructor. */ insert into employee values (666999, address_book_t(address_t(500, 'Oracle Parkway', 'Redwood Shores', 'CA', 94065), address_t(400, 'Mission Street', 'Fremont', 'CA', 94555))); END;
A Form VII expression converts one collection-typed value into another collection-typed value.
CAST allows you to convert collection-typed values of one
type into another collection type. You can cast an unnamed collection (such
as the result set of a subquery) or a named collection (such as a VARRAY
or a nested table) into a type-compatible named collection. The type_name
must be the name of a collection type and the operand must evaluate
to a collection value.
To cast a named collection type into another named collection
type, the elements of both collections must be of the same type.
If the result set of subquery can evaluate to multiple
rows, you must specify the MULTISET keyword. The rows resulting from the
subquery form the elements of the collection value into which they are
cast. Without the MULTISET keyword, the subquery is treated as a scalar
subquery, which is not supported in the CAST expression. In other words,
scalar subqueries as arguments of the CAST operator are not valid in Oracle8.
The CAST examples that follow use the following user-defined types and tables:
CREATE TYPE address_t AS OBJECT (no NUMBER, street CHAR(31), city CHAR(21), state CHAR(2)); CREATE TYPE address_book_t AS TABLE OF address_t; CREATE TYPE address_array_t AS VARRAY(3) OF address_t; CREATE TABLE emp_address (empno NUMBER, no NUMBER, street CHAR(31), city CHAR(21), state CHAR(2)); CREATE TABLE employees (empno NUMBER, name CHAR(31)); CREATE TABLE dept (dno NUMBER, addresses address_array_t);
CAST a subquery:
SELECT e.empno, e.name, CAST(MULTISET(SELECT ea.no, ea.street, ea.city, ea.state FROM emp_address ea WHERE ea.empno = e.empno) AS address_book_t) FROM employees e;
CAST converts a VARRAY type column into a nested table. The table values are generated by a flattened subquery. See "Using Flattened Subqueries".
SELECT * FROM THE(SELECT CAST(d.addresses AS address_book_t) FROM dept d WHERE d.dno = 111) a WHERE a.city = 'Redwood Shores';
The following example casts a MULTISET expression with an ORDER BY clause:
CREATE TABLE projects (empid NUMBER, projname VARCHAR2(10)); CREATE TABLE employees (empid NUMBER, ename VARCHAR2(10)); CREATE TYPE projname_table_type AS TABLE OF VARCHAR2(10);
An example of a MULTISET expression with the above schema is:
SELECT e.name, CAST(MULTISET(SELECT p.projname FROM projects p WHERE p.empid=e.empid ORDER BY p.projname) AS projname_table_type) FROM employees e;
A Form VIII expression returns a nested CURSOR. This form
of expression is similar to the PL/SQL REF cursor.
A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when
The following restrictions apply to the CURSOR expression:
SELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum, p.projname FROM projects p WHERE p.empno = e.empno) FROM TABLE(d.employees) e) FROM dept d WHERE d.dno = 605;
A Form IX expression constructs a reference to an object.
In a SQL statement, REF takes as its argument a table alias
associated with a row of an object table or an object view. A REF value
is returned for the object instance that is bound to the variable or row.
For more information about REFs, see Oracle8
Concepts.
SELECT REF(e) FROM employee_t e WHERE e.empno = 10000;
This example uses REF in a predicate:
SELECT e.name FROM employee_t e INTO :x WHERE REF(e) = empref1;
A Form X expression returns the row object.
In a SQL statement, VALUE takes as its argument a correlation
variable (table alias) associated with a row of an object table.
SELECT VALUE(e) FROM employee e WHERE e.name = 'John Smith';
A Form XI expression specifies attribute reference and method
invocation.
The column parameter can be an object or REF column. Examples in this section use the following user-defined types and tables:
CREATE OR REPLACE TYPE employee_t AS OBJECT (empid NUMBER, name CHAR(31), birthdate DATE, MEMBER FUNCTION age RETURN NUMBER, PRAGMA RESTRICT REFERENCES(age, RNPS, WNPS, WNDS) ); CREATE OR REPLACE TYPE BODY employee_t AS MEMBER FUNCTION age RETURN NUMBER IS var NUMBER; BEGIN var := months_between(ROUND(SYSDATE, 'YEAR'), ROUND(birthdate, 'YEAR'))/12; RETURN(var); END; END; / CREATE TABLE department (dno NUMBER, manager EMPLOYEE_T);
The following examples update and select from the object columns and method defined above.
UPDATE department d SET d.manager.empid = 100; SELECT d.manager.name, d.manager.age() FROM department d;
A decoded expression uses the special DECODE syntax:
To evaluate this expression, Oracle compares expr
to each search value one by one. If expr is equal to a search,
Oracle returns the corresponding result. If no match is found, Oracle
returns default, or, if default is omitted, returns null.
If expr and search contain character data, Oracle compares
them using nonpadded comparison semantics. For information on these semantics,
see the section "Datatype Comparison Rules".
The search, result, and default values can
be derived from expressions. Oracle evaluates each search value
only before comparing it to expr, rather than evaluating all search
values before comparing any of them with expr. Consequently, Oracle
never evaluates a search if a previous search is equal to
expr.
Oracle automatically converts expr and each search
value to the datatype of the first search value before comparing.
Oracle automatically converts the return value to the same datatype as
the first result. If the first result has the datatype CHAR
or if the first result is null, then Oracle converts the return
value to the datatype VARCHAR2. For information on datatype conversion,
see "Data Conversion".
In a DECODE expression, Oracle considers two nulls to be
equivalent. If expr is null, Oracle returns the result of
the first search that is also null.
The maximum number of components in the DECODE expression,
including expr, searches, results, and default
is 255.
This expression decodes the value DEPTNO. If DEPTNO is 10, the expression evaluates to 'ACCOUNTING'; if DEPTNO is 20, it evaluates to 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the expression returns 'NONE'.
DECODE (deptno,10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATION', 'NONE')
A list of expressions is a parenthesized series of expressions
separated by a comma.
An expression list can contain up to 1000 expressions. Some valid expression lists are:
10, 20, 40) ('SCOTT', 'BLAKE', 'TAYLOR') (LENGTH('MOOSE') * 57, -SQRT(144) + 72, 69)
A condition specifies a combination of one or more expressions
and logical operators that evaluates to either TRUE, FALSE, or unknown.
You must use this syntax whenever condition appears in SQL commands
in Chapter 4, "Commands".
You can use a condition in the WHERE clause of these statements:
You can use a condition in any of these clauses of the SELECT command:
A condition could be said to be of the "logical" datatype,
although Oracle does not formally support such a datatype.
The following simple condition always evaluates to TRUE:
1 = 1
The following more complex condition adds the SAL value to the COMM value (substituting the value 0 for null) and determines whether the sum is greater than the number constant 2500:
NVL(sal, 0) + NVL(comm, 0) > 2500
Logical operators can combine multiple conditions into a single condition. For example, you can use the AND operator to combine two conditions:
(1 = 1) AND (5 < 7)
Here are some valid conditions:
name = 'SMITH' emp.deptno = dept.deptno hiredate > '01-JAN-88' job IN ('PRESIDENT', 'CLERK', 'ANALYST') sal BETWEEN 500 AND 1000 comm IS NULL AND sal = 2000
Conditions can have several forms. The description of each
command in Chapter 4, "Commands", documents
the restrictions on the conditions in the command. The sections that follow
describe the various forms of conditions.
A Form I condition specifies a comparison with expressions
or subquery results.
For information on comparison operators, see "Comparison
Operators".
A Form II condition specifies a comparison with any or all members in a list or subquery.
See "Subqueries".
A Form III condition tests for membership in a list or subquery.
A Form IV condition tests for inclusion in a range.
A Form V condition tests for nulls.
A Form VI condition tests for existence of rows in a subquery.
A Form VII condition specifies a test involving pattern matching.
A Form VIII condition specifies a combination of other conditions.