[ORACLE] with as select、case when else end、replace

最近学习《ORACLE编程艺术》时，第三章跟踪文件作者写了一个查询语句，将v$diag_info的 ADR Home的值替换成$home$，上网查询了使用到的with as select、case when、replace等的作用，才看懂了，在此记录一下。 查询语句内容如下：with home as (select value home from v$diag_info where name='ADR Home')select name,       case when value <> home.home    then replace (value,home.home,'$home$')    else value    end value from v$diag_info,home; with as select:    该语句会创建一个临时表，用于下面的查询，在这里是创建了一个home表，表中有一行一列，列名为home，值为v$diag_info 视图中name为 ADR Home的行的value值，在我的环境下是 /u01/app/oracle/diag/rdbms/cdb1/CDB1，下面的查询使用home.home（表名.列名）即可访问这个值。 case when else end：    满足case when 后面的条件，得到then后面的值，和编程语言中的switch类似。这里判断v$diag_info视图的行的value值是否是home.home（即   /u01/app/oracle/diag/rdbms/cdb1/CDB1），若不是，则使用replace函数替换，否则，保留值。目的是排除掉  ADR Home 这一行，使它显示    /u01/app/oracle/diag/rdbms/cdb1/CDB1， 而不是 $HOME$。 replace：     替换字符串的函数，分别有3个参数。     replace(str1,str2,str3)     str1是原来的字符串，这里就是每行value列的值。     str2是要被替换的字符串，这里是home.home（ 即    /u01/app/oracle/diag/rdbms/cdb1/CDB1）     str3是要替换成的字符串，这里是 $home$     就是在str1中查找str2，找到则将str1中的str2替换成str3，找不到则不变。     这里replace就是实现将value列的值包含  /u01/app/oracle/diag/rdbms/cdb1/CDB1 的，将  /u01/app/oracle/diag/rdbms/cdb1/CDB1 替换成 $home$ 综上，整个查询就是匹配 select name,value from v$diag_info 语句结果集中除了 ADR Home 的所有行，若存在  /u01/app/oracle/diag/rdbms/cdb1/CDB1 值，将其替换成 $home$ ,便于阅读理解。 替换前的结果： sys@PDB1> select name,value from v$diag_info; NAME                           VALUE ------------------------- ---------------------------------------------------------------------- Diag Enabled                TRUE ADR Base                       /u01/app/oracle ADR Home                    /u01/app/oracle/diag/rdbms/cdb1/CDB1 Diag Trace                     /u01/app/oracle/diag/rdbms/cdb1/CDB1/trace Diag Alert                      /u01/app/oracle/diag/rdbms/cdb1/CDB1/alert Diag Incident                 /u01/app/oracle/diag/rdbms/cdb1/CDB1/incident Diag Cdump                  /u01/app/oracle/diag/rdbms/cdb1/CDB1/cdump Health Monitor              /u01/app/oracle/diag/rdbms/cdb1/CDB1/hm Default Trace File           /u01/app/oracle/diag/rdbms/cdb1/CDB1/trace/CDB1_ora_72322.trc Active Problem Count      0 Active Incident Count     0 11 rows selected. 替换后的结果： sys@PDB1> with home as    2  (select value home from v$diag_info where name='ADR Home')   3  select name,   4         case when value <> home.home   5        then replace (value,home.home,'$home$')   6        else value   7        end value   8     from v$diag_info,home; NAME                           VALUE ------------------------- ---------------------------------------------------------------------- Diag Enabled                TRUE ADR Base                      /u01/app/oracle ADR Home                   /u01/app/oracle/diag/rdbms/cdb1/CDB1 Diag Trace                   $home$/trace Diag Alert                    $home$/alert Diag Incident               $home$/incident Diag Cdump                $home$/cdump Health Monitor            $home$/hm Default Trace File         $home$/trace/CDB1_ora_72322.trc Active Problem Count      0 Active Incident Count     0 11 rows selected.