1、oracle 的伪列以及伪表 oracle 系统为了实现完整的关系数据库功能，系统专门提供了一组成为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象时由我们完成的，而是在我们建立时由 Oracle 完成的。Oracle 目前有以下伪列：一、伪列：CURRVAL AND NEXTVAL 使用序列号的保留字LEVEL 查询数据所对应的层级ROWID 记录的唯一标识ROWNUM 限制查询结果集的数量二、伪表DUAL 表该表主要目的是为了保证在使用 SELECT 语句中的语句的完整性而提供的。一般用于验证函数。例如：select sysdate,to_char(sysdate,yyyy
2、-mm-dd HH24:mm:ss) from dualOracle 伪列 RowID一、什么是伪列 RowID？1、首先是一种数据类型，唯一标识一条记录物理位置的一个 id，基于 64 位编码的 18 个字符显示。2、未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。二、RowID 的用途1,在开发中使用频率应该是挺多的，特别在一些 update 语句中使用更加频繁。所以oracle ERP 中大部份的视图都会加入 rowid 这个字段。在一些 cursor 定义时也少不了加入 rowid。但往往我们在开发过程中，由于连接的表很多，再加上程序的复制，有时忽略了 rowid 对应
3、的是那一个表中 rowid，所以有时过程出错，往往花上很多时间去查错，最后查出来既然是 update 时带的 rowid 并非此表的 rowid,所以在发现很多次的错误时，重视 rowid 起来了，开发中一定要注意 rowid 的匹配2，能以最快的方式访问表中的一行。3，能显示表的行是如何存储的。4，作为表中唯一标识。三，RowID 的组成rowid 确定了每条记录是在 Oracle 中的哪一个数据对象，数据文件、块、行上。 ROWID 的格式如下：数据对象编号 文件编号 块编号 行编号 OOOOOO FFF BBBBBB RRR 由 data_object_id# + rfile# + bl
4、ock# + row# 组成，占用 10 个 bytes 的空间， 32bit 的 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 dupCity
5、Infoas select * from cityInfo-制造重复数据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 B
6、y id);2.2.查找少量重复记录select * from dupCityInfo a where rowid 1) And ROWID Not In (Select Min(ROWID) From dupCityInfo Group By id Having Count(*) 1);方法 2：Delete from dupCityInfo Where ROWID Not In(-每个组中最小的 ROWIDSelect Min(ROWID) From dupCityInfo Group By id);(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)
7、：Delete from dupCityInfo a where rowid10 没有记录，因为第一条不满足去掉的话，第二条的 ROWNUM 又成了 1，所以永远没有满足条件的记录。或者可以这样理解：ROWNUM 是一个序列，是 oracle 数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则 rownum 值为 1，第二条为 2，依次类推。如果你用,=,=,between.and这些条件，因为从缓冲区或数据文件中得到的第一条记录的 rownum 为 1，则被删除，接着取下条，可是它的 rownum 还是 1，又被删除，依次类推，便没有了数据。有了以上从不同方面建立起来的对 rown
8、um 的概念，那我们可以来认识使用 rownum 的几种现像1. select rownum,c1 from t1 where rownum != 10 为何是返回前 9 条数据呢？它与 select rownum,c1 from tablename where rownum =10,所以只显示前面 9 条记录。也可以这样理解，rownum 为 9 后的记录的 rownum 为10，因条件为 !=10，所以去掉，其后记录补上，rownum 又是 10，也去掉，如果下去也就只会显示前面 9 条记录了。2. 为什么 rownum 1 时查不到一条记录，而 rownum 0 或 rownum =1
9、却总显示所有的记录？因为 rownum 是在查询到的结果集后加上去的，它总是从 1 开始。3. 为什么 between 1 and 10 或者 between 0 and 10 能查到结果，而用 between 2 and 10 却得不到结果？原因同上一样，因为 rownum 总是从 1 开始。从上可以看出，任何时候想把 rownum = 1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了 rownum=1 就像空中楼阁一般不能存在，所以你的 rownum 条件要包含到 1 。但如果就是想要用 rownum 10 这种条件的话话就要用子查询, 把 rownum 先生成，然后对他进行查询。
10、select * from (selet rownum as rn，t1.* from a where .)where rn 10一般代码中对结果集进行分页就是这么干的。另外：rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置 ID，在 DB 中唯一。只要记录没被搬动过，rowid 是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum 那些情况发生。另外还要注意：rownum 不能以任何基表的名称作为前缀。 对于 rownum 来说它是 oracle 系统顺序分配
11、为从查询返回的行的编号，返回的第一行分配的是 1，第二行是 2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum 不能以任何表的名称作为前缀。(1) rownum 对于等于某值的查询条件如果希望找到学生表中第一条学生的信息，可以使用 rownum=1 作为条件。但是想找到学生表中第二条学生的信息，使用 rownum=2 结果查不到数据。因为 rownum 都是从 1 开始，但是 1 以上的自然数在 rownum 做等于判断是时认为都是 false 条件，所以无法查到rownum = n（n1 的自然数）。select rownum,id,name from student wh
12、ere rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）(2）rownum 对于大于某值的查询条件如果想找到从第二行记录以后的记录，当使用 rownum2 是查不出记录的，原因是由于rownum 是一个总是从 1 开始的伪列，Oracle 认为 rownum n(n1 的自然数)这种条件依旧不成立，所以查不到记录。查找第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的 rownum 必须要有别名，否则还是不会查出记录来，这是因为 rownum 不是某个表的列，如果不起别名的话，无法知道 rownum 是子查询的列还是主查询的列。select * from
13、(select rownum no ,id,name from student) where no2;NO ID NAME- - -3 200003 李三4 200004 赵四（3）rownum 对于小于某值的查询条件rownum 对于 rownum1 的自然数）的条件认为是成立的，所以可以找到记录。select rownum,id,name from student where rownum =2;NO ID NAME- - -2 200002 王二3 200003 李三（4）rownum 和排序Oracle 中的 rownum 的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的
14、rowmun 行数据就必须注意了。select rownum ,id,name from student order by name;ROWNUM ID NAME- - -3 200003 李三2 200002 王二1 200001 张一4 200004 赵四可以看出，rownum 并不是按照 name 列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid 也是顺序分配的。为了解决这个问题，必须使用子查询；select rownum/*主查询中的 rownum,与子查询无关*/ ,id,name from (select * from student order by name)
15、;ROWNUM ID NAME- - -1 200003 李三2 200002 王二3 200001 张一4 200004 赵四这样就成了按 name 排序，并且用 rownum 标出正确序号（有小到大）笔者在工作中有一上百万条记录的表，在 jsp 页面中需对该表进行分页显示，便考虑用rownum 来作，下面是具体方法(每页显示 20 条)： “select * from tabname where rownum,=,=,Between.and。由于 rownum 是一个总是从 1 开始的伪列，Oracle 认为这种条件不成立。另外，这个方法更快：select * from (select r
16、ownum r,a from yourtable where rownum 10这样取出第 11-20 条记录!(先选再排序再选)要先排序再选则须用 select 嵌套：内层排序外层选。 rownum 是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有 1 就永远不会有 2! rownum 是在查询集合产生的过程中产生的伪列，并且如果 where 条件中存在 rownum 条件的话，则:1： 假如判定条件是常量，则： 只能 rownum = 1, start with increment by MaxValue NoMaxValue /没有上限例如：create s
17、equence mySeqstart with 1increment 1删除序列：drop sequence 修改序列:alter sequence start with increment by MaxValue 查看序列：使用下列视图之一：Dba_SequencesAll_ SequencesUser_ Sequences访问序列：CurVal 返回序列的当前值NextVal 返回序列的下一个值例如：select mySeq.NextVal,city from postConnect by 语句该语句结合伪列 rownum 或 level 可以产生一个结果集.1. 基本用法：产生 1100
18、 之间的整数Select rownum xh from dual connect by rownum=100;Select level xh from dual connect by level=100;2. 高级用法2.1.产生所有汉字，汉字内码为：1996840869 之间select t.* from(select rownum xh,nchr(rownum) hz from dualconnect by rownum65535) twhere t.xh between 19968 and 408692.2.查找某个汉字的内码使用 CTE：with myChinese as(select
19、 t.* from(select rownum xh,nchr(rownum) hz from dualconnect by rownum65535) twhere t.xh between 19968 and 40869)select * from myChinese where hz=东 查找汉字东 的内码2.3.拆分字符串with t as (select 中华人民共和国 sentence from dual)select substr(sentence,rownum,1) from tconnect by rownum=(select length(sentence) from t)-
20、order by NLSSORT(substr(sentence,rownum,1) , NLS_SORT=SCHINESE_STROKE_M);-按笔画排序一、集合操作UNION 由每个查询选择的所有不重复的行 并集不包含重复值UNION ALL 由每个查询选择的所有的行，包括所有重复的行 完全并集包含重复值INTERSECT 由每个查询选择的所有不重复的相交行 交集MINUS 在第一个查询中，不在后面查询中，并且结果行不重复 差集所有的集合运算与等号的优先级相同，如果 SQL 语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle 服务器将以从左到右的顺序计算。你应该使用圆括
21、号来明确地指定带另外的集合运算的 INTERSECT (相交) 运算查询中的赋值顺序。Union all 效率一般比 union 高。1.1.union 和 union allUNION(联合)运算 UNION 运算返回所有由任一查询选择的行。用 UNION 运算从多表返回所有行，但除去任何重复的行。 例：Sql 代码 1. select e1.empno,e1.ename,e1.mgr from emp e1 union select e2.empno,e2.job,e2.sal 2. from emp e2 原则 :(1)被选择的列数和列的数据类型必须是与所有用在查询中的 SELECT 语
22、句一致。列的名字不必相同。 (2)联合运算在所有被选择的列上进行。 (3)在做重复检查的时候不忽略空(NULL)值。 (4)IN 运算有比 UNION 运算高的优先级。 (5)在默认情况下，输出以 SELECT 子句的第一列的升序排序。 在例子中将输出empno,ename,mgr 三列数据。全联合(UNION ALL)运算 用全联合运算从多个查询中返回所有行。 原则： (1)和联合不同，重复的行不被过滤，并且默认情况下输出不排序。 不能使用 DISTINCT 关键字。 (2) 使用：Select statement union | union all Select statement;1.2
23、.intersect 交集操作相交运算 用相交运算返回多个查询中所有的公共行。 无重复行。原则:(1).在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的一样，但列的名字不必一样。 相交的表的倒序排序不改变结果。 (2). 相交不忽略空值。 (3). 使用：Select statement intersect all Select statement;1.3. minus 差集操作相减运算 用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个 SELECT 语句减第二个 SELECT 语句)。 原则: (1)在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT 语句中的一样，但列的名字不必一样。
