Progress
Version 9
Product Update Bulletin
Progress SQL-92 Support For Progress Sequences
With the release of Version 9.1D, Progress SQL-92 supports the sequence generator used in Progress 4GL applications. Sequences are database objects that generate unique integers. Because the SQL-92 sequence generator generates numbers independently of tables, the same sequence can be used for multiple tables.
In order to support the generation of sequences, the following additions were made to Progress SQL-92:
The following sections detail the above additions to Progress SQL-92.
CREATE SEQUENCE Statement
Use the following syntax to generate a sequence:
schema_name
Specifies the schema to contain the sequence. If schema_name is not specified, the sequence generator creates the sequence in the current schema.
NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.sequence_name
Specifies the name of the sequence to be created.
INCREMENT BY
Specifies the interval between sequence numbers. The value can be any positive or negative integer, but cannot be 0. When the value is positive, the sequence ascends. If it is negative, the sequence descends. The default value is 1.
START WITH
Specifies the first sequence number generated. In an ascending sequence, the value must be greater than or equal to the MINVALUE. In a descending sequence, the value must be greater than or equal to the MAXVALUE. For ascending sequences, the default value is MINVALUE. For descending sequences, the default value is MAXVALUE.
MAXVALUE
Specifies the maximum value for the sequence to generate. For an ascending sequence, the default value is 2,147,483,647. For a descending sequence, the default value is -1.
NOMAXVALUE
Specifies -1 as the
MAXVALUE
for descending sequences and 2,147,483,647 as theMAXVALUE
for ascending sequences.
MINVALUE
Specifies the minimum value the sequence can generate. For an ascending sequence, the default value is 0. For a descending sequence, the default value is -2,147,483,648.
NOMINVALUE
Specifies 0 as the
MINVALUE
for ascending sequences and -2,147,483,648 as theMINVALUE
for descending sequences.
CYLCE
Indicates that the sequence will continue to generate values after reaching the value assigned to
MAXVALUE
(if sequence ascends)or
MINVALUE
(if sequence descends).
NOCYCLE
Indicates that the sequence cannot generate more values after reaching the value assigned to
MAXVALUE
(if sequence ascends) orMINVALUE
(if sequence descends). The SQL-92 sequence generator usesNOCYCLE
as the default ifCYCLE
is not specified.In the following example, a sequence is used to generate unique customer numbers when a new customer is inserted into the table
EXAMPLEpub.customer
.
DROP SEQUENCE Statement
The
DROP SEQUENCE
Statement removes a sequence from a schema. You must have Database Administration privileges to remove a sequence.Use the following syntax to remove a sequence:
schema_name
Specifies the schema name that contains the sequence. If this is not specified, Progress SQL-92 drops the sequence, if present, from the current schema.
NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.sequence_name
Specifies the sequence to be dropped.
In the following example, a sequence named customer is dropped from the public (PUB) schema.
EXAMPLE
CURRVAL and NEXTVAL In a Statement
Sequences contain two methods: CURRVAL and NEXTVAL. As detailed in the "CREATE SEQUENCE Statement" section, when you create a sequence you can define its initial value and the increment between its values. CURRVAL returns the current value of the sequence and NEXTVAL returns the sequence’s next value. References to NEXTVAL increment the sequence value by the defined increment and return the new value. References to CURRVAL always return the sequence’s current value, which is the value returned by the last reference to NEXTVAL.
Use the following syntax to reference the current value of a sequence:
schema
Specifies the schema that contains the sequence. To refer to the current value of a sequence in the schema of another user, you must have SELECT object privilege on the sequence.
NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.sequence
Specifies the name of the sequence whose current value you want.
Use the following syntax to reference the next value of a sequence:
schema
Specifies the schema that contains the sequence. To refer to the next value of a sequence in the schema of another user, you must have SELECT object privilege on the sequence.
NOTE: Currently, with Version 9.1D, SQL-92 supports only the PUBLIC (PUB) schema.sequence
Specifies the name of the sequence whose next value you want.
NOTE: A statement referencing NEXTVAL for a noncycling sequence returns an error after reaching the maximum value. For more information on cycling and noncycling sequences, see the "CREATE SEQUENCE Statement" section.Use CURRVAL and NEXTVAL in the:
CURRVAL and NEXTVAL cannot be used in:
EXAMPLES
- A query of a view.
- A SELECT statement with a GROUP BY clause that references a sequence.
- A SELECT statement with an ORDER BY clause that references a sequence.
- A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator.
- The WHERE clause of a SELECT or UPDATE statement.
- The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement.
- The condition of a CHECK constraint.
In the following example, the Progress SQL-92 sequence generator returns the current value of the customer sequence:
NOTE: Sequences are not associated with tables. This example provides reference to a table in order to conform to correct SQL-92 syntax.In the following example, the sequence generator increments the customer sequence and uses its value for a new customer inserted into the table
pub.customer
:
GRANT Statement
Use the GRANT statement to assign privileges for accessing sequences. As database administrator, you can assign SELECT and UPDATE privileges to specific sequence objects.
Use the following syntax to assign sequence privileges:
SELECT
Allows specified user to read data from the sequence
UPDATE
Allows specified user to modify data for the sequence
EXAMPLEIn this example, the sequence generator grants user slsadmin the ability to modify the customer number sequence.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |