Oracle培训教材

 
一.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里新写一行参数 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NLS_DATE_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://www.ltesting.net