db19序列

创建序列
CREATE SEQUENCE xl_seq;

查询序列
SELECT xl_seq.NEXTVAL FROM dual;

使用伪列获取序列号
SELECT xl_seq.CURRVAL FROM dual;

SELECT xl_seq.NEXTVAL FROM dual
CONNECT BY level <= 5;

从 10 开始，递增 10，最小值 10，最大值 100
CYCLE让序列达到限制后产生值，降序序列为最小值    CACHE：缓存大小的最小值为 2
CREATE SEQUENCE id_seq INCREMENT BY 10
START WITH 10 MINVALUE 10 MAXVALUE 100
CYCLE  CACHE 2;

要获取序列的下一个值，您可以使用NEXTVAL伪列：显示10
SELECT id_seq.NEXTVAL FROM dual;

要获取序列的当前值
SELECT id_seq.CURRVAL FROM dual;

显示10 20 30 40 到 100 在返回10
SELECT id_seq.NEXTVAL FROM dual
CONNECT BY level <= 11;

删除序列
DROP SEQUENCE my_xl1;

在表列示例中使用序列  插入数据，使用序列
CREATE TABLE tasks(
    id NUMBER PRIMARY KEY,
    title VARCHAR2(255) NOT NULL
);
CREATE SEQUENCE tab2_id_xl;  创建序列

INSERT INTO tab2(id, xing_ming)
VALUES(tab2_id_xl.NEXTVAL, 'aaa');

INSERT INTO tab2(id, xing_ming)
VALUES(tab2_id_xl.NEXTVAL, 'aaa');

SELECT  * FROM tab2;

使用序列的默认值
CREATE SEQUENCE tab3_xl;  创建序列
CREATE TABLE tab3 (
  id          NUMBER DEFAULT tab3_xl.NEXTVAL,
  xing VARCHAR2(30)
);

插入数据测试        序列显示：1   999    null    2
INSERT INTO tab3 (xing) VALUES ('aaa');
INSERT INTO tab3 (id, xing) VALUES (999, 'bbb');
INSERT INTO tab3 (id, xing) VALUES (NULL, 'ccc');
INSERT INTO tab3 (xing) VALUES ('ddd');
SELECT  * FROM tab3;

创建序列和测试表    插入数据测试
CREATE SEQUENCE master_seq;
CREATE SEQUENCE detail_seq;

CREATE TABLE master (
  id          NUMBER DEFAULT master_seq.NEXTVAL,
  description VARCHAR2(30)
);

CREATE TABLE detail (
  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   NUMBER DEFAULT master_seq.CURRVAL,
  description VARCHAR2(30)
);

INSERT INTO master (description) VALUES ('Master 1');
INSERT INTO detail (description) VALUES ('Detail 1');
INSERT INTO detail (description) VALUES ('Detail 2');

INSERT INTO master (description) VALUES ('Master 2');
INSERT INTO detail (description) VALUES ('Detail 3');
INSERT INTO detail (description) VALUES ('Detail 4');

detail表内容    ID 1 2 3 4        MASTER_ID：1 1 2 2 
SELECT * FROM master;
SELECT * FROM detail;

序列空值   插入数据 
CREATE SEQUENCE default_seq;
CREATE SEQUENCE default_on_null_seq;
CREATE TABLE t2 (
  col1        NUMBER DEFAULT default_seq.NEXTVAL,
  col2        NUMBER DEFAULT ON NULL default_on_null_seq.NEXTVAL,
  description VARCHAR2(30)
);

INSERT INTO t2 (description) VALUES ('aaa');
INSERT INTO t2 (col1, col2, description) VALUES (999, 999,'999,999,DESCRIPTION');
INSERT INTO t2 (col1, col2, description) VALUES (NULL, NULL, 'NULL,NULL,DESCRIPTION');
INSERT INTO t2 (description) VALUES ('bbb');

col1的值 1 999 null 2        col2的值 1 999 2 3
desc t2
SELECT * FROM t2;

全局序列，退出在进去，会保存序列结果        
显示 1 2
CREATE SEQUENCE my_global_sequence_2 GLOBAL;
SELECT my_global_sequence_2.NEXTVAL FROM dual;
sqlplus lh/123@PDB1
SELECT my_global_sequence_2.NEXTVAL FROM dual;

退出在进入，不会保存序列号
CREATE SEQUENCE my_session_sequence SESSION;
SELECT my_session_sequence.NEXTVAL FROM dual;
sqlplus lh/123@PDB1
SELECT my_session_sequence.NEXTVAL FROM dual;

创建标准的序列    查询 
create sequence biaozhun_xl;
select sequence_name, scale_flag, extend_flag from user_sequences
where sequence_name='BIAOZHUN_XL';
select biaozhun_xl.nextval from dual;

create or replace procedure pop_bowie as
begin
for i in 1..100000 loop
insert into bowie values (biaozhun_xl.nextval, 'DAVID BOWIE');
commit;
end loop;
end;
/

LF_BLKS：187        PCT_USED：100
exec pop_bowie
analyze index bowie_id_i validate structure;
select name, lf_blks, pct_used from index_stats;

在开个窗口执行        LF_BLKS：374       PCT_USED：100
exec pop_bowie
analyze index bowie_id_i validate structure;
select name, lf_blks, pct_used from index_stats;

在开个窗口执行        LF_BLKS：562       PCT_USED：100
exec pop_bowie
analyze index bowie_id_i validate structure;
select name, lf_blks, pct_used from index_stats;

创建带SCALE的序列
create sequence ziggy_seq scale;
select sequence_name, scale_flag, extend_flag from user_sequences
where sequence_name='ZIGGY_SEQ';

select ziggy_seq.nextval from dual;
创建表
create table ziggy (id number constraint ziggy_id_i primary key, name varchar2(42));
填充表
create or replace procedure pop_ziggy as
begin
for i in 1..100000 loop
insert into ziggy values (ziggy_seq.nextval, 'DAVID BOWIE');
commit;
end loop;
end;
/
开三个窗口    LF_BLKS：326        PCT_USED：100
exec pop_ziggy
analyze index ziggy_id_i validate structure;
select name, lf_blks, pct_used from index_stats;

LF_BLKS：652        PCT_USED：100
exec pop_ziggy
analyze index ziggy_id_i validate structure;
select name, lf_blks, pct_used from index_stats;

LF_BLKS：1284        PCT_USED：100
exec pop_ziggy
analyze index ziggy_id_i validate structure;
select name, lf_blks, pct_used from index_stats;

查询结果 101268 101278 101022
select distinct substr(to_char(id),1,6) from ziggy;

30w条数据 
select * from ziggy


create sequence major_tom_seq maxvalue 9999 scale;
select sequence_name, scale_flag, extend_flag from user_sequences
where sequence_name='MAJOR_TOM_SEQ';

ORA-64603: 无法为 MAJOR_TOM_SEQ 实例化 NEXTVAL。将队列放
宽 3 位数或使用 SCALE EXTEND 变更队列。
select major_tom_seq.nextval from dual;

修改序列长度
alter sequence major_tom_seq maxvalue 9999999999;
select major_tom_seq.nextval from dual;

extend：带扩展
create sequence bowie_seq maxvalue 9999 scale extend;
select sequence_name, scale_flag, extend_flag from user_sequences
where sequence_name='BOWIE_SEQ';
查询结果    1011360001
select bowie_seq.nextval from dual;