创建序列
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;
db19序列
最新推荐文章于 2024-03-08 09:38:52 发布