版次注意事項
|Add the following note to this section in Chapter 3:
| The following names, when used in the context of SQL Procedures,
| are restricted to the characters allowed in an
| ordinary identifier, even if the names are delimited:
|
| - condition-name
| - label
| - parameter-name
| - procedure-name
| - SQL-variable-name
| - statement-name
|A paragraph in this section has been changed to the following:
| Note that the size of a URL parameter or function result is the same
| on both input or output and is bound by the length of the DATALINK column.
| However, in some cases the URL value returned has an access token attached.
| In situations where this is possible, the output location must have
| sufficient storage space for the access token and the length of
| the DATALINK column. Hence, the actual length of the comment and
| URL in its fully expanded form provided on input should be restricted
| to accommodate the output storage space. If the restricted length is
| exceeded, this error is raised.
|The syntax diagram has changed:
| .-operator------------------------------.
| V |
|>>----+-----+---+-function--------------+--+-------------------><
| +- + -+ +-(expression)----------+
| '- - -' +-constant--------------+
| +-column-name-----------+
| +-host-variable---------+
| +-special-register------+
| +-(scalar-fullselect)---+
| +-labeled-duration------+
| +-case-expression-------+
| +-cast-specification----+
| +-dereference-operation-+
| +-OLAP-function---------+
| +-method-invocation-----+
| +-subtype-treatment-----+
| '-sequence-reference----'
|
|operator
|
| (1)
||---+-CONCAT------+---------------------------------------------|
| +- / ---------+
| +- * ---------+
| +- + ---------+
| '- - ---------'
|
|Note:
- ||| may be used as a synonym for CONCAT.
|
|The following represents a correction to the "OLAP Functions" section under
|"Expressions" in Chapter 3.
|
|aggregation-function
|
||--column-function--OVER---(--+------------------------------+-->
| '-| window-partition-clause |--'
|
|>----+--------------------------------------------------------------------+>
| '-| window-order-clause |--+--------------------------------------+--'
| '-| window-aggregation-group-clause |--'
|
|>---------------------------------------------------------------|
|
|window-order-clause
|
| .-,-------------------------------------------.
| V .-| asc option |---. |
||---ORDER BY-----sort-key-expression--+------------------+--+---|
| '-| desc option |--'
|
|asc option
|
| .-NULLS LAST--.
||---ASC--+-------------+----------------------------------------|
| '-NULLS FIRST-'
|
|desc option
|
| .-NULLS FIRST--.
||---DESC--+--------------+--------------------------------------|
| '-NULLS LAST---'
|
|window-aggregation-group-clause
|
||---+-ROWS--+---+-| group-start |---+---------------------------|
| '-RANGE-' +-| group-between |-+
| '-| group-end |-----'
|
|group-end
|
||---+-UNBOUNDED FOLLOWING-----------+---------------------------|
| '-unsigned-constant--FOLLOWING--'
|
|In the window-order-clause description:
|
- |NULLS FIRST
- |The window ordering considers null values before all non-null values
|in the sort order.
- |NULLS LAST
- |The window ordering considers null values after all non-null values
|in the sort order.
|
|In the window-aggregation-group-clause description:
|
- |window-aggregation-group-clause
- |The aggregation group of a row R is a set of rows, defined relative
|to R in the ordering of the rows of R's partition. This clause specifies
|the aggregation group. If this clause is not specified, the default is the
|same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, providing a cumulative
|aggregation result.
|
- |ROWS
- |Indicates the aggregation group is defined by counting rows.
- |RANGE
- |Indicates the aggregation group is defined by an offset from a sort
|key.
- |group-start
- |Specifies the starting point for the aggregation group. The aggregation
|group end is the current row. Specification of the group-start clause is
|equivalent to a group-between clause of the form "BETWEEN group-start AND
|CURRENT ROW".
- |group-between
- |Specifies the aggregation group start and end based on either ROWS
|or RANGE.
- |group-end
- |Specifies the ending point for the aggregation group. The aggregation
|group start is the current row. Specification of the group-end clause
|is equivalent to a group-between clause of the form "BETWEEN
|CURRENT ROW AND group-end".
- |UNBOUNDED PRECEDING
- |Includes the entire partition preceding the current row. This can be
|specified with either ROWS or RANGE. Also, this can be specified with multiple
|sort-key-expressions in the window-order-clause.
- |UNBOUNDED FOLLOWING
- |Includes the entire partition following the current row. This can be
|specified with either ROWS or RANGE. Also, this can be specified with multiple
|sort-key-expressions in the window-order-clause.
- |CURRENT ROW
- |Specifies the start or end of the aggregation group based on the current
|row. If ROWS is specified, the current row is the aggregation group boundary.
|If RANGE is specified, the aggregation group boundary includes the set of
|rows with the same values for the sort-key-expressions as the current
|row. This clause cannot be specified in group-bound2 if group-bound1 specifies value FOLLOWING.
- |value PRECEDING
- |Specifies either the range or number of rows preceding the current
|row. If ROWS is specified, then value is a positive integer indicating
|a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause.
|There can only be one sort-key-expression, and the data type of the sort-key-expression
|must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or value FOLLOWING.
- |value FOLLOWING
- |Specifies either the range or number of rows following the current
|row. If ROWS is specified, then value is a positive integer indicating
|a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause.
|There can only be one sort-key-expression, and the data type of the sort-key-expression
|must allow addition.
|
|
|The following information should be added to the end of the Expressions
|section (after "Subtype Treatment").
|
|
|
|
|
|
|
|
|sequence-reference
|
||--+-| nextval-expression |-+-----------------------------------|
| '-| prevval-expression |-'
|
|nextval-expression
|
||---NEXTVAL FOR--sequence-name----------------------------------|
|
|prevval-expression
|
||---PREVVAL FOR--sequence-name----------------------------------|
|
|
- |NEXTVAL FOR sequence-name
- |A NEXTVAL expression returns the next value for the sequence specified
|by sequence-name.
- |PREVVAL FOR sequence-name
-
|A PREVVAL expression returns the most recently generated
|value for the specified sequence for a previous statement within the current
|session. This value can be repeatedly referenced using PREVVAL expressions
|specifying the name of the sequence. There may be multiple instances of PREVVAL
|expressions specifying the same sequence name within a single statement and
|they all return the same value.
|A PREVVAL expression can only be used
|if a NEXTVAL expression specifying the same sequence name has already been
|referenced in the current user session (in the current or a previous transaction)
|(SQLSTATE 51035).
|
- |Note:
-
|
- |A new sequence number is generated when a NEXTVAL expression specifies
|the name of the sequence. However, if there are multiple instances of
|a NEXTVAL expression specifying the same sequence name within a query,
|the counter for the sequence is incremented only once for each row of
|the result.
- |The most recently generated value for a sequence can be repeatedly referenced
|using a PREVVAL expression specifying the name of the sequence. There may
|be multiple instances of a PREVVAL expression specifying the same sequence
|name within a single statement.
- |The same sequence number can be used as a unique key value in two separate
|tables by referencing the sequence number with a NEXTVAL expression for
|the first row (this generates the sequence value), and a PREVVAL expression
|for the other rows (this instance of PREVVAL refers to the sequence value
|generated by the NEXTVAL expression in the previous statement), as shown
|below:
| INSERT INTO order(orderno, custno)
| VALUES (NEXTVAL FOR order_seq, 123456);
|
| INSERT INTO line_item (orderno, partno, quantity)
| VALUES (PREVVAL FOR order_seq, 987654, 1);
- |Examples of where NEXTVAL and PREVVAL expressions can be specified are:
|
- |select-statement or SELECT INTO statement: within the select-clause as
|long as the statement does not contain a DISTINCT keyword, a GROUP BY clause,
|an ORDER BY clause, a UNION keyword, an INTERSECT keyword, or EXCEPT keyword
- |INSERT statement: within a VALUES clause
- |INSERT statement: within the select-clause of the fullselect
- |UPDATE statement: within the select-clause of the fullselect of an expression
|in the SET clause (either searched or positioned UPDATE statement)
- |VALUES INTO statement: within the select-clause of the fullselect of an
|expression
|
- |Examples of where NEXTVAL and PREVVAL expressions cannot be specified
|(SQLSTATE 428F9) are:
|
- |join condition of a full outer join
- |DEFAULT value for a column in a CREATE TABLE or ALTER TABLE statement
- |generated column definition in a CREATE TABLE or ALTER TABLE statement
- |condition of a CHECK constraint
- |CREATE TRIGGER statement
- |CREATE VIEW statement
- |CREATE METHOD statement
- |CREATE FUNCTION statement.
|
- |In addition, a NEXTVAL expression cannot be specified (SQLSTATE 428F9)
|in :
|
- |CASE expression
- |parameter list of an aggregate function
- |subquery
- |SELECT statement that contains a DISTINCT operator
- |join condition of join
- |GROUP BY clause of a SELECT statement
- |SELECT statement that is combined with another SELECT statement using
|the UNION, INTERSECT, or EXCEPT set operator
- |nested table expression
- |parameter list of a table function
- |WHERE clause of a SELECT, DELETE, or UPDATE statement
- |ORDER BY clause
- |parameter list of a CALL statement.
|
- |When a value is generated for a sequence, that value is consumed, and
|the next time that a value is needed, a new value will be generated.
|This is true even when the statement containing the NEXTVAL expression
|fails.
- |If an INSERT statement includes a NEXTVAL expression in the VALUES
|list for the column, and if some error occurs at some point during the
|execution of the INSERT (it could be a problem in generating the next
|sequence value, or a problem with the value for another column), then an
|insertion failure occurs, and the value generated for the sequence is
|considered to be consumed. In some cases, reissuing the same INSERT statement
|might lead to success.
|For example, consider an error that is the
|result of the existence of a unique index for the column for which NEXTVAL
|was used and the sequence value generated already exists in the index.
|It is possible that the next value generated for the sequence is a value
|that does not exist in the index and so the subsequent INSERT would succeed.
- |If in generating a value for a sequence, the maximum value for the sequence
|is exceeded (or the minimum value for a descending sequence) and cycles
|are not permitted, then an error occurs (SQLSTATE 23522). In this case,
|the user could ALTER the sequence to extend the range of acceptable values,
|or enable cycles for the sequence, or DROP and CREATE a new sequence
|with a different data type that has a larger range of values.
|For
|example, a sequence may have been defined with a data type of SMALLINT, and
|eventually the sequence runs out of assignable values. To redefine the
|sequence as INTEGER, you would need to drop and recreate the sequence
|with the new definition.
- |A reference to PREVVAL in a select statement of a cursor refers to a value
|that was generated for the specified sequence prior to the opening of the
|cursor. However, closing the cursor can affect the values returned by PREVVAL
|for the specified sequence in subsequent statements, or even for the same
|statement in the event that the cursor is reopened. This would be the case
|when the select statement of the cursor included a reference to NEXTVAL for
|the same sequence name.
|
|
|These examples assume that there is a table called "order" and that
|a sequence called "order_seq" is created as follows:
|CREATE SEQUENCE order_seq
| START WITH 1
| INCREMENT BY 1
| NOMAXVALUE
| NOCYCLE
| CACHE 24
|
- |Some examples of how to generate an "order_seq" sequence number with a
|NEXTVAL expression for the sequence created above:
|INSERT INTO order(orderno, custno)
| VALUES (NEXTVAL FOR order_seq, 123456);
|or,
|UPDATE order
| SET orderno = NEXTVAL FOR order_seq
| WHERE custno = 123456;
|or,
|VALUES NEXTVAL FOR order_seq INTO :hv_seq;
|
[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]