SQL Support

Note: PL/SQL does not support data definition commands such as ALTER and CREATE. For an explanation and workaround, see "Using DDL and Dynamic SQL" .

Data Manipulation

Transaction Control

Simultaneously, Oracle makes permanent or undoes all database changes made by a transaction. If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction. Thus, the database is restored to its former state automatically.

You use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands to control transactions. COMMIT makes permanent any database changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction. Used with ROLLBACK, SAVEPOINT undoes part of a transaction. SET TRANSACTION establishes a read-only transaction.

SQL Functions

The group functions GLB and LUB are available only with Trusted Oracle. GLB and LUB return the greatest lower bound and least upper bound of an operating system label, respectively. For more information, see Trusted Oracle7 Server Administrator's Guide.

You can use the group functions in SQL statements, but not in procedural statements. Group functions operate on entire columns unless you use the SELECT GROUP BY statement to sort returned rows into subgroups. If you omit the GROUP BY clause, the group function treats all returned rows as a single group.

You call a group function using the syntax

function_name([ALL | DISTINCT] expr)

where expr is an expression that refers to one or more database columns. If you specify the ALL option (the default), the group function considers all column values including duplicates. For example, the following statement returns the sample standard deviation (s) of all values in the comm column:

SELECT STDDEV(comm) INTO comm_sigma FROM emp;

If you specify the DISTINCT option, the group function considers only distinct values. For example, the following statement returns the number of different job titles in the emp table:

SELECT COUNT(DISTINCT job) INTO job_count FROM emp;

The COUNT function lets you specify the asterisk (*) option, which returns the number of rows in a table. For example, the following statement returns the number of employees in the emp table:

SELECT COUNT(*) INTO emp_count FROM emp;

Except for COUNT(*), all group functions ignore nulls.

SQL Pseudocolumns

Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert values into, update values in, or delete values from a pseudocolumn.

You can use pseudocolumns in SQL statements, but not in procedural statements. In the following example, you use the database sequence empno_seq and the pseudocolumn NEXTVAL (which returns the next value in a database sequence) to insert a new employee number into the emp table:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, new_ename, ...);

Brief descriptions of the pseudocolumns follow. For more information, see Oracle7 Server SQL Reference.

CURRVAL and NEXTVAL

CURRVAL returns the current value in a specified sequence. Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number. A reference to NEXTVAL stores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To obtain the current or next value in a sequence, you must use dot notation, as follows:

sequence_name.CURRVAL
sequence_name.NEXTVAL

After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. However, you can use CURRVAL and NEXTVAL only in a select list, the VALUES clause, and the SET clause. In the following example, you use a sequence to insert the same employee number into two tables:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);
INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction.

LEVEL

You specify the direction in which the query walks the tree (down from the root or up from the branches) with the PRIOR operator. In the START WITH clause, you specify a condition that identifies the root of the tree.

ROWID

DECLARE
   row_id ROWID;

When you select or fetch a rowid into a ROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string. Then, you can compare the ROWID variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor. For an example, see "Fetching Across Commits" .

ROWNUM

You can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Also, you can use ROWNUM in the WHERE clause of a SELECT statement to limit the number of rows retrieved, as follows:

DECLARE
   CURSOR c1 IS SELECT empno, sal FROM emp
      WHERE sal > 2000 AND ROWNUM < 10;  -- returns 10 rows

The value of ROWNUM increases only when a row is retrieved, so the only meaningful use of ROWNUM in a WHERE clause is

... WHERE ROWNUM < constant;

For example, the following condition cannot be met because the first nine rows are never retrieved:

... WHERE ROWNUM = 10;

ROWLABEL Column

A common use of ROWLABEL is to filter query results. For example, the following statement counts only those rows with a security level higher than "unclassified":

SELECT COUNT(*) INTO head_count FROM emp
   WHERE ROWLABEL > 'UNCLASSIFIED';

SQL Operators

Comparison Operators

ALL	Compares a value to each value in a list or returned by a subquery and yields TRUE if all of the individual comparisons yield TRUE.
ANY, SOME	Compares a value to each value in a list or returned by a subquery and yields TRUE if any of the individual comparisons yields TRUE.
BETWEEN	Tests whether a value lies in a specified range.
EXISTS	Returns TRUE if a subquery returns at least one row.
IN	Tests for set membership.
IS NULL	Tests for nulls.
LIKE	Tests whether a character string matches a specified pattern, which can include wildcards.

Set Operators

Row Operators

SQL92 Conformance

Note: SQL92 is known officially as International Standard ISO/IEC 9075:1992, Database Language SQL, which is also available as ANSI Document ANSI X3.135-1992.

SQL92 specifies a "conforming SQL language" and, to allow implementation in stages, defines three language levels:

• Full SQL

• Intermediate SQL (a subset of Full SQL)

• Entry SQL (a subset of Intermediate SQL)