openGauss开源数据库实战十七

任务十七 openGauss逻辑结构:序列和序列函数

任务目标

通过本任务,掌握序列的管理及序列函数的使用。

实施步骤

一、创建序列对象

1.创建表时使用serial数据类型自动创建序列对象

创建一个表的时候,如果指定表中某列的数据类型为serial,将自动创建一个以tablename_columnname_seq命名的序列对象。

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r
DROP TABLE IF EXISTS test;
create table test(id serial primary key,testnum serial);

执行下面的gsql元命令,查看序列test_testnum_seq的信息:

\d test_testnum_seq

将表的某个serial数据类型的列删除时,会自动删除隐含创建的序列对象。执行下面的SOL语句,删除表test的testnum列(数据类型为serial):

ALTER TABLE test DROP COLUMN testnum;

执行下面的gsql元命令,再次查看序列test_testnum_seq的信息:

\d test_testnum_seq

我们发现,该序列因为表的列被删除了,因而自动被删除掉了。删除某个表时,会连带删除创建表时为scrial数据类型的列隐含创建的序列。执行下面的命令,先查看序列testid_seq的信息,然后删除表test,再查看序列test_id_seq的信息:

\d test_id_seq
DROP TABLE test;
\d test_id_seq

2.使用CREATE SEQUENCE语句手动创建一个序列对象

为确保创建测试用的序列成功(多次做实验有可能没有删除之前创建的),首先执行下面的删除命令(如果序列不存在删除会报错,不过我们本来要的就是序列不存在):

DROP SEQUENCE IF EXISTS lab18_test_seq1;
DROP SEQUENCE IF EXISTS lab18_test_seq2;

执行下面的SQL语句,创建两个序列对象:

CREATE SEQUENCE lab18_test_seq1
                     INCREMENT BY 1
                     MINVALUE    1
                     NO MAXVALUE
                     START WITH 1;
CREATE SEQUENCE lab18_test_seq2
                     INCREMENT BY 1
                     MINVALUE    1
                     NO MAXVALUE
                     START WITH 1;

二、查看序列对象

在gsql中执行\d SerialName可以显示序列对象的信息。执行下面的gsql元命令,显示序列lab18_test_seq1的信息:

\d lab18_test_seq1

在gsql中执行\d SerialName可以显示序列对象的信息。执行下面的gsql元命令,显示序列lab18_test_seq2的信息:

\d lab18_test_seq2

三、使用序列函数操作序列

测试前首先创建测试表:

DROP TABLE IF EXISTS test;
CREATE TABLE test( col1  int);

1.nextval()函数

nextval()函数返回下一个从未使用过的序列值。因此,第一次在某个序列上调用nextval()函数,将获取该序列的开始值start_value。此后再次调用该序列的nextva()函数,将返回序列的下一个值(序列最后一个值last_value加上步进值increment_by)。步进值可以为正,也可以为负,因此每执行一次nextval()函数,序列的当前值就会递增(减)一次。

可以看出,该序列的开始值(start_value)是1,步进值(increment_by)是1。
执行下面的插入语句,语句中会调用nextva()函数来获取序列的值:

INSERT INTO test VALUES(nextval('lab18_test_seq1'));
INSERT INTO test VALUES(nextval('lab18_test_seq1'));
select * from test;

对序列lab18_test_seq1进行了两次取值,因此序列lab18_test_seq1的最后值是2,执行下面的命令可以确认这一点:

\d lab18_test_seq1

2.currval()函数

currval()函数可获取序列的当前值,如果序列的值还从未被使用过,调用curval()函数将会报错。多次执行currval()函数不会改变序列的当前值:

select currval('lab18_test_seq1');
select currval('lab18_test_seq1');
select currval('lab18_test_seq1');

3.lastval()函数

lastval()函数可查看最近执行了nextval()函数的序列的最后的值。下面的例子首先对序列lab18_test_seq1执行nextval()函数,然后执行lastval()函数获取序列lab18_test_seq1的最后的值;

INSERT INTO test VALUES(nextval('lab18_test_seq1'));
select lastval();

再对序列lab18_test_seq2执行nextval()函数,然后执行lastval()函数获取序列lab18_test_seq1的最后的值;

INSERT INTO test VALUES(nextval('lab18_test_seq2'));
select lastval();

4.setval()函数

setva()函数可重新设置序列的当前值。
(1)两个参数的setval(SeqName,StartVal)函数两个参数的setval()函数用于设置序列的起始值start_value,并假设该值已经被使用过,下一次执行nextval()函数的话,将返回start_value + increment_by（步进值）。

下面的语句中,首先调用两个参数的setval()函数,将序列lab18_test_seq1的start_value设置为100,然后调用nextval()函数,将序列的值插入表test中。因为我们使用两个参数的setval()函数重新设置序列的stat_value值为100,因此接下来调用序列的nextval()函数时,返回的值是start_value+increment_by=100+1=101。测试结果如下:

(2)三个参数的setval(SeqName,StartVal,Boolean)函数,新增加的第三个参数是一个布尔类型的参数。当第三个参数的值为true时,三个参数的setval()函数等价于两个参数的setval()函数。下面的语句中,首先使用第三个参数的值为true的setval()函数,将序列lab18_test_seq1的start_value设置为1000。这和使用两个参数的setval()函数效果一样。接下来调用序列的nextval()函数,返回的值是start_value+increment by=1000+1=1001。测试结果如下:

SELECT setval('lab18_test_seq1',1000, 'true');
select currval('lab18_test_seq1');
INSERT INTO test VALUES(nextval('lab18_test_seq1'));
select currval('lab18_test_seq1');
select * from test;

当第三个参数的值为false时,三个参数setval()函数设置序列的start_value值,并假设该值从未被使用过,下一次执行nextval()函数的话,将返回start_value的值。下面的语句中,首先使用第三个参数的值为false的setval()函数,设置序列lab18_test_seq1的start_val值为10000,接下来调用序列的nextval()函数,返回的值是start_value=10000。测试结果如下:

SELECT setval('lab18_test_seq1',10000, 'false');
select currval('lab18_test_seq1');
INSERT INTO test VALUES(nextval('lab18_test_seq1'));
select currval('lab18_test_seq1');
select * from test;

四、删除序列

执行下面的SQL语句,删除序列:

DROP SEQUENCE lab18_test_seq1;
DROP SEQUENCE lab18_test_seq2;

drop table test;
\q