ORA-02287:此處不允許序號(sequence number not allowed here) 的避免以及強制實現
來源：程序員人生 發布時間：2015-01-26 09:21:38 閱讀次數：5765次
問題場景1：
SELECT id,name FROM (select SEQ_B_LOG_ID.NEXTVAL id , 'elong_deo' name from dual);
問題場景2：
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語句到達我們的目的。
場景1解決：
SELECT SEQ_B_LOG_ID.NEXTVAL id ,name FROM (select 'elong_deo' name from dual);
場景2解決：
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
-- 獲得序列下1個值
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;
場景1解決：
SELECT id,name FROM (select get_seq_next('SEQ_B_LOG_ID') id , 'elong_deo' name from dual);
場景2解決：
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;
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
