Guide to Using SQL: Computed and Automatic Columns

Guide to Using SQL: Computed and Automatic Columns

A feature of Oracle Rdb

By Ian Smith Oracle Rdb Relational Technology Group Oracle Corporation

1

Oracle Rdb Journal ? Automatic Columns

Guide to Using SQL: Computed and Automatic Columns

Computed columns are nothing new to Oracle Rdb and have been available since its first release in 1984. A special type of column - know as a computed by column - defines a calculation instead of a data type. This special column takes no space within the table but allows the programmer to fetch the value at run-time using the select statement, or via a cursor.

Oracle Rdb builds on this support in release V7.1 by adding a new type of computed column; automatic columns. These columns are stored with the row but its value is calculated at either insert or update time and like computed by columns they are read only.

This article reviews all types of computed columns available in Rdb.1 The examples in the article use SQL language from Oracle Rdb V7.1 or later.

COMPUTED BY Columns

To create a virtual or computed by column use either the create table or alter table ... add column clause. This special column computes a value when selected or when referenced in a WHERE clause. Consider this simple example:

SQL> create table PERSON (

cont> (employee_id integer,

cont> employee_id_disp computed by

cont> SUBSTRING (CAST(employee_id + 100000 as VARCHAR (6)) from 2)

cont>

...);

The column EMPLOYEE_ID_DISP is used to display the employee id with leading zeros. The combination of CAST, SUBSTRING and addition is stored within the table definition and relieves the application program from performing this type of conversion.

1 This is a revised version of an article that was first published in May 2002.

2

Oracle Rdb Journal ? Automatic Columns

The computed by expression can be a simple value expression such as CURRENT_USER, a call to a user define function, or as complex as allowed by the full power of the SQL subquery syntax.

SQL> alter table DEPARTMENTS

cont> add column EMPLOYEE_COUNT

cont> computed by

cont> (select count (*)

cont>

from JOB_HISTORY jh

cont>

where job_end is NULL -- in the current job

cont>

and jh.department_code = departments.department_code);

The values are computed at select time therefore no space is required within the row, and the expression need not be evaluated during insert or update of the table unless it is referenced in a where clause, a constraint or a trigger. Neither the insert nor the update statement may assign values to this type of column.

A computed by column may reference other computed columns in the same table. Rdb expands each expression when referenced so that the correct value is returned.

Note: If many computed by columns use subquery syntax in the computed by expression, then Oracle recommends that these calculations be replaced with calls to SQL functions to limit the tables contexts used by these computed columns.

AUTOMATIC Columns

Rdb includes another type of read-only column called AUTOMATIC AS columns. Automatic columns are closely related to computed by columns; however, the computed values are evaluated at INSERT and UPDATE time and stored in the database.

The database designer can define an AUTOMATIC column to be computed and stored during INSERT, UPDATE or during both these statements. These columns can also be used as part of an index key, and referenced by constraints.

3

Oracle Rdb Journal ? Automatic Columns

SQL> create table PERSON

cont>

person_key

cont>

automatic insert as GET_NEW_ID () primary key,

cont>

...);

SQL> create unique index PERSON_INDEX on PERSON (person_key);

This partial example shows the use a SQL function to calculate a unique value for the primary key field. The column PERSON_KEY will inherit its data type from the value expression ? in this case the returned data type from the SQL function. The CAST operator can be used to adjust the result data type. For example, if the expression returns a DOUBLE PRECISION value you may prefer to have the column return a BIGINT(4) type instead.

As read-only columns they may not be targets for an UPDATE or INSERT statement and are therefore ideal for calculating auditing information that you do not want modified by unprivileged users.

The SQL syntax allow for three types of AUTOMATIC columns: automatic insert as that is calculated only during an INSERT statement, automatic update as that is calculated only during an UPDATE statement and automatic as that is calculated at both UPDATE and INSERT time.

Consider this simple example:

? When a row is inserted track who executed the statement (CURRENT_USER) and when this action occurred (CURRENT_TIMESTAMP).

? When a row is inserted or updated record the change timestamp.

SQL> create table PRODUCTS

cont>

product_id

integer primary key,

cont>

entered_by

automatic insert as current_user,

cont>

change_dt

automatic as current_timestamp,

cont>

...);

Here the ENTERED_BY column will contain the user name of the user who inserted the row, and the current timestamp will be written to CHANGE_DT. Rdb will revise the CHANGE_DT column automatically during subsequent UPDATE statements.

4

Oracle Rdb Journal ? Automatic Columns

Note: in prior versions read-only columns were included in the default column list for INSERT, even though they could not be modified. Starting with Rdb V7.1 read-only columns (COMPUTED BY and AUTOMATIC) are excluded from the default column list for INSERT. This simplifies programming by allowing new COMPUTED BY and AUTOMATIC columns to be added without requiring changes to existing code.

Identity Columns

Rdb allows one column of a table to have the IDENTITY attribute. This attribute changes the column to an AUTOMATIC column with an associated sequence. Most of the comments concerning AUTOMATIC columns in this article also apply to IDENTITY columns.

SQL> create table PERSON

cont>

person_key

cont>

integer identity primary key,

cont>

...);

SQL> create unique index PERSON_INDEX on PERSON (person_key);

This is a very similar example to that shown above for Automatic columns. In this case we use identity rather than using a SQL function to compute the unique number. Adding primary key allows other tables to reference this column from foreign key definitions.

5

Oracle Rdb Journal ? Automatic Columns

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download