ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现
来源:互联网 发布:日向日足 知乎 编辑:程序博客网 时间:2024/06/08 16:08
问题场景一:
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 dualunionselect SEQ_B_AUTHORITY_ID.NEXTVAL,1, 'admin:role:toEditAuthority', '跳转角色权限编辑', '/admin/role/toEditAuthority.htm', 1,210,4, 1 from dualunionselect SEQ_B_AUTHORITY_ID.NEXTVAL,1, 'admin:role:findAuthsByRoleId', '获取角色权限', '/admin/role/findAuthsByRoleId.htm', 1,210,4, 1 from dualunionselect 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 dualunion allselect 1, 'admin:role:toEditAuthority', '跳转角色权限编辑', '/admin/role/toEditAuthority.htm', 1,210,4, 1 from dualunion allselect 1, 'admin:role:findAuthsByRoleId', '获取角色权限', '/admin/role/findAuthsByRoleId.htm', 1,210,4, 1 from dualunion allselect 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 numberis 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 numberis 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 dualunionselect get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:toEditAuthority', '跳转角色权限编辑', '/admin/role/toEditAuthority.htm', 1,210,4, 1 from dualunionselect get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:findAuthsByRoleId', '获取角色权限', '/admin/role/findAuthsByRoleId.htm', 1,210,4, 1 from dualunionselect get_seq_next('SEQ_B_AUTHORITY_ID'),1, 'admin:role:updateRoleAuths', '更新角色权限', '/admin/role/updateRoleAuths.htm', 1,210,4, 1 from dual;
2 5
- ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现
- ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现
- ORA-02287: sequence number not allowed here
- ORA-02287: sequence number not allowed here解决
- ORA-02287: 此处不允许序号
- ORA-02287: 此处不允许序号
- ORA-02287: 此处不允许序号
- ORA-02287: 此处不允许序号
- ORA-02287: 此处不允许序号
- insert into...select from...ORA-02287: sequence number not allowed here问题
- oracle-序列 ora-02287 此处不允许序号
- Oracle的自动增长序列时提示 : ORA-02287: 此处不允许序号
- Mybatis 批量插入带oracle序列例子+ORA-02287: 此处不允许序号
- Mybatis 批量插入带oracle序列例子+ORA-02287: 此处不允许序号
- Oracle ORA-00984: column not allowed here
- ORA-01733: virtual column not allowed here
- oracle 此处不允许有序号
- 用merge into 的时候提示 ORA-01733: virtual column not allowed here
- [译]Google C++编程风格指南(五)
- cocos2dx之控制台输出
- cocos2dx - SQlite
- SAX解析方式
- 查看局域网主机ip
- ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现
- POJ 3525 Most Distant Point from the Sea
- 我对学习的一点看法
- 残花败柳
- 简单的ant打包,修改渠道号
- JavaScript之一:简介和使用
- 关于VS 中添加OpenCV 使用配置问题(版本号原因)
- c++编程常犯错误
- ios更改UITabBarController背景以及选中背景图片的方法 以及隐藏tabbar