CREATE SERIAL

Serial is an object that creates a unique sequence number, and has the following characteristics.

Description

You can create a serial object in the database by using the CREATE SERIAL statement.

Syntax

CREATE SERIAL serial_name

[ START WITH initial ]

[ INCREMENT BY interval]

[ MINVALUE min | NOMINVALUE ]

[ MAXVALUE max | NOMAXVALUE ]

[ CACHE integer | NOCACHE ]

Example 1

--creating serial with default values

CREATE SERIAL order_no;

 

--creating serial within a specific range

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;

--creating serial with specifying the number of cached serial values

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000 CACHE 3;

 

--selecting serial information from the db_serial class

SELECT * FROM db_serial;

 

  name            current_val      increment_val         max_val         min_val         cyclic      started       cached_num        att_name

====================================================================================================================================================

'order_no'      10006            2                     20000           10000                0            1                3            NULL

Example 2

The following example shows how to create the athlete_idx table to store athlete codes and names and then create an instance by using the order_no. NEXT_VALUE increases the serial number and returns its value.

CREATE TABLE athlete_idx( code INT, name VARCHAR(40) );

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;

INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Park');

INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Kim');

INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Choo');

INSERT INTO athlete_idx VALUES (order_no.CURRENT_VALUE, 'Lee');

SELECT * FROM athlete_idx;

 

         code  name

===================================

        10000  'Park'

        10002  'Kim'

        10004  'Choo'

        10004  'Lee'