|
Home > Archive > SQL Anywhere Feedback > September 2005 > suggestion: sequences
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
suggestion: sequences
|
|
| krisztian pinter 2005-09-13, 7:23 am |
|
Sorry for the cross post, but i put it in wrong group fist.
---- orig post ----
ASA covers autoincrement fields quite well. However, in some situations,=
it would be better to handle the ID generation separately from the data
inserts. Here is the proposal:
We need a dedicated sequence generator structure. For those, who are
familiar with oracle sequences, i have the same feature in mind.
A sequence would be created with CREATE SEQUENCE statement. It contains
a start and an optional end value, plus an optional step. You can use
the sequence in expressions (e.g. SELECT MySequence.Next). It would also=
smart to include global autoincrement features to it. ID generation
is out of the scope of transactions, so they can not be rolled back
(same as autoincrement fields).
Benefits:
In many applications, it is necessary to build complete in-memory
master-detail structures with more records, then save it in one go.
That is because you can implement a Cancel button that way. Now we
need to link the records via some internal method, and on creation,
insert each row, then read back the ID values, update the memory data,
and go to the next row. This is a burden. With sequences, you would
be able to pre-generate the IDs as rows created in memory. It of
course leads to waste IDs on cancel, but it can be acceptable.
The method supports every feature of the old technique, as you
simply can write
INSERT INTO MYTABLE VALUES (MySeq.Next, ...)
or even, it is possible to define fields to get default values from
a sequence
MyField INT DEFAULT FROM SEQUENCE MySeq
| |
| Breck Carter [TeamSybase] 2005-09-13, 7:23 am |
| For the record, and without editorial comment, here are some excerpts
from the Oracle 10g docs on the subject of sequences.
Breck
=====
CREATE SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
]
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
]... ;
If you specify none of the following clauses, then you create an
ascending sequence that starts with 1 and increases by 1 with no upper
limit. Specifying only INCREMENT BY -1 creates a descending sequence
that starts with -1 and decreases with no lower limit.
* To create a sequence that increments without bound, for
ascending sequences, omit the MAXVALUE parameter or specify
NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or
specify the NOMINVALUE.
* To create a sequence that stops at a predefined limit, for an
ascending sequence, specify a value for the MAXVALUE parameter. For a
descending sequence, specify a value for the MINVALUE parameter. Also
specify NOCYCLE. Any attempt to generate a sequence number once the
sequence has reached its limit results in an error.
* To create a sequence that restarts after reaching a predefined
limit, specify values for both the MAXVALUE and MINVALUE parameters.
Also specify CYCLE. If you do not specify MINVALUE, then it defaults
to NOMINVALUE, which is the value 1.
INCREMENT BY Specify the interval between sequence numbers. This
integer value can be any positive or negative integer, but it cannot
be 0. This value can have 28 or fewer digits. The absolute of this
value must be less than the difference of MAXVALUE and MINVALUE. If
this value is negative, then the sequence descends. If the value is
positive, then the sequence ascends. If you omit this clause, then the
interval defaults to 1.
START WITH Specify the first sequence number to be generated. Use
this clause to start an ascending sequence at a value greater than its
minimum or to start a descending sequence at a value less than its
maximum. For ascending sequences, the default value is the minimum
value of the sequence. For descending sequences, the default value is
the maximum value of the sequence. This integer value can have 28 or
fewer digits.
Note: This value is not necessarily the value to which an ascending
cycling sequence cycles after reaching its maximum or minimum value.
MAXVALUE Specify the maximum value the sequence can generate. This
integer value can have 28 or fewer digits. MAXVALUE must be equal to
or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE Specify NOMAXVALUE to indicate a maximum value of 1027 for
an ascending sequence or -1 for a descending sequence. This is the
default.
MINVALUE Specify the minimum value of the sequence. This integer value
can have 28 or fewer digits. MINVALUE must be less than or equal to
START WITH and must be less than MAXVALUE.
NOMINVALUE Specify NOMINVALUE to indicate a minimum value of 1 for an
ascending sequence or -1026 for a descending sequence. This is the
default.
CYCLE Specify CYCLE to indicate that the sequence continues to
generate values after reaching either its maximum or minimum value.
After an ascending sequence reaches its maximum value, it generates
its minimum value. After a descending sequence reaches its minimum, it
generates its maximum value.
NOCYCLE Specify NOCYCLE to indicate that the sequence cannot generate
more values after reaching its maximum or minimum value. This is the
default.
CACHE Specify how many values of the sequence the database
preallocates and keeps in memory for faster access. This integer value
can have 28 or fewer digits. The minimum value for this parameter is
2. For sequences that cycle, this value must be less than the number
of values in the cycle. You cannot cache more values than will fit in
a given cycle of sequence numbers. Therefore, the maximum value
allowed for CACHE must be less than the value determined by the
following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, all cached sequence values that have not
been used in committed DML statements are lost. The potential number
of lost values is equal to the value of the CACHE parameter.
Note: Oracle recommends using the CACHE setting to enhance performance
if you are using sequences in a Real Application Clusters environment.
NOCACHE Specify NOCACHE to indicate that values of the sequence are
not preallocated. If you omit both CACHE and NOCACHE, the database
caches 20 sequence numbers by default.
ORDER Specify ORDER to guarantee that sequence numbers are generated
in order of request. This clause is useful if you are using the
sequence numbers as timestamps. Guaranteeing order is usually not
important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are
using Oracle Database with Real Application Clusters. If you are using
exclusive mode, sequence numbers are always generated in order.
NOORDER Specify NOORDER if you do not want to guarantee sequence
numbers are generated in order of request. This is the default.
Example
Creating a Sequence: Example The following statement creates the
sequence customers_seq in the sample schema oe. This sequence could be
used to provide customer ID numbers when rows are added to the
customers table.
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
The first reference to customers_seq.nextval returns 1000. The second
returns 1001. Each subsequent reference will return a value 1 greater
than the previous reference.
=====
Sequence Pseudocolumns
A sequence is a schema object that can generate unique sequential
values. These values are often used for primary and unique keys. You
can refer to sequence values in SQL statements with these
pseudocolumns:
* CURRVAL: Returns the current value of a sequence
* NEXTVAL: Increments the sequence and returns the next value
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
sequence.CURRVAL
sequence.NEXTVAL
To refer to the current or next value of a sequence in the schema of
another user, you must have been granted either SELECT object
privilege on the sequence or SELECT ANY SEQUENCE system privilege, and
you must qualify the sequence with the schema containing it:
schema.sequence.CURRVAL
schema.sequence.NEXTVAL
To refer to the value of a sequence on a remote database, you must
qualify the sequence with a complete or partial name of a database
link:
schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink
See Also: "Referring to Objects in Remote Databases" for more
information on referring to database links
Where to Use Sequence Values
You can use CURRVAL and NEXTVAL in the following locations:
* The select list of a SELECT statement that is not contained in a
subquery, materialized view, or view
* The select list of a subquery in an INSERT statement
* The VALUES clause of an INSERT statement
* The SET clause of an UPDATE statement
Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in
the following constructs:
* A subquery in a DELETE, SELECT, or UPDATE statement
* A query of a view or of a materialized view
* A SELECT statement with the DISTINCT operator
* A SELECT statement with a GROUP BY clause or ORDER BY clause
* A SELECT statement that is combined with another SELECT
statement with the UNION, INTERSECT, or MINUS set operator
* The WHERE clause of a SELECT statement
* The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE
statement
* The condition of a CHECK constraint
Within a single SQL statement that uses CURRVAL or NEXTVAL, all
referenced LONG columns, updated tables, and locked tables must be
located on the same database.
How to Use Sequence Values
When you create a sequence, you can define its initial value and the
increment between its values. The first reference to NEXTVAL returns
the initial value of the sequence. Subsequent references to NEXTVAL
increment the sequence value by the defined increment and return the
new value. Any reference to CURRVAL always returns the current value
of the sequence, which is the value returned by the last reference to
NEXTVAL.
Before you use CURRVAL for a sequence in your session, you must first
initialize the sequence with NEXTVAL. Please refer to CREATE SEQUENCE
for information on sequences.
Within a single SQL statement containing a reference to NEXTVAL,
Oracle increments the sequence once:
* For each row returned by the outer query block of a SELECT
statement. Such a query block can appear in the following places:
o A top-level SELECT statement
o An INSERT ... SELECT statement (either single-table or
multitable). For a multitable insert, the reference to NEXTVAL must
appear in the VALUES clause, and the sequence is updated once for each
row returned by the subquery, even though NEXTVAL may be referenced in
multiple branches of the multitable insert.
o A CREATE TABLE ... AS SELECT statement
o A CREATE MATERIALIZED VIEW ... AS SELECT statement
* For each row updated in an UPDATE statement
* For each INSERT statement containing a VALUES clause
* For each row merged by a MERGE statement. The reference to
NEXTVAL can appear in the merge_insert_clause or the
merge_update_clause or both. The NEXTVALUE value is incremented for
each row updated and for each row inserted, even if the sequence
number is not actually used in the update or insert operation. If
NEXTVAL is specified more than once in any of these locations, then
the sequence is incremented once for each row and returns the same
value for all occurrences of NEXTVAL for that row.
If any of these locations contains more than one reference to NEXTVAL,
then Oracle increments the sequence once and returns the same value
for all occurrences of NEXTVAL.
If any of these locations contains references to both CURRVAL and
NEXTVAL, then Oracle increments the sequence and returns the same
value for both CURRVAL and NEXTVAL.
A sequence can be accessed by many users concurrently with no waiting
or locking.
Finding the next value of a sequence: Example This example selects the
next value of the employee sequence in the sample schema hr:
SELECT employees_seq.nextval
FROM DUAL;
Inserting sequence values into a table: Example This example
increments the employee sequence and uses its value for a new employee
inserted into the sample table hr.employees:
INSERT INTO employees
VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe',
'555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null,
30);
Reusing the current value of a sequence: Example This example adds a
new order with the next order number to the master order table. It
then adds suborders with this number to the detail order table:
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 1, 2359);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 2, 3290);
INSERT INTO order_items (order_id, line_item_id, product_id)
VALUES (orders_seq.currval, 3, 2381);
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Bruce Hay 2005-09-13, 9:23 am |
| To obtain identity values before inserting, use the get_identity() function.
It provides additional functionality for tables with autoincrement columns.
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
"krisztian pinter" <pinterkr@freemail.hu> wrote in message
news:opsw09e4qgwwfeh
v@kar_wst_pint...
Sorry for the cross post, but i put it in wrong group fist.
---- orig post ----
ASA covers autoincrement fields quite well. However, in some situations,
it would be better to handle the ID generation separately from the data
inserts. Here is the proposal:
We need a dedicated sequence generator structure. For those, who are
familiar with oracle sequences, i have the same feature in mind.
A sequence would be created with CREATE SEQUENCE statement. It contains
a start and an optional end value, plus an optional step. You can use
the sequence in expressions (e.g. SELECT MySequence.Next). It would also
smart to include global autoincrement features to it. ID generation
is out of the scope of transactions, so they can not be rolled back
(same as autoincrement fields).
Benefits:
In many applications, it is necessary to build complete in-memory
master-detail structures with more records, then save it in one go.
That is because you can implement a Cancel button that way. Now we
need to link the records via some internal method, and on creation,
insert each row, then read back the ID values, update the memory data,
and go to the next row. This is a burden. With sequences, you would
be able to pre-generate the IDs as rows created in memory. It of
course leads to waste IDs on cancel, but it can be acceptable.
The method supports every feature of the old technique, as you
simply can write
INSERT INTO MYTABLE VALUES (MySeq.Next, ...)
or even, it is possible to define fields to get default values from
a sequence
MyField INT DEFAULT FROM SEQUENCE MySeq
| |
| M. Searer 2005-09-13, 8:23 pm |
| One of the problem with identities are that you are limited to single identity
per table.
"Bruce Hay" < h_a_y@i~a~n~y~w~h~e~
r~e.c_o_m> wrote in message
news:4326c96a@forums
-2-dub...
> To obtain identity values before inserting, use the get_identity() function.
> It provides additional functionality for tables with autoincrement columns.
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
> "krisztian pinter" <pinterkr@freemail.hu> wrote in message
> news:opsw09e4qgwwfeh
v@kar_wst_pint...
>
> Sorry for the cross post, but i put it in wrong group fist.
>
> ---- orig post ----
>
> ASA covers autoincrement fields quite well. However, in some situations,
> it would be better to handle the ID generation separately from the data
> inserts. Here is the proposal:
>
> We need a dedicated sequence generator structure. For those, who are
> familiar with oracle sequences, i have the same feature in mind.
>
> A sequence would be created with CREATE SEQUENCE statement. It contains
> a start and an optional end value, plus an optional step. You can use
> the sequence in expressions (e.g. SELECT MySequence.Next). It would also
> smart to include global autoincrement features to it. ID generation
> is out of the scope of transactions, so they can not be rolled back
> (same as autoincrement fields).
>
> Benefits:
>
> In many applications, it is necessary to build complete in-memory
> master-detail structures with more records, then save it in one go.
> That is because you can implement a Cancel button that way. Now we
> need to link the records via some internal method, and on creation,
> insert each row, then read back the ID values, update the memory data,
> and go to the next row. This is a burden. With sequences, you would
> be able to pre-generate the IDs as rows created in memory. It of
> course leads to waste IDs on cancel, but it can be acceptable.
>
> The method supports every feature of the old technique, as you
> simply can write
>
> INSERT INTO MYTABLE VALUES (MySeq.Next, ...)
>
> or even, it is possible to define fields to get default values from
> a sequence
>
> MyField INT DEFAULT FROM SEQUENCE MySeq
>
>
| |
| krisztian pinter 2005-09-14, 3:23 am |
| On 13 Sep 2005 05:43:50 -0700, Bruce Hay < h_a_y@i~a~n~y~w~h~e~
r~e.c_o_m>=
=
wrote:
> To obtain identity values before inserting, use the get_identity() =
> function.
> It provides additional functionality for tables with autoincrement =
> columns.
get_identity does the job fine. it is not exactly the nicest solution,
but has the functionality we need. thanks for pointing this out.
| |
| Breck Carter [TeamSybase] 2005-09-14, 7:23 am |
| On 13 Sep 2005 14:39:19 -0700, "M. Searer" <nospam@nospam.com> wrote:
>One of the problem with identities are that you are limited to single identity
>per table.
Not so. It may be a problem with get_identity(), but you can have
multiple DEFAULT AUTOINCREMENT columns in a table.
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
|
|
|
|
|