ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现

最新推荐文章于 2025-09-03 19:00:21 发布

原创最新推荐文章于 2025-09-03 19:00:21 发布 · 10w+ 阅读

33 ·

CC 4.0 BY-SA版权

文章标签：

#oracle序列 #sequence #ORA-02287

oracle 同时被 2 个专栏收录

8 篇文章

订阅专栏

elong的oracle点滴

6 篇文章

订阅专栏

本文介绍在Oracle数据库中如何正确使用序列生成唯一标识符，避免常见限制，并提供了解决方案，包括通过函数调用序列值的方法。

问题场景一：

SELECT id,name FROM (select SEQ_B_LOG_ID.NEXTVAL id , 'elong_deo' name from dual);

问题场景二：

insert into b_authority
  (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag)
select SEQ_B_AUTHORITY_ID.NEXTVAL,1, 'admin:role:listRole', '角色分页查询', '/admin/role/listRole.htm', 1,210,4, 1 from dual
union
select SEQ_B_AUTHORITY_ID.NEXTVAL,1, 'admin:role:toEditAuthority', '跳转角色权限编辑', '/admin/role/toEditAuthority.htm', 1,210,4, 1 from dual
union
select SEQ_B_AUTHORITY_ID.NEXTVAL,1, 'admin:role:findAuthsByRoleId', '获取角色权限', '/admin/role/findAuthsByRoleId.htm', 1,210,4, 1 from dual
union
select SEQ_B_AUTHORITY_ID.NEXTVAL,1, 'admin:role:updateRoleAuths', '更新角色权限', '/admin/role/updateRoleAuths.htm', 1,210,4, 1 from dual;

出现此提示的原因是oracle不让这样使用，具体说明如下：

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the

following constructs:
■ A subquery in a DELETE, SELECT, or UPDATE statement
■ A query of a view or of a materialized view
■ A SELECT statement with the DISTINCT operator
■ A SELECT statement with a GROUP BY clause or ORDER BY clause
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
■ The WHERE clause of a SELECT statement
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
■ The condition of a CHECK constrain

问题解决之避免：

所谓的避免指的是不走入oracle序列的禁区，也就是尽量不要符合上述几个情况，通过合理更改SQL语句达到我们的目的。

场景一解决：

SELECT SEQ_B_LOG_ID.NEXTVAL id ,name FROM (select  'elong_deo' name from dual);

场景二解决：

insert into b_authority
  (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag)
select SEQ_B_AUTHORITY_ID.NEXTVAL,t.c1,t.c2,t.c3,t.c4,t.c5,t.c6,t.c7 from (select 1 c1, 'admin:role:listRole' c2, '角色分页查询' c3, '/admin/role/listRole.htm' c4, 1 c5,210 c6,4 c7, 1 c8 from dual
union all
select 1, 'admin:role:toEditAuthority', '跳转角色权限编辑', '/admin/role/toEditAuthority.htm', 1,210,4, 1 from dual
union all
select 1, 'admin:role:findAuthsByRoleId', '获取角色权限', '/admin/role/findAuthsByRoleId.htm', 1,210,4, 1 from dual
union all
select 1, 'admin:role:updateRoleAuths', '更新角色权限', '/admin/role/updateRoleAuths.htm', 1,210,4, 1 from dual) t;

问题解决之另类强制执行：

很多oracle语句在使用的时候会有限制，但是Function在大多数情况下没有限制，我们可以通过程序来获取nextval以及currval

-- 获取序列下一个值
create or replace function get_seq_next (seq_name in varchar2) return number
is
  seq_val number ;
begin
  execute immediate 'select '|| seq_name|| '.nextval from dual' into seq_val ;
  return seq_val ;
end get_seq_next;

-- 获取序列当前值(需先执行nextval)
create or replace function get_seq_curr (seq_name in varchar2) return number
is
  seq_val number ;
begin
  execute immediate 'select '|| seq_name|| '.currval from dual' into seq_val ;
  return seq_val ;
end get_seq_curr;

场景一解决：

SELECT id,name FROM (select get_seq_next('SEQ_B_LOG_ID') id , 'elong_deo' name from dual);

场景二解决：

insert into b_authority
  (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag)
select get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:listRole', '角色分页查询', '/admin/role/listRole.htm', 1,210,4, 1 from dual
union
select get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:toEditAuthority', '跳转角色权限编辑', '/admin/role/toEditAuthority.htm', 1,210,4, 1 from dual
union
select get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:findAuthsByRoleId', '获取角色权限', '/admin/role/findAuthsByRoleId.htm', 1,210,4, 1 from dual
union
select get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:updateRoleAuths', '更新角色权限', '/admin/role/updateRoleAuths.htm', 1,210,4, 1 from dual;