oracle伪列以及伪表.docx

oracle伪列以及伪表.docx

《oracle伪列以及伪表.docx》由会员分享，可在线阅读，更多相关《oracle伪列以及伪表.docx（23页珍藏版）》请在冰点文库上搜索。

oracle伪列以及伪表

oracle的伪列以及伪表

  oracle系统为了实现完整的关系数据库功能，系统专门提供了一组成为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象时由我们完成的，而是在我们建立时由Oracle完成的。

Oracle目前有以下伪列：

一、伪列：

 CURRVALANDNEXTVAL使用序列号的保留字

 LEVEL查询数据所对应的层级

 ROWID记录的唯一标识

 ROWNUM限制查询结果集的数量

二、伪表

DUAL表

该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。

一般用于验证函数。

例如：

selectsysdate,to_char（sysdate,'yyyy-mm-ddHH24:

mm:

ss'）fromdual

Oracle伪列RowID

一、什么是伪列RowID？

1、首先是一种数据类型，唯一标识一条记录物理位置的一个id，基于64位编码的18个字符显示。

2、未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。

二、RowID的用途

1,在开发中使用频率应该是挺多的，特别在一些update语句中使用更加频繁。

所以oracleERP中大部份的视图都会加入rowid这个字段。

  在一些cursor定义时也少不了加入rowid。

但往往我们在开发过程中，由于连接的表很多，再加上程序的复制，有时忽略了rowid对应的是那一个表中rowid，所以有时过程出错，

  往往花上很多时间去查错，最后查出来既然是update时带的rowid并非此表的rowid,所以在发现很多次的错误时，重视rowid起来了，开发中一定要注意rowid的匹配

2，能以最快的方式访问表中的一行。

3，能显示表的行是如何存储的。

4，作为表中唯一标识。

三，RowID的组成

rowid确定了每条记录是在Oracle中的哪一个数据对象，数据文件、块、行上。

ROWID的格式如下：

数据对象编号       文件编号       块编号           行编号

  OOOOOO            FFF               BBBBBB   RRR

  由data_object_id#+rfile#+block#+row#  组成，占用10个bytes的空间，

   32bit的data_object_id#,

   10bit的rfile#,  

   22bit的block#,

   16bit的row#.  

  所以每个表空间不能超过1023个数据文件。

四、RowID的应用

1。

准备数据：

  当试图对库表中的某一列或几列创建唯一索引时，

  系统提示ORA-01452：

不能创建唯一索引，发现重复记录。

   /*connscott/tiger

   Createtableempaasselect*fromemp;

   （为以下演示）插入重复记录

--创建一个表

createtabledupCityInfo

as

select*fromcityInfo

--制造重复数据

insertintodupCityInfoselect*fromcityInfo

2.查找重复记录的几种方法：

  2.1.查找大量重复记录

   方法1：

selectid,count（*）fromdupCityInfogroupbyidhavingcount（*）>1;

方法2：

Select*FromdupCityInfoWhereROWIDNotIn（

--每个组中最小的ROWID（或者最大的ROWID）

SelectMin（ROWID）

FromdupCityInfo

GroupByid

）;

  2.2.查找少量重复记录

select*fromdupCityInfoawhererowid<>（

selectmax（rowid）fromdupCityInfowhereid=a.id

）;

  3.删除重复记录的几种方法：

（1）.适用于有大量重复记录的情况（列上建有索引的时候，用以下语句效率会很高）：

方法1：

DeletefromdupCityInfo

WhereidIn（

SelectidFromdupCityInfoGroupByidHavingcount（*）>1

）

AndROWIDNotIn（

SelectMin（ROWID）FromdupCityInfoGroupByidHavingCount（*）>1

）;

方法2：

DeletefromdupCityInfoWhereROWIDNotIn（

--每个组中最小的ROWID

SelectMin（ROWID）FromdupCityInfoGroupByid）;

（2）.适用于有少量重复记录的情况（注意，对于有大量重复记录的情况，用以下语句效率会很低）：

DeletefromdupCityInfoawhererowid<>（

selectmax（rowid）fromdupCityInfowhereid=a.id）;

Oracle伪列RowNum

一、概述：

rownum从1开始；

    rownum按照记录插入时的顺序给记录排序，所以有orderby的子句时一定要注意啊！

    使用时rownum，orderby字段是否为主键有什么影响？

    子查询中rownumrn，而rn用到外查询中到底是怎样的序列？

    若id主键是按照从小到大的顺序插入的，select语句没有groupby和orderby的子句时，rownum的顺序和id顺序基本一致。

二、实例：

假设某个表t1（c1）有20条记录，如果用selectrownum,c1fromt1whererownum<10,只要是用小于号，查出来的结果很容易地与一般理解在概念上能达成一致，应该不会有任何疑问的。

可如果用selectrownum,c1fromt1whererownum>10（如果写下这样的查询语句，这时候在您的头脑中应该是想得到表中后面10条记录），你就会发现，显示出来的结果要让您失望了，那问题是出在哪呢？

因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列（强调：

先要有结果集）。

简单的说rownum是对符合条件结果的序列号。

它总是从1开始排起的。

所以你选出的结果不可能没有1，而有其他大于1的值。

所以您没办法期望得到下面的结果集：

11aaaaaaaa

12bbbbbbb

13ccccccc

.................

rownum>10没有记录，因为第一条不满足去掉的话，第二条的ROWNUM又成了1，所以永远没有满足条件的记录。

或者可以这样理解：

ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。

它取得第一条记录则rownum值为1，第二条为2，依次类推。

如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

有了以上从不同方面建立起来的对rownum的概念，那我们可以来认识使用rownum的几种现像

1.selectrownum,c1fromt1whererownum!

=10

为何是返回前9条数据呢？

它与selectrownum,c1fromtablenamewhererownum<10返回的结果集是一样的呢？

     因为是在查询到结果集后，显示完第9条记录后，之后的记录也都是!

=10,或者>=10,所以只显示前面9条记录。

也可以这样理解，rownum为9后的记录的rownum为10，因条件为!

=10，所以去掉，其后记录补上，rownum又是10，也去掉，如果下去也就只会显示前面9条记录了。

2.为什么rownum>1时查不到一条记录，而rownum>0或rownum>=1却总显示所有的记录？

     因为rownum是在查询到的结果集后加上去的，它总是从1开始。

3.为什么between1and10或者between0and10能查到结果，而用between2and10却得不到结果？

      原因同上一样，因为rownum总是从1开始。

从上可以看出，任何时候想把rownum=1这条记录抛弃是不对的，它在结果集中是不可或缺的，少了rownum=1就像空中楼阁一般不能存在，所以你的rownum条件要包含到1。

但如果就是想要用rownum>10这种条件的话话就要用子查询,把rownum先生成，然后对他进行查询。

select*

from（seletrownumasrn，t1.*fromawhere...）

wherern>10

一般代码中对结果集进行分页就是这么干的。

另外：

rowid与rownum虽都被称为伪列，但它们的存在方式是不一样的，rowid可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。

只要记录没被搬动过，rowid是不变的。

rowid相对于表来说又像表中的一般列，所以以rowid为条件就不会有rownum那些情况发生。

另外还要注意：

rownum不能以任何基表的名称作为前缀。

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

（1）rownum对于等于某值的查询条件

如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。

但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。

因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum=n（n>1的自然数）。

selectrownum,id,namefromstudentwhererownum=1;

（可以用在限制返回记录条数的地方，保证不出错，如：

隐式游标）

（2）rownum对于大于某值的查询条件

  如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle认为rownum>n（n>1的自然数）这种条件依旧不成立，所以查不到记录。

查找第二行以后的记录可使用以下的子查询方法来解决。

注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

select*from（selectrownumno,id,namefromstudent）whereno>2;

       NOID    NAME

-------------------------------------------------------------------

        3200003李三

        4200004赵四

（3）rownum对于小于某值的查询条件

rownum对于rownum1的自然数）的条件认为是成立的，所以可以找到记录。

selectrownum,id,namefromstudentwhererownum<3;

ROWNUMID    NAME

-------------------------------------------------------------------

 1200001张一

 2200002王二

查询rownum在某区间的数据，必须使用子查询。

例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。

但是这样的操作会在大数据集中影响速度。

select*from（

selectrownumno,id,namefromstudentwhererownum<=3

）

whereno>=2;

       NOID    NAME

-------------------------------------------------------------------

        2200002王二

        3200003李三

（4）rownum和排序

Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。

selectrownum,id,namefromstudentorderbyname;

  ROWNUMID    NAME

-------------------------------------------------------------------

        3200003李三

        2200002王二

        1200001张一

        4200004赵四

可以看出，rownum并不是按照name列来生成的序号。

系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。

为了解决这个问题，必须使用子查询；

selectrownum/*主查询中的rownum,与子查询无关*/,id,namefrom（

select*fromstudentorderbyname

）;

   ROWNUMID    NAME

-------------------------------------------------------------------

        1200003李三

        2200002王二

        3200001张一

        4200004赵四

这样就成了按name排序，并且用rownum标出正确序号（有小到大）

笔者在工作中有一上百万条记录的表，在jsp页面中需对该表进行分页显示，便考虑用rownum来作，下面是具体方法（每页显示20条）：

“select*fromtabnamewhererownum<20orderbyname"但却发现oracle却不能按自己的意愿来执行，而是先随便取20条记录，然后再orderby，后经咨询oracle,说rownum确实就这样，想用的话，只能用子查询来实现先排序，后rownum，方法如下：

"select*from（select*fromtabnameorderbyname）whererownum<20",但这样一来，效率会低很多。

后经笔者试验，只需在orderby的字段上加主键或索引即可让oracle先按该字段排序，然后再rownum；方法不变：

“select*fromtabnamewhererownum<20orderbyname"

取得某列中第N大的行

selectcolumn_namefrom（

selecttable_name.*,dense_rank（）over（orderbycolumndesc）rank

fromtable_name

）

whererank=&N；

假如要返回前5条记录：

select*fromtablenamewhererownum<6;（或是rownum<=5或是rownum!

=6）

假如要返回第5-9条记录：

select*fromtablename

where…

andrownum<10

minus

select*fromtablename

where…

andrownum<5

orderbyname

选出结果后用name排序显示结果。

（先选再排序）

注意：

只能用以上符号（<、<=、!

=）。

select*fromtablenamewhererownum!

=10;返回的是前９条记录。

不能用：

>,>=,=,Between...and。

由于rownum是一个总是从1开始的伪列，Oracle认为这种条件不成立。

另外，这个方法更快：

select*from（

selectrownumr,afromyourtablewhererownum<=20

orderbyname

）

wherer>10

这样取出第11-20条记录!

（先选再排序再选）

要先排序再选则须用select嵌套：

内层排序外层选。

rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!

rownum是在查询集合产生的过程中产生的伪列，并且如果where条件中存在rownum条件的话，则:

1：

假如判定条件是常量，则：

只能rownum=1,<=大于1的自然数，=大于1的数是没有结果的；大于一个数也是没有结果的

即当出现一个rownum不满足条件的时候则查询结束thisisstopkey（一个不满足，系统将该记录过滤掉，则下一条记录的rownum还是这个，所以后面的就不再有满足记录，thisisstopkey）；

2：

假如判定值不是常量，则：

若条件是=var,则只有当var为1的时候才满足条件，这个时候不存在stopkey,必须进行fullscan,对每个满足其他where条件的数据进行判定，选出一行后才能去选rownum=2的行……

以下摘自《中国IT实验室》

1.在oracle中实现selecttopn

  由于oracle不支持selecttop语句，所以在oracle中经常是用orderby跟rownum的组合来实现selecttopn的查询。

简单地说，实现方法如下所示：

select　列名１．．．列名ｎ　from    

（select　列名１．．．列名ｎ　from表名orderby列名１．．．列名ｎ）

whererownum<=n（抽出记录数）

orderbyrownumasc

  下面举个例子简单说明一下。

顾客表customer（id,name）有如下数据：

IDNAME

  01first

  02Second

  03third

  04forth

  05fifth

  06sixth

  07seventh

  08eighth

  09ninth

  10last

  则按NAME的字母顺抽出前三个顾客的SQL语句如下所示：

select*from

  （select*fromcustomerorderbyname）

  whererownum<=3

  orderbyrownumasc

  输出结果为：

  IDNAME

  08eighth

  05fifth

  01first

序列

可以保证多个用户对同一张表进行操作时生成唯一的整数，通常用来做表的主键。

创建序列：

createsequence<序列名字>

startwith<起始值>

incrementby<增长值>

[MaxValue<最大值>]

[NoMaxValue]//没有上限

例如：

createsequencemySeq

startwith1

increment1

删除序列：

dropsequence<序列名字>

修改序列:

altersequence<序列名字>

[startwith<起始值>]

[incrementby<增长值>]

[MaxValue<最大值>]

查看序列：

使用下列视图之一：

Dba_Sequences

All_Sequences

User_Sequences

访问序列：

CurVal返回序列的当前值

NextVal返回序列的下一个值

例如：

selectmySeq.NextVal,cityfrompost

Connectby语句

该语句结合伪列rownum或level可以产生一个结果集.

1.基本用法：

产生1~~100之间的整数

Selectrownumxhfromdualconnectbyrownum<=100;

Selectlevelxhfromdualconnectbylevel<=100;

2.高级用法

2.1.产生所有汉字，汉字内码为：

19968~~~40869之间

selectt.*from（

selectrownumxh,nchr（rownum）hzfromdual

connectbyrownum<65535

）t

wheret.xhbetween19968and40869

2.2.查找某个汉字的内码

使用CTE：

withmyChineseas（

selectt.*from（

selectrownumxh,nchr（rownum）hzfromdual

connectbyrownum<65535

）t

wheret.xhbetween19968and40869

）

select*frommyChinesewherehz='东'–查找汉字'东'的内码

2.3.拆分字符串

withtas（select'中华人民共和国'sentencefromdual）

selectsubstr（sentence,rownum,1）fromt

connectbyrownum<=（selectlength（sentence）fromt）

--orderbyNLSSORT（substr（sentence,rownum,1）,'NLS_SORT=SCHINESE_STROKE_M'）;--按笔画排序

一、集合操作

UNION      由每个查询