Oracle培训教材

类别:数据库 点击:0 评论:0 推荐:

 

一.SQL语言简介 1. SQL概述

SQL是一种面向数据库的通用数据处理语言规范,能完成以下几类功能:提取查询数据,插入修改删除数据,生成修改和删除数据库对象,数据库安全控制,数据库完整性及数据保护控制。

数据库对象包括表、视图、索引、同义词、簇、触发器、函数、过程、包、数据库链、快照等(表空间、回滚段、角色、用户)。数据库通过对表的操作来管理存储在其中的数据。

2. 数据库查询

1)    用SELECT语句从表中提取查询数据。语法为

SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

说明:SELECT子句用于指定检索数据库的中哪些列,FROM子句用于指定从哪一个表或视图中检索数据。

2)    SELECT中的操作符及多表查询WHERE子句。(LIKE,IS,…)

  WHERE子句中的条件可以是一个包含等号或不等号的条件表达式,也可以是一个含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比较运算符的条件式,还可以是由单一的条件表达通过逻辑运算符组合成复合条件。

比较操作符 =  >  <  >=  <=  !=  <>

SQL操作符 BETWEEN … AND…          IN   LIKE            IS NULL     

              NOT BETWEEN … AND… NOT IN NOT LIKE   IS NOT NULL

逻辑操作符 AND      OR  NOT

3)    ORDER BY   子句

ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列,返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。

4)    连接查询

利用SELECT语句进行数据库查询时,可以把多个表、视图的数据结合起来,使得查询结果的每一行中包含来自多个表达式或视图的数据,这种操作被称为连接查询。

连接查询的方法是在SELECT命令的FROM子句中指定两个或多个将被连接查询的表或视图,并且在WHERE子句告诉ORACLE如何把多个表的数据进行合并。根据WHERE子句中的条件表达式是等还是不等式,可以把连接查询分为等式连接和不等式连接。

5)    子查询

如果某一个SELECT命令(查询1)出现在另一个SQL命令(查询2)的一个子句中,则称查询1是查询2的子查询。

 

3.     基本数据类型(NUMBER,VARCHAR2,DATE) 

 ORACEL支持下列内部数据类型:

l       VARCHAR2 变长字符串,最长为2000字符。

l       NUMBER   数值型。

l       LONG      变长字符数据,最长为2G字节。

l       DATE       日期型。

l       RAW       二进制数据,最长为255字节。

l       LONG RAW  变长二进制数据,最长为2G字节。

l       ROWID      二六进制串,表示表的行的唯一地址。

l       CHAR       定长字符数据,最长为255。

 

4.     常用函数用法:

     一个函数类似于一个算符,它操作数据项,返回一个结果。函数在格式上不同于算符,它个具有变元,可操作0个、一个、二个或多个变元,形式为:

函数名(变元,变元,…)

函数具有下列一般类形:

l         单行函数

l         分组函数

1)单行函数对查询的表或视图的每一行返回一个结果行。它有数值函数,字符函数,日期函数,转换函数等。

2)分组函数返回的结果是基于行组而不是单行,所以分组函数不同于单行函数。在许多分组函数中可有下列选项:

l         DISTRNCT 该选项使分组函数只考虑变元表达式中的不同值。

l         ALL该选项使分组函数考虑全部值,包含全部重复。

全部分组函数(除COUNT(*)外)忽略空值。如果具有分组函数的查询,没有返回行或只有空值(分组函数的变元取值的行),则分组函数返回空值。

l       单行函数

1)        数字函数

ABS  取绝对值  POWER 乘方   LN   10为底数取冪

SQRT 平方根     EXP       e的n次乘方  LOG(m,n)   m为底数n取冪        

数学运算函数:ACOS ATAN ATAN2 COS COSH  SIGN SIN SINH TAN TANH

CEIL   大于或等于取整数  

FLOOR        小于或等于取整数

MOD          取余数               

ROUND(n,m)   按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,'Y')是年的第一天

TRUNC(n,m) 按m的位数取前面的数值如果trunc(日期), 确省的是去掉时间

 

2)        字符函数

CHR                   按数据库的字符集由数字返回字符 

CONCAT(c1,c2)              把两个字符c1,c2组合成一个字符, 和 || 相同

REPLACE(c,s,r)        把字符c里出现s的字符替换成r, 返回新字符

SUBSTR(c,m,n)           m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,

               m小与0,字符c从后面m处开始取n位字符

TRANSLATE(c,f1,t1)       字符c按f1到t1的规则转换成新的字符串

INITCAP             字符首字母大写,其它字符小写

LOWER                     字符全部小写

UPPER                 字符全部大写

LTRIM(c1,c2)              去掉字符c1左边出现的字符c2

RTRIM(c1,c2)

TRIM(c1,c2)               去掉字符c1左右两边的字符c2

LPAD(c1,n,c2)            字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位

RPAD(c1,n,c2)

 

3)        日期函数

ADD_MONTHS(d,n)                日期值加n月

LAST_DAY(d)                  返回当月的最后一天的日期

MONTHS_BETWEEN(d1,d2)               两个日期值间的月份,d1<d2 返回负数

NEXT_DAY(d)                 返回日期值下一天的日期

SYSDATE                  当前的系统时间

                      DUAL是SYS用户下一个空表,它只有一个字段dummy

4)        转换函数(1)

TO_CHAR(date,'日期显示格式')         

TO_CHAR(number)                 用于显示或报表的格式对齐

TO_DATE(char,'日期显示格式')          

TO_LOB                                 把long字段转换成lob字段

TO_NUMBER(char)                        用于计算或者比较大小

 

5)        转换函数(2)

to_date里日期显示格式

YYYY          年   YEAR    YYY      YY  Y

Q           季度     

MM              月   MONTH       MON    

W          星期 (week of month)       WW, IW (week of year)   

(说明:周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)

DD         日   DAY             DY 

HH24            小时      HH12            HH 

MI         分钟     

SS          秒

如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数 NL2005-2-28_FORMAT=yyyy-mm-dd hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NL2005-2-28_FORMAT=yyyy-mm-dd hh24:mi:ss

 

6)        转换函数(3)

如果想固定日期的显示格式可以用alter session命令改变

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

它的作用顺序如下:

initialization parameter

Environment variable

ALTER SESSION command

 

7)        转换函数 (4)

to_char(number)里数字显示格式

9     数字位

0     数字前面补0                  to_char(-1200,'00000.00')

.      小数点的位置   

,      标记位置的逗号       用在数字显示格式的左边           

L     根据数据库字符集加货币符号    to_char(-1200,'L9999.99')       

B     把数字0显示为空格,用在数字显示格式的右边

       

MI  右边的负数标记                       to_char(-1200,'9999.99MI')

PR    括起来的负数                     to_char(-1200,'9999.99PR')

EEEE     用指数方式表示                        to_char(-1200,'9999.99EEEE')

 

8)        输入字符,返回数字的函数

instr(c1,c2)     字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询

length(c)  按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1

 

9)        有逻辑比较的函数NVL(EXPR1, EXPR2)函数

解释:      IF EXPR1=NULL      RETURN EXPR2

        ELSE    RETURN EXPR1

10)    DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数

解释: IF AA=V1 THEN RETURN R1

          IF AA=V2 THEN RETURN R2

        ..…

  ELSE

  RETURN NULL           

举例: decode(id,1,'dept sale',2,'dept tech')

l        集合函数      经常和group by一起使用

1)      集合函数列表

AVG             (DISTINCT | ALL | N)                     取平均值

COUNT        (DISTINCT | ALL | N | expr | * )     统计数量

MAX             (DISTINCT | ALL | N)                     取最大值

MIN             (DISTINCT | ALL | N)                     取最小值

SUM             (DISTINCT | ALL | N)                     取合计值

STDDEV             (DISTINCT | ALL | N)                     取偏差值,如果组里选择的内容都相同,结果为0

VARIANCE (DISTINCT | ALL | N)                        取平方偏差值

 

2)      使用集合函数的语法

SELECT column, group_function FROM table

WHERE condition       GROUP BY group_by_expression

HAVING group_condition  ORDER BY column;

3)      使用count时的注意事项

SELECT COUNT(*) FROM table;

SELECT COUNT(常量) FROM table;

 

都是统计表中记录数量,如果没有PK后者要好一些

SELECT COUNT(all 字段名) FROM table;

SELECT COUNT(字段名) FROM table;

 

不会统计为NULL的字段的数量

SUM,AVG时都会忽略为NULL的字段

 

4)    用group by时的限制条件

SELECT字段名不能随意, 要包含在GROUP BY的字段里

 GROUP BY后ORDER BY时不能用位置符号和别名

 限制GROUP BY的显示结果, 用HAVING条件

 

5)      例子

SQL> select title,sum(salary) payroll from s_emp

where title like 'VP%'   group by title

having sum(salary)>5000    order by sum(salary) desc;

找出某表里字段重复的记录数, 并显示

SQL> select (duplicate field names) from table_name

     group by (list out fields) having count(*)>1;

5. 数据操纵语言命令:

数据库操纵语言(DML)命令用于查询和操纵模式对象中的数据,它不隐式地提交当前事务。它包含UPDATE、INSERT、DELETE、EXPLAIN PLAN、SELECT和LOCK TABLE 等命令。下面简单介绍一下:

1)        UPDATE tablename SET {column1=expression1,column2=expression2,…} WHERE {conditions};

例如:S     QL>UPDATE EMP

            SET JOB =’MANAGER’

             WHERE ENAME=’MAPTIN’;

              SQL >SELECT * FROM EMP;

UPDATE子句指明了要修改的数据库是EMP,并用WHERE子句限制了只对名字(ENAME)为’MARTIN’的职工的数据进行修改,SET子句则说明修改的方式,即把’MARTION’的工作名称(JOB)改为’MARAGER’.

 

2)        INSERT INTO tablename {column1,column2,…} VALUES {expression1,expression2,…};

例如:SQL>SELECT INTO DEPT(DNAME, DEPTNO)

                     VALUES (‘ACCOUNTING’,10)

 

3)        DELETE FROM tablename WHERE {conditions};

例如:SQL>DELETE FROM EMP

                     WHERE EMPNO = 7654;

DELETE命令删除一条记录,而且DELETE命令只能删除整行,而不能删除某行中的部分数据.

 

 

4)        事务控制命令

提交命令(COMMIT):可以使数据库的修改永久化.设置AUTOCOMMIT为允许状态:SQL >SET AUTOCOMMIT ON;

回滚命令(ROLLBACK):消除上一个COMMIT命令后的所做的全部修改,使得数据库的内容恢复到上一个COMMIT执行后的状态.使用方法是:

SQL>ROLLBACK;

 

二.Oracle扩展PL/SQL简介 1. PL/SQL概述。

PL/SQL是Oracle对SQL规范的扩展,是一种块结构语言,即构成一个PL/SQL程序的基本单位(过程、函数和无名块)是逻辑块,可包含任何数目的嵌套了快。这种程序结构支持逐步求精方法解决问题。一个块(或子块)将逻辑上相关的说明和语句组合在一起,其形式为:

DECLARE

---说明

BEGIN

---语句序列

EXCEPTION

---例外处理程序

END;

它有以下优点:

l        支持SQL;

l        生产率高;

l        性能好;

l        可称植性;

l        与ORACLE集成.

 

2. PL/SQL体系结构

PL/SQL运行系统是种技术,不是一种独立产品,可认为这种技术是PL/SQL块和子程序的一种机,它可接收任何有效的PL/SQL块或子程序。如图所示:

 

 

PL/SQL块

              PL/SQL机

 

                     

PL/SQL块

过程性语句

执行器

SQL语句

 

 

 

 

 

 

 

 

 

 

 

PL/SQL机可执行过程性语句,而将SQL语句发送到ORACLE服务器上的SQL语句执行器。在ORACLE预编译程序或OCI程序中可嵌入无名的PL/SQL块。如果ORACLE具有PROCEDURAL选件,有名的PL/SQL块(子程序)可单独编译,永久地存储在数据库中,准备执行。

 

 

3. PL/SQL基础:

PL/SQL有一字符集、保留字、标点、数据类型、严密语法等,它与SQL有相同表示,现重点介绍。

1)        数据类型:如下表所示

 

 

数据类型

子类型

纯量类型

数值

BINARY_INTEGER

NATURAL,POSITIVE

NUMBER

DEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT

字符

CHAR

CHARACTER,STRING

VARCHAR2

VARCHAR

LONG

 

LONG RAW

 

RAW

 

RAWID

 

逻辑

BOOLEAN

 

日期

DATE

 

组合

类型

记录

RECORD

 

TABLE

 

 

2)        变量和常量

在PL/SQL程序中可将值存储在变量和常量中,当程序执行时,变量的值可以改变,而常量的值不能改变。

 

3)        程序块式结构:

DECLARE

        变量说明部分;

BEGIN

        执行语句部分;

[EXCEPTION

        例外处理部分;]

END;

 

4. 控制语句:

分支语句:

IF condition THEN

        Sequence_of_statements;

END IF;

 

IF condition THEN

        Sequence_of_statement1;

ELSE

        Sequence_of_statement2;

END IF;

 

IF condition1 THEN

        Sequence_of_statement1;

ELSIF condition2 THEN

        Sequence_of_statement2;

ELSIF condition3 THEN

        Sequence_of_statement3;

END IF;

 

 

5. 循环语句:

LOOP

              Sequence_of_statements;

              IF condition THEN

                     EXIT;

              END IF;

END LOOP;

      WHILE condition LOOP

              Sequence_of_statements;

END LOOP;

 

FOR counter IN lower_bound..higher_bound LOOP

              Sequence_of_statements;

END LOOP;

 

6. 子程序:

存储过程:

              CREATE PROCEDURE 过程名 (参数说明1,参数说明2, 。。。) IS

                     [局部说明]

              BEGIN

                     执行语句;

              END 过程名;

 

存储函数:

              CREATE FUNCTION 函数名 (参数说明1,参数说明2, 。。。)

RETURN 类型 IS

                     [局部说明]

              BEGIN

                     执行语句;

                     END 函数名;

三.Decode()函数使用技巧 1. 含义解释:

  DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下:
IF 条件=值1 THEN
    RETURN(翻译值1)
ELSIF 条件=值2 THEN
    RETURN(翻译值2)
    ......
ELSIF 条件=值n THEN
    RETURN(翻译值n)

ELSE
    RETURN(缺省值)
END IF

2. 使用方法:

1)            比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1

例如:
变量1=10,变量2=20
则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。

表、视图结构转化
现有一个商品销售表sale,表结构为:
month    char(6)      --月份
sell    number(10,2)   --月销售金额

现有数据为:
200001  1000
200002  1100
200003  1200
200004  1300
200005  1400
200006  1500
200007  1600
200101  1100
200202  1200
200301  1300

想要转化为以下结构的数据:
year   char(4)      --年份
month1  number(10,2)   --1月销售金额
month2  number(10,2)   --2月销售金额
month3  number(10,2)   --3月销售金额
month4  number(10,2)   --4月销售金额
month5  number(10,2)   --5月销售金额
month6  number(10,2)   --6月销售金额
month7  number(10,2)   --7月销售金额
month8  number(10,2)   --8月销售金额
month9  number(10,2)   --9月销售金额
month10  number(10,2)   --10月销售金额
month11  number(10,2)   --11月销售金额
month12  number(10,2)   --12月销售金额

结构转化的SQL语句为:
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
    select
    substrb(month,1,4),
    sum(decode(substrb(month,5,2),'01',sell,0)),
    sum(decode(substrb(month,5,2),'02',sell,0)),
    sum(decode(substrb(month,5,2),'03',sell,0)),
    sum(decode(substrb(month,5,2),'04',sell,0)),
    sum(decode(substrb(month,5,2),'05',sell,0)),
    sum(decode(substrb(month,5,2),'06',sell,0)),
    sum(decode(substrb(month,5,2),'07',sell,0)),
    sum(decode(substrb(month,5,2),'08',sell,0)),
    sum(decode(substrb(month,5,2),'09',sell,0)),
    sum(decode(substrb(month,5,2),'10',sell,0)),
    sum(decode(substrb(month,5,2),'11',sell,0)),
    sum(decode(substrb(month,5,2),'12',sell,0))
    from sale
    group by substrb(month,1,4);

四.NULL 使用详解 1. 含义解释:

问:什么是NULL?
答:在我们不知道具体有什么数据的时候,也即未知,可以用NULL,我们称它为空,ORACLE中,含有空值的表列长度为零。
ORACLE允许任何一种数据类型的字段为空,除了以下两种情况:
1、主键字段(primary key),
2、定义时已经加了NOT NULL限制条件的字段

说明:
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同。
3、对空值做加、减、乘、除等运算操作,结果仍为空。
4、NULL的处理使用NVL函数。
5、比较时使用关键字用“is null”和“is not null”。
6、空值不能被索引,所以查询时有些符合条件的数据可能查不出来,count(*)中,用nvl(列名,0)处理后再查。
7、排序时比其他数据都大(索引默认是降序排列,小→大),所以NULL值总是排在最后。

2. 使用方法:

SQL> select 1 from dual where null=null;

 

没有查到记录

 

SQL> select 1 from dual where null='';

 

没有查到记录

 

SQL> select 1 from dual where ''='';

 

没有查到记录

 

SQL> select 1 from dual where null is null;

 

        1

---------

        1

 

SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

 

        1

---------

        1

 

对空值做加、减、乘、除等运算操作,结果仍为空。

SQL> select 1+null from dual;

SQL> select 1-null from dual;

SQL> select 1*null from dual;

SQL> select 1/null from dual;

 

 

查询到一个记录.

 

注:这个记录就是SQL语句中的那个null

 

设置某些列为空值

update table1 set 列1=NULL where 列1 is not null;

 

 

现有一个商品销售表sale,表结构为:

month    char(6)      --月份

sell    number(10,2)   --月销售金额

 

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

insert into sale(month) values('200009');(注意:这条记录的sell值为空)

commit;

共输入12条记录

 

SQL> select * from sale where sell like '%';

 

MONTH       SELL

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

200001      1000

200002      1100

200003      1200

200004      1300

200005      1400

200006      1500

200007      1600

200101      1100

200202      1200

200301      1300

200008      1000

 

查询到11记录.

 

结果说明:

查询结果说明此SQL语句查询不出列值为NULL的字段

此时需对字段为NULL的情况另外处理。

SQL> select * from sale where sell like '%' or sell is null;

SQL> select * from sale where nvl(sell,0) like '%';

 

MONTH       SELL

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

200001      1000

200002      1100

200003      1200

200004      1300

200005      1400

200006      1500

200007      1600

200101      1100

200202      1200

200301      1300

200008      1000

200009

 

查询到12记录.

 

Oracle的空值就是这么的用法,我们最好熟悉它的约定,以防查出的结果不正确。

五.如何查找、删除表中重复的记录 1. 问题提出:

当我们想要为一个表创建唯一索引时,如果该表有重复的记录,则无法创建成功。

2. 方法原理:

1)       Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,
  rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。

2)       在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中
  那些具有最大rowid的就可以了,其余全部删除。

3)       以下语句用到了3项技巧:rowid、子查询、别名。

3. 实现方法:

SQL> create table a (

  2  bm char(4),            --编码

  3  mc varchar2(20)               --名称

  4  )

  5  /

 

表已建立.

 

SQL> insert into a values('1111','1111');

SQL> insert into a values('1112','1111');

SQL> insert into a values('1113','1111');

SQL> insert into a values('1114','1111');

 

SQL> insert into a select * from a;

 

插入4个记录.

 

SQL> commit;

 

完全提交.

 

SQL> select rowid,bm,mc from a;

 

ROWID              BM   MC

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

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111

 

查询到8记录.

 

 

查出重复记录

SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

 

ROWID              BM   MC

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

000000D5.0000.0002 1111 1111

000000D5.0001.0002 1112 1111

000000D5.0002.0002 1113 1111

000000D5.0003.0002 1114 1111

 

删除重复记录

SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

 

删除4个记录.

 

SQL> select rowid,bm,mc from a;

 

ROWID              BM   MC

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

000000D5.0004.0002 1111 1111

000000D5.0005.0002 1112 1111

000000D5.0006.0002 1113 1111

000000D5.0007.0002 1114 1111

六.如何正确利用Rownum来限制查询所返回的行数 1. 含义解释:

1)       rownum是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数。

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

2. 使用方法:

现有一个商品销售表sale,表结构为:

month    char(6)      --月份

sell    number(10,2)   --月销售金额

 

create table sale (month char(6),sell number);

insert into sale values('200001',1000);

insert into sale values('200002',1100);

insert into sale values('200003',1200);

insert into sale values('200004',1300);

insert into sale values('200005',1400);

insert into sale values('200006',1500);

insert into sale values('200007',1600);

insert into sale values('200101',1100);

insert into sale values('200202',1200);

insert into sale values('200301',1300);

insert into sale values('200008',1000);

commit;

 

SQL> select rownum,month,sell from sale where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)

 

   ROWNUM MONTH       SELL

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

        1 200001      1000

 

SQL> select rownum,month,sell from sale where rownum=2;(1以上都查不到记录)

 

没有查到记录

 

SQL> select rownum,month,sell from sale where rownum>5;

(由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件不成立,查不到记录)

 

 

没有查到记录

 

只返回前3条纪录

SQL> select rownum,month,sell from sale where rownum<4;

 

   ROWNUM MONTH       SELL

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

        1 200001      1000

        2 200002      1100

        3 200003      1200

 

 

如何用rownum实现大于、小于逻辑?(返回rownum在4—10之间的数据)(minus操作,速度会受影响)

SQL> select rownum,month,sell from sale where rownum<10

  2  minus

  3  select rownum,month,sell from sale where rownum<5;

 

   ROWNUM MONTH       SELL

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

        5 200005      1400

        6 200006      1500

        7 200007      1600

        8 200101      1100

        9 200202      1200

 

想按日期排序,并且用rownum标出正确序号(有小到大)

SQL> select rownum,month,sell from sale order by month;

 

   ROWNUM MONTH       SELL

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

        1 200001      1000

        2 200002      1100

        3 200003      1200

        4 200004      1300

        5 200005      1400

        6 200006      1500

        7 200007      1600

       11 200008      1000

        8 200101      1100

        9 200202      1200

       10 200301      1300

 

查询到11记录.

 

可以发现,rownum并没有实现我们的意图,系统是按照记录入库时的顺序给记录排的号,rowid也是顺序分配的

 

SQL> select rowid,rownum,month,sell from sale order by rowid;

 

ROWID                 ROWNUM MONTH       SELL

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

000000E4.0000.0002         1 200001      1000

000000E4.0001.0002         2 200002      1100

000000E4.0002.0002         3 200003      1200

000000E4.0003.0002         4 200004      1300

000000E4.0004.0002         5 200005      1400

000000E4.0005.0002         6 200006      1500

000000E4.0006.0002         7 200007      1600

000000E4.0007.0002         8 200101      1100

000000E4.0008.0002         9 200202      1200

000000E4.0009.0002        10 200301      1300

000000E4.000A.0002        11 200008      1000

 

查询到11记录.

 

正确用法,使用子查询

SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;

 

   ROWNUM MONTH       SELL

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

        1 200001      1000

        2 200002      1100

        3 200003      1200

        4 200004      1300

        5 200005      1400

        6 200006      1500

        7 200007      1600

        8 200008      1000

        9 200101      1100

       10 200202      1200

       11 200301      1300

 

按销售金额排序,并且用rownum标出正确序号(有小到大)

SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;

 

   ROWNUM MONTH       SELL

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

        1 200001      1000

        2 200008      1000

        3 200002      1100

        4 200101      1100

        5 200003      1200

        6 200202      1200

        7 200004      1300

        8 200301      1300

        9 200005      1400

       10 200006      1500

       11 200007      1600

 

查询到11记录.

 

利用以上方法,如在打印报表时,想在查出的数据中自动加上行号,就可以利用rownum。

 

返回第5—9条纪录,按月份排序

SQL> select * from (select rownum row_id ,month,sell

  2  from (select month,sell from sale group by month,sell))

  3  where row_id between 5 and 9;

 

    ROW_ID MONTH        SELL

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

         5 200005       1400

         6 200006       1500

         7 200007       1600

         8 200008       1000

         9 200101       1100

 

本文地址:http://com.8s8s.com/it/it20059.htm