Oracle 查询技巧与优化

共计 28223 个字符，预计需要花费 71 分钟才能阅读完成。

前言

作为一个程序员在写 SQL 时往往注重结果而忽略了本该应用的技巧和更优性能的选择（之前本人一直也是这样），当公司没有一个 DBA（据我了解大多数中小公司都是没有的）为我们做 SQL 优化时那我们理应将 SQL 尽可能的写的高效简洁，而不是拿“我是一个 java 程序员或.NET 程序员不擅长这些”为借口，博主本人也是以能写出更高效和更优秀的 SQL 为目标而构想出本系列 blog，通过实际的生产环境数据进行演练并总结学习一些程序员也应当具备的某些 DBA 的素质，废话不多说，首先开始第一篇学习与总结，第一篇相对比较简单，回顾一下 Oracle 中基本的查询操作和一些函数。

单表查询与排序

首先准备数据，这里的数据是从生产环境 DB 中备份出来的，即某市考试招生系统的一个基础学生信息表，数据量也不大大概 1W+，约 50 个字段，主要是学生的个人基本信息：

接下来看一下几个较为简单的查询技巧和注意点。

分页排序

如题，从分页语句开始，都知道 oracle 中是通过 rownum 来进行分页的，例如我们查询 20 条到 30 条之间的数据：

select *
  from (select rownum rn, t.*
          from (select sid_, stuname_, csrq_ from t_studentinfo) t
         where rownum <= 30) s
 where s.rn > 20

如上所示，这种分页方式在绝大多数情况下是最高效的，而并非是通过 s.rn>20 and s<=30，因为这种不如上面的效率高。接下来如果在分页的基础上添加排序，例如：查询 20 条到 30 条之间的数据并按出生日期（csrq_）排序，很简单，我们只需在最里层的查询中加上排序语句即可：

select *
  from (select rownum rn, t.*
          from (select sid_, stuname_, csrq_
                  from t_studentinfo
                 order by csrq_) t
         where rownum <= 30) s
 where s.rn > 20

查询结果如下图所示：

取随机 n 条记录排序

如题，很简单，利用 oracle 提供的随机函数包 dbms_random 来完成，首选简单了解一下随机数，即 dbms_ramdom.value：

select dbms_random.value from dual

运行上述 SQL 即可以返回一个大于 0 小于 1 的 38 位精度的随机数，如下图：

搞清楚了这一点，我们接下来就写一个按随机数排序，例如取学生表的前 10 条数据并按随机数排序，比如这样写：

select bmh_, stuname_, csrq_
  from T_STUDENTINFO
 where rownum < 10
 order by dbms_random.value;

连续运行三次，看一下查询结果：

乍一看仿佛没问题，没错，是按随机数排序了，每次运行结果的顺序都不一样，但是请仔细观察，这三次运行结果的学生仿佛是同一批人，只是顺序发生了改变，没错，这才是问题的关键，我们上面的写法是先取学生，后排序，也就是说结果已经提前确定，只是顺序在发生变化，这样的效果并不是我们预期的，我们期望的是在全体学生中随机抽取 10 人，所以我们需要改写一下我们的 SQL，使其先让全体学生排序，再取前 10 名学生：

select t.*
  from (select bmh_, stuname_, csrq_
          from T_STUDENTINFO
         order by dbms_random.value) t
 where rownum < 10

这回再连续运行三次看一下查询结果：

这次再仔细观察一下三次的运行结果很明显已经没有一个重复的人了，这也就达到了我们的预期，所以往往 SQL 的书写顺序也对结果有着决定性的影响，这一点尤为重要。

查询中的条件逻辑

如题，oracle 提供了 case…when 语句使得我们可以在 SQL 中灵活运用多条件分支来根据值转换为不同的查询结果，例如：

select sid_,
       stuname_,
       case mzdm_
         when 1 then
          '汉族'
         when 3 then
          '满族'
         when 4 then
          '回族'
         when 9 then
          '蒙古族'
       end as mz
  from t_studentinfo
 where sid_ in ('36697', '34115', '39590', '30692')
 order by mzdm_

查询结果如下：

在实际应用中像这种民族 code 肯定应该关联对应的字典表的键来获取值，这里我们仅仅想演示一下 case…when 语句的用法，显而易见，以 case 开头并以 end 结尾，中间是 when 条件，并在 end 关键字之后可以通过 as 起别名，很方便。

空值转换

如题，又是一个很常用的东西，比如如果某一列查询为空时，我们想将空值替换为我们指定的一个值，这里就可以选择 nvl 函数或者 coalesce 函数，均可以达到期望的效果：

select tcsdm_ from t_studentinfo where sid_ in ('33405','29982','28974');
select nvl(tcsdm_,'999') tcsdm from t_studentinfo where sid_ in ('33405','29982','28974');
select coalesce(tcsdm_,'999') tcsdm from t_studentinfo where sid_ in ('33405','29982','28974');

运行结果如下图：

但这里还是要区分一下这两个函数的用法，首先 nvl 函数只能传 2 个参数：NVL(string1, replace_with)，功能很简单，即：如果 string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回原来的值。再需要注意一点就是 string1 与 replace_with 需要保持同一数据类型，接下来再看一看 coalesce 函数，它是支持多个参数的，形如：COALESCE(exp1,exp2,...,expn)，n>=2，同样它的含义也和 nvl 区别很大（尽管它们达到的效果相同），即：返回第一个不为空的表达式，如果都为空则返回空值。关于使用哪一个好应该也是显而易见的，即在多参数的情况下 coalesce 函数明显更具优势，因为它避免了需要嵌套 nvl 函数，若是只有两个参数的话就无所谓了，正如我们上面的例子，看个人喜好。

模糊查询中的通配符

如题，偶尔在查询中也会遇到这种问题，首先创建一个测试用的视图：

create or replace view v_test_02 as
   select '_AAA' as column1 from dual
   union all
   select '_\AAA' as column1 from dual
   union all
   select '_AAAB_' as column1 from dual
   union all
   select 'QAAA' as column1 from dual;

直入主题，需求是我想查询包含字符串 “_AAA” 的结果，那么条件中的 like 语句该怎么写呢？我们先试一下这样写看看：

select * from v_test_02 where column1 like '_AAA%';

看一下运行结果：

如上图所示，第三行的 QAAA 这个结果并不是我们预期希望得到的，但是通配符“_”就代表 1 个字符，所以这个 Q 自然会被该通配符匹配到，无可厚非，所以为了解决这个问题我们必须引入转义字符的概念，下面是修改后的 SQL：

select * from v_test_02 where column1 like '\_AAA%' escape '\';

此时再看一下查询结果：

这次就对了，所以此处注意一下转义字符以及 escape 关键字的用法，尽管这种情况很少遇见，但也值得一提。

多字段排序

很有意思的一个问题，这里我们拿学生成绩表的数据来举例更贴切一些，学生成绩表也很简单，包含了学生的考号、各科的文化课成绩以及总分，这里我们只查 3 列，假设我们的排序条件是这样的：按总分倒序排列并按语文的单科成绩升序排列，那得到的结果会是怎么的呢？首先看一下 SQL 语句：

select sid_, totalscore_, ywscore_
  from T_STUDENTSCORE t
 order by 2 desc, 3 asc

再看一下运行结果：

如上图所示，首先有一个小地方就是 order by 后面写的数字，2 就代表第 2 列，3 就代表第 3 列，当然也可以直接跟列名，观察一下查询结果，不难发现依旧是按 totalscore_（总分）倒序排列的，但 ywscore_（语文单科成绩）似乎排列的有有些混乱，没关系，注意一下图中我用红色格子隔开的列，一个格子一个格子看，不难发现在每个格子内，它确实是升序排列的，这就是多字段排序的结论，即：以写在靠前位置的排序列为基准，将靠后位置的排序列分成若干组，然后每组的数据再单独进行排序。

空值排序

如题，如果按某一列排序，但那一列存在许多空值，它们是如何显示的？在 Oracle 中空值默认排序在后面，比如学生表中有许多学生没有填出生日期，我们又按出生日期这一列倒序排列，那么可以看到如下结果：

如上图所示，前 112 名学生的出生日期均为空，从 113 位学生开始，即年龄最小的学生依次排下去。那么也许你觉得这样并不好，明明为空为什么按大的算呢？我想把空的都放到前面去，很简单，只需要在 SQL 末尾加上两个关键字（nulls last）即可：

这回可以看到前面并没有空值了，出生日期大的排在最前面，而空的已经排在了 11000+ 的末尾位置了。

总结

简单记录了一下在单表查询与排序中我个人认为值得关注和常用一些点（如果还有好的优化方式或者应当关注的点后面还会陆续添加，也欢迎各位提出毕竟我个人能力有限），部分知识点和灵感来自《Oracle 查询优化改写》书中，但我是以程序员的视角而非 DBA（也达不到）去描述在项目中遇到的或可能遇到的种种查询语句，并结合实际生产环境的数据进行举例说明，旨在提高我作为程序员应当具备和提高的 SQL 能力，当然如果有同学能从本系列 blog 中得到提升那就更好了，希望如此，The End。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-09/135542p2.htm

前言

上一篇 blog 介绍了 Oracle 中的单表查询和排序的相关技巧，本篇 blog 继续介绍查询中用的最多的——多表查询的技巧与优化方式，下面依旧通过一次例子看一个最简单的多表查询。

多表查询

上一篇中提到了学生信息表的民族代码（mzdm_）这个字段通常应该关联字典表来查询其对应的汉字，实际上我们也是这么做的，首先简单看一下表结构，首先是字典表：

如上图，可以看到每个民族代码和名称都是由两个字段——“itemkey_”和“itemvalue_”以键值形式对应起来的，而学生信息表只存了民族代码字段（mzdm_），所以通过 mzdm_和 itemkey_相对应就能很好的查询出民族对应的汉字了，比如这样写：

select t1.*, t2.itemvalue_ mzmc_
  from (select sid_, stuname_, mzdm_ from t_studentinfo) t1
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'EthnicType') t2
    on t1.mzdm_ = t2.itemkey_;

接下来查看一下运行结果：

如上写法（左连接查询）是我在项目中运用最多的形式之一，暂不评论好坏与效率，总之查询结果是很好的展现出来了，接下来就具体研究一下多表查询的几种方式与区别。

UNION ALL

如题，这是我们第一个介绍的操作多表的方式就是 UNION 和 UNION ALL，UNION 和 UNION ALL 也是存在一定区别的，首先明确一点基本概念，UNION 和 UNION ALL 是用来合并多个数据集的，例如将两个 select 语句的结果合并为一个整体：

select bmh_, stuname_, csrq_, mzdm_
  from t_studentinfo
 where mzdm_ = 2
union all
select bmh_, stuname_, csrq_, mzdm_
  from t_studentinfo
 where mzdm_ = 5

查询结果如下：

如上图所示，把 mzdm_为 2 和 5 的结果集合并在了一起，那么接下来把 UNION ALL 换成 UNION 再看一下运行结果：

注意观察上图中的第一列 BMH_不难发现，UNION 进行了排序（默认规则排序，即按查询结果的首列进行排序），这就是它与 UNION ALL 的区别之一，再看一下下面这两个 SQL 和查询结果：

select bmh_, stuname_, csrq_, mzdm_
  from t_studentinfo
 where mzdm_ in (2, 5)
   and csrq_ like '200%';

运行结果如下：

select bmh_, stuname_, csrq_, mzdm_
  from t_studentinfo
 where mzdm_ in (2, 5)
   and csrq_ like '2001%';

运行结果如下：

可以看到第二段查询结果肯定是包含在第一段查询结果之内的，那么它们进行 UNION 和 UNION ALL 又会有何区别呢？分别看一下，首先是 UNION ALL：

如上图，不难发现使用 UNION ALL 查询出了上面两个结果集的总和，包括 6 对重复数据 + 5 条单独的数据总共 17 条，那么再看看 UNION 的结果：

显而易见，和 UNION ALL 相比 UNION 帮我们自动剔除了 6 条重复结果，得到的是上面两个结果集的并集，同时并没有排序，这也就是 UNION ALL 与 UNION 的第二个区别了，最后简单总结一下 UNION 与 UNION ALL 的区别：

1. UNION 会自动去除多个结果集合中的重复结果，而 UNION ALL 则将所有的结果全部显示出来，不管是不是重复。
2. UNION 会对结果集进行默认规则的排序，而 UNION ALL 则不会进行任何排序。

所以效率方面很明显 UNION ALL 要高于 UNION，因为它少去了排序和去重的工作。当然还有一点需要注意，UNION 和 UNION ALL 也可以用来合并不同的两张表的结果集，但是字段类型和个数需要匹配，例如：

select sid_, stuname_, mzdm_
  from t_studentinfo
 where sid_ = '33405'
union all
select did_, itemvalue_, itemkey_
  from t_dict
 where did_ = '366'

查看一下运行结果：

当数据配型不匹配或是列数不匹配时则会报错：

当列数不够时完全也可以用 NULL 来代替从而避免上图中的错误。最后再举个例子看一下 UNION ALL 在某些比较有意义的场景下的作用，首先创建一张临时表：

with test as
 (select 'aaa' as name1, 'bbb' as name2
    from dual
  union all
  select 'bbb' as name1, 'ccc' as name2
    from dual
  union all
  select 'ccc' as name1, 'ddd' as name2
    from dual
  union all
  select 'ddd' as name1, 'eee' as name2
    from dual
  union all
  select 'eee' as name1, 'fff' as name2
    from dual
  union all
  select 'fff' as name1, 'ggg' as name2
    from dual)
select * from test;

运行结果如下：

我们的需求也很简单，即：统计 NAME1 和 NAME2 中每个不同的值出现的次数。谈一下思路，首先统计 NAME1 每个值出现的次数，再统计 NAME2 每个值出现的次数，最后对上面两个结果集进行 UNION ALL 合并，最后再进行一次分组和排序即可：

with test as
 (select 'aaa' as name1, 'bbb' as name2
    from dual
  union all
  select 'bbb' as name1, 'ccc' as name2
    from dual
  union all
  select 'ccc' as name1, 'ddd' as name2
    from dual
  union all
  select 'ddd' as name1, 'eee' as name2
    from dual
  union all
  select 'eee' as name1, 'fff' as name2
    from dual
  union all
  select 'fff' as name1, 'ggg' as name2
    from dual)
select namex, sum(times) times
  from (select name1 namex, count(*) times
          from test
         group by name1
        union all
        select name2 namex, count(*) times
          from test
         group by name2)
 group by namex
 order by namex;

运行结果如下：

OK，很好的完成了查询，那么关于 UNION 和 UNION ALL 暂且介绍到这里。

是否使用 JOIN

如题，blog 开头写的那个例子是使用 LEFT JOIN 完成两张表的关联查询的，那么另外也可以不用 JOIN 而通过 WHERE 条件来完成以达到相同的效果：

select t1.sid_, t1.stuname_, t1.mzdm_, t2.itemvalue_ mzmc_
  from t_studentinfo t1, t_dict t2
 where t1.mzdm_ = t2.itemkey_
   and t2.itemname_ = 'EthnicType';

运行效果如下：

回头看一下 blog 开头的 SQL 和运行效果，可以发现和上图一模一样，那使用哪一种更合适呢？JOIN 的写法是 SQL-92 的标准，多表关联时候使用 JOIN 方式进行关联查询可以更清楚的看到各表之间的联系，也方便维护 SQL，所以还是不建议上面使用 WHERE 的查询方式，而是应该使用 JOIN 的写法。

IN 和 EXISTS

如题，这也是在查询中经常用到的，尤其是 IN 关键字，在项目中使用的相当频繁，经常会有通过 for 循环和 StringBuffer 来拼接 IN 语句的写法，那么接下来就仔细看一下 IN 和 EXISTS 的使用场景以及效率问题，依旧通过举例说明，比如这个需求，查询所有汉族学生的成绩：

explain plan for select *
  from t_studentscore
 where bmh_ in (select bmh_ from t_studentinfo where mzdm_ = 1);

select * from table(dbms_xplan.display());

观察一下执行计划：

1 Plan hash value: 902966761
2
3 ————————————————————————————-
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ————————————————————————————-
6 | 0 | SELECT STATEMENT | | 535 | 37985 | 240 (1)| 00:00:03 |
7 |* 1 | HASH JOIN | | 535 | 37985 | 240 (1)| 00:00:03 |
8 |* 2 | TABLE ACCESS FULL| T_STUDENTINFO | 535 | 5885 | 207 (1)| 00:00:03 |
9 | 3 | TABLE ACCESS FULL| T_STUDENTSCORE | 11642 | 682K| 32 (0)| 00:00:01 |
10 ————————————————————————————-
11
12 Predicate Information (identified by operation id):
13 —————————————————
14
15 1 – access(“BMH_”=SYS_OP_C2C(“BMH_”))
16 2 – filter(“MZDM_”=1)

同理，将 IN 换成 EXISTS 再来看一下 SQL 和执行计划：

explain plan for select *
  from t_studentscore ts
 where exists (select 1
          from t_studentinfo
         where mzdm_ = 1
           and bmh_ = ts.bmh_);

select * from table(dbms_xplan.display());

观察一下执行计划：

1 Plan hash value: 3857445149
2
3 —————————————————————————————
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 —————————————————————————————
6 | 0 | SELECT STATEMENT | | 1 | 71 | 240 (1)| 00:00:03 |
7 |* 1 | HASH JOIN RIGHT SEMI| | 1 | 71 | 240 (1)| 00:00:03 |
8 |* 2 | TABLE ACCESS FULL | T_STUDENTINFO | 535 | 5885 | 207 (1)| 00:00:03 |
9 | 3 | TABLE ACCESS FULL | T_STUDENTSCORE | 11642 | 682K| 32 (0)| 00:00:01 |
10 —————————————————————————————
11
12 Predicate Information (identified by operation id):
13 —————————————————
14
15 1 – access(“TS”.”BMH_”=SYS_OP_C2C(“BMH_”))
16 2 – filter(“MZDM_”=1)

如上所示，尽管 IN 的写法用了 HASH JOIN（哈希连接）而 EXISTS 的写法用了 HASH JOIN RIGHT SEMI（哈希右半连接），但它们的执行计划却没有区别，效率都是一样的，这是因为数据量不大，所以有一点结论就是 在简单查询中，IN 和 EXISTS 是等价的。还有一点需要明确，在早期的版本中仿佛有这样的规则：

1. 子查询结果集小，用 IN。
2. 外表小，子查询表大，用 EXISTS。

这两个说法在 Oracle11g 中已经是完全错误的了！在 Oracle8i 中这样也许还经常是正确的，但 Oracle 9i CBO 就已经优化了 IN 和 EXISTS 的区别，Oracle 优化器有个查询转换器，很多 SQL 虽然写法不同，但是 Oracle 优化器会根据既定规则进行查询重写，重写为优化器觉得效率最高的 SQL，所以可能 SQL 写法不同，但是执行计划却是完全一样的，所以还有个结论就是：关于 IN 和 EXISTS 哪种更高效应该及时查看 PLAN，而不是记固定的结论，至少在目前的 Oracle 版本中是这样的。

INNER LEFT RIGHT FULL JOIN

如题，很常用的几种连接方式，下面就分别看一下它们之间的区别。

INNER JOIN

首先是内连接（INNER JOIN），顾名思义，INNER JOIN 返回的是两表相匹配的数据，依旧以 blog 开头的例子改写为 INNER JOIN：

select t1.sid_, t1.stuname_, t1.mzdm_, t2.itemvalue_ mzmc_
  from t_studentinfo t1
 inner join t_dict t2
    on t1.mzdm_ = t2.itemkey_
 where t2.itemname_ = 'EthnicType';

运行结果如下：

可以看到和上面的结果依旧是完全一样，但这个例子没有说明 INNER JOIN 的特点，所以就再重新创建两张表说明一下问题，这次用比较经典的学生表班级表来进行测试：

create table T_TEST_STU
(sid     INTEGER,
  stuname VARCHAR2(20),
  clsid   INTEGER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

create table T_TEST_CLS
(cid   INTEGER,
  cname VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

表创建好后插入测试数据：

insert into T_TEST_STU (SID, STUNAME, CLSID) values (1, '张三', 1);

insert into T_TEST_STU (SID, STUNAME, CLSID) values (2, '李四', 1);

insert into T_TEST_STU (SID, STUNAME, CLSID) values (3, '小明', 2);

insert into T_TEST_STU (SID, STUNAME, CLSID) values (4, '小李', 3);

insert into T_TEST_CLS (CID, CNAME) values (1, '三年级 1 班');

insert into T_TEST_CLS (CID, CNAME) values (5, '三年级 5 班');

如上所示，可以看到非常简单，学生表插入了 4 条数据，班级表插入了 1 条数据，用学生表的 clsid 来关联班级表的 cid 查询一下班级名称，下面看一下使用 INNER JOIN 的查询语句：

select t1.sid, t1.stuname, t2.cname
  from t_test_stu t1
 inner join t_test_cls t2
    on t1.clsid = t2.cid;

运行后可以看到查询结果：

如上所示，很好的验证了 INNER JOIN 的概念，即 返回两表均匹配的数据，由于班级表只有 1 条 1 班的数据和 1 条 5 班的数据，而学生表仅有两名 1 班的学生并且没有任何 5 班的学生，所以自然只能返回两条。

LEFT JOIN

如题，LEFT JOIN 是以左表为主表，返回左表的全部数据，右表只返回相匹配的数据，将上面的 SQL 改为 LEFT JOIN 看一下：

select t1.sid, t1.stuname, t2.cname
  from t_test_stu t1
  left join t_test_cls t2
    on t1.clsid = t2.cid;

看一下运行结果：

如上图所示，也非常简单，因为右表（班级表）并没有 2 班和 3 班的数据，所以班级名称不会显示。

RIGHT JOIN

如题，RIGHT JOIN 和 LEFT JOIN 是相反的，以右表数据为主表，左表仅返回相匹配的数据，同理将上面的 SQL 改写为 RIGHT JOIN 的形式：

select t1.sid, t1.stuname, t2.cname
  from t_test_stu t1
 right join t_test_cls t2
    on t1.clsid = t2.cid;

运行结果如下：

如上图，由于是以班级表为主表进行关联，所以匹配到 1 班的 2 名学���以及 5 班的数据。

FULL JOIN

如题，顾名思义，FULL JOIN 就是不管左右两边是否匹配，一次性显示出所有的查询结果，相当于 LEFT JOIN 和 RIGHT JOIN 结果的并集，依旧将上面的 SQL 改写为 FULL JOIN 并查看结果：

select t1.sid, t1.stuname, t2.cname
  from t_test_stu t1
  full join t_test_cls t2
    on t1.clsid = t2.cid;

运行结果如下：

到这里这 4 种 JOIN 查询方式就已经简要的介绍完毕，单从概念上来将还是很好理解和区分的。

自关联

如题，这是一个使用场景比较特殊的关联方式，个人感觉如果数据库合理设计的话不会出现这种需求吧，既然提到了就举例说明一下，依旧以上面的测试学生表为例，现在需要添加一个字段：

alter table T_TEST_STU add leader INTEGER;

假设有如下需求，每个学生都有一个直属 leader，负责检查作业，老师为了避免作弊行为不会指定两个人相互检查，而是依次错开，比如学生 A 检查学生 B，学生 B 检查学生 C，所以我们的表数据可以这样来描述这个问题：

如上图，张三的 LEADER 是李四，李四的 LEADER 是小明，小明的 LEADER 是小李，而小李的 LEADER 又是张三，那么问题来了，该如何查询得到每个学生的 LEADER 的姓名呢？没错，这里就用到了自关联查询，简单的讲就是把同一张表查两遍并进行关联，用视图来说明获取更清晰，所以首先创建两个视图：

CREATE OR REPLACE VIEW V_STU as select * from T_TEST_STU;
CREATE OR REPLACE VIEW V_LEADER as select * from T_TEST_STU;

接下来就通过自关联查询：

select v1.SID, v1.STUNAME, v1.CLSID, v1.LEADER, v2.STUNAME leader
  from V_STU v1
  left join V_LEADER v2
    on v1.LEADER = v2.SID
 order by v1.SID

运行结果如下：

如上图所示，这样就通过自关联很好的查询出了每个学生对应的 LEADER 的姓名。

NOT IN 和 NOT EXISTS

如题，我们现在有一张学生信息表和一张录取结果表，例如我们想知道有哪些学生没被录取，即学生表有数据但录取表却没有该学生的数据，这时就可以用到 NOT IN 或 NOT EXISTS，依旧结合执行计划看一看这种方式的差异：

explain plan for
select * from t_studentinfo where bmh_ not in (select bmh_ from t_lq);

select * from table(dbms_xplan.display());

观察一下执行计划：

1 Plan hash value: 4115710565
2
3 —————————————————————————————–
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 —————————————————————————————–
6 | 0 | SELECT STATEMENT | | 119 | 52003 | 551 (1)| 00:00:07 |
7 |* 1 | HASH JOIN RIGHT ANTI NA| | 119 | 52003 | 551 (1)| 00:00:07 |
8 | 2 | TABLE ACCESS FULL | T_LQ | 11643 | 93144 | 343 (1)| 00:00:05 |
9 | 3 | TABLE ACCESS FULL | T_STUDENTINFO | 11772 | 4931K| 207 (1)| 00:00:03 |
10 —————————————————————————————–
11
12 Predicate Information (identified by operation id):
13 —————————————————
14
15 1 – access(“BMH_”=”BMH_”)

接下来将 SQL 转换为 NOT EXISTS 再看一下执行计划：

explain plan for 
select * from t_studentinfo t1 where not exists (select null from t_lq t2 where t1.bmh_ = t2.bmh_);

select * from table(dbms_xplan.display());

执行结果如下：

1 Plan hash value: 270337792
2
3 ————————————————————————————–
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ————————————————————————————–
6 | 0 | SELECT STATEMENT | | 119 | 52003 | 551 (1)| 00:00:07 |
7 |* 1 | HASH JOIN RIGHT ANTI| | 119 | 52003 | 551 (1)| 00:00:07 |
8 | 2 | TABLE ACCESS FULL | T_LQ | 11643 | 93144 | 343 (1)| 00:00:05 |
9 | 3 | TABLE ACCESS FULL | T_STUDENTINFO | 11772 | 4931K| 207 (1)| 00:00:03 |
10 ————————————————————————————–
11
12 Predicate Information (identified by operation id):
13 —————————————————
14
15 1 – access(“T1”.”BMH_”=”T2”.”BMH_”)

如上所示，两个 PLAN 都应用了 HASH JOIN RIGHT ANTI，所以它们的效率是一样的，所以在 Oracle11g 中关于 NOT IN 和 NOT EXISTS 也没有绝对的效率优劣，依旧是要通过 PLAN 来判断和测试哪种更高效。

多表查询时的空值处理

如题，假设有以下需求，我需要查询一下性别不为男的学生的录取分数，但在这之前我首先给学生表添加一条报名号（bmh_）为 null 的学生数据，如下所示：

接下来写查询语句，这里刻意用一下 NOT IN 关键字而不是 IN 关键字：

select bmh_, stuname_, lqfs_
  from t_lq
 where bmh_ not in (select bmh_ from t_studentinfo where sextype_ = 1)

运行结果如下图所示：

我们惊奇的发现没有任何数据被查出来，这就是因为 NOT IN 后的子查询中的 5000+ 结果中仅仅有一条存在 NULL 值，所以这个查询整体就不会显示任何结果，有点一只老鼠毁了一锅汤的感觉，这也正是 Oracle 的特性之一，即：如果 NOT IN 关键字后的子查询包含空值，则整体查询都会返回空，所以这类查询务必要加非 NULL 判断条件，即：

select bmh_, stuname_, lqfs_
  from t_lq
 where bmh_ not in (select bmh_
                      from t_studentinfo
                     where sextype_ = 1
                       and bmh_ is not null);

这次再看一下运行结果：

如上图所示，这次就很好的查询出了我们需要的结果。

总结

简单记录一下 Oracle 多表查询中的各种模式以及个人认为值得注意的一些点和优化方式，希望对读到的同学有所帮助和提高，The End。

前言

作为一个程序员在写 SQL 时往往注重结果而忽略了本该应用的技巧和更优性能的选择（之前本人一直也是这样），当公司没有一个 DBA（据我了解大多数中小公司都是没有的）为我们做 SQL 优化时那我们理应将 SQL 尽可能的写的高效简洁，而不是拿“我是一个 java 程序员或.NET 程序员不擅长这些”为借口，博主本人也是以能写出更高效和更优秀的 SQL 为目标而构想出本系列 blog，通过实际的生产环境数据进行演练并总结学习一些程序员也应当具备的某些 DBA 的素质，废话不多说，首先开始第一篇学习与总结，第一篇相对比较简单，回顾一下 Oracle 中基本的查询操作和一些函数。

单表查询与排序

首先准备数据，这里的数据是从生产环境 DB 中备份出来的，即某市考试招生系统的一个基础学生信息表，数据量也不大大概 1W+，约 50 个字段，主要是学生的个人基本信息：

接下来看一下几个较为简单的查询技巧和注意点。

分页排序

如题，从分页语句开始，都知道 oracle 中是通过 rownum 来进行分页的，例如我们查询 20 条到 30 条之间的数据：

select *
  from (select rownum rn, t.*
          from (select sid_, stuname_, csrq_ from t_studentinfo) t
         where rownum <= 30) s
 where s.rn > 20

如上所示，这种分页方式在绝大多数情况下是最高效的，而并非是通过 s.rn>20 and s<=30，因为这种不如上面的效率高。接下来如果在分页的基础上添加排序，例如：查询 20 条到 30 条之间的数据并按出生日期（csrq_）排序，很简单，我们只需在最里层的查询中加上排序语句即可：

select *
  from (select rownum rn, t.*
          from (select sid_, stuname_, csrq_
                  from t_studentinfo
                 order by csrq_) t
         where rownum <= 30) s
 where s.rn > 20

查询结果如下图所示：

取随机 n 条记录排序

如题，很简单，利用 oracle 提供的随机函数包 dbms_random 来完成，首选简单了解一下随机数，即 dbms_ramdom.value：

select dbms_random.value from dual

运行上述 SQL 即可以返回一个大于 0 小于 1 的 38 位精度的随机数，如下图：

搞清楚了这一点，我们接下来就写一个按随机数排序，例如取学生表的前 10 条数据并按随机数排序，比如这样写：

select bmh_, stuname_, csrq_
  from T_STUDENTINFO
 where rownum < 10
 order by dbms_random.value;

连续运行三次，看一下查询结果：

乍一看仿佛没问题，没错，是按随机数排序了，每次运行结果的顺序都不一样，但是请仔细观察，这三次运行结果的学生仿佛是同一批人，只是顺序发生了改变，没错，这才是问题的关键，我们上面的写法是先取学生，后排序，也就是说结果已经提前确定，只是顺序在发生变化，这样的效果并不是我们预期的，我们期望的是在全体学生中随机抽取 10 人，所以我们需要改写一下我们的 SQL，使其先让全体学生排序，再取前 10 名学生：

select t.*
  from (select bmh_, stuname_, csrq_
          from T_STUDENTINFO
         order by dbms_random.value) t
 where rownum < 10

这回再连续运行三次看一下查询结果：

这次再仔细观察一下三次的运行结果很明显已经没有一个重复的人了，这也就达到了我们的预期，所以往往 SQL 的书写顺序也对结果有着决定性的影响，这一点尤为重要。

查询中的条件逻辑

如题，oracle 提供了 case…when 语句使得我们可以在 SQL 中灵活运用多条件分支来根据值转换为不同的查询结果，例如：

select sid_,
       stuname_,
       case mzdm_
         when 1 then
          '汉族'
         when 3 then
          '满族'
         when 4 then
          '回族'
         when 9 then
          '蒙古族'
       end as mz
  from t_studentinfo
 where sid_ in ('36697', '34115', '39590', '30692')
 order by mzdm_

查询结果如下：

在实际应用中像这种民族 code 肯定应该关联对应的字典表的键来获取值，这里我们仅仅想演示一下 case…when 语句的用法，显而易见，以 case 开头并以 end 结尾，中间是 when 条件，并在 end 关键字之后可以通过 as 起别名，很方便。

空值转换

如题，又是一个很常用的东西，比如如果某一列查询为空时，我们想将空值替换为我们指定的一个值，这里就可以选择 nvl 函数或者 coalesce 函数，均可以达到期望的效果：

select tcsdm_ from t_studentinfo where sid_ in ('33405','29982','28974');
select nvl(tcsdm_,'999') tcsdm from t_studentinfo where sid_ in ('33405','29982','28974');
select coalesce(tcsdm_,'999') tcsdm from t_studentinfo where sid_ in ('33405','29982','28974');

运行结果如下图：

但这里还是要区分一下这两个函数的用法，首先 nvl 函数只能传 2 个参数：NVL(string1, replace_with)，功能很简单，即：如果 string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回原来的值。再需要注意一点就是 string1 与 replace_with 需要保持同一数据类型，接下来再看一看 coalesce 函数，它是支持多个参数的，形如：COALESCE(exp1,exp2,...,expn)，n>=2，同样它的含义也和 nvl 区别很大（尽管它们达到的效果相同），即：返回第一个不为空的表达式，如果都为空则返回空值。关于使用哪一个好应该也是显而易见的，即在多参数的情况下 coalesce 函数明显更具优势，因为它避免了需要嵌套 nvl 函数，若是只有两个参数的话就无所谓了，正如我们上面的例子，看个人喜好。

模糊查询中的通配符

如题，偶尔在查询中也会遇到这种问题，首先创建一个测试用的视图：

create or replace view v_test_02 as
   select '_AAA' as column1 from dual
   union all
   select '_\AAA' as column1 from dual
   union all
   select '_AAAB_' as column1 from dual
   union all
   select 'QAAA' as column1 from dual;

直入主题，需求是我想查询包含字符串 “_AAA” 的结果，那么条件中的 like 语句该怎么写呢？我们先试一下这样写看看：

select * from v_test_02 where column1 like '_AAA%';

看一下运行结果：

如上图所示，第三行的 QAAA 这个结果并不是我们预期希望得到的，但是通配符“_”就代表 1 个字符，所以这个 Q 自然会被该通配符匹配到，无可厚非，所以为了解决这个问题我们必须引入转义字符的概念，下面是修改后的 SQL：

select * from v_test_02 where column1 like '\_AAA%' escape '\';

此时再看一下查询结果：

这次就对了，所以此处注意一下转义字符以及 escape 关键字的用法，尽管这种情况很少遇见，但也值得一提。

多字段排序

很有意思的一个问题，这里我们拿学生成绩表的数据来举例更贴切一些，学生成绩表也很简单，包含了学生的考号、各科的文化课成绩以及总分，这里我们只查 3 列，假设我们的排序条件是这样的：按总分倒序排列并按语文的单科成绩升序排列，那得到的结果会是怎么的呢？首先看一下 SQL 语句：

select sid_, totalscore_, ywscore_
  from T_STUDENTSCORE t
 order by 2 desc, 3 asc

再看一下运行结果：

如上图所示，首先有一个小地方就是 order by 后面写的数字，2 就代表第 2 列，3 就代表第 3 列，当然也可以直接跟列名，观察一下查询结果，不难发现依旧是按 totalscore_（总分）倒序排列的，但 ywscore_（语文单科成绩）似乎排列的有有些混乱，没关系，注意一下图中我用红色格子隔开的列，一个格子一个格子看，不难发现在每个格子内，它确实是升序排列的，这就是多字段排序的结论，即：以写在靠前位置的排序列为基准，将靠后位置的排序列分成若干组，然后每组的数据再单独进行排序。

空值排序

如题，如果按某一列排序，但那一列存在许多空值，它们是如何显示的？在 Oracle 中空值默认排序在后面，比如学生表中有许多学生没有填出生日期，我们又按出生日期这一列倒序排列，那么可以看到如下结果：

如上图所示，前 112 名学生的出生日期均为空，从 113 位学生开始，即年龄最小的学生依次排下去。那么也许你觉得这样并不好，明明为空为什么按大的算呢？我想把空的都放到前面去，很简单，只需要在 SQL 末尾加上两个关键字（nulls last）即可：

这回可以看到前面并没有空值了，出生日期大的排在最前面，而空的已经排在了 11000+ 的末尾位置了。

总结

简单记录了一下在单表查询与排序中我个人认为值得关注和常用一些点（如果还有好的优化方式或者应当关注的点后面还会陆续添加，也欢迎各位提出毕竟我个人能力有限），部分知识点和灵感来自《Oracle 查询优化改写》书中，但我是以程序员的视角而非 DBA（也达不到）去描述在项目中遇到的或可能遇到的种种查询语句，并结合实际生产环境的数据进行举例说明，旨在提高我作为程序员应当具备和提高的 SQL 能力，当然如果有同学能从本系列 blog 中得到提升那就更好了，希望如此，The End。

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-09/135542p2.htm

前言

前两篇 blog 分别介绍了 Oracle 中的单表查询（Oracle 查询技巧与优化（一）单表查询与排序）与多表查询（Oracle 查询技巧与优化（二）多表查询）的相关技巧与优化，那么接下来本篇 blog 就具体研究一下 Oracle 查询中使用频率很高的一种数据类型——“字符串”的相关操作与注意点。

常用操作符与函数

如题，先简单回顾一下我个人认为在 Oracle 的查询或存储过程中使用频率较高的操作符与函数，作为基础知识回顾，首先是最常用的字符串连接符“||”，即 Oracle 中的字符串拼接符号，例如：

select 'wlwlwlwl' || '015' || 'csdn' as title from dual;

运行结果如下：

再看几个简单的比较常用的字符串函数，首先是 instr 与 substr，这两个函数经常结合使用，下面分别看一下，先来看看相对简单一些的 substr 函数，它的语法格式如下：SUBSTR(cExpression，nStartPosition [，nCharactersReturned])，通俗的讲，第 1 个参数是源字符串，第 2 个参数是开始截取的位置，第 3 个参数是截取长度，例如：

select substr('abcdefg',0,1) as newstr from dual; // 返回 a 
select substr('abcdefg',1,1) as newstr from dual; // 返回 a，0 和 1 都代表第 1 个字符
select substr('abcdefg',2,4) as newstr from dual; // 返回 bcde，注意包含位置字符
select substr('abcdefg',-3,2) as newstr from dual; // 返回 ef，负数表示从后往前数位置，其余不变

substr 函数实在不需要做过多说明，接下来看看 instr 函数，它的作用是“在一个字符串中查找指定的字符, 返回被查找到的指定的字符的位置”，有点类似于 java 中 String 的 indexOf 方法：

select instr('abcd','a') from dual; // 返回 1 
select instr('abcd','b') from dual; // 返回 2 
select instr('abcd','c') from dual; // 返回 3 
select instr('abcd','e') from dual; // 返回 0 

如上所示，如果找不到指定的子串，则返回 0，以上是 instr 函数最简单的用法，接下来具体看一下 instr 函数完整的语法格式：instr(string1,string2[,start_position[,nth_appearence]])，可以看到它最多支持 4 个参数，下面分别解释一下每个参数的意思：

1. string1，即源字符串。
2. string2，目标字符串，即要在 string1 中查找的字符串。
3. start_position，从 string1 开始查找的位置。可选，默认为 1，正数时，从左到右检索，负数时，从右到左检索。
4. nth_appearence，查找第几次出现 string2。可选，默认为 1，不能为负。

如上所示，之所以 instr 函数很强大往往是依赖第 4 个参数的作用，在某些场合往往能起到关键作用，下面看一个我项目中的例子，首先学生表有一个字段记录了每个学生的体育考试选考科目，每人选 5 门待考科目，每个科目都有一个代码，在学生表存储的数据格式是“每门科目代码加逗号拼接的字符串”，形如：

如上图，每一个选考项在字典表均可对应：

现在假设我们有这样一个需求，把每个学生的每一门体育选考项目的代码和名称分别列出来，达到以下的效果：

如上图所示，该如何实现呢？首先思路很明确，把学生表记录的选考科目代码字符串进行拆分，然后再一一列举，那么此时需要注意的一个问题是代码的长度不确定，科目代码可能是 1 位数字，例如：1,2,3,8,9，但也可能存在两位数字，例如：1,2,3,11,12，那么直接用 substr 按位截取肯定是行不通的，此时我们应该想办法如何按符号截取，每个考试科目代码中间都是用逗号隔开的，如果能找到相邻两个逗号之间的数字，那么问题就迎刃而解了，这里需要用到的就是前面说的 instr 和 substr 相结合：

select t1.sid_,
       t1.stuname_,
       t1.km1,
       t2.itemvalue_ km1name,
       t1.km2,
       t3.itemvalue_ km2name,
       t1.km3,
       t4.itemvalue_ km3name,
       t1.km4,
       t5.itemvalue_ km4name,
       t1.km5,
       t6.itemvalue_ km5name
  from (select sid_,
               stuname_,
               substr(tysxkm_, 0, instr(tysxkm_, ',', 1, 1) - 1) as km1,
               substr(tysxkm_,
                      instr(tysxkm_, ',', 1, 1) + 1,
                      instr(tysxkm_, ',', 1, 2) - instr(tysxkm_, ',', 1, 1) - 1) as km2, 
               substr(tysxkm_,
                      instr(tysxkm_, ',', 1, 2) + 1,
                      instr(tysxkm_, ',', 1, 3) - instr(tysxkm_, ',', 1, 2) - 1) as km3,
               substr(tysxkm_,
                      instr(tysxkm_, ',', 1, 3) + 1,
                      instr(tysxkm_, ',', 1, 4) - instr(tysxkm_, ',', 1, 3) - 1) as km4,
               substr(tysxkm_, instr(tysxkm_, ',', 1, 4) + 1) as km5
          from t_studentinfo) t1
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t2
    on t1.km1 = t2.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t3
    on t1.km2 = t3.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t4
    on t1.km3 = t4.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t5
    on t1.km4 = t5.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t6
    on t1.km5 = t6.itemkey_;

如上所示，第 15 行 找到第 1 个逗号的位置，同时通过减 1 来算出截取��度，17-18 行 则是找到第 2 个和第 3 个逗号的位置，并截取出其中的考试科目代码，依此类推，而 25 行 则是找到最后一个逗号的位置并直接截取后半段得到最后一项考试科目代码，这样就完成了每项考试科目代码的分割，核心思想是通过符号来分割，在 blog 后面将介绍一种更为简便的方式（正则函数 REGEXP_SUBSTR），此处暂且用 substr 和 instr 组合的方式来实现，旨在回顾基础性的重点内容，接下来再看一些 Oracle 字符串相关的基础性常用的函数，比如求字符串长度：

select length('abcdefg') as length_ from dual; // 返回 7 

去空格函数 trim：

select trim('abcdefg') from dual; // 去左右空格
select ltrim('abcdefg') from dual; // 去左空格
select rtrim('abcdefg') from dual; // 去右空格

还有字母大小写转换函数：

select upper('AbCdEfG') from dual; // 小写转大写 返回 ABCDEFG
select lower('AbCdEfG') from dual; // 大写转小写 返回 abcdefg

Oracle 中基础性的常用函数先记录这么多，接下来具体研究一下 Oracle 中较为复杂的字符串应用场景以及相关函数。

字符串文字中包含引号

如题，单引号的转义问题，解决方法很简单，只需要把一个单引号换成两个单引号表示即可：

除此之外，在 Oracle10g 开始引入了 q -quote 特性，允许按照指定规则，也就是 Q 或 q 开头，字符串前后使用界定符”’”，规则很简单：

1. q-quote 界定符可以是除了 TAB、空格、回车外的任何单字符或多字节字符。
2. 界定符可以是[]、{}、<>、()，而且必须成对出现。

举例来看一下：

如上图所示，格式也很简单，不必再做过多解释。

计算字符在字符串中出现的次数

如题，例如有如下字符串：GREEN,RED,YELLOW,BLUE,WHITE，现在需要用 SQL 查询出其中包含的单词个数，通常我们只需要算出字符串中的逗号个数再加 1 就行了，那么如何计算字符串中某个字符的个数呢？这里就要用到 Oracle 的正则表达式函数了：

select length(regexp_replace('GREEN,RED,YELLOW,BLUE,WHITE', --source_char，源字符串
                             '[^,]', --pattern，正则表达式（替换满足正则的值）
                             '', --replace_string，替换后的目标字符串
                             1, --position，起始位置（默认为 1）
                             0, --occurrence，替换的次数（0 是无限次）
                             'i')) --match_parameter，匹配行为（'i' 是无视大小写）
       + 1 as count_
  from dual;

运行结果如下：

如上图所示，注释中简要说明了 REGEXP_REPLACE 函数每个参数的含义，通常只需要前三个参数即可，在 Oracle 的 Database Online Documentation 中可以看到该函数的格式：

再简单解释一下上面的 SQL 语句，可以发现正则表达式是 [^,]，中括号中的 ^ 符号表示“不包含以及否定的意思”（而中括号外的 ^ 则表示字符串的开始，更多的 Oracle 正则表达式可参考 Oracle 正则表达式使用介绍），所以regexp_replace('GREEN,RED,YELLOW,BLUE,WHITE', '[^,]','') 就表示将“除了逗号以外的所有字符替换成空”，然后通过 length 函数就算出了逗号的长度（个数），最后再加 1 就得到单词的个数了。从 Oracle 11g 开始又引入了一个新的正则函数 REGEXP_COUNT 使得这个问题的解决方案更加简单了：

select regexp_count('GREEN,RED,YELLOW,BLUE,WHITE', ',') + 1 as count_
  from dual;

运行结果如下：

如上图所示，REGEXP_COUNT 函数可以直接计算出逗号的个数，所以就无需再通过 REGEXP_REPLACE 去迂回计算了，但注意这个函数是 Oracle 11g 之后引入的，既然提到了 REGEXP_COUNT，下面就具体看一下它的语法格式：

如上图，可以看到 REGEXP_COUNT 相较于 REGEXP_REPLACE 简单一些，必选参数依然两个，用法也很简单，关于 REGEXP_COUNT 暂且先介绍这么多。

从字符串中删除不需要的字符

如题，这次以经典的 scott.emp 表为例：

比如我有这样一个需求，我想把 ENAME 这一列中的名字中包含的元音字母（AEIOU）去掉，那么该如何做呢？上一小节提到过 REGEXP_REPLACE 这个函数，显而易见，就用它就可以轻松的完成替换：

select regexp_replace(ename, '[AEIOU]') as ENAME from scott.emp;

运行结果如下：

如上图所示，通过 REGEXP_REPLACE 函数很容易将 AEIOU 这 5 个字母转换为空字符串，从而避免了直接用 replace 函数要进行多层嵌套的问题。

字符和数字相分离

如题，回到我们的学生表，首先创建一个测试用的视图：

create or replace view v_test_11 as
select bmh_ || stuname_ as data from t_studentinfo;

select * from v_test_11;

运行结果如下：

如上所示，我们通过拼接学生的考号和姓名组成了新的一列 data，现在的需求是再将这一列按考号和姓名拆分开来，如何实现呢？很明显拆分的是数字和汉字，那么自然用正则最为合适了：

select regexp_replace(data, '[0-9]') as name1,
       regexp_replace(data, '[^0-9]') as zkzh
  from v_test_11;

运行结果如下：

如上图，可以看到成功拆分了考号和姓名，简单解释一下上面的正则，[0-9]代表数字 [0123456789]，还可以表示为[[:digit:]]，那么将数字全部替换后自然就剩下了所有的汉字组成的 NAME1 字段，同理，[^0-9] 表示 [0-9] 的外集，即“除了 0123456789”之外的所有字符，那么将除了数字之外的所有字符替换为空，剩下的自然就是纯数字组成的 ZKZH 字段了，还需要注意一点就是 ^ 符号，它在方括号表达式内的意思是“否定、非、相反”的意思，如果它在方括号表达式以外则表示“字符串开始”的意思，这个会在后面的例子中再做说明。

按字符串中的数值排序

如题，依旧先创建一个测试视图：

create or replace view v_test_05 as
select stuname_ || '' ||schoolname_ || ' ' || lqfs_ as data from t_lq order by stuname_;

select * from v_test_05;

运行结果如下：

如上图，我们再一次进行了拼接构造数据，这次拼接了三列，分别是学生姓名，所在学校以及考试总分，我们这次的需求是按照分数倒序排列（目前是按照姓名拼音字母顺序排序的），该如何处理呢？通过这几个例子应该找出规律了，这种提取字符串中某一类型的数据再做相关操作的直接用正则表达式肯定是最方便的！所以我们依然通过正则提取列中的分数再来进行排序即可：

select data from v_test_05 order by to_number(regexp_replace(data, '[^0-9]')) desc

运行结果如下所示：

如上图，依旧通过正则 [^0-9] 将字符串中的非数字全部替换为空，然后得到纯分数字符串后再通过 to_number 函数转换为数字即可排序，与上面的几个例子思路基本一致，都应用了正则函数 REGEXP_REPLACE，关于 REGEXP_REPLACE 的函数至此就介绍的差不多了，最后再补充一个例子是今年项目中遇到的一个问题，需求是“将查询出的字符串每个字符中间加空格后返回”，比如：数据库中的字段值原本是”abc”，那么查询出来应当是”a b c”，如果是”王小明”，那么查询出来应当是”王 小 明”，解决这个问题最简单的方式依然是通过正则：

select regexp_replace('abc','(.)','\1') as data from dual

运行结果如下图所示：

如上图所示，正则 (.) 表示匹配除换行符 n 之外的任意单个字符，而 \1 则是每个字符自身再加一个空格，所以就得到了我们预期的结果。

提取第 n 个分隔的子串

如题，这里就会用到我们 blog 开头用 instr 和 substr 写的那个例子的简便写法，上面也说了会用到将要介绍的第三个正则函数 REGEXP_SUBSTR，所以先看看这个正则函数的语法格式以及参数说明：

如上图，看一下每个参数的含义：

1. source_string：源字符串
2. pattern：正则表达式
3. position：起始位置
4. occurrence：第 n 个能匹配正则的字符串

可以看到和前面的正则函数都差不多，接下来将 blog 开头的例子改写成 REGEXP_SUBSTR 的形式：

select t1.sid_,
       t1.stuname_,
       t1.km1,
       t2.itemvalue_ km1name,
       t1.km2,
       t3.itemvalue_ km2name,
       t1.km3,
       t4.itemvalue_ km3name,
       t1.km4,
       t5.itemvalue_ km4name,
       t1.km5,
       t6.itemvalue_ km5name
  from (select sid_,
               stuname_,
               regexp_substr(tysxkm_, '[^,]+', 1, 1) as km1,
               regexp_substr(tysxkm_, '[^,]+', 1, 2) as km2,
               regexp_substr(tysxkm_, '[^,]+', 1, 3) as km3,
               regexp_substr(tysxkm_, '[^,]+', 1, 4) as km4,
               regexp_substr(tysxkm_, '[^,]+', 1, 5) as km5
          from t_studentinfo) t1
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t2
    on t1.km1 = t2.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t3
    on t1.km2 = t3.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t4
    on t1.km3 = t4.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t5
    on t1.km4 = t5.itemkey_
  left join (select itemkey_, itemvalue_
               from t_dict
              where itemname_ = 'SportsType') t6
    on t1.km5 = t6.itemkey_;

运行结果如下：

如上图，可以看到通过 REGEXP_SUBSTR 来做“按符号截取字符串”要比 instr 和 substr 组合好用的多，正则中加号表示匹配 1 次以上的意思，所以 [^,]+ 所以表示 匹配不包含逗号的多个字符 ，在此也就表示被逗号分隔后的各个子串。REGEXP_SUBSTR 的第 3 个参数表示“从第 1 个字符开始”，同其它正则函数的 position 都是一样的，而重点是第 4 个参数，表示 第 n 个能匹配到该正则的字符串，分隔之后自然就是按自然顺序就可以取到各字符串了。

分解 IP 地址

如题，例如需要将一个 ip 地址中的各段取出来，显而易见和上面的需求完全一样，只不过一个是逗号分隔，而 ip 地址是点分隔：

select regexp_substr(v.ipaddr, '[^.]+', 1, 1) as firstpart,
       regexp_substr(v.ipaddr, '[^.]+', 1, 2) as secondpart,
       regexp_substr(v.ipaddr, '[^.]+', 1, 3) as thirdpart,
       regexp_substr(v.ipaddr, '[^.]+', 1, 4) as fourthpart
  from (select '192.168.0.100' as ipaddr from dual) v

运行结果如下：

和上一个例子用法一模一样，在此就不做过多赘述了。

查询只包含字母或数字型的数据

如题，说到查询中的正则，那么肯定是要用到 REGEXP_LIKE 这个函数了，依旧是先看一下 REGEXP_LIKE 的语法格式和参数说明：

如上图所示，REGEXP_LIKE 只有 3 个参数，用法类似于普通的 LIKE 模糊查询，下面依次看一下这每个参数的含义：

1. source_string：源字符串
2. pattern：正则表达式
3. match_parameter：匹配参数，例如’i’，作用是忽略大小写

REGEXP_LIKE 返回匹配正则的字符，依旧通过例子来看，首先创建一个测试 view：

create or replace view v_test_06 as
select '123' as data from dual union all
select 'abc' from dual union all
select '123abc' from dual union all
select 'abc123' from dual union all
select 'a1b2c3' from dual union all
select 'a1b2c3#' from dual union all
select '3$' from dual union all
select 'a 2' from dual union all
select '0123456789' from dual union all
select 'b3#45' from dual;

查询一下这个 view：

如上所示，可以看到准备了一组测试数据，那么接下来的需求是“查询只包含字母或数字的值“，就是说只能包含字母和数字，不能有其它字符，很明显依然要通过正则来判断，下面先看一下完整的写法：

select data from  v_test_06 where regexp_like(data,'^[0-9A-Za-z]+$');

运行结果如下：

如上图，可以看到已经成功过滤出只包含字符和数字的值，那么接下来具体解释一下这个正则 ^[0-9A-Za-z]+$，首先 REGEXP_LIKE 对应普通的 LIKE，REGEXP_LIKE(data,’[ABC]’) 就相当于 LIKE‘%A%’or LIKE‘%B%’or LIKE‘%C%’，而 REGEXP_LIKE(data,’[0-9A-Za-z]+’)就相当于 LIKE‘% 数字 %’or LIKE‘% 小写字母 %’or LIKE‘% 大写字母 %’。需要格外注意的一点就是 ^ 符号，它在中括号外面表示字符串开始（在前面的例子中均在字符串内，表示否定及相反），^[0-9A-Za-z]意思就是 匹配以任意数字或者任意大小写字母开头的字符串 ，而正则中的$ 符号表示字符串结束，所以 [0-9A-Za-z]$ 就表示 匹配任意数字或大小写字母结尾的字符串，完全类似于模糊查询的 LIKE‘A%’和 LIKE‘%A’，但是 ^$ 在一起的时候的就是精确查找了，比如：

那么上面这个正则 ^[0-9A-Za-z]+$ 为何能匹配到所有“只包含字符或数字的值”呢？其实加号（+）在这里也起到了关键作用，加号在正则中的意思是“匹配前面的表达式一次或多次”，所以在这里加号就 表示每一个字符都要匹配 [0-9A-Za-z] 这个规则且以数字字母开头和结尾，所以这样就查出了只包含字符或数字的值。

列转行

如题，最后的一个话题，谈谈 Oracle11.2 版本开始提供的一个用于列传行的函数 listagg，用法和 postgresql9.3 中的 string_agg 函数基本一致，我在之前的博客也专门介绍过 postgres 的 string_agg 这个函数（postgresql 9.3 自定义聚合函数实现多行数据合并成一列），但语法上比 postgres 的 string_agg 函数更繁琐一些，首先来看一下 listagg 这个函数的语法格式：

如上图，listagg 函数有 4 个参数，下面简单解释一下：

1. measure_expr：分组中每个列的表达式，也就是说需要合并的列
2. delimiter：分隔符，不设置的话，就表示无分割符
3. order_by_clause：进行合并中要遵守的排序顺序
4. query_partition_clause：表示 listagg 是具有分析函数 analyze funcation 特性

所以说 listagg 尽管更多被用作聚集函数，但它还是有 analyze funcation 特性。下面通过一个例子具体看一下 listagg 的用法，首先学生体育成绩表有如下数据：

如上图，是一张体育考试成绩表，每名学生有 5 门考试成绩，可以看到 ZKZH_这一列是考号，SKXMDM_是之前说过的每门考试科目的代码，而 HSCJ_就是考试成绩了，假设我现在的需求是按考号分组，将每名学生的 SKXMDM_用逗号拼接，同时求体育总成绩，该怎么做呢？很典型的一个列传行，这里用 list_agg 函数就再合适不过了：

select zkzh_,
       listagg(skxmdm_, ',') within group(order by zkzh_) as skxmdm,
       sum(hscj_) score
  from T_SPORTTESTSCORE t
 where t.kz1_ = 1
 group by zkzh_

运行结果如下：

如上图所示，可以看到很好的完成了列的合并以及 sum 求和，到此为止 Oracle 中字符串相关的函数暂且介绍到这里，后面有机会还会陆续添加。

总结

本篇 blog 着重记录了 Oracle 中和字符串相关的一些个人认为比较常用及重要的函数和相关使用场景，重难点是那 4 个正则函数，如果在查询中能灵活运用正则函数的话确实能快捷的实现一些复杂的需求，最后由于个人能力有限难免有疏漏之处欢迎各位读者批评指正，同样也希望对读了本文的新手朋友们有所帮助，The End。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135542.htm