A SELECT statement consists of a query with an optional ORDER BY clause and an optional FOR UPDATE clause. The SELECT statement is so named because the typical first word of the query construct is SELECT. (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 FOR UPDATE clause makes the result an updatable cursor. The SELECT statement supports the FOR FETCH ONLY clause. The FOR FETCH ONLY clause is synonymous with the FOR READ ONLY clause.
Query [ORDER BY clause] [FOR UPDATE clause] WITH {RR|RS|CS|UR}
You can set the isolation level in a SELECT statement using the WITH {RR|RS|CS|UR} syntax.
-- 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;
If a SELECT statement meets the requirements listed below, cursors are updatable only if you specify FOR UPDATE in the FOR clause (see FOR UPDATE clause).
There is no SQL language statement to assign a name to a cursor. Instead, you 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 Chapter 5 of the Derby Developer's Guide.
Cursors are read-only by default. For a cursor to be updatable, you must specify FOR UPDATE in the FOR clause (see FOR UPDATE clause).
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 CREATE VIEW statement).
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.