ORA-02287此处不允许序号(sequence number not allowed here) 的避免及解决办法

川长思鸟来 2022-09-06 01:16 276阅读 0赞

# ORA-02287:此处不允许序号(sequence number not allowed here) 的避免及解决办法 #

> 原文链接：https://blog.csdn.net/qq525099302/article/details/43053291

#### 问题场景一： ####

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;