Specifying Values (extended_value_spec)

Values are specified (extended_value_spec) using one of the keywords DEFAULT or STAMP or by specifying the values (value_spec: Literals, parameter specifications, key words and so on).

Structure

Syntax Syntax

<extended_value_spec> ::=
  DEFAULT
| STAMP
| <value_spec>

<value_spec> ::=
  <literal>
| <parameter_spec>
| NULL
| USER
| USERGROUP
| SYSDBA
| CURRENT_SCHEMA
| UID
| [<schema_name>.]<sequence_name>.NEXTVAL
| [<schema_name>.]<sequence_name>.CURRVAL
| <table_name>.CURRVAL
| DATE
| TIME
| TIMESTAMP
| UTCDATE
| TIMEZONe
| UTCDIFF
| TRUE
| FALSE
| TRANSACTION

End of the code.

Examples

SQL Tutorial, Number Generators for Tables

DEFAULT

DEFAULT identifies the default value for the column in a CREATE TABLE statement or ALTER TABLE statement. If one of these values is not defined, DEFAULT cannot be used to specify values.

The DEFAULT keyword can be used in the following SQL statements: INSERT statement, UPDATE statement. The DEFAULT keyword can be used in a DEFAULT predicate.

STAMP

The database system is capable of generating unique values. These comprise a series of consecutive numbers starting at X'000000000001'. The values are assigned in ascending order. There is no guarantee that a sequence of values is uninterrupted. The STAMP keyword returns the next value generated by the database system.

The STAMP keyword can be used in the following SQL statements (only on columns of data type CHAR(n) BYTE where n>=8): INSERT statement, UPDATE statement

If the user wants to find out the generated value before it is applied to a column, the following SQL statement must be used: NEXT STAMP Statement

<value_spec>

The following options are available for specifying values:

Specifying Values (value_spec)
<value_spec>	Explanation
`<literal>`	Literal
`<parameter_spec>`	Parameter specification
`NULL`	NULL Value
`USER \| USERGROUP`	Current user name or name of the user group to which the database user calling the SQL statement belongs. If the database user does not belong to a user group, the database user name is displayed.
`SYSDBA`	Database system administrator
`CURRENT_SCHEMA`	Current schema
`UID`	Identification of the current database user. This `DEFAULT` specification can only be specified with numeric data type and a minimum of 10 places before the decimal point.
`[<schema_name>.]<sequence_name>.NEXTVAL` `\| [<schema_name>.]<sequence_name>.CURRVAL`	`NEXTVAL` shows the next available value generated by the number generator (sequence `[<schema_name>.]<sequence_name>`). `CURRVAL` shows the value currently being used which was generated by the number generator (sequence `[<schema_name>.]<sequence_name>`). Further Explanations: CREATE SEQUENCE Statement (create_sequence_statement)
`<table_name>.CURRVAL`	Last value assigned in the current database session for the `SERIAL` column in the table `table_name`.
`DATE \| TIME \| TIMESTAMP`	Current date, time, time stamp.
`UTCDATE`	Current `UTC` time stamp (Greenwich Mean Time)
`TIMEZONE`	Time difference in the format `hhmmss` (in the data type `FIXED(6)`) between local time and `UTC` time.
`UTCDIFF`	Time difference in hours (in the data type `FIXED(4,2)`) between local time and `UTC` time.
`TRUE \| FALSE`	Corresponding value of a column of the data type BOOLEAN
`TRANSACTION`	Identification of the current transaction. This is a value of data type `CHAR(6) BYTE`.

More Information

Expression

Key specification

INSERT Statement

DEFAULT Specification