AntDB-Oracle Compatibility Developer’s Manual P3–64

Other types of functions

Sequence manipulation functions

This section describes the functions used to manipulate sequence objects in AntDB. A sequence object (also called a sequence generator or simply a sequence) is a specific table of single rows of records created by the CREATE SEQUENCE command. A sequence object typically produces a unique identifier for the table's records. The sequence functions listed below provide a simple, multi-user-safe way to retrieve the number of consecutive sequences from a sequence object.

sequence.NEXTVAL

sequence.CURRVAL

sequence is the identifier assigned to the sequence in the CREATE SEQUENCE command. The following describes how to use these functions.

NEXTVAL

This function serves to point the current position of the sequence object to the next value of this object. This operation cannot be undone after it is completed. Even if multiple sessions execute NEXTVAL synchronously, each session will only be able to safely obtain a unique sequence value.

CURRVAL

Returns the most recently obtained value in the sequence for the function NEXTVAL in the current session. (An error is reported if NEXTVAL has never been called in the sequence in the session). Note that because this function returns the value of the sequence in the session it is in, it gives a predictable answer as to whether any other sessions have executed NEXTVAL since the beginning of the current session.

If a sequence object has been created with default parameters, then calling the NEXTVAL function on this object will return consecutive values starting from 1. Additional system behavior for sequences can be obtained by using specific parameters in the CREATE SEQUENCE command.

Important note: To avoid blocking of synchronous transactions that fetch values from the same sequence, the function NEXTVAL operation is never rolled back; that is, once a value has been fetched, it is assumed to have been used even if the transaction executing the function NEXTVAL is subsequently interrupted. This means that an interrupted transaction may leave a "hole" of unused values in the sequence of values already assigned.

See the CREATE SEQUENCE command in the SQL Commands section for details.

character set

Name	Number of arguments	Function	case
NLS_CHARSET_ID	1	Return character set name reference should id value, AntDB's character set ID value and Name are different from Oracle, which is not compatible with Oracle.	select nls_charset_id('zhs16gbk') from dual;
NLS_CHARSET_NAME	1	Return character set name according to character id, AntDB's character set ID value and Name are different from Oracle, which is not compatible with Oracle.	select nls_charset_name(37) from dual;

Conditional Function

Name	Number of arguments	Function	For example:
coalesce	1~n	The first non-null value in the argument. Only return null if all parameters are null
NULLIF	2	NULLIF(value1, value2) returns null if value1 and value2 are equal, otherwise it returns value1.
LNNVL	1	lnnvl(condition), the argument is an expression, if the expression is true, it returns false; the expression is false, it returns true.	LNNVL(commission_pct >= .2)
NANVL	2	nanvl(n1,n2), return n2 if n1 is NaN, otherwise return n1.	NANVL(4.6,0)
NVL	2	NVL(c1,c2) , from left to right, returns the first argument that is not null. If both parameters are null, return null.	NVL(TO_CHAR(commission_pct), 'Not Applicable')
NVL2	3	NVL2(c1,c2,c3), if c1 is not empty then return c2, if c1 is empty then return c3	NVL2(commission, salary + commission, salary)
DECODE	2n	decode(condition,value1,return value1,value2,return value2,valuen,return valuen,default value), if condition=value1, return "return value1; if condition=value2, return "return value2", and so on, if no corresponding value is found, return the default value.