Syntax query
[ ORDER BY clause ]
[ result offset clause ]
[ fetch first clause ]
[ FOR UPDATE clause ]
[ WITH { RR | RS | CS | UR } ]
A SELECT statement consists of a query with an optional ORDER BY clause, an optional
result offset clause,
an optional fetch first clause,
an optional FOR UPDATE clause,
and an optional isolation level.
The SELECT statement is so named because the typical first word of the query
construct is SELECT. (A query includes the VALUES expression and UNION, INTERSECT, and EXCEPT
expressions as well as SELECT expressions).
The ORDER BY clause guarantees
the ordering of the ResultSet.
The result offset clause and the
fetch first clause can be used to fetch only a subset of the
otherwise selected rows, possibly with an offset into the result set.
The FOR UPDATE clause makes
the result set's cursor updatable. The SELECT statement supports the FOR FETCH
ONLY clause. The FOR FETCH ONLY clause is synonymous with the FOR READ ONLY
clause.
You can set the isolation level in a
SELECT statement using the WITH { RR | RS | CS | UR } syntax.
For queries that do not select a specific column from the tables involved in
the SELECT statement (for example, queries that use COUNT(*)),
the user must have at least one column-level SELECT privilege or table-level
SELECT privilege. See
for more information.
Example -- lists the names of the expression
-- SAL+BONUS+COMM as TOTAL_PAY and
-- orders by the new name TOTAL_PAY
SELECT FIRSTNME, SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
ORDER BY TOTAL_PAY
-- creating an updatable cursor with a FOR UPDATE clause
-- to update the start date (PRSTDATE) and the end date (PRENDATE)
-- columns in the PROJECT table
SELECT PROJNO, PRSTDATE, PRENDATE
FROM PROJECT
FOR UPDATE OF PRSTDATE, PRENDATE
-- set the isolation level to RR for this statement only
SELECT *
FROM Flights
WHERE flight_id BETWEEN 'AA1111' AND 'AA1112'
WITH RR A SELECT statement returns a ResultSet.
A cursor is a pointer to a specific row in ResultSet. In Java
applications, all ResultSets have an underlying associated SQL
cursor, often referred to as the result set's cursor. The cursor can
be updatable, that is, you can update or delete rows as you step through the ResultSet if
the SELECT statement that generated it and its underlying query meet cursor
updatability requirements, as detailed below. The FOR UPDATE clause can be used
to ensure a compilation check that the SELECT statement meets the requiremments
of a updatable cursors, or to limit the columns that can be updated.
The ORDER BY clause
allows you to order the results of the SELECT. Without the ORDER BY clause,
the results are returned in random order.
Requirements for updatable cursors and updatable
ResultSets
Only simple, single-table SELECT cursors
can be updatable. The SELECT statement for updatable ResultSets
has the same syntax as the SELECT statement for updatable cursors. To generate
updatable cursors:
- The SELECT statement must not include an ORDER BY clause.
- The underlying query must be a selectExpression.
- The selectExpression in
the underlying query must not include:
- DISTINCT
- Aggregates
- GROUP BY clause
- HAVING clause
- ORDER BY clause
- The FROM clause in the underlying query must not have:
- More than one table in its FROM clause
- Anything other than one table name
- selectExpressions
- Subqueries
- If the underlying query has a WHERE clause, the WHERE clause must not
have subqueries.
Cursors are read-only by default. To produce an updatable
cursor besides meeting the requirements listed above, the concurrency mode for
the ResultSet must be ResultSet.CONCUR_UPDATABLE or the SELECT
statement must have FOR UPDATE in the FOR clause
(see ).
There is no SQL language statement to assign a name to
a cursor. Instead, one can use the JDBC API to assign names to cursors or retrieve
system-generated names. For more information, see "Naming or accessing the
name of a cursor" in the
.
Statement dependency system The SELECT depends
on all the tables and views named in the query and the conglomerates (units
of storage such as heaps and indexes) chosen for access paths on those tables.
CREATE INDEX does not invalidate a prepared SELECT statement. A DROP INDEX
statement invalidates a prepared SELECT statement if the index is an access
path in the statement. If the SELECT includes views, it also depends on the
dictionary objects on which the view itself depends (see ).
Any
prepared UPDATE WHERE CURRENT or DELETE WHERE CURRENT statement against a
cursor of a SELECT depends on the SELECT. Removing a SELECT through a java.sql.Statement.close request
invalidates the UPDATE WHERE CURRENT or DELETE WHERE CURRENT.
The SELECT
depends on all aliases used in the query. Dropping an alias invalidates a
prepared SELECT statement if the statement uses the alias.