Oracle Auto Increment Column - Sequence as Default Value
2024-08-31 04:09:21
Solution 1: Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only in trigger, which requires context switching from PL/SQL engine to SQL engine. So we need to create before insert trigger for each row, and assign sequence new value to the column using select into clause.
create table test_tab
(
id number primary key
);
create sequence test_seq start with 1 increment by 1 nocycle;
create or replace trigger test_trg
before insert on test_tab
for each row
begin
select test_seq.nextval into :new.id
from dual;
end;
/
Solution 2: From Oracle 11g, we can directly assign a sequence value to a pl/sql variable in trigger, So we can create before insert trigger for each row, and assign sequence nextval to the column directly.
create table test_tab
(
id number primary key
);
create sequence test_seq start with 1 increment by 1 nocycle;
create or replace trigger test_trg
before insert on test_tab
for each row
begin
:new.id := test_seq.nextval;
end;
/
Solution 3: With Oracle 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.
create sequence test_seq start with 1 increment by 1 nocycle;
create table test_tab
(
id number default test_seq.nextval primary key
);
最新文章
- 浅谈C#中常见的委托<;Func,Action,Predicate>;(转)
- SourceTree推送时,增加额外的远程仓库,不用每次都自定义粘贴复制网络
- TortoiseGit安装详解
- OPNET下 op_pk_copy()的时间问题
- -_-#Tiny Raytracer
- leetcode Binary Search Tree Iterator python
- MFC自绘控件学习总结第二贴
- 窗口显示于parent控件上(用到了ManualDock函数)
- WPF4.5新特性(MSDN的翻译读不太懂)
- IT软件开发中常用的英语词汇
- 使用SQL-Front启动MySQL8.0报错
- canvas-a11htmlANDcanvas.html
- Hibernate 再接触 总结
- hdu5064 DLX可重复覆盖+二分
- React Native中的约束规范
- hudson用SVN插件下载代码,用ant插件打包, 用SSH插件部署
- boosting方法
- BZOJ5289 HNOI/AHOI2018排列(贪心+堆)
- Template Method Design Pattern in Java
- 创建Server(tomcat)时候报Cannot create a server using the selected type