Introduction To SQL

Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request. This chapter provides background information on SQL as used by most database systems.

This chapter contains these topics:

* History of SQL

* SQL Standards

* Embedded SQL

* Lexical Conventions

SQL Standards

Oracle Corporation strives to comply with industry-accepted standards and participates actively in SQL standards committees. Industry-accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the standards are technically identical.

The latest SQL standard was adopted in July 1999 and is often called SQL:99. The formal names of this standard are:

* ANSI X3.135-1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")

* ISO/IEC 9075:1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")

How SQL Works

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:

* It processes sets of data as groups rather than as individual units.

* It provides automatic navigation to the data.

* It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.

Essentially, SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.

SQL provides statements for a variety of tasks, including:

* Querying data

* Inserting, updating, and deleting rows in a table

* Creating, replacing, altering, and dropping objects

* Controlling access to the database and its objects

* Guaranteeing database consistency and integrity

SQL unifies all of the above tasks in one consistent language.

Common Language for All Relational Databases

All major relational database management systems support SQL, so you can transfer all skills you have gained with SQL from one database to another. In addition, all programs written in SQL are portable. They can often be moved from one database to another with very little modification.

Embedded SQL

Embedded SQL refers to the use of standard SQL statements embedded within a procedural programming language. The embedded SQL statements are documented in the Oracle precompiler books.

Embedded SQL is a collection of these statements:

* All SQL commands, such as SELECT and INSERT, available with SQL with interactive tools

* Dynamic SQL execution commands, such as PREPARE and OPEN, which integrate the standard SQL statements with a procedural programming language

Embedded SQL also includes extensions to some standard SQL statements. Embedded SQL is supported by the Oracle precompilers. The Oracle precompilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers.

Each of these Oracle precompilers translates embedded SQL programs into a different procedural language:

* Pro*C/C++ precompiler

* Pro*COBOL precompiler

* SQL*Module for ADA

Lexical Conventions

The following lexical conventions for issuing SQL statements apply specifically to Oracle's implementation of SQL, but are generally acceptable in other SQL implementations.

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle evaluates the following two statements in the same manner:

SELECT last_name,salary*12,MONTHS_BETWEEN(hire_date, SYSDATE)
FROM employees;

SELECT last_name,
salary * 12,
MONTHS_BETWEEN( hire_date, SYSDATE )
FROM employees;

Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names.

DataTypes

Each value manipulated by Oracle has a datatype. A value's datatype associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.

When you create a table or cluster, you must specify a datatype for each of its columns. When you create a procedure or stored function, you must specify a datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the column's datatype. For example, if you insert '01-JAN-98' into a DATE column, Oracle treats the '01-JAN-98' character string as a DATE value after verifying that it translates to a valid date.

Oracle provides a number of built-in datatypes as well as several categories for user-defined types. The syntax of Oracle datatypes appears in the diagrams that follow. The text of this section is divided into the following sections:

Oracle Built-in Datatypes

Table 2-1 summarizes Oracle built-in datatypes.

Table 2-1 Built-In Datatype Summary

Code^a	Built-In Datatype	Description
1	`VARCHAR2(size) [BYTE \| CHAR]`	Variable-length character string having maximum length `size` bytes or characters. Maximum `size` is 4000 bytes, and minimum is 1 byte or 1 character. You must specify `size` for `VARCHAR2`. `BYTE` indicates that the column will have byte length semantics; `CHAR` indicates that the column will have character semantics.
2	`NUMBER(p,s)`	Number having precision `p` and scale `s`. The precision `p` can range from 1 to 38. The scale `s` can range from -84 to 127.
8	`LONG`	Character data of variable length up to 2 gigabytes, or 2³¹ -1 bytes.
12	`DATE`	Valid date range from January 1, 4712 BC to December 31, 9999 AD.
180	`TIMESTAMP` (`fractional_seconds_precision`)	Year, month, and day values of date, as well as hour, minute, and second values of time, where `fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values of `fractional_seconds_precision` are 0 to 9. The default is 6.
181	`TIMESTAMP` (`fractional_seconds_precision`) `WITH` `TIME` `ZONE`	All values of `TIMESTAMP` as well as time zone displacement value, where `fractional_seconds_precision` is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values are 0 to 9. The default is 6.
231	`TIMESTAMP` (`fractional_seconds_precision`) `WITH` `LOCAL` `TIME` `ZONE`	All values of `TIMESTAMP` `WITH` `TIME` `ZONE`, with the following exceptions: Data is normalized to the database time zone when it is stored in the database. When the data is retrieved, users see the data in the session time zone.
69	`ROWID`	Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the `ROWID` pseudocolumn.
96	`CHAR(size)[BYTE \| CHAR]`	Fixed-length character data of length `size` bytes. Maximum `size` is 2000 bytes. Default and minimum `size` is 1 byte. `BYTE` and `CHAR` have the same semantics as for `VARCHAR2`.
96	`NCHAR(size)`	Fixed-length character data of length `size` characters or bytes, depending on the choice of national character set. Maximum `size` is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum `size` is 1 character or 1 byte, depending on the character set.
112	`CLOB`	A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the `CHAR` database character set. Maximum size is 4 gigabytes.
112	`NCLOB`	A character large object containing unicode characters. Both fixed-width and variable-width character sets are supported, both using the `NCHAR` database character set. Maximum size is 4 gigabytes. Stores national character set data.
113	`BLOB`	A binary large object. Maximum size is 4 gigabytes.
114	`BFILE`	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
^aThe codes listed for the datatypes are used internally by Oracle. The datatype code of a column or object attribute is returned by the `DUMP` function.

Character Datatypes

Character datatypes store character (alphanumeric) data, which are words and free-form text, in the database character set or national character set. They are less restrictive than other datatypes and consequently have fewer properties. For example, character columns can store all alphanumeric values, but NUMBER columns can store only numeric values.

Character data is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC, specified when the database was created. Oracle supports both single-byte and multibyte character sets.

These datatypes are used for character data:

CHAR Datatype
NCHAR Datatype
NVARCHAR2 Datatype
VARCHAR2 Datatype

CHAR Datatype

The CHAR datatype specifies a fixed-length character string. Oracle subsequently ensures that all values stored in that column have the length specified by size. If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, Oracle returns an error.

The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored.

When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. If you use the CHAR qualifier, for example CHAR(10 CHAR), you supply the column length in characters. A character is technically a codepoint of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics.

VARCHAR2 Datatype

The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length. If you try to insert a value that exceeds the specified length, Oracle returns an error.

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual length of the string stored is permitted to be zero. Oracle treats zero-length strings as nulls. You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a codepoint of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

VARCHAR Datatype

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics.

NUMBER Datatype

The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10^-130 and 9.9...9 x 10¹²⁵ (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10¹²⁶, Oracle returns an error.

Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

Specify an integer using the following form:

NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

NUMBER

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

Scale and Precision

Specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, Oracle returns an error. If a value exceeds the scale, Oracle rounds it.

The following examples show how Oracle stores data using different precisions and scales.

7456123.89	`NUMBER`	7456123.89
7456123.89	`NUMBER(9)`	7456124
7456123.89	`NUMBER(9,2)`	7456123.89
7456123.89	`NUMBER(9,1)`	7456123.9
7456123.89	`NUMBER(6)`	exceeds precision
7456123.89	`NUMBER(7,-2)`	7456100
7456123.89	`NUMBER(7,2)`	exceeds precision

Negative Scale

If the scale is negative, the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

Scale Greater than Precision

You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, Oracle returns an error message. If the value exceeds the scale, Oracle rounds the value. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point. The following examples show the effects of a scale greater than precision:

Actual Data	Specified As	Stored As
.01234	`NUMBER(4,5)`	.01234
.00012	`NUMBER(4,5)`	.00012
.000127	`NUMBER(4,5)`	.00013
.0000012	`NUMBER(2,7)`	.0000012
.00000123	`NUMBER(2,7)`	.0000012

Floating-Point Numbers

Oracle lets you specify floating-point numbers, which can have a decimal point anywhere from the first to the last digit or can have no decimal point at all. An exponent may optionally be used following the number to increase the range (e.g. 1.777 e^-20). A scale value is not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not restricted.

You can specify floating-point numbers with the range of values discussed in "NUMBER Datatype". The format is defined in "Number Literals. Oracle also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

FLOAT specifies a floating-point number with decimal precision 38 or binary precision 126.
FLOAT(b) specifies a floating-point number with binary precision b. The precision b can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

LONG Datatype

LONG columns store variable-length character strings containing up to 2 gigabytes, or 2³¹-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer.

You can reference LONG columns in SQL statements in these places:

SELECT lists
SET clauses of UPDATE statements
VALUES clauses of INSERT statements

The use of LONG values is subject to some restrictions:

A table can contain only one LONG column.
You cannot create an object type with a LONG attribute.
LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
LONG columns cannot be indexed.
A stored function cannot return a LONG value.
You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.
Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
If a table has both LONG and LOB columns, you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
A table with LONG columns cannot be stored in a tablespace with automatic segment-space management.

LONG columns cannot appear in certain parts of SQL statements:

GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
The UNIQUE operator of a SELECT statement
The column list of a CREATE CLUSTER statement
The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
SQL built-in functions, expressions, or conditions
SELECT lists of queries containing GROUP BY clauses
SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
SELECT lists of CREATE TABLE ... AS SELECT statements
ALTER TABLE ... MOVE statements
SELECT lists in subqueries in INSERT statements

Triggers can use the LONG datatype in the following manner:

A SQL statement within a trigger can insert data into a LONG column.
If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), a LONG column can be referenced in a SQL statement within a trigger.
Variables in triggers cannot be declared using the LONG datatype.
:NEW and :OLD cannot be used with LONG columns.

You can use the Oracle Call Interface functions to retrieve a portion of a LONG value from the database.

DATE Datatype

The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.

You can specify a date value as a literal, or you can convert a character or numeric value to a date value with the TO_DATE function. To specify a date as a literal, you must use the Gregorian calendar. You can specify an ANSI date literal, as shown in this example:

DATE '1998-12-25'

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date literal, as in the following example:

TO_DATE('98-DEC-25:17:30','YY-MON-DD:HH24:MI')

The default date format for an Oracle date literal is specified by the initialization parameter NLS_DATE_FORMAT. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.

Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.

If you specify a date value without a time component, the default time is 12:00:00 am (midnight). If you specify a date value without a date, the default date is the first day of the current month.

Oracle DATE columns always contain both the date and time fields. If your queries use a date format without a time portion, you must ensure that the time fields in the DATE column are set to zero (that is, midnight). Otherwise, Oracle may not return the query results you expect. Here are some examples that assume a table my_table with a number column row_num and a DATE column datecol:

INSERT INTO my_table VALUES (1, SYSDATE);

INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));

SELECT * FROM my_table;

   ROW_NUM DATECOL

---------- ---------

         1 04-OCT-00

         2 04-OCT-00

SELECT * FROM my_table

   WHERE datecol = TO_DATE('04-OCT-00','DD-MON-YY');

   ROW_NUM DATECOL

---------- ---------

         2 04-OCT-00

If you know that the time fields of your DATE column are set to zero, then you can query your DATE column as shown in the second example above, or by using the DATE literal:

SELECT * FROM my_table WHERE datecol = DATE '2000-10-04';

However, if the DATE column contains nonzero time fields, then you must filter out the time fields in the query to get the correct result. For example:

SELECT * FROM my_table WHERE TRUNC(datecol) = DATE'2000-10-04';

Oracle applies the TRUNC function to each row in the query, so performance is better if you ensure the zero value of the time fields in your data. To ensure that the time fields are set to zero, use one of the following methods during inserts and updates:

Use the TO_DATE function to mask out the time fields:

·                INSERT INTO my_table VALUES

·                   (3, TO_DATE('4-APR-2000','DD-MON-YYYY'));

·

Use the DATE literal:

·                INSERT INTO my_table VALUES (4, '04-OCT-00');

·

Use the TRUNC function:

·                INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));

·

The date function SYSDATE returns the current system date and time. The function CURRENT_DATE returns the current session date.

Date Arithmetic

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the hiredate column of the sample table employees from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values.

Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

Using Julian Dates

A Julian date is the number of days since January 1, 4712 bc. Julian dates allow continuous dating from a common reference. You can use the date format model "J" with date functions TO_DATE and TO_CHAR to convert between Oracle DATE values and their Julian equivalents.

Example

This statement returns the Julian equivalent of January 1, 1997:

SELECT TO_CHAR(TO_DATE('01-01-1997', 'MM-DD-YYYY'),'J')

    FROM DUAL;

TO_CHAR

--------

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [ (fractional_seconds_precision)]

where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP as a literal as follows:

TIMESTAMP'1997-01-31 09:26:50.124'

TIMESTAMP WITH TIME ZONE Datatype

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone displacement in its value. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the TIMESTAMP WITH TIME ZONE datatype as follows:

TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME ZONE

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example,

TIMESTAMP '1999-04-15 8:00:00 -8:00'

is the same as

TIMESTAMP '1999-04-15 11:00:00 -5:00'

That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.

You can replace the UTC offset with the TZR (time zone region) format element. For example, the following example has the same value as the preceding example:

TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

To eliminate the ambiguity of boundary cases when the daylight savings time switches, use both the TZR and a corresponding TZD format element. The following example ensures that the preceding example will return a daylight savings time value:

TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT'

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then Oracle interprets the ambiguous datetime as standard time.

TIMESTAMP WITH LOCAL TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone displacement in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the TIMESTAMP WITH LOCAL TIME ZONE datatype as follows:

TIMESTAMP [ (fractional_seconds_precision) ] WITH LOCAL TIME ZONE

where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

There is no literal for TIMESTAMP WITH LOCAL TIME ZONE.

ROWID Datatype

Each row in the database has an address. You can examine a row's address by querying the pseudocolumn ROWID. Values of this pseudocolumn are hexadecimal strings representing the address of each row. These strings have the datatype ROWID. You can also create tables and clusters that contain actual columns having the ROWID datatype. Oracle does not guarantee that the values of such columns are valid rowids.

Restricted Rowids

Beginning with Oracle8, Oracle SQL incorporated an extended format for rowids to efficiently support partitioned tables and indexes and tablespace-relative data block addresses (DBAs) without ambiguity.

Character values representing rowids in Oracle7 and earlier releases are called restricted rowids. Their format is as follows:

block.row.file

where:

block is a hexadecimal string identifying the data block of the datafile containing the row. The length of this string depends on your operating system.
row is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0.
file is a hexadecimal string identifying the database file containing the row. The first datafile has the number 1. The length of this string depends on your operating system.

Extended Rowids

The extended ROWID datatype stored in a user column includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from the data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.

Extended rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, as well as the plus sign (+) and forward slash (/). Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

Compatibility and Migration

The restricted form of a rowid is still supported in Oracle9i for backward compatibility, but all tables return rowids in the extended format.

UROWID Datatype

Each row in a database has an address. However, the rows of some tables have addresses that are not physical or permanent or were not generated by Oracle. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.

Oracle uses "universal rowids" (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID pseudocolumn (as are the physical rowids of heap-organized tables).

Oracle creates logical rowids based on a table's primary key. The logical rowids do not change as long as the primary key does not change. The ROWID pseudocolumn of an index-organized table has a datatype of UROWID. You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized table (that is, using the SELECT ROWID statement). If you wish to store the rowids of an index-organized table, you can define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.

Literals

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all character literals; 5001 is a numeric literal. Character literals are enclosed in single quotation marks, which enable Oracle to distinguish them from schema object names.

This section contains these topics:

o Text Literals

o Integer Literals

o Number Literals

o Interval Literals

Many sql statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the sections that follow.

To specify a datetime or interval datatype as a literal, you must take into account any optional precisions included in the datatypes. Examples of specifying datetime and interval datatypes as literals are provided in the relevant sections of "Datatypes".

Text Literals

Text specifies a text or character literal. You must use this notation to specify values whenever 'text' or char appear in expressions, conditions, sql functions, and sql statements in other parts of this reference.

The syntax of text is as follows:

text::=

where

* N specifies representation of the literal using the national character set. Text entered using this notation is translated into the national character set by Oracle when used.

* c is any member of the user's character set, except a single quotation mark (').

* ' ' are two single quotation marks that begin and end text literals. To represent one single quotation mark within a literal, enter two single quotation marks.

A text literal must be enclosed in single quotation marks. This reference uses the terms text literal and character literal interchangeably.

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

* Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.

* A text literal can have a maximum length of 4000 bytes.

Here are some valid text literals:

'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'

Integer Literals

You must use the integer notation to specify an integer whenever integer appears in expressions, conditions, sql functions, and sql statements described in other parts of this reference.

The syntax of integer is as follows:

integer::=

where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer can store a maximum of 38 digits of precision.

Here are some valid integers:

7
+255

Number Literals

You must use the number notation to specify values whenever number appears in expressions, conditions, sql functions, and sql statements in other parts of this reference.

The syntax of number is as follows:

number::=

where

* + or - indicates a positive or negative value. If you omit the sign, a positive value is the default.

* digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.

* e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.

A number can store a maximum of 38 digits of precision.

If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, you must specify numeric literals with 'text' notation. In such cases, Oracle automatically converts the text literal to a numeric value.

For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:

'5,123'

Here are some valid representations of number:

25
+6.34
0.5
25e-03
-1

Nulls

If a column in a row has no value, then the column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand..

Nulls in SQL Functions

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null..

Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500..

Nulls with Comparison Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function..

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal..

Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN. .

Following table shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query..

If A is:	Condition	Evaluates to:
10	a IS NULL	FALSE
10	a IS NOT NULL	TRUE
NULL	a IS NULL	TRUE
NULL	a IS NOT NULL	FALSE
10	a = NULL	UNKNOWN
10	a != NULL	UNKNOWN
NULL	a = NULL	UNKNOWN
NULL	a != NULL	UNKNOWN
NULL	a = 10	UNKNOWN
NULL	a != 10	UNKNOWN

Pseudocolumns

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:

* CURRVAL and NEXTVAL

* LEVEL

* ROWID

* ROWNUM

CURRVAL and NEXTVAL

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in sql statements with these pseudocolumns:

CURRVAL

The CURRVAL pseudocolumn returns the current value of a sequence.

NEXTVAL

The NEXTVAL pseudocolumn increments the sequence and returns the next value.

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

Where to Use Sequence Values

You can use CURRVAL and NEXTVAL in:

* The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view

* The SELECT list of a subquery in an INSERT statement

* The VALUES clause of an INSERT statement

* The SET clause of an UPDATE statement

Restrictions: You cannot use CURRVAL and NEXTVAL:

* A subquery in a DELETE, SELECT, or UPDATE statement

* A query of a view or of a materialized view

* A SELECT statement with the DISTINCT operator

* A SELECT statement with a GROUP BY clause or ORDER BY clause

* A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

* The WHERE clause of a SELECT statement

* DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

* The condition of a CHECK constraint

Also, within a single sql statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

How to Use Sequence Values

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Within a single sql statement, Oracle will increment the sequence only once for each row. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

A sequence can be accessed by many users concurrently with no waiting or locking.

Finding the current value of a sequence: Example

This example selects the current value of the employee sequence in the sample schema hr:

SELECT employees_seq.currval
FROM DUAL;

Inserting sequence values into a table: Example

This example increments the employee sequence and uses its value for a new employee inserted into the sample table hr.employees:

INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe',
'555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null,
30);

Reusing the current value of a sequence: Example

This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:

INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);

INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);

LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.

To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses.

ROWID

For each row in the database, the ROWID pseudocolumn returns a row's address. Oracle9i rowid values contain information necessary to locate a row:

* The data object number of the object

* Which data block in the datafile

* Which row in the data block (first row is 0)

* Which datafile (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Values of the ROWID pseudocolumn have the datatype ROWID or UROWID.

Rowid values have several important uses:

* They are the fastest way to access a single row.

* They can show you how a table's rows are stored.

* They are unique identifiers for rows in a table.

You should not use ROWID as a table's primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its rowid may change. If you delete a row, Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

Example

This statement selects the address of all rows that contain data for employees in department 20:

SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
SET column1 = ROWNUM;

SQL - Comments

You can associate comments with sql statements and schema objects.

Comments Within SQL Statements

Comments within sql statements do not affect the statement execution, but they may make your application easier for you to read and maintain. You may want to include a comment in a statement that describes the statement's purpose within your application.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using either of these means:

* Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.

* Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

A sql statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example

These statements contain many comments:

SELECT last_name, salary + NVL(commission_pct, 0),
job_id, e.department_id
/* Select all employees whose compensation is
greater than that of Pataballa.*/
FROM employees e, departments d
/*The DEPARTMENTS table is used to get the department name.*/
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct,0) > /* Subquery: */
(SELECT salary + NVL(commission_pct,0)
/* total compensation is salar + commission_pct */
FROM employees
WHERE last_name = 'Pataballa');

SELECT last_name, -- select the name
salary + NVL(commission_pct, 0),-- total compensation
job_id, -- job
e.department_id -- and department
FROM employees e, -- of all employees
departments d
WHERE e.department_id = d.department_id
AND salary + NVL(commission_pct, 0) > -- whose compensation
-- is greater than
(SELECT salary + NVL(commission_pct,0) -- the compensation
FROM employees
WHERE last_name = 'Pataballa') -- of Pataballa.

;

Comments on Schema Objects

You can associate a comment with a table, view, materialized view, or column using the COMMENT command. Comments associated with schema objects are stored in the data dictionary.

Hints

You can use comments in a sql statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses these hints as suggestions for choosing an execution plan for the statement.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, or DELETE keyword. The syntax below shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

* DELETE, INSERT, SELECT, or UPDATE is a DELETE, INSERT, SELECT, or UPDATE keyword that begins a statement block. Comments containing hints can appear only after these keywords.

* + is a plus sign that causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter (no space is permitted).

* hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, separate the hints by at least one space.

* text is other commenting text that can be interspersed with the hints.

all_rows_hint::=

The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

and_equal_hint::=

The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.

append_hint::=

The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. (Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. In addition, direct-path INSERT bypasses the buffer cache and ignores integrity constraints. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

cache_hint::=

The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.

choose_hint::=

The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a sql statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.

cluster_hint::=

The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.

cursor_sharing_exact_hint::=

Oracle can replace literals in sql statements with bind variables if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the sql statement without any attempt to replace literals by bind variables.

driving_site_hint::=

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.

fact_hint::=

The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.

first_rows_hint::=

The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual sql statement for fast response. FIRST_ROWS(n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.

full_hint::=

The FULL hint explicitly chooses a full table scan for the specified table.

hash_hint::=

The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.

hash_aj_hint::=

For a specific query, place the HASH_SJ, MERGE_SJ, or NL_SJ hint into the EXISTS subquery. HASH_SJ uses a hash semi-join, MERGE_SJ uses a sort merge semi-join, and NL_SJ uses a nested loop semi-join.

hash_sj_hint::=

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.

index_asc_hint::=

The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.

index_combine_hint::=

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.

index_desc_hint::=

The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

index_ffs_hint::=

The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.

leading_hint::=

The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

merge_hint::=

The MERGE hint lets you merge a view on a per-query basis.

If a view's query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view's query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.

Complex merging is not cost-based--that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.

merge_aj_hint::=

See HASH_AJ hint.

merge_sj_hint::=

See HASH_SJ hint.

nl_aj_hint::=

See HASH_AJ hint.

nl_sj_hint::=

See HASH_SJ hint.

noappend_hint::=

The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

nocache_hint::=

The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.

no_expand_hint::=

The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.

no_fact_hint::=

The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.

no_index_hint::=

The NO_INDEX hint explicitly disallows a set of indexes for the specified table.

no_merge_hint::=

The NO_MERGE hint causes Oracle not to merge mergeable views.

noparallel_hint::=

The NOPARALLEL hint overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.

Restriction: You cannot parallelize a query involving a nested table.

noparallel_index_hint::=

The NOPARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.

no_push_pred_hint::=

The NO_PUSH_PRED hint prevents pushing of a join predicate into the view.

norewrite_hint::=

The NOREWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. Use the NOREWRITE hint on any query block of a request.

no_unnest_hint::=

If you enabled subquery unnesting with the UNNEST_SUBQUERY parameter, then the NO_UNNEST hint turns it off for specific subquery blocks.

ordered_hint::=

The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.

If you omit the ORDERED hint from a sql statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.

ordered_predicates_hint::=

The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.

If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the order specified by the following rules. Predicates:

* Without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.

* With user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.

* With user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.

* Not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.

* With subqueries are evaluated last in the order specified in the WHERE clause. parallel_hint::=

The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion.

parallel_index_hint::=

The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.

pq_distribute_hint::=

The PQ_DISTRIBUTE hint improves parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.

Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint if both tables are serial.

push_pred_hint::=

The PUSH_PRED hint forces pushing of a join predicate into the view.

push_subq_hint::=

The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

rewrite_hint::=

The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.

Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.

rowid_hint::=

The ROWID hint explicitly chooses a table scan by rowid for the specified table.

rule_hint::=

The RULE hint explicitly chooses rule-based optimization for a statement block. It also makes the optimizer ignore other hints specified for the statement block.

star_hint::=

The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.

star_transformation_hint::=

The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

unnest_hint::=

If the UNNEST_SUBQUERY parameter is set to true, then the UNNEST hint checks the subquery block for validity only. If it is valid, then subquery unnesting is enabled without Oracle checking the heuristics.

use_concat_hint::=

The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

The USE_CONCAT hint turns off IN-list processing and OR-expands all disjunctions, including IN-lists.

use_hash_hint::=

The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join.

use_merge_hint::=

The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join.

use_nl_hint::=

The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table.

SQL - Database Objects

Oracle recognizes objects that are associated with a particular schema and objects that are not associated with a particular schema, as described in the sections that follow.

Schema Objects

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

* Clusters

* Constraints

* Database links

* Database triggers

* Dimensions

* External procedure libraries

* Index-organized tables

* Indexes

* Indextypes

* Java classes, Java resources, Java sources

* Materialized views

* Materialized view logs

* Object tables

* Object types

* Object views

* Operators

* Packages

* Sequences

* Stored functions, stored procedures

* Synonyms

* Tables

* Views

Nonschema Objects

Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:

* Contexts

* Directories

* Parameter files (PFILEs) and server parameter files (SPFILEs)

* Profiles

* Roles

* Rollback segments

* Tablespaces

* Users

Parts of Schema Objects

Some schema objects are made up of parts that you can or must name, such as:

* Columns in a table or view

* Index and table partitions and subpartitions

* Integrity constraints on a table

* Packaged procedures, packaged stored functions, and other objects stored within a package

Partitioned Tables and Indexes

Tables and indexes can be partitioned. When partitioned, these schema objects consist of a number of parts called partitions, all of which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index columns.

When you partition a table or index using the range method, you specify a maximum value for the partitioning key column(s) for each partition. When you partition a table or index using the list method, you specify actual values for the partitioning key column(s) for each partition. When you partition a table or index using the hash method, you instruct Oracle to distribute the rows of the table into partitions based on a system-defined hash function on the partitioning key column(s). When you partition a table or index using the composite-partitioning method, you specify ranges for the partitions, and Oracle distributes the rows in each partition into one or more hash subpartitions based on a hash function. Each subpartition of a table or index partitioned using the composite method has the same logical attributes.

Partition-Extended and Subpartition-Extended Names

Partition-extended and subpartition-extended names let you perform some partition-level and subpartition-level operations, such as deleting all rows from a partition or subpartition, on only one partition or subpartition. Without extended names, such operations would require that you specify a predicate (WHERE clause). For range- and list-partitioned tables, trying to phrase a partition-level operation with a predicate can be cumbersome, especially when the range partitioning key uses more than one column. For hash partitions and subpartitions, using a predicate is more difficult still, because these partitions and subpartitions are based on a system-defined hash function.

Partition-extended names let you use partitions as if they were tables. An advantage of this method, which is most useful for range-partitioned tables, is that you can build partition-level access control mechanisms by granting (or revoking) privileges on these views to (or from) other users or roles.To use a partition as a table, create a view by selecting data from a single partition, and then use the view as a table.

You can specify partition-extended or subpartition-extended table names for the following DML statements:

* DELETE

* INSERT

* LOCK TABLE

* SELECT

* UPDATE

Syntax

The basic syntax for using partition-extended and subpartition-extended table names is:

partition_extended_name::=

Partition Extended Name

Restrictions

Currently, the use of partition-extended and subpartition-extended table names has the following restrictions:

* No remote tables: A partition-extended or subpartition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions and subpartitions, create a view at the remote site that uses the extended table name syntax and then refer to the remote view.

* No synonyms: A partition or subpartition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.

Example

In the following statement, sales is a partitioned table with partition jan97. You can create a view of the single partition jan97, and then use it as if it were a table. This example deletes rows from the partition.

CREATE VIEW sales_Q1_2000 AS
SELECT * FROM sales PARTITION (Q1_2000);

DELETE FROM sales_Q1_2000 WHERE amount < 0;

SQL - Schema Object Names and Qualifiers

This section provides:

* Rules for naming schema objects and schema object location qualifiers

* Guidelines for naming schema objects and qualifiers

SQL - Schema Object Naming Rules

The following rules apply when naming schema objects:

1. Names must be from 1 to 30 bytes long with these exceptions:

* Names of databases are limited to 8 bytes.

* Names of database links can be as long as 128 bytes.

2. Names cannot contain quotation marks.

3. Names are not case sensitive.

4. A name must begin with an alphabetic character from your database character set unless surrounded by double quotation marks.

5. Names can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Oracle strongly discourages you from using $ and #. Names of database links can also contain periods (.) and "at" signs (@).

If your database character set contains multibyte characters, Oracle recommends that each name for a user or a role contain at least one single-byte character.

6. A name cannot be an Oracle reserved word.

Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words.

7. Do not use the word DUAL as a name for an object or part. DUAL is the name of a dummy table.

8. The Oracle SQL language contains other words that have special meanings. These words include datatypes, function names, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally. Therefore, if you use these words as names for objects and object parts, your SQL statements may be more difficult to read and may lead to unpredictable results.

In particular, do not use words beginning with "SYS_" as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.

9. Within a namespace, no two objects can have the same name.

The following schema objects share one namespace:

* Tables

* Views

* Sequences

* Private synonyms

* Stand-alone procedures

* Stand-alone stored functions

* Packages

* Materialized views

* User-defined types

Each of the following schema objects has its own namespace:

* Indexes

* Constraints

* Clusters

* Database triggers

* Private database links

* Dimensions

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

Each of the following nonschema objects also has its own namespace:

* User roles

* Public synonyms

* Public database links

* Tablespaces

* Rollback segments

* Profiles

* Parameter files (PFILEs) and server parameter files (SPFILEs)

9. Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.

10. Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name.

11. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

12. A name can be enclosed in double quotation marks. Such names can contain any combination of characters, including spaces, ignoring rules 3 through 7 in this list. This exception is allowed for portability, but Oracle recommends that you do not break rules 3 through 7. If you give a schema object a name enclosed in double quotation marks, you must use double quotation marks whenever you refer to the object.

Enclosing a name in double quotes enables it to:

* Contain spaces

* Be case sensitive

* Begin with a character other than an alphabetic character, such as a numeric character

* Contain characters other than alphanumeric characters and _, $, and #

* Be a reserved word

By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

emp
"emp"
"Emp"
"EMP "

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

emp
EMP
"EMP"

If you give a user or password a quoted name, the name cannot contain lowercase letters.

Database link names cannot be quoted.

SQL - Schema Object Naming Examples

The following examples are valid schema object names:

ename
horse
scott.hiredate
"EVEN THIS & THAT!"
a_very_long_and_valid_name

Although column aliases, table aliases, usernames, and passwords are not objects or parts of objects, they must also follow these naming rules with these exceptions:

* Column aliases and table aliases exist only for the execution of a single SQL statement and are not stored in the database, so rule 12 does not apply to them.

* Passwords do not have namespaces, so rule 9 does not apply to them.

* Do not use quotation marks to make usernames and passwords case sensitive.

SQL - Schema Object Naming Guidelines

Here are several helpful guidelines for naming objects and their parts:

* Use full, descriptive, pronounceable names (or well-known abbreviations).

* Use consistent naming rules.

* Use the same name to describe the same entity or attribute across tables.

When naming objects, balance the objective of keeping names short and easy to use with the objective of making names as descriptive as possible. When in doubt, choose the more descriptive name, because the objects in the database may be used by many people over a period of time. Your counterpart ten years from now may have difficulty understanding a database with a name like pmdd instead of payment_due_date.

Using consistent naming rules helps users understand the part that each table plays in your application. One such rule might be to begin the names of all tables belonging to the FINANCE application with fin_.

Use the same names to describe the same things across tables. For example, the department number columns of the sample employees and departments tables are both named deptno.

SQL - Queries and Subqueries

A query is an operation that retrieves data from one or more tables or views. In this reference, a top-level SELECT statement is called a query, and a query nested within another SQL statement is called a subquery.

This section describes some types of queries and subqueries and how to use them..

Creating Simple Queries

The list of expressions that appears after the SELECT keyword and before the FROM clause is called the select list. Within the select list, you specify one or more columns in the set of rows you want Oracle to return from one or more tables, views, or materialized views. The number of columns, as well as their datatype and length, are determined by the elements of the select list..

If two or more tables have some column names in common, you must qualify column names with names of tables. Otherwise, fully qualified column names are optional. However, it is always a good idea to qualify table and column references explicitly. Oracle often does less work with fully qualified table and column names..

You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query..

You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement..

Hierarchical Queries

If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause: .

* START WITH specifies the root row(s) of the hierarchy..

* CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. Some part of the connect_by_condition must use the PRIOR operator to refer to the parent row..

* PRIOR evaluates the connect_by_condition for the parent row of the current row in a hierarchical query. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators..

The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:.

* If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing..

* Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy..

Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:.

1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list)..

2. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition..

3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows..

4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row..

5. If the query contains a WHERE clause without a join, Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. .

To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery..

If the CONNECT BY condition results in a loop in the hierarchy, Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row..

Examples.

The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:.

SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.

EMPLOYEE_ID LAST_NAME                 MANAGER_ID

----------- ------------------------- ----------

        101 Kochhar                          100

        108 Greenberg                        101

        109 Faviet                           108

        110 Chen                             108

        111 Sciarra                          108

        112 Urman                            108

        113 Popp                             108

        200 Whalen                           101

The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:.

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL

----------- ------------------------- ---------- ----------

        101 Kochhar                          100          1

        108 Greenberg                        101          2

        109 Faviet                           108          3

        110 Chen                             108          3

        111 Sciarra                          108          3

        112 Urman                            108          3

        113 Popp                             108          3

The UNION [ALL], INTERSECT, MINUS Operators

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. 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..

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:.

* If both queries select values of datatype CHAR, the returned values have datatype CHAR..

* If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2..

Restrictions on set operators:

* The set operators are not valid on columns of type BLOB, CLOB, BFILE, varray, or nested table..

* The UNION, INTERSECT, and MINUS operators are not valid on LONG columns..

* To reference a column, you must use an alias to name the column..

* You cannot also specify the for_update_clause with these set operators..

* You cannot specify the order_by_clause in the subquery of these operators..

* You cannot use these operators in SELECT statements containing TABLE collection expressions. .

The following examples combine the two query results with each of the set operators..

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:.

SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse" FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name
FROM warehouses;.

LOCATION_ID Department Warehouse ----------- --------------------- -------------------------- 1400 IT 1400 Southlake, Texas 1500 Shipping 1500 San Francisco 1600 New Jersey 1700 Accounting 1700 Administration 1700 Benefits 1700 Construction . . ..

UNION ALL Example

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:.

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;.

SELECT location_id FROM locations
UNION ALL
SELECT location_id FROM departments;.

A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator..

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:.

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;.

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:.

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;.

Sorting Query Results

Use the ORDER BY clause to order the rows selected by a query. Sorting by position is useful in the following cases:.

* To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause..

* For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query..

The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause. .

Joins

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity. .

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list..

To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables..

In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query. .

Equijoins

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE. .

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition. .

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, the optimizer may choose a join order that avoids producing an intermediate Cartesian product..

Inner Joins

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition..

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition..

* To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B..

* To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A..

* To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax..

Oracle Corporation recommends that you use the ANSI OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the ANSI syntax:.

* You cannot specify the (+) operator in a query block that also contains ANSI JOIN syntax..

* The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view..

* If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join..

* The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator..

* A condition containing the (+) operator cannot be combined with another condition using the OR logical operator..

* A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression..

* A condition cannot compare any column marked with the (+) operator with a subquery..

If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle will return only the results of a simple join..

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. .

Using Subqueries

A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery..

A subquery can contain another subquery. Oracle imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause..

If columns in a subquery have the same name as columns in the containing statement, you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view..

Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement..

A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department. .

Use subqueries for the following purposes:.

* To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement.

* To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement.

* To define one or more values to be assigned to existing rows in an UPDATE statement.

* To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements.

* To define a table to be operated on by a containing query..

You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements..

Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Outer references ("left-correlated subqueries") are allowed only in the FROM clause of a SELECT statement. .

Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where expr is called for in syntax. .

Unnesting of Nested Subqueries

Subqueries are "nested" when they appear in the WHERE clause of the parent statement. When Oracle evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins..

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the subquery's immediate outer query block..

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:.

* Uncorrelated IN subqueries.

* IN and EXISTS correlated subqueries as long, as they do not contain aggregate functions or a GROUP BY clause.

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:.

* You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery..

* You can unnest other subqueries by specifying the UNNEST hint in the subquery .

Selecting from the DUAL Table

DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. .

Distributed Queries

Oracle's distributed database management system architecture lets you access data in remote databases using Oracle Net and an Oracle server. You can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table, view, or materialized view. .

Restrictions on Distributed Queries

Distributed queries are currently subject to the restriction that all tables locked by a FOR UPDATE clause and all tables with LONG columns selected by the query must be located on the same database. For example, the following statement will raise an error:.

SELECT employees_ny.*
FROM employees_ny@ny, departments
WHERE employees_ny.department_id = departments.department_id
AND departments.department_name = 'ACCOUNTING'
FOR UPDATE OF employees_ny.salary; .

The following statement fails because it selects long_column, a LONG value, from the employees_review table on the ny database and locks the employees table on the local database:.

SELECT employees.employee_id, review.long_column, employees.salary
FROM employees, employees_review@ny review
WHERE employees.employee_id = employees_review.employee_id
FOR UPDATE OF employees.salary;.

In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.

SQL - Types of SQL Statements

The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:

* Data Definition Language (DDL) Statements

* Data Manipulation Language (DML) Statements

* Transaction Control Statements

* Session Control Statements

* System Control Statements

Data Definition Language (DDL) Statements

Data definition language (DDL) statements enable you to perform these tasks:

* Create, alter, and drop schema objects

* Grant and revoke privileges and roles

* Analyze information on a table, index, or cluster

* Establish auditing options

* Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.

Oracle implicitly commits the current transaction before and after every DDL statement.

Many DDL statements may cause Oracle to recompile or reauthorize schema objects. For information on how Oracle recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this

DDL statements are supported by PL/SQL with the use of the DBMS_SQL package.

Data Definition Language Statements

ALTER CLUSTER
ALTER DATABASE
ALTER DIMENSION
ALTER FUNCTION
ALTER INDEX
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
ALTER PACKAGE
ALTER PROCEDURE
ALTER PROFILE
ALTER RESOURCE COST
ALTER ROLE
ALTER ROLLBACK SEGMENT
ALTER SEQUENCE
ALTER TABLE
ALTER TABLESPACE
ALTER TRIGGER
ALTER TYPE
ALTER USER
ALTER VIEW
ANALYZE
ASSOCIATE STATISTICS
AUDIT
COMMENT
CREATE CLUSTER
CREATE CONTEXT
CREATE CONTROLFILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE DIMENSION
CREATE DIRECTORY
CREATE FUNCTION
CREATE INDEX
CREATE INDEXTYPE
CREATE LIBRARY
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PFILE
CREATE PROCEDURE
CREATE PROFILE
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE SCHEMA
CREATE SEQUENCE
CREATE SYNONYM
CREATE SPFILE
CREATE TABLE
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
DISASSOCIATE STATISTICS
DROP CLUSTER
DROP CONTEXT
DROP DATABASE LINK
DROP DIMENSION
DROP DIRECTORY
DROP FUNCTION
DROP INDEX
DROP INDEXTYPE
DROP LIBRARY
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DROP OPERATOR
DROP PACKAGE
DROP PROCEDURE
DROP PROFILE
DROP ROLE
DROP ROLLBACK SEGMENT
DROP SEQUENCE
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP TYPE
DROP USER
DROP VIEW
GRANT
NOAUDIT
RENAME
REVOKE
TRUNCATE

Data Manipulation Language (DML) Statements

Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.

CALL
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
MERGE
SELECT
UPDATE
The CALL and EXPLAIN PLAN statements are supported in PL/SQL only when executed dynamically. All other DML statements are fully supported in PL/SQL.

Transaction Control Statements

Transaction control statements manage changes made by DML statements.

Transaction Control Statements

Statement

COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
All transaction control statements except certain forms of the COMMIT and ROLLBACK commands are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK.

Session Control Statements

Session control statements dynamically manage the properties of a user session. These statements do not implicitly commit the current transaction.

PL/SQL does not support session control statements.

Session Control Statements

Statement

ALTER SESSION

SET ROLE

System Control Statement

The single system control statement dynamically manages the properties of an Oracle instance. This statement does not implicitly commit the current transaction.

ALTER SYSTEM is not supported in PL/SQL.

SQL - SAVEPOINT to UPDATE

SAVEPOINT

Purpose

Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.

Keywords and Parameters

savepoint

Specify the name of the savepoint to be created.

Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.

Example

To update Banda's and Greene's salary in the demo table hr.employees, check that the total department salary does not exceed 314,000, then reenter Greene's salary, enter:

UPDATE employees
SET salary = 7000
WHERE last_name = 'Banda';
SAVEPOINT banda_sal;

UPDATE employees
SET salary = 12000
WHERE last_name = 'Greene';
SAVEPOINT greene_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT banda_sal;

UPDATE employees
SET salary = 11000
WHERE last_name = 'Greene';
COMMIT;

SET CONSTRAINT[S]

Purpose

Use the SET CONSTRAINTS statement to specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed.

Prerequisites

To specify when a deferrable constraint is checked, you must have SELECT privilege on the table to which the constraint is applied unless the table is in your schema.

Keywords and Parameters

constraint

Specify the name of one or more integrity constraints.

ALL

Specify ALL to set all deferrable constraints for this transaction.

IMMEDIATE

Specify IMMEDIATE to indicate that the conditions specified by the deferrable constraint are checked immediately after each DML statement.

DEFERRED

Specify DEFERRED to indicate that the conditions specified by the deferrable constraint are checked when the transaction is committed.

Examples

Setting Constraints Examples

The following statement sets all deferrable constraints in this transaction to be checked immediately following each DML statement:

SET CONSTRAINTS ALL IMMEDIATE;

The following statement checks three deferred constraints when the transaction is committed:

SET CONSTRAINTS emp_job_nn, emp_salary_min,
hr.emp_job_fk@houston DEFERRED;

SET ROLE

Purpose

Use the SET ROLE statement to enable and disable roles for your current session.

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES.

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

Prerequisites

You must already have been granted the roles that you name in the SET ROLE statement.

Keywords and Parameters

role

Specify a role to be enabled for the current session. Any roles not listed and not already enabled are disabled for the current session.

In the IDENTIFIED BY password clause, specify the password for a role. If the role has a password, you must specify the password to enable the role.

Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.

ALL

Specify ALL to enable all roles granted to you for the current session except those optionally listed in the EXCEPT clause.

Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.

If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.

Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.

NONE

Specify NONE to disable all roles for the current session, including the DEFAULT role.

Examples

Setting Roles Examples

To enable the role gardener identified by the password marigolds for your current session, issue the following statement:

SET ROLE gardener IDENTIFIED BY marigolds;

To enable all roles granted to you for the current session, issue the following statement:

SET ROLE ALL;

To enable all roles granted to you except dw_manager, issue the following statement:

SET ROLE ALL EXCEPT dw_manager;

To disable all roles granted to you for the current session, issue the following statement:

SET ROLE NONE;

SET TRANSACTION

Purpose

Use the SET TRANSACTION statement to establish the current transaction as read only or read write, establish its isolation level, or assign it to a specified rollback segment.

The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. Oracle implicitly commits the current transaction before and after executing a data definition language (DDL) statement.

Prerequisites

If you use a SET TRANSACTION statement, it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTION statement.

Keywords and Parameters

READ ONLY

The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.

All subsequent queries in that transaction only see changes committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables.

Restriction: Only the following statements are permitted in a read-only transaction:

* Subqueries (that is, SELECT statements without the for_update_clause)

* LOCK TABLE

* SET ROLE

* ALTER SESSION

* ALTER SYSTEM

READ WRITE

Specify READ WRITE to establish the current transaction as a read/write transaction. This clause establishes statement-level read consistency, which is the default.

Restriction: You cannot toggle between transaction-level and statement-level read consistency in the same transaction.

ISOLATION LEVEL Clause

Use the ISOLATION LEVEL clause to specify how transactions containing database modifications are handled.

* The SERIALIAZBLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, then the DML statement fails.

* The READ COMMITTED setting is the default Oracle transaction behavior. If the transaction contains DML that requires row locks held by another transaction, then the DML statement waits until the row locks are released.

USE ROLLBACK SEGMENT Clause

Specify USE ROLLBACK SEGMENT to assign the current transaction to the specified rollback segment. This clause also implicitly establishes the transaction as a read/write transaction.

This clause lets you to assign transactions of different types to rollback segments of different sizes. For example:

* If no long-running queries are concurrently reading the same tables, you can assign small transactions to small rollback segments, which are more likely to remain in memory.

* You can assign transactions that modify tables that are concurrently being read by long-running queries to large rollback segments, so that the rollback information needed for the read-consistent queries is not overwritten.

* You can assign transactions that insert, update, or delete large amounts of data to rollback segments large enough to hold the rollback information for the transaction.

You cannot use the READ ONLY clause and the USE ROLLBACK SEGMENT clause in a single SET TRANSACTION statement or in different statements in the same transaction. Read-only transactions do not generate rollback information and therefore are not assigned rollback segments.

NAME Clause

Use the NAME clause to assign a name to the current transaction. This clause is especially useful in distributed database environments when you must identify and resolve in-doubt transactions. The text string is limited to 255 bytes.

If you specify a name for a distributed transaction, when the transaction commits, the name becomes the commit comment, overriding any comment specified explicitly in the COMMIT statement.

Examples

The following statements could be run at midnight of the last day of every month to count the products and quantities on hand in the Toronto warehouse in the sample Order Entry (oe) schema. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse.

COMMIT;
SET TRANSACTION READ ONLY NAME 'Toronto';
SELECT product_id, quantity_on_hand FROM inventories
WHERE warehouse_id = 5;
COMMIT;

The first COMMIT statement ensures that SET TRANSACTION is the first statement in the transaction. The last COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.

The following statement assigns your current transaction to the rollback segment rs_1:

SET TRANSACTION USE ROLLBACK SEGMENT rs_1;

TRUNCATE

Caution:

You cannot roll back a TRUNCATE statement.

Purpose

Use the TRUNCATE statement to remove all rows from a table or cluster and reset the STORAGE parameters to the values when the table or cluster was created.

Removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates the table's dependent objects, requires you to regrant object privileges on the table, and requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its storage parameters. Truncating has none of these effects.

Prerequisites

To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.

Keywords and Parameters

TABLE Clause

Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema, Oracle assumes the table is in your own cluster.

* You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.

* Oracle changes the NEXT storage parameter of table to be the size of the last extent deleted from the segment in the process of truncation.

* Oracle also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.

* If table is not empty, Oracle marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table.

* For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.

# If table (whether it is a regular or index-organized table) contains LOB columns, all LOB data and LOB index segments are truncated.

# If table is partitioned, all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.

Restrictions:

* You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

* You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

* If table belongs to a hierarchy, it must be the root of the hierarchy.

* If a domain index is defined on table, neither the index nor any index partitions can be marked IN_PROGRESS.

MATERIALIZED VIEW LOG Clause

The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.

Restrictions:

* You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

* You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

* If table belongs to a hierarchy, it must be the root of the hierarchy.

* If a domain index is defined on table, neither the index nor any index partitions can be marked IN_PROGRESS.

MATERIALIZED VIEW LOG Clause

The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.

CLUSTER Clause

Specify the schema and name of the cluster to be truncated. You can truncate only an indexed cluster, not a hash cluster. If you omit schema, Oracle assumes the cluster is in your own schema.

When you truncate a cluster, Oracle also automatically deletes all data in the indexes of the cluster tables.

STORAGE Clauses

The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.

DROP STORAGE

Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. This is the default. REUSE STORAGE

Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations.

Examples

Simple TRUNCATE Example

The following statement removes all rows from the employees table and returns the freed space to the tablespace containing employees:

TRUNCATE TABLE employees;

The above statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.

Retaining Free Space After Truncate Example

The following statement removes all rows from all tables in the personnel cluster, but leaves the freed space allocated to the tables:

TRUNCATE CLUSTER personnel REUSE STORAGE

The above statement also removes all data from all indexes on the tables in the personnel cluster.

Preserving Materialized View Logs After Truncate Example

The following statements are examples of truncate statements that preserve materialized view logs:

TRUNCATE TABLE sales PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE orders;

UPDATE

Purpose

Use the UPDATE statement to change existing values in a table or in a view's base table

Prerequisites

For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.

For you to update values in the base table of a view:

* You must have UPDATE privilege on the view, and

* Whoever owns the schema containing the view must have UPDATE privilege on the base table.

If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table whose column values you are referencing (such as the columns in a where_clause) to perform an UPDATE.

The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.

Examples

Simple Examples

The following statement gives null commissions to all employees with the job sa_clerk:

UPDATE employees
SET commission_pct = NULL
WHERE job = 'SA_CLERK';

The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:

UPDATE employees SET
job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
WHERE first_name||' '||last_name = 'Douglas Grant';

The following statement increases the balance of bank account number 5001 in the accounts table on a remote database accessible through the database link boston:

UPDATE accounts@boston
SET balance = balance + 500
WHERE acc_no = 5001;

PARTITION Example

The following example updates values in a single partition of the sales table:

UPDATE sales PARTITION (sales_q1_1999) s
SET s.promo_id = 494;

Complex Example

The next example shows the following syntactic constructs of the UPDATE statement:

* Both forms of the update_set_clause together in a single statement

* A correlated subquery

* A where_clause to limit the updated rows

UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);

The above UPDATE statement performs the following operations:

* Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)

* Sets department_id for these employees to the department_id corresponding to Bombay (location_id 2100)

* Sets each employee's salary to 1.1 times the average salary of their department

* Sets each employee's commission to 1.5 times the average commission of their department

SET VALUE Example

The following statement updates a row of object table table1 by selecting a row from another object table table2:

UPDATE table1 p SET VALUE(p) =
(SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
WHERE p.id = 10;

The subquery uses the value object reference function in its expression.

Correlated Update Example

The following example updates particular rows of the projs nested table corresponding to the department whose department equals 123:

UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1
WHERE p.pno IN (123, 456);

RETURNING Clause Example

The following example returns values from the updated row and stores the result in PL/SQL variables bnd1, bnd2, bnd3:

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;

SQL-Book