oracle伪列以及伪表

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

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

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

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

5、City Infoselect * from cityl nfo2. 查找重复记录的几种方法：2.1. 查找大量重复记录方法1：select id , count (*) from dupCityInfogroup by idhavingcount (*) 1;方法2：Select * From dupCitylnfo Where ROWID Not In (-每个组中最小的ROWID(或者最大的 ROWID)Select Min (ROWID)From dupCityInfoGroup By id)；2.2. 查找少量重复记录select * from dupCityInfo awhere

6、rowid (select max (rowid ) from dupCityInfowhere id =a. id)；3. 删除重复记录的几种方法：(1).适用于有大量重复记录的情况(列上建有索引的时候，用以下语句效率会很高)：方法1：Delete from dupCityInfoWhere id In (Select id From dupCityInfo Group By id Having count (*)1)And ROWID Not In (Select Min (ROWID) From dupCityInfo Group By id Having Count (*) 1)；方法

7、2 :Deletefrom dupCityInfoWhere ROWID Not In (-每个组中最小的 ROWIDSelect Min (ROWID) From dupCitylnfoGroup By id );(2).适用于有少量重复记录的情况 (注意，对于有大量重复记录的情况，用以下语句效率会很低)：Delete from dupCity Info awhere rowid (select max( rowid ) from dupCityInfo where id =a. id );Oracle伪列RowNu一、概述：rownum从1开始；rownum 按照记录插入时的顺序给记录排序

8、，所以有order by的子句时一定要注意啊！使用时rownum，order by 字段是否为主键有什么影响？子查询中rownum rn，而rn用到外查询中到底是怎样的序列？若id主键是按照从小到大的顺序插入的，select语句没有group by和order by的子句时，rownum的顺序和id顺序基本一致。二、实例：假设某个表 t1（c1） 有 20 条记录，如果用 select rownum,c1 from t1 where rownum 10 （如果写下这样的查询语句，这时候在您的头脑中应该是想得到表中后面10条记录），你就会发现，显示出来的结果要让您失望了，那问题是出在哪呢？因为R

9、OWNU对结果集加的一个伪列，即先查到结果集之后再加上去的一个列（强调:先要有结果集）。简单的说rownum是对符合条件结果的序列号。它 总是从1开始排起的。 所以你选出的结果不可能没有1,而有其他大于1的值。所以您没办法期望得到下面的结果11 aaaaaaaa12 bbbbbbb13 cccccccrownum 10没有记录，因为第一条不满足去掉的话，第二条的ROWNU又成了 1，所以永远没有满足条件的记录。或者可以这样理解：ROWNUM个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得 第一条记录则rownum值为1，第二条为2，依次类推。如果你用 ,=,=,betwe

10、en.and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1,则被删除，接着取下条，可是它的rown um还是1，又被删除，依次类推，便没有了数据。有了以上从不同方面建立起来的对rownum的概念，那我们可以来认识使用 rownum的几种现像1. select rownu m,c1 from t1 where rownum != 10为何是返回前 9 条数据呢？它与 select rown um,c1 from table name where rownum =10,所以只显示前面 9条记录。也可以这样理解，rownum为9后的记录的rownum为10,因条件为!=10，所

11、以去掉，其后记录补上，rownum又是10，也去掉，如果下去也就只 会显示前面9条记录了。2. 为什么rownum 1时查不到一条记录，而rownum 0 或rownum =1却总显示所有的记录？因为rownum是在查询到的结果集后加上去的，它总是从1开始。3. 为什么 between 1 and 10 或者 between 0 and 10 能查到结果，而用 between 2 and 10却得不到结果？原因同上一样，因为rownum总是从1开始。从上可以看出，任何时候想把rownum =1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了row nu m=1就像空中楼阁一般不能存在，所

12、以你的rownum条件要包含到1 。但如果就是想要用 rownum 10这种条件的话话就要用子查询，把rownum先生成，然后对他进行查询。select *from (selet rownum as rn ，t1.* from a where .) where rn 10一般代码中对结果集进行分页就是这么干的。另外：rowid与rownum虽都被称为伪列， 但它们的存在方式是不一样的，rowid可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情

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

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

15、道rownum是子查询的列还是主查询的列。select * from(select row num no ,id, name from stude nt) where no2;NO ID NAME3 200003李三4 200004赵四(3) rownum对于小于某值的查询条件row num对于rown um1的自然数)的条件认为是成立的，所以可以找到记录。 select rownu m,id, name from stude nt where rownum 3;ROWNUM ID NAME1 200001 张一2 200002 王二查询rownum在某区间的数据，必须使用子查询。例如要查询r

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

17、 ,id, name from stude nt order by n ame;ROWNUM ID NAME3 200003李三2 200002王二1 200001张一4 200004赵四可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录 排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询；select row num/* 主查询中的 rownum,与子查询无关 */ ,id, name from (select * from stude nt order by n ame);ROWNUM ID NAME1 200003李三2 200002

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

19、（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 from（select table_ name.*,de nse_ra nk（） over （order by colu mn desc） rankfrom table

20、_ name）where rank = &N ;假如要返回前5条记录：select * from table name where row num 6;（或是 rownum = 5 或是 rownum != 6）假如要返回第 5-9条记录：select * from table namewhere and rownum 10min usselect * from table namewhere and rownum5order by n ame选出结果后用name排序显示结果。（先选再排序）注意：只能用以上符号（、,=,=,Between.and 。由于rownum是一个总是从 1开始的伪列，

21、Oracle 认为这 种条件不成立。另外，这个方法更快：select * from （select row num r,a from yourtable where rownum 10这样取出第11-20条记录!（先选再排序再选）要先排序再选则须用select嵌套：内层排序外层选。rownum是随着结果集生成的，一旦生成，就不会变化了；同时 ，生成的结果是依次递加的， 没有1就永远不会有2!rownum是在查询集合产生的过程中产生的伪列，并且如果where条件中存在rownum条件的话，则：1：假如判定条件是常量，则：只能rownum = 1, =大于1的自然数，=大于1的数是没有结果的；大于

22、一个数也是没 有结果的即 当出现一个rownum不满足条件的时候则查询结束this is stop key（一个不满足，系统将该记录过滤掉，则下一条记录的rownum还是这个，所以后面的就不再有满足记录，this is stop key ）；2：假如判定值不是常量，则：若条件是=var ,则只有当var为1的时候才满足条件，这个时候不存在stop key , 必须进行full scan ,对每个满足其他 where条件的数据进行判定，选出一行后才能去选rownum=2 的行以下摘自中国IT实验室1. 在 oracle 中实现 select top n由于oracle 不支持 select to

23、p 语句，所以在 oracle 中经常是用 order by 跟rownum 的组合来实现select top n 的查询。简单地说，实现方法如下所示：select 列名1.列名n from（select 列名1.列名n from 表名order by 列名1.列名n ） where rownum=n （抽出记录数）order by rownum asc下面举个例子简单说明一下。顾客表customer（id,name） 有如下数据：ID NAME01 first02 Second03 third04 forth05 fifth06 sixth07 seve nth08 eighth09 nin

24、th10 last则按NAME勺字母顺抽出前三个顾客的SQL语句如下所示：select * from（select * from customer order by n ame）where rownum =3order by row num asc输出结果为：ID NAME08 eighth05 fifth01 first序列可以保证多个用户对同一张表进行操作时生成唯一的整数，通常用来做表的主键。 创建序列：create sequencestart with in creme nt by MaxValue NoMaxValue / 没有上限例如：create sequenee mySeqsta

25、rt with 1in creme nt 1删除序列：drop sequenee 修改序列：alter sequenee start with in creme nt by MaxValue 查看序列：使用下列视图之一：Dba_SequencesAll_ SequencesUser_ Sequences访问序列：CurVal返回序列的当前值NextVal返回序列的下一个值例如：select mySeq.NextVal,city from postConnect by 语句该语句结合伪列rownum或level可以产生一个结果集.1. 基本用法：产生1100之间的整数Select rownum

26、xh from dual connect by row num =100 ;Select level xh from dual connect by level =100;2. 高级用法2.1. 产生所有汉字，汉字内码为：1996840869之间select t.* from (selectrow num xh, nchr( rownum ) hz from dualconnect by row num 65535)twhere t.xhbetwee n19968 and 408692.2. 查找某个汉字的内码使用CTE:withmyChineseas (select t.*from (sel

27、ectrow num xh, nchr(row num ) hz from dualconnectby row num 65535)twhere t.xhbetween19968 and 40869)select * frommyChinesewhere hz=东-查找汉字东的内码2.3.拆分字符串with t as ( select 中华人民共和国 sentence from dual) select substr(sentence,rownum , 1) from tconnect by rownum ( select dept_avg from avg_costs) order by d

28、n ame从上面的查询可以看出，前面的with查询的结果可以被后面的with查询重用，并且对with查询的结果列支持别名的使用，在最终查询中必须要引用所有with查询，否则会报错ora-32035 错误.方法2：with a as(select avg(sum(sal) as avg_sal from emp group by dept no)Select * from (select dept no ,sum(sal) as total2 from emp group by dept no ) where total2 (select a.avg_sal from a)注意：列别名不能在where中使用.例3:找出平均成绩大