Go语言视频零基础入门到精通

admin

问题场景一：
  

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

复制代码

问题场景二：
  
  

1. insert into b_authority
2.   (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag)
3. select SEQ_B_AUTHORITY_ID.NEXTVAL,1, "admin:role:listRole", "角色分页查询", "/admin/role/listRole.htm", 1,210,4, 1 from dual
4. union
5. select SEQ_B_AUTHORITY_ID.NEXTVAL,1, "admin:role:toEditAuthority", "跳转角色权限编辑", "/admin/role/toEditAuthority.htm", 1,210,4, 1 from dual
6. union
7. select SEQ_B_AUTHORITY_ID.NEXTVAL,1, "admin:role:findAuthsByRoleId", "获取角色权限", "/admin/role/findAuthsByRoleId.htm", 1,210,4, 1 from dual
8. union
9. 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);

复制代码

场景二解决：
  

1. insert into b_authority
2.   (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag)
3. 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
4. union all
5. select 1, "admin:role:toEditAuthority", "跳转角色权限编辑", "/admin/role/toEditAuthority.htm", 1,210,4, 1 from dual
6. union all
7. select 1, "admin:role:findAuthsByRoleId", "获取角色权限", "/admin/role/findAuthsByRoleId.htm", 1,210,4, 1 from dual
8. union all
9. select 1, "admin:role:updateRoleAuths", "更新角色权限", "/admin/role/updateRoleAuths.htm", 1,210,4, 1 from dual) t;

复制代码

  
问题解决之另类强制执行：
很多oracle语句在使用的时候会有限制，但是Function在大多数情况下没有限制，我们可以通过程序来获取nextval以及currval
  

1. -- 获取序列下一个值
2. create or replace function get_seq_next (seq_name in varchar2) return number
3. is
4.   seq_val number ;
5. begin
6.   execute immediate "select "|| seq_name|| ".nextval from dual" into seq_val ;
7.   return seq_val ;
8. end get_seq_next;

复制代码

1. -- 获取序列当前值(需先执行nextval)
2. create or replace function get_seq_curr (seq_name in varchar2) return number
3. is
4.   seq_val number ;
5. begin
6.   execute immediate "select "|| seq_name|| ".currval from dual" into seq_val ;
7.   return seq_val ;
8. end get_seq_curr;

复制代码

场景一解决：

  

1. SELECT id,name FROM (select get_seq_next("SEQ_B_LOG_ID") id , "elong_deo" name from dual);

复制代码

  
场景二解决：
  

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

复制代码