oracle伪列以及伪表

1、实用文案标准文档oracle的伪列以及伪表oracle系统为了实现完整的关系数据库功能，系统专门提供了一组成为伪列(P seudocolum n)的数据库列，这些列不是在建立对象时由我们完成的，而是在我们建 立时由Oracle完成的。Oracle目前有以下伪列:一、伪列:CURRVAL AND NEXTVAL使用序列号的保留字LEVEL查询数据所对应的层级ROWID 记录的唯一标识ROWNUM 限制查询结果集的数量二、伪表DUAL 表该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。般用于验证函数。例如:select sysdate ,to_char( sysdate ，y

2、yyy-mm-dd HH24:mm:ss' ) from dualOracle 伪列 RowID、什么是伪列RowID ？id，基于64位编码的18个1、首先是一种数据类型，唯一标识一条记录物理位置的一个字符显示。2、未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。、RowID的用途1, 在开发中使用频率应该是挺多的，特别在一些up date语句中使用更加频繁。所以oracleERP中大部份的视图都会加入rowid这个字段。在一些cursor定义时也少不了加入rowid。但往往我们在开发过程中,由于连接的表很多，再加上程序的复制，有时忽略了rowid 对应的是那一个表中

3、rowid，所以有时过程出错,往往花上很多时间去查错，最后查出来既然是up date时带的rowid并非此表的rowid,所以在发现很多次的错误时，重视rowid起来了，开发中一定要注意rowid的匹配2，能以最快的方式访问表中的一行。3，能显示表的行是如何存储的。4，作为表中唯一标识。三，RowID的组成rowid确定了每条记录是在Oracle中的哪一个数据对象，数据文件、块、行上。ROWID 的格式如下:数据对象编号文件编号块编号行编号OOOOOOFFFRRR由 data_object_id# + rfile# + block# + row#组成，占用10个bytes的空间,32bit的

4、data_object_id#,10 bit的 rfile#,22bit的 block#,16 bit的 row#.所以每个表空间不能超过1023个数据文件。四、RowID的应用1。准备数据:当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452:不能创建唯一索引，发现重复记录。/*conn scott/tigerCreate table empa as select * from emp;（为以下演示）插入重复记录-创建一个表 create table dup City Info asselect * from citylnfo-制造重复数据insert into dup

5、City Info select * from city Info2. 查找重复记录的几种方法:2.1.查找大量重复记录方法1 :select id ,co unt (*) from dup City Infogroup by id havi ng count (*) > 1;方法2 :Select * From dupCityInfoWhere ROWID Not In (-每个组中最小的 ROWID（或者最大的 ROWID）Select Min (ROWID )From dup City InfoGroup By id）；22查找少量重复记录select * from dup Cit

6、y Info awhere rowid <>(select max (rowid ) from dup City Infowhere id =a. id）；3. 删除重复记录的几种方法:（1）.适用于有大量重复记录的情况例上建有索引的时候，用以下语句效率会很高）：方法1 :Delete from dup CityInfoWhere id In (Select id From dupCityInfo)And ROWID Not In (Select Min (ROWID ) From)；Group Bydu pCityInfoid Having count (*)> 1Grou

7、p By id Having Count (*) > 1方法2 :Not In (Delete from dup CityI nfo Where ROWID-每个组中最小的 ROWIDGroup By id );Select Min (ROWID ) From dupCityInfo（2）.适用于有少量重复记录的情况（注意，对于有大量重复记录的情况，用以下语句效率会很低）：where rowid <>(Delete from dup City Info aselect max (rowid)from dup City Info where id =a. id );Oracle

8、 伪列 RowNum、概述:rownum 从1开始;rownum 按照记录插入时的顺序给记录排序，所以有order by 的子句时一定要注意啊!使用时rownum , order by 字段是否为主键有什么影响?子查询中rownum rn ，而rn用到外查询中到底是怎样的序列?若id主键是按照从小到大的顺序插入的，select语句没有group by 和order by 的子句时，row num的顺序和id顺序基本一致。、实例:假设某个表t1(c1) 有 20 条记录，如果用select rownum,c1 from t1 whererownum < 10,只要是用小于号，查出来的结果很

9、容易地与一般理解在概念上能达成一致,应该不会有任何疑问的。可如果用 select row nu m,c1 from t1 where row num > 10 (如果写下这样的查询语句，这时候在您的头脑中应该是想得到表中后面10条记录），你就会发现，显示出来的结果要让您失望了，那问题是出在哪呢?因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列（强调：先要有结果集）。简单的说rown um 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。所以您没办法期望得到下面的结果集:11 aaaaaaaa12 bbbbbbb1

10、3 cccccccrownum >10没有记录，因为第一条不满足去掉的话，第二条的ROWNUM 又成了 1，所以永远没有满足条件的记录。或者可以这样理解:ROWNUM 是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum 值为1，第二条为2，依次类推。如果你用>,>=,=,between.and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum 为 1，则被删除，接rownum 的!= 10,或着取下条，可是它的rown um 还是1，又被删除，依次类推，便没有了数据。有了以上从不同方面建立起来的对rownum 的概

11、念，那我们可以来认识使用 几种现像1. select row nu m,c1 from t1 where rownum != 10为何是返回前 9条数据呢？它与 select rown um,c1 from table name whererown um < 10返回的结果集是一样的呢?因为是在查询到结果集后，显示完第9条记录后，之后的记录也都是者>=10,所以只显示前面 9条记录。也可以这样理解，rownum 为9后的记录的rownum为10,因条件为！=10，所以去掉，其后记录补上，rownum 又是10，也去掉，如果下去 也就只会显示前面 9条记录了。2.为什么 rownum

12、 >1时查不到一条记录，而 rownum >0 或 rownum >=1 却总 显示所有的记录?因为rownum 是在查询到的结果集后加上去的，它总是从1开始。3.为什么 between 1 and 10 或者 between 0 and 10 能查到结果，而用 between 2 and 10 却得不到结果?原因同上一样，因为rownum 总是从1开始。从上可以看出，任何时候想把rownum=1rownum = 1这条记录抛弃是不对的，它在结果集中是不可或缺的，少了像空中楼阁一般不能存在，所以你的rown um 条件要包含到 1。但如果就是想要用rownum > 10

13、这种条件的话话就要用子查询，把rownum 先生成, 后对他进行查询。select * from (selet rownum as rn ， t1.* from a where .) where rn >10般代码中对结果集进行分页就是这么干的。另外：rowid与rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid相对于表来说又像表中的一般列，所以以rowid 为条件就 不会有rownum 那些情况发生。另外还要注意：rown um不能以任何基表的名称作为前

14、缀。对于rownum 来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum 不能以任何表的名称作为前缀。（1） row num对于等于某值的查询条件如果希望找到学生表中第一条学生的信息,可以使用rownum=1 作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2 结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum 做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。select rownu m,id, n

15、ame from stude nt where row num=1（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）（2） rownum对于大于某值的查询条件如果想找到从第二行记录以后的记录，当使用rownum>2 是查不出记录的，原因是由于rownum 是一个总是从1开始的伪列，Oracle认为row num > n（n >1 的自然数）这种条件rownum 必依旧不成立，所以查不到记录。查找第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的须要有别名，否则还是不会查出记录来，这是因为rownum 不是某个表的列，如果不起别名的话，无法知道 rownum

16、 是子查询的列还是主查询的列。select * from(select row num no ,id, name from stude nt) where no>2;NO ID NAME3 200003 李三4 200004 赵四(3) rownum 对于小于某值的查询条件rownum 对于rownum<n(n>1的自然数)的条件认为是成立的，所以可以找到记录。select rownu m,id, name from stude nt where rownum <3;ROWNUM IDNAME1 200001 张一2 200002 王二查询rownum 在某区间的数据，

17、必须使用子查询。 例如要查询rownum 在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句， 先让它返回小于等于三的记录行，然后在主查询中判断新的rownum 的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。select * from (select row num n o,id, name from stude nt where row num <=3where no >=2;NO ID NAME2 200002 王二3 200003 李三(4)rownum 和排序Oracle中的rownum 的是在取数据的时候产生的序号，所以想对指定

18、排序的数据去指定的rowmun 行数据就必须注意了。select rownum ,id, name from stude nt order by n ame;ROWNUM IDNAME3200003李三2200002王二1 200001张一4200004赵四可以看出，rownum 并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询;select row num /* 主查询中的 rownum, 与子查询无关 */ ,id, name from (select * from stude nt order by n

19、ame）；ROWNUM IDNAME1 200003李三2200002王二3200001张一4200004赵四这样就成了按name排序，并且用rownum标出正确序号（有小到大）笔者在工作中有一上百万条记录的表，在jsp页面中需对该表进行分页显示，便考虑用rownum 来作，下面是具体方法 （每页显示20条）：“select * from tab name where row num <20 order by n ame"但却发现oracle却不能按自己的意愿来执行，而是先随便取20条记录，然后再order by ，后经咨询oracle,说rownum 确实就这样，想用的话，只

20、能用子查询来实现先排序，后rownum ，方法如下:"select * from (select * from tab name order by n ame) where rownum <20",但这样一来，效率会低很多。后经笔者试验，只需在order by的字段上加主键或索引即可让oracle先按该字段排序,然后再rownum ;方法不变:"select * from tab name where row num <20 order by n ame"取得某列中第N大的行select colu mn_n ame fromselect ta

21、ble_ name.*,de nse_ra nk() over (order by colu mn desc) rankfrom table_ namewhere rank = &N假如要返回前5条记录:select * from table name where row num <6;(或是 row num <= 5 或是 row num != 6)假如要返回第5-9条记录:select * from table name where and rownum <10minus select * from table name where and rownum<5

22、 order by n ame选出结果后用name排序显示结果。（先选再排序注意：只能用以上符号（、=、!=）。返回的是前9条记录。不能用：>,>=,=,Betwee n.a nd。由于 row num是一个总是从1开始的伪列，Oracle认select * from table name where row num != 10;为这种条件不成立。另外，这个方法更快:select * from (select row num r,a from yourtable where rownum <= 20order by n ame where r > 10这样取出第11-2

23、0条记录!（先选再排序再选）要先排序再选则须用select嵌套：内层排序外层选。rownum是随着结果集生成的,旦生成，就不会变化了；同时,生成的结果是依次递加的,没有1就永远不会有2!rownum 是在查询集合产生的过程中产生的伪列，并且如果where 条件中存在 rownum条件的话，则：1 :假如判定条件是常量，则:只能rownum = 1, <= 大于1的自然数，=大于1的数是没有结果的；大于一个数也是没有结果的即 当出现一个 rownum 不满足条件的时候则查询结束this is stop key（一个不满足,系统将该记录过滤掉， 则下一条记录的rownum 还是这个，所以后面

24、的就不再有满足记录,this is stop key ）；2:假如判定值不是常量，则:若条件是=var ,则只有当var为1的时候才满足条件，这个时候不存在 stop key ,必须进行full scan ,对每个满足其他 where 条件的数据进行判定，选出一行后才能去选rownum=2 的行 以下摘自中国IT实验室1.在 oracle 中实现 select top n由于oracle 不支持select top 语句，所以在 oracle 中经常是用 order by 跟rownum 的组合来实现select top n的查询。简单地说，实现方法如下所示:select 列名1.列名n fr

25、om （select 列名1.列名n from 表名 order by 列名1.列名n ）where rownum<=n（扌由出记录数）order by rownum ascF面举个例子简单说明一下。顾客表customer(id,name)有如下数据:ID NAME01 first02 Second03 third04 forth05 fifth06 sixth07 seve nth08 eighth09 ninth10 last则按NAME的字母顺抽出前三个顾客的 SQL语句如下所示: select * from(select * from customer order by n am

26、e)where row num <=3order by row num asc输出结果为:ID NAME08 eighth05 fifth01 first序列可以保证多个用户对同一张表进行操作时生成唯一的整数，通常用来做表的主键。创建序列：create sequenee< 序歹U名字 >start with < 起始值 >in creme nt by< 增长值 >MaxValue < 最大值 >NoMaxValue /没有上限例如:create seque nee mySeqstart with 1 in creme nt 1删除序列:dr

27、op seque nee <序列名字修改序列:alter seque nee序列名字start with起始值in creme nt by增长值MaxValue最大值查看序列:使用下列视图之一:Dba_SequencesAll_ Seque ncesUser_ Seque nces访问序列:CurVal返回序列的当前值NextVal返回序列的下一个值例如：select mySeq.NextVal,city from postConnect by语句该语句结合伪列rownum或level可以产生一个结果集.1.基本用法:产生1100 之间的整数Select row numxh from d

28、ual connect by row num <= 100 ;Select level xh from dual connect by level <=100 ;2.高级用法2.1.产生所有汉字，汉字内码为:1996840869之间dualselect t.* from (select row numxh, nchr( rownum ) hz from connect by row num < 65535where t.xh between 19968 and 4086922查找某个汉字的内码使用CTE:with myCh in ese as(select t.* from

29、(select row num xh, nchr( row num ) hz from dualconnect by row num <65535wheret.xh between 19968 and40869select * frommyChinese where hz='东'-查找汉字'东'的内码2.3.拆分字符串with t as (select'中华人民共和国'sentence fromdual)select substr(sentence, rownum,1) from tfrom t)-order by NLSSORT(subs

30、tr(sentence, rownum ,1)，NLS_SORT=SCHINESE_STROKE_M' );-按 笔画排序connect by rownum <=( selectlength(sentence)一、集合操作UNION由每个查询选择的所有不重复的行并集不包含重复值UNION ALL由每个查询选择的所有的行，包括所有重复的行完全并集包含重复值INTERSECT由每个查询选择的所有不重复的相交行交集MINUS在第一个查询中，不在后面查询中，并且结果行不重复差集所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle

31、服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT （相交）运算查询中的赋值顺序。Un io n all 效率一般比un io n高。1.1.union禾R union allUNION（联合）运算UNION运算返回所有由任一查询选择的行。用UNION 运算从多表返回所有行，但除去任 何重复的行。例:Sql代码1. select e1.e mpno ,e1.e name,e1.mgr from emp e1 union select e2.e mpno ,e2.job,e2.sal2. from emp e2原则：SELECT语句一致。列的在例子中将

32、输出(1)被选择的列数和列的数据类型必须是与所有用在查询中的名子不必相冋。(2)联合运算在所有被选择的列上进行。(3)在做重复检查的时候不忽略空(NULL)值。(4)IN运算有比UNION运算高的优先级。在默认情况下，输出以 SELECT子句的第一列的升序排序。empno,en ame,mgr三歹U数据。全联合(UNION ALL)运算用全联合运算从多个查询中返回所有行。原则:(1)和联合不同，重复的行不被过滤，并且默认情况下输出不排序。不能使用DISTINCT关键字。(2) 使用:Select stateme nt union | union all Select stateme nt;1.

33、2.i ntersect交集操作相交运算用相交运算返回多个查询中所有的公共行。无重复行。原则:(1).在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。相交的表的倒序排序不改变结果。.相交不忽略空值。.使用:Select stateme nt in tersect all Select stateme nt;1.3. min us 差集操作 相减运算用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中(第一个SELECT语句减第二个 SELECT语句)。原则:在查询中被SELECT语句选择的列数和数据类型必须与在查

34、询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。对于MINUS 运算，在 WHERE子句中所有的列都必须在 SELECT子句中。集合运算的原则?在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配 ?可以用圆括号改变执行的顺序 ?ORDER BY子句：-只能出现在语句的最后-从第一个 SELECT语句接收列名、别名，或者位置记号注：？除了 UNION ALL，重复行自动被清除?在结果中的列名是第一个查询中出现的列名?除了 UNION ALL ，默认情况下按升序顺序输出exists 和 not exists 的使用1.谓词exists和in概述Exists用于只能用于子

35、查询，可以替代 in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料.in不管匹配到匹配不到都全部匹配完毕使用exists可以将子查询结果定为常量，不影响查询效果，而且效率高。如查询所有销售部门员工的姓名，对比如下:IN is ofte n better if the results of the subquery are very smallWhen you write a query using the IN clause, you're telli ng the rule-based op timizer that you want the inner q

36、uery to drive the outer query.When you write EXISTS in a where clause, you're telli ng the op timizer that you want the outer query to be run first, using each value to fetch a value from the inner query.In many cases, EXISTS is better because it requires you to sp ecify a join con diti on.which

37、 can in voke an INDEX sca n. However, IN is ofte n better if the results of thesubquery are very small. You usually want to run the query that retur ns the smaller set of results first.2.1 n和exists使用原则:2.1.若子查询结果集比较小，优先使用in。2.2.若外层查询比子查询小，优先使用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，若使用exists，则oracle会

38、优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。使用inselect last_ name, titlefrom s_emp where dep t_id in(select id from s_de pt where n ame='Sales');使用existsselect last_ name,titlefrom s_emp e where exists(select 'x'- 把查询结果定为constant，提高效率from s_de pt s where s.id=e.de pt_id and s.n ame='Sal

39、es');2.3 not exists 的使用与exists含义相反，也在子查询中使用，用于替代not in。其他一样。如查询不在销售部的员工姓名select last_ name,titlefrom s_emp ewhere not exists（select 'x'- 把查询结果定为constant，提高效率from s_de pt s where s.id=e.de pt_id and s.n ame='Sales');3. with子句Oracle9i新增语法1.使用with子句可以让子查询重用相同的with查询块，通过select调用，一般在

40、with查询用到多次情况下。2.with子句的返回结果存到用户的临时表空间中，只做一次查询，提高效率。3. 有多个查询的时候，第1个用with，后面的不用 with，并且用逗号隔开。,查询必须用括4. 最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割号括起来5.如果定义了 with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询）6.前面的with子句定义的查询在后面的with子句中可以使用。With子句目的是为了重用查询。语法:With alias na

41、me as (selecti ), -as 和 select 中的括号都不能省略alias name2 as(select2 ),-后面的没有with，逗号分割alias namen as(select n )-与下面的查询之间没有逗号Select .如查询销售部门员工的姓名:-with clausewith a as(select id from s_de pt where n ame='Sales' order by id)使用select查询别名select last_ name,titlefrom s_emp where dep t_id in (select * fr

42、om a);-例1 :查询citylnfo表同一省中具有最小id和最大id的城市信息方法1 :with sta as (select province,min (id) min Value,max(id) maxValuefrom cityl nfo group by provinceselect c.* from city Info c,stawhere (vin ce=sta .province)an d(c.id=sta. min Value or c.id=sta.maxValue)order by vince方法2 :with sta as ( select p

43、rovince,min(id) minValue,max(id) maxValue from cityInfo group by pr ovince)select * from cityInfo c where (pr ovince,id) in (select p rovince,minValue from sta)or (pr ovince,id) in (select pr ovince,maxValue from sta)order by pr ovince方法3 :with sta as ( select p rovince,min(id) minValue,max(id) maxV

44、alue from cityInfo group by pr ovince)select c.* from cityInfo cinner join sta on (c.pr ovince=sta .p rovince) and(c.id=sta.minValue or c.id=sta.maxValue)order by c.p rovince方法4:（感觉速度慢！，也许是因为外查询记录太多？）with sta as ( select province, min (id ) minValue ,max (id ) maxValue from cityInfo group by provinc

45、e)select c.* from cityInfo cwhere exists (select 1 from sta where(vince=vince)and (c.id =sta. minValue or c.id =sta. maxValue )order by vince例2 :查询出部门的总薪水大于所有部门 平均总薪水 的部门。部门表s_dept，员工表s_emp。1步with查分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第询查出所有部门的总薪水，第2步用with从第1步获得的结果表中查询出平均薪水,最后利用这两次的 with查询比较总薪水大于平均薪水的结果，如下:方法1:-step1:查询出部门名和部门的总薪水with dep t_costs as(select a.d name,sum(b.sal) dep t_total from dept a,e mp b where a.de ptno