[转载]ORA-02287: 此处不允许序号
开发人员反映序列不能使用,自己回来测试了一下
select test.sequence.nextval,mgr,sum(sal) from emp group by mgr
ORA-02287: 此处不允许序号
果然,在外面再包一层就可以了
SQL> select test_sequence.nextval,mgr,sm from (select mgr,sum(sal) sm from emp group by mgr );
NEXTVAL MGR SM
---------- ----- ----------
27 7839 8275
28 7782 1300
29 7698 6550
30 7902 5800
31 7566 6000
32 7788 1100
6 rows selected
看来序列使用时有限制的
看到书上说有这么多限制
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
总结,无法使用序列的CURRVAL 和NEXTVAL的情况为:
■ A subquery in a DELETE, SELECT, or UPDATE statement
(子查询包括在DELETE,SELECT,UPDATE语句中)
■ A query of a view or of a materialized view
(对视图和物化视图的查询)
■ A SELECT statement with the DISTINCT operator
(SELECT查询中用到了DISTINCT)
■ A SELECT statement with a GROUP BY clause or ORDER BY clause --这个就是我遇到的那种情况
(SELECT查询中用到了GROUP BY ,ORDER BY )
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
(SELECT查询和其他SELECT查询结合,并且用到了UNION,INTERSECT,MINUS等操作)
■ The WHERE clause of a SELECT statement
(SELECT查询语句中的where条件不能用序列
例子:select 1 from dual where t_emp_calendar_seq.nextval = 95)
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
(??)
■ The condition of a CHECK constrain
(在CHECK限制条件中不能用)
最新文章
- 《.NET之美》消息及勘误
- PowerPoint基础
- ISO语言代码
- struts2权威指南学习笔记:struts2引入自定义库
- Delphi中的基础数据类型
- 《Java并发编程实战》读书笔记
- sqlserver2008 ,只能选C盘目录,不能选其它盘目录
- 读取iOS通讯录
- Blocks 推出矩阵公式。矩阵快速密
- 解决Xcode7多个模拟器的方法
- Java学习笔记18(Object类)
- C指针和数组的关系详解
- Groovy的脚本统一于类的世界
- 【DWM1000】 code 解密5一ACHOR 第一次回家Main 函数
- 微信小程序scroll-view不能实现下拉刷新
- 单点登录SSO图示和讲解(有代码范例)转帖
- 自学Zabbix4.2.1 Application介绍
- [Ynoi2016]这是我自己的发明(莫队)
- HDU 5985 概率
- python流行的原因
热门文章
- 并发编程从零开始(九)-ConcurrentSkipListMap&;Set
- 六个好习惯让你的PCB设计更优(转)
- word-break-ii leetcode C++
- clnt_create: RPC: Port mapper failure - Unable to receive: errno 113 (No route to host)
- 装了这几个IDEA插件,基本上一站式开发了!
- Jmeter 踩坑记录(七)
- mac 工作区
- C# for Beginner Part 21 to 30
- 菜鸡的Java笔记 第十二 - java 构造方法与匿名对象
- idea插件 Background Image Plus 随机更换背景图片