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…INLIKE;IS NULL;;;
NOT BETWEEN … AND… NOT IN NOT LIKEIS 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 乘方LN10为底数取冪
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;;;BMMC
------------------ ---- -------
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;;;BMMC
------------------ ---- --------------------
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;;;BMMC
------------------ ---- --------------------
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
