这个实验将会得出如下几个结论：
1. Oracle 10g数据库以select * 方式获取数据的顺序基本上是以初始化到数据库的顺序显示的；
2. Oracle 10g数据库以select * 方式获取数据不会进行排序，只有在使用order by的情况下才能保证数据的顺序；
3. 若实现rownum与数据递增顺序保持同步，这里给出一个使用子查询的方式的实现方式。

实验BEGIN：
1.创建一个简单的测试表t，只包含一列x
sec@ora10g> create table t (x varchar2(30));

Table created.

2.初始化测试表t的数据，注意这里数据初始化顺序（a，d，c，b）
sec@ora10g> insert into t values ('a');
sec@ora10g> insert into t values ('d');
sec@ora10g> insert into t values ('c');
sec@ora10g> insert into t values ('b');
sec@ora10g> commit;

3.验证结论1和2，select * 方式获取数据的顺序基本上是以初始化到数据库的顺序显示的，没有顺序
sec@ora10g> select * from t;

X
------------------------------
a
d
c
b

4.观察一下此时rownum和数据的顺序关系
sec@ora10g> select rownum,t.* from t;

    ROWNUM X
---------- ------------------------------
         1 a
         2 d
         3 c
         4 b

5.对数据列x进行主动排序后，观察一下rownum与数据列x的顺序，这里rownum的顺序是混乱的
sec@ora10g> select rownum,t.* from t order by 2;

    ROWNUM X
---------- ------------------------------
         1 a
         4 b
         3 c
         2 d

6.实现结论3，可以通过下面这个子查询的方法得到一个rownum与有序的数据列x保持一致的递增顺序
sec@ora10g> select rownum,tt.x from ( select x from t order by x) tt;

    ROWNUM X
---------- ------------------------------
         1 a
         2 b
         3 c
         4 d

7.如何在实际中应用这个实验结论
举一个利用这个结论来解决最近项目组的一个SQL需求的例子。
需求如下：要求按照x的递增顺序，更新描述字段为“DESCRIPTION-0001”样式的递增内容，“DESCRIPTION-”是固定的，“0001”是四位表示的序列号
实现需求如下：
1）在t表中添加一个描述字段y
sec@ora10g> alter table t add y varchar2(30);

Table altered.

2）使用如下的SQL语句即可完成需求（如果觉得这个SQL不好理解的话，可以先创建一个中间表，再关联这个中间表更新表t，其实下面的SQL思路是一样的，只是使用了一条SQL语句来实现）
sec@ora10g> UPDATE t
2     SET y = (SELECT tt.y
3                FROM (SELECT x, 'DESCRIPTION-' || LPAD (ROWNUM, 4, '0') y
4                        FROM (SELECT   x
5                                  FROM t
6                              ORDER BY x)) tt
7               WHERE tt.x = t.x);

4 rows updated.

3）最后确认数据已经按照需求修改成功
sec@ora10g> select * from t;

X                              Y
------------------------------ ------------------------------
a                              DESCRIPTION-0001
d                              DESCRIPTION-0004
c                              DESCRIPTION-0003
b                              DESCRIPTION-0002

通过上面的这种方法就可以既利用到了rownum，又避免因默认无法排序问题导致更新错误的问题了。

-- The End --

ITPub博客

【实验】【SQL】排序与rownum的关系暨利用rownum处理一实际需求

最新文章