《oracle伪列以及伪表》由会员分享，可在线阅读，更多相关《oracle伪列以及伪表（15页珍藏版）》请在金锄头文库上搜索。
1、oracle的伪列以及伪表 oracle系统为了实现完整的关系数据库功能，系统专门提供了一组成为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象时由我们完成的，而是在我们建立时由Oracle完成的。Oracle目前有以下伪列：一、 伪列： CURRVAL AND NEXTVAL 使用序列号的保留字 LEVEL 查询数据所对应的层级 ROWID 记录的唯一标识 ROWNUM 限制查询结果集的数量二、伪表DUAL 表该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。一般用于验证函数。例如：select sysdate,to_char(sysdate,yyyy-m
2、m-dd HH24:mm:ss) from dualOracle伪列RowID一、什么是伪列RowID？1、首先是一种数据类型，唯一标识一条记录物理位置的一个id，基于64位编码的18个字符显示。2、未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。二、RowID的用途1,在开发中使用频率应该是挺多的，特别在一些update语句中使用更加频繁。所以oracle ERP中大部份的视图都会加入rowid这个字段。 在一些cursor定义时也少不了加入rowid。但往往我们在开发过程中，由于连接的表很多，再加上程序的复制，有时忽略了rowid对应的是那一个表中rowid，所以有时过程出
3、错， 往往花上很多时间去查错，最后查出来既然是update时带的rowid并非此表的rowid,所以在发现很多次的错误时，重视rowid起来了，开发中一定要注意rowid的匹配2，能以最快的方式访问表中的一行。3，能显示表的行是如何存储的。4，作为表中唯一标识。三，RowID的组成rowid确定了每条记录是在Oracle中的哪一个数据对象，数据文件、块、行上。 ROWID 的格式如下： 数据对象编号 文件编号 块编号 行编号 OOOOOO FFF BBBBBB RRR 由 data_object_id# + rfile# + block# + row# 组成，占用10个bytes的空间， 32
4、bit的 data_object_id#, 10 bit 的 rfile#, 22bit 的 block#, 16 bit 的 row#. 所以每个表空间不能超过1023个 数据文件。四、RowID的应用1。准备数据： 当试图对库表中的某一列或几列创建唯一索引时， 系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。 /*conn scott/tiger Create table empa as select * from emp;(为以下演示)插入重复记录-创建一个表create table dupCityInfoas select * from cityInfo-制造重复数据
5、insert into dupCityInfo select * from cityInfo 2. 查找重复记录的几种方法：2.1.查找大量重复记录方法1：select id,count(*) from dupCityInfo group by id having count(*) 1;方法2： Select * From dupCityInfo Where ROWID Not In(-每个组中最小的ROWID(或者最大的ROWID)Select Min(ROWID) From dupCityInfo Group By id);2.2.查找少量重复记录select * from dupCity
6、Info a where rowid(select max(rowid) from dupCityInfo where id=a.id );3.删除重复记录的几种方法： (1).适用于有大量重复记录的情况(列上建有索引的时候，用以下语句效率会很高)：方法1： Delete from dupCityInfo Where 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 C
7、ount(*) 1);方法2：Delete from dupCityInfo Where ROWID Not In(-每个组中最小的ROWIDSelect Min(ROWID) From dupCityInfo Group By id);(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：Delete from dupCityInfo a where rowid(select max(rowid) from dupCityInfo where id=a.id );Oracle伪列RowNum一、概述：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条记录)，你就会发现，显示出来的结果要让您失望了，那问题是出在哪
9、呢？因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。所以您没办法期望得到下面的结果集：11 aaaaaaaa12 bbbbbbb13 ccccccc.rownum 10 没有记录，因为第一条不满足去掉的话，第二条的ROWNUM又成了1，所以永远没有满足条件的记录。或者可以这样理解：ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果
10、你用,=,=,between.and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。有了以上从不同方面建立起来的对 rownum 的概念，那我们可以来认识使用 rownum 的几种现像1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢？它与 select rownum,c1 from tablename where rownum =10,所以只显示前面9条记录。也可以这样理解，rownum 为9后的记录的 rownum为10
11、，因条件为 !=10，所以去掉，其后记录补上，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 这条记录抛弃是不对的，它在结果集中是不可或缺的
12、，少了rownum=1 就像空中楼阁一般不能存在，所以你的 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 相对于表来说又
13、像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。另外还要注意：rownum不能以任何基表的名称作为前缀。 对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。(1) rownum 对于等于某值的查询条件如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时
14、认为都是false条件，所以无法查到rownum = n（n1的自然数）。select rownum,id,name from student where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）(2）rownum对于大于某值的查询条件 如果想找到从第二行记录以后的记录，当使用rownum2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum n(n1的自然数)这种条件依旧不成立，所以查不到记录。查找第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。select * from(select rownum no ,id,name from student) where no2; NO ID NAME- - -
