oracle中的伪列和伪表

oracle的伪列以及伪表

(2011-08-26 07:06:11)

转载▼

标签： 育儿

分类：oracle开发设计

oracle的伪列以及伪表

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

一、伪列：

  CURRVAL AND NEXTVAL 使用序列号的保留字

  LEVEL 查询数据所对应的层级

  ROWID 记录的唯一标识

  ROWNUM 限制查询结果集的数量
二、伪表

DUAL 表

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

一般用于验证函数。例如：

select sysdate,to_char(sysdate,'yyyy-mm-dd HH24: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#,
    10 bit 的rfile#, 
    22bit 的block#,
    16 bit 的row#. 
   所以每个表空间不能超过1023个 数据文件。

四、RowID的应用
1。准备数据：
   当试图对库表中的某一列或几列创建唯一索引时，
   系统提示 ORA-01452：不能创建唯一索引，发现重复记录。
    ,id,namefrom (

select * from student order by name

);
    ROWNUMID    NAME
---------- ---------------------------------------------------------
        1 200003 李三
        2 200002 王二
        3 200001 张一
        4 200004 赵四
这样就成了按name排序，并且用rownum标出正确序号（有小到大）
笔者在工作中有一上百万条记录的表，在jsp页面中需对该表进行分页显示，便考虑用rownum来作，下面是具体方法(每页显示20条)：
“select * from tabname where rownum<20 order byname" 但却发现oracle却不能按自己的意愿来执行，而是先随便取20条记录，然后再orderby，后经咨询oracle,说rownum确实就这样，想用的话，只能用子查询来实现先排序，后rownum，方法如下：
"select * from (select * from tabname order by name) whererownum<20",但这样一来，效率会低很多。
后经笔者试验，只需在order by的字段上加主键或索引即可让oracle先按该字段排序，然后再rownum；方法不变：   

“select * from tabname where rownum<20 order byname"

取得某列中第N大的行
select column_name from (

select table_name.*,dense_rank() over (order by column desc)rank

from table_name

)

where rank = &N；

 

假如要返回前5条记录：
select * from tablename where rownum<6;(或是rownum<= 5 或是rownum != 6)

假如要返回第5-9条记录：
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。(先选再排序)

注意：只能用以上符号(<、<=、!=)。

 

select * from tablename where rownum != 10;返回的是前９条记录。
不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle认为这种条件不成立。

另外，这个方法更快：
select * from (

select rownum r,a from yourtable where rownum <=20

order by name

)

where r > 10

这样取出第11-20条记录!(先选再排序再选)

 

要先排序再选则须用select嵌套：内层排序外层选。
rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
rownum 是在查询集合产生的过程中产生的伪列，并且如果where条件中存在 rownum 条件的话，则:

1： 假如判定条件是常量，则：
只能 rownum = 1, <= 大于1 的自然数， = 大于1的数是没有结果的；大于一个数也是没有结果的
即 当出现一个 rownum 不满足条件的时候则 查询结束 this is stopkey（一个不满足，系统将该记录过滤掉，则下一条记录的rownum还是这个，所以后面的就不再有满足记录，this is stopkey）；

2： 假如判定值不是常量，则：
若条件是 = var , 则只有当 var 为1 的时候才满足条件，这个时候不存在 stop key ,必须进行full scan,对每个满足其他where条件的数据进行判定，选出一行后才能去选rownum=2的行……

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

1.在oracle中实现select top n

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

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

select　列名１．．．列名ｎ　from   
(select　列名１．．．列名ｎ　from 表名 order by 列名１．．．列名ｎ)
where rownum<=n（抽出记录数）
order by rownum asc

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

顾客表customer(id,name)有如下数据：

ID NAME

   01 first

   02 Second

   03 third

   04 forth

   05 fifth

   06 sixth

   07 seventh

   08 eighth

   09 ninth

   10 last

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

select * from

   (select * from customerorder by name)

   whererownum<=3

   order by rownum asc

   输出结果为：

   ID NAME

   08 eighth

   05 fifth

   01 first

序列

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

创建序列：

      create sequence <序列名字>

      start with <起始值>

      increment by <增长值>

      [MaxValue <最大值>]

      [NoMaxValue] //没有上限

      例如：

             create sequence  mySeq

                    start with 1

                    increment 1

 

删除序列：

      drop sequence <序列名字>

修改序列:

      alter sequence <序列名字>

             [start with <起始值>]

      [increment by <增长值>]

      [MaxValue <最大值>]

查看序列：

      使用下列视图之一：

             Dba_Sequences

      All_ Sequences
             User_ Sequences

访问序列：

      CurVal 返回序列的当前值

      NextVal 返回序列的下一个值

例如：select mySeq.NextVal,city from post

 

 

 

Connect by 语句

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

1.   基本用法：

产生1~~100之间的整数

Select rownum xh from dual connect byrownum<=100;

Select level xh from dual connect bylevel<=100;

2.  高级用法

2.1.产生所有汉字，汉字内码为：19968~~~40869之间

  select t.* from(
    selectrownum xh,nchr(rownum) hz from dual
    connect byrownum<65535
  ) t
  where t.xh between 19968 and 40869

 

2.2.查找某个汉字的内码

      使用CTE：

with myChinese as(
      select t.* from(
          select rownum xh,nchr(rownum) hz from dual
          connect by rownum<65535
      ) t
      where 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 t
connect by rownum<=(select length(sentence) fromt)
--order by NLSSORT(substr(sentence,rownum,1) ,'NLS_SORT=SCHINESE_STROKE_M');--按笔画排序

 

 

 

一、集合操作

UNION        由每个查询选择的所有不重复的行         并集不包含重复值
UNIONALL        由每个查询选择的所有的行，包括所有重复的行        完全并集包含重复值
INTERSECT        由每个查询选择的所有不重复的相交行         交集
MINUS        在第一个查询中，不在后面查询中，并且结果行不重复         差集

 

  所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT(相交) 运算查询中的赋值顺序。

Union all 效率一般比union高。
1.1.union和union all
UNION(联合)运算
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。

例：

Sql代码

select  e1.empno,e1.ename,e1.mgr from emp e1union selecte2.empno,e2.job,e2.sal   
from emp e2 
select  e1.empno,e1.ename,e1.mgr from emp e1 unionselect e2.empno,e2.job,e2.sal

from emp e2

 

原则 :
     (1)被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
      (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.intersect交集操作
相交运算
用相交运算返回多个查询中所有的公共行。 无重复行。

原则:
    (1).在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT语句中的一样，但列的名字不必一样。
相交的表的倒序排序不改变结果。     (2).
相交不忽略空值。     (3).
使用：
Select statement intersect all Select statement;

 

1.3. minus差集操作
相减运算
用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。
原则:
    (1)在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
     (2)对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。

集合运算的原则
•在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配
•可以用圆括号改变执行的顺序
•ORDER BY子句：–只能出现在语句的最后–从第一个SELECT语句接收列名、别名，或者位置记号

注：•除了UNION ALL，重复行自动被清除
•在结果中的列名是第一个查询中出现的列名
•除了UNION ALL，默认情况下按升序顺序输出

 

二、exists和not exists的使用

1. 谓词exists和in概述

Exists用于只能用于子查询，可以替代in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料.

in不管匹配到匹配不到都全部匹配完毕.

使用exists可以将子查询结果定为常量，不影响查询效果，而且效率高。如查询所有销售部门员工的姓名，对比如下：
IN is often better if the results of the subquery are verysmall
When you write a query using the IN clause, you're telling therule-based optimizer that you want the inner query to drive theouter query.
When you write EXISTS in a where clause, you're telling theoptimizer that you want the outer query to be run first, using eachvalue to fetch a value from the inner query.
In many cases, EXISTS is better because it requires you to specifya join condition, which can invoke an INDEX scan. However, IN isoften better if the results of the subquery are very small. Youusually want to run the query that returns the smaller set ofresults first.

 

2.In和exists使用原则：

2.1.若子查询结果集比较小，优先使用in。

2.2.若外层查询比子查询小，优先使用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。

使用in

select last_name, title
       from s_emp
       where dept_id in
               (select id
               from s_dept
               wherename='Sales');       
使用exists
select last_name,title
      from s_emp e
      where  exists
      (select 'x' --把查询结果定为constant，提高效率
       from s_dept s where s.id=e.dept_id and s.name='Sales');

2.3 not exists的使用
     与exists 含义相反，也在子查询中使用，用于替代not in。其他一样。如查询不在销售部的员工姓名
select last_name,title
      from s_emp e
      where  not exists
      (select 'x' --把查询结果定为constant，提高效率
       from s_dept s where s.id=e.dept_id and s.name='Sales');

 

3．with子句

Oracle9i新增语法

1.使用with子句可以让子查询重用相同的with查询块,通过select调用，一般在with查询用到多次情况下。

2.with子句的返回结果存到用户的临时表空间中，只做一次查询，提高效率。

3.有多个查询的时候，第1个用with，后面的不用with，并且用逗号隔开。

4.最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割,查询必须用括号括起来

5.如果定义了with子句，而在查询中不使用，那么会报ora-32035错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询）

6.前面的with子句定义的查询在后面的with子句中可以使用。

With子句目的是为了重用查询。

语法：

With alias_name as (select1), --as和select中的括号都不能省略

alias_name2 as (select2),--后面的没有with，逗号分割

…

alias_namen as (select n) –与下面的查询之间没有逗号

 

Select ….

如查询销售部门员工的姓名：

--with clause
with a as

   (select id from s_deptwhere name='Sales' order by id)

 

select last_name,title

   from s_emp where dept_id in(select * from a);--使用select查询别名

 

例1：查询cityInfo表同一省中具有最小id和最大id的城市信息

方法1：

with sta as (

select province,min(id) minValue,max(id) maxValue

from cityInfo

group by province

)
select c.* from cityInfo c,sta

where (c.province=sta.province)

and(c.id=sta.minValue or c.id=sta.maxValue)

order by c.province

方法2：

with sta as ( select province,min(id) minValue,max(id) maxValuefrom cityInfo group by province)
select * from cityInfo c where (province,id) in (selectprovince,minValue from sta)
      or (province,id) in (select province,maxValue from sta)
order by province

方法3：

with sta as ( select province,min(id) minValue,max(id) maxValuefrom cityInfo group by province)
select c.* from cityInfo c
   inner join sta
   on (c.province=sta.province)and(c.id=sta.minValue or c.id=sta.maxValue)
order by c.province

方法4：(感觉速度慢！,也许是因为外查询记录太多？)

with sta as ( select province,min(id) minValue,max(id) maxValuefrom cityInfo group by province)
select c.* from cityInfo c
      where exists(
            select 1 from sta where
            (c.province=sta.province) and(c.id=sta.minValue orc.id=sta.maxValue)
         )
order by c.province

 

例2：查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept，员工表s_emp。

分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1步with查询查出所有部门的总薪水，第2步用with从第1步获得的结果表中查询出平均薪水，最后利用这两次的with查询比较总薪水大于平均薪水的结果，如下：

 方法1：

--step1:查询出部门名和部门的总薪水

with dept_costs as(  

   select a.dname,sum(b.sal)dept_total  

   from  depta,emp b  

   wherea.deptno=b.deptno  

   group bya.dname  

),  

--step2:利用上一个with查询的结果，计算部门的平均总薪水  

avg_costs as(  

   selectsum(dept_total)/count(*)dept_avg  

    fromdept_costs           

   )  

--step3:从两个with查询中比较并且输出查询结果  

select dname,dept_total  

     from dept_costs  

     where dept_total>(  

        select dept_avg  

         from avg_costs  

     )  

   order by dname

with

--step1:查询出部门名和部门的总薪水

dept_costs as(

           select a.dname,sum(b.sal) dept_total

             from

                     dept a,emp b

                    where a.deptno=b.deptno

                    group by a.dname

),

--step2:利用上一个with查询的结果，计算部门的平均总薪水

avg_costs as(

          select sum(dept_total)/count(*) dept_avg

           fromdept_costs        

)

--step3:从两个with查询中比较并且输出查询结果

select dname,dept_total

  from dept_costs

  where

  dept_total>

    (

    select dept_avg

     from

    avg_costs

    )

   order by dname;

 

从上面的查询可以看出，前面的with查询的结果可以被后面的with查询重用，并且对with查询的结果列支持别名的使用，在最终查询中必须要引用所有with查询，否则会报错ora-32035错误.

方法2：

with a as(  

select avg(sum(sal))  as avg_sal fromemp  group bydeptno  

)  

Select * from (select deptno ,sum(sal) as total2  fromemp    group by deptno)   

where total2  >(select a.avg_salfrom a) 

 

with a as(

select avg(sum(sal))  as avg_sal fromemp  group by deptno

)

Select * from (select deptno ,sum(sal) as total2  fromemp    group by deptno )

where total2  >(select a.avg_salfrom a) 

 注意：列别名不能在where中使用.

 

例3：找出平均成绩大于各班最小平均成绩的班名及其该班平均成绩

--各班最小的平均分
with minAvgScore as
(
select min(avgScore) minValue from (
     select class,avg(score) avgScore from studentscore group byclass
   )
)
select class,avg(score) avgScore
from studentscore
group by class
having avg(score)>(select minValue fromminAvgScore)

 

例4：一个查询，如果查询的结果行不满足是10的倍数，则补空行，直到是查询出的行数是10的倍数。例如：select * fromtrademark这个查询。

select 10-mod(count(*),10) shumu from trademark 返回表trademark中的总行数差几个才为10的倍数。

with cnt as (select 10-mod(count(*),10) shumu from trademark)–查询比10的倍数差几个空行
--Oracle 10g写法：

select id,name from trademark
unionall       --空行加进去
select null,null  --补空行
from dual connect by rownum<=(select shumu fromcnt); --10个中connect by可以使用子查询

 

Oracle 9i 写法：
with cnt as (select 10-mod(count(*),10) shumu from trademark)–查询比10的倍数差几个空行
select id,name
 

from trademark
unionall       --空行加进去
select null,null  --补空行
from all_objects where rownum<=(select shumu fromcnt);--使用all_objects行比较多
      其中：all_objects 为一个系统视图，大约有40000条记录