Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Expressions, Conditions, and Queries, 2 of 4
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:
SELECT
statement
WHERE
clause and HAVING
clause
CONNECT
BY
, START
WITH
, and ORDER
BY
clauses
VALUES
clause of the INSERT
statement
SET
clause of the UPDATE
statement
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':
SET ename = LOWER(ename);
Expressions have several forms, as shown in the following syntax:
expr::=
Oracle does not accept all forms of expressions in all parts of all SQL statements. You must use appropriate expression notation whenever expr appears in conditions, SQL functions, or SQL statements in other parts of this reference. The description of each statement in Chapter 7, "SQL Statements", documents the restrictions on the expressions in the statement. The sections that follow describe and provide examples of the various forms of expressions.
A simple expression specifies column, pseudocolumn, constant, sequence number, or null.
simple_expression::=
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 materialized view. Qualifying a public synonym with "PUBLIC
" is supported only in data manipulation language (DML) statements, not data definition language (DDL) statements.
The pseudocolumn can be either LEVEL
, ROWID
, or ROWNUM
. You can use a pseudocolumn only with a table, not with a view or materialized view. NCHAR
and NVARCHAR2
are not valid pseudocolumn datatypes.
Some valid simple expressions are:
emp.ename 'this is a text string' 10 N'this is an NCHAR string'
A compound expression specifies a combination of other expressions.
compound_expression::=
Note that some combinations of functions are inappropriate and are rejected. For example, the LENGTH
function is inappropriate within an aggregate function.
Some valid compound expressions are:
('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY')
A variable expression specifies a host variable with an optional indicator variable. Note that this form of expression can appear only in embedded SQL statements or SQL statements processed in an Oracle Call Interface (OCI) program.
variable_expression::=
Some valid variable expressions are:
:employee_name INDICATOR :employee_name_indicator_var :department_location
A built-in function expression specifies a call to a single-row SQL function.
built_in_function_expression::=
Some valid built-in function expressions are:
LENGTH('BLAKE') ROUND(1234.567*43) SYSDATE
For information on built-in functions, see "SQL Functions". See also "Aggregate Functions".
A function expression specifies a call to
function_expression::=
Some valid user-defined function expressions are:
circle_area(radius) payroll.tax_rate(empno) scott.payrol.tax_rate(dependents, empno)@ny DBMS_LOB.getlength(column_name)
A type constructor expression specifies a call to a type constructor. The argument to the type constructor is any expression or subquery.
type_constructor_expression::=
If type_name is an object type, then the argument list must be an ordered list, 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.
If type_name is a varray or nested table type, then the argument list can contain zero or more arguments. Zero arguments implies construction of an empty collection. Otherwise, each argument corresponds to an element value whose type is the element type of the collection type.
If type_name is an object type, a varray, or a nested table type, the maximum number of arguments it can contain is 1000 minus some overhead.
This example shows the use of an expression in the call to a type constructor.
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;
This example illustrates the use of a subquery in the call to the type constructor.
CREATE TYPE employee AS OBJECT ( empno NUMBER, ename VARCHAR2(20)); CREATE TABLE emptbl of EMPLOYEE; INSERT INTO emptbl VALUES(7377, 'JOHN'); CREATE TYPE project AS OBJECT ( pname VARCHAR2(25), empref REF employee); CREATE TABLE depttbl (dno number, proj project); INSERT INTO depttbl values(10, project('SQL Extensions', (SELECT REF(p) FROM emptbl p WHERE ename='JOHN')));
A CAST
expression converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.
CAST_expression::=
CAST
allows you to convert built-in datatypes or collection-typed values of one type into another built-in datatype or collection type. You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.
For the operand, expr can be either a built-in datatype or a collection type, and subquery must return a single value of collection type or built-in type. MULTISET
informs Oracle to take the result set of the subquery and return a collection value. Table 5-1 shows which built-in datatypes can be cast into which other built-in datatypes. (CAST
does not support LONG
, LONG
RAW
, or any of the LOB
datatypes.)
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 Oracle8i.
SELECT CAST ('1997-10-22' AS DATE) FROM DUAL; SELECT * FROM t1 WHERE CAST (ROWID AS VARCHAR2) = '01234';
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);
This example casts 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:
SELECT CAST(d.addresses AS address_book_t) FROM dept d WHERE d.dno = 111;
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.ename, 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 CURSOR
expression returns a nested cursor. This form of expression is similar to the PL/SQL REF cursor.
CURSOR_expression::=
A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:
Restrictions: The following restrictions apply to the CURSOR
expression:
SELECT
statement that is not nested in any other query expression, except when it is a subquery of the CURSOR
expression itself.
SELECT
list of the query specification.
BIND
and EXECUTE
operations on nested cursors.
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;
An object access expression specifies attribute reference and method invocation.
object_access_expression::=
The column
parameter can be an object or REF
column.
When a type's member function is invoked in the context of a SQL statement, if the SELF
argument is null, Oracle returns null and the function is not invoked.
Examples in this section use the following user-defined types and tables:
CREATE OR REPLACE TYPE employee_t AS OBJECT (empid NUMBER, name VARCHAR2(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 := TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate) /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 DECODE
expression uses the special DECODE
syntax:
DECODE_expression::=
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')
CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. The syntax is:
CASE_expression::=
Oracle searches for the first WHEN
... THEN
pair for which condition is true.
CASE
expression is expr1.
At least one occurrence of expr1 or expr2 must be non-null.
The following statement finds the average salary of all employees in the EMP table. If an employee's salary is less than $2000, the CASE
expression uses $2000 instead.
SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END) from emp e;
An expression list is a series of expressions separated by a comma. The entire series is enclosed in parentheses.
expression_list::=
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)
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|