文章标题

一、数值函数
–ABS ( n )
用法：返回n的绝对值。
参数：n为数值数据类型，或者可以隐式转化为数值类型的其他数据类型。

–CEIL ( n )
用法：返回大于或等于n的最小整数。向上取整
参数：n可以为任意数值类型或可以隐式转化成数值的其他类型。
说明：返回值的类型与参数的数据类型相同。

–FLOOR ( n )
用法：返回小于或等于n的最大整数。向下取整
参数：n可以为任意数值类型或可以隐式转化成数值的其他类型。返回值的类型与参数的数据类型相同。

–MAX ( expr )
用法：聚合函数，返回所有行中expr的最大值。

–MEDIAN ( expr )
用法：聚合函数，返回所有行中expr的中位数。
参数：expr只能是数值类型或日期时间类型。

–MIN ( expr )
用法：聚合函数，返回所有行中expr的最小值。

–MOD ( n2, n1 )
用法：返回n2除以n1后的余数。如果n1为0，则返回n2。

–GREATEST ( { expr [,expr … ] } )
用法：返回一个或多个表达式中的最大值。

–LEAST ( { expr [,expr … ] } )
用法：返回一个或多个表达式中的最小值。

–SIGN ( n )
用法：返回n的符号。对于NUMBER，当n<0时，返回-1；当n=0时，返回0；当n>0时，返回1。对于BINARY_FLOAT或BINARY_DOUBLE，但n<0时，返回-1；当n>=0或n=NaN时，返回1。

–ROUND ( date [, fmt] )
用法：返回关于格式fmt的完整日期。fmt默认为DAY。

–ROUND ( n [, integer] )
用法：返回四舍五入后的n。计算原理是如果n为0，总是返回0；如果n为正数，则返回FLOOR(n * POWER(10, integer) + 0.5) *POWER(10, -integer)；如果n为负数，则返回-ROUND(-n, integer)。
参数：n的默认值为0，当n大于0时，表示小数点右边进行四舍五入；当n小于0时，表示小数点左边进行四舍五入。

–TRUNC ( date [, fmt] )
用法：截取掉有fmt指定的日期部分。

–TRUNC(NUMBER)
语法：TRUNC ( n1 [, n2 ] )
用法：将n1截取到小数点的n2位置。如果n2>0，从小数点右边截取；如果n1<0，从小数点左边截取。

–##字符函数
–INITCAP ( char )
用法：将char转化成 每个 单词首字母大写，其他字母小写的形式。单词是由空格或其他非字母数字字符来间隔。
参数：char可以为CHAR，VARCHAR2，NCHAR或NVARCHAR2，返回值类型与char相同。
例子：
SQL> SELECTINITCAP(‘ORACLE DATABASE’) reslt FROM DUAL;
Oracle Database
SQL> SELECT INITCAP(‘Oracledatabase’) reslt FROM DUAL;
Oracle Database

–LOWER ( char )
用法：返回字符串的小写形式。

–UPPER ( char )
用法：将char中字符串转化为大写字母。

–LENGTH ( char )
用法：返回字符串char的长度。

–vsize ( char )
返回表达式所需的字节数
select vsize(‘函数’) from dual; –4个字节
select length(‘函数’) from dual; –2个长度

–LPAD ( expr1, n [,expr2 ] )
用法：返回长度为n的expr1字符串。如果expr1的长度小于n，则用expr2左填充expr1到长度n为止；expr2默认为一个空格符。如果expr1的长度大于n，截取expr1前n个字符返回。

–RPAD ( expr1, n [,expr2 ] )
用法：返回长度为n的expr1字符串。如果expr1的长度小于n，则用expr2右填充expr1到长度n为止；expr2默认为一个空格符。如果expr1的长度大于n，截取expr1前n个字符返回。

–LTRIM ( char [, set] )
用法：删除char从左边开始的包含在set中的字符，直到遇到第一个不再set中的字符。set默认是空格符。
例子：
SQL> SELECTLTRIM(‘<===>ORACLE<===>’, ‘<>=’) result FROM DUAL;
ORACLE<===>

–RTRIM ( char [, set] )
用法：删除char从右边开始的包含在set中的字符，直到遇到第一个不再set中的字符。set默认是空格符。

–TRIM ( [ { { LEADING| TRAILING | BOTH } [ trim_character ] | trim_character } FROM ] trim_source )
用法：删除trim_source中开头或结尾的trim_character中的字符。
如果指定了LEADING，表示从开头删除；
如果指定了TRAILING，表示从结尾删除；
如果指定了BOTH，表示从两头删除。
如果没有指定trim_source，默认是空格符。如果只指定了trim_source，表示删除两头的空格符。
trim_character只能是一个字符。
例子：
SQL> SELECT TRIM( ‘-’ FROM ‘–ORACLE’) result FROM DUAL;
ORACLE

SQL> SELECTTRIM(’ OE’ FROM ’ ORACLE DATABASE ‘)result FROM DUAL;
ORA-30001: trim setshould have only one character

–CONCAT ( char1,char2 )
用法：返回值是char1连接char2，效果与连接符||一样。
参数：char1和char2可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB。

–REPLACE ( char,search_string [, replacement_string ] )
用法：将char中的所有search_string替换成replacement_string。如果忽略replacement_string或为NULL，那将删除char中的所有search_string。

–INSTR ( string,substring [, position [, occurrence ] ] )
用法：在string中，从position开始搜索substring，返回第occurrence次匹配的位置。如果没找到，返回0。position和occurrence的默认值是1。当position为负数时，表示从string的后面开始向前搜索。

–SUBSTR ( char,position [, substring_length ] )
用法：从char中position开始截取长度为substring_length的字符串。如果position为0，则改成1；如果position为正数，则从char的左边开始数；如果position为负数，则从char的右边开始数。如果substring_length忽略，则截取到char的最后。

–TRANSLATE ( expr,from_string, to_string )
用法：将expr中from_string中的字符替换成to_string中的相应字符。当from_string包含的字符多于to_string，没有对应的字符就删除其在expr中的字符。如果一个字符在from_string中出现多次，只替换成第一个相应的字符。

–TO_CHAR(CHARACTER)
语法:TO_CHAR ( { nchar |clob | nclob } )
用法：将NCHAR，NVARCHAR2，CLOB或NCLOB转化成数据库字符集，返回值总是VARCHAR2类型。当LOB的长度超过VARCHAR2的最大长度时，Oracle会返回一个错误。

–TO_CHAR(DATETIME)
语法：TO_CHAR ( { datetime| interval } [, fmt [, ‘nlsparam’ ] ] )
用法：将DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND，INTERVAL YEAR TO MONTH转化成格式为fmt的VARCHAR2类型。如果忽略fmt，将以下面的规则进行处理：DATE转化成默认的日期格式；TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE转化成默认的时间戳格式；TIMESTAMP WITH TIME ZONE转化成默认的时间戳加时区的格式；INTERVAL值转化成数字代表的格式。
参数：nlsparam可以指定会话的语言，如NLS_DATE_LANGUAGE=language。

–TO_CHAR(NUMBER)
语法：TO_CHAR ( n [, fmt[, ‘nlsparam’ ] ] )
用法：将数值n转化成格式是fmt的VARCHAR2。如果n是负数，负号会放在格式化的字符串之前，如TO_CHAR(-1, ‘9 ′ )返回− 1，而不是$-1。
参数：nlsparam可以指定小数点、千分位、货币符号（Local和International）。如NLS_NUMERIC_CHARACTERS=’dg’，NLS_CURRENCY=’text’，NLS_ISO_CURRENCY=territory。

–##日期函数
TO_DATE格式(以时间:2007-11-02 13:45:25为例)

Year:
yy two digits 两位年 显示值:07
yyy three digits 三位年 显示值:007
yyyy four digits 四位年 显示值:2007

Month:
mm number 两位月 显示值:11
mon abbreviated 字符集表示 显示值:11月,若是英文版,显示nov
month spelled out 字符集表示 显示值:11月,若是英文版,显示november

Day:
dd number 当月第几天 显示值:02
ddd number 当年第几天 显示值:02
d number 当周第几天数字 显示值:5
dy abbreviated 当周第几天简写 显示值:星期五,若是英文版,显示fri
day spelled out 当周第几天全写 显示值:星期五,若是英文版,显示friday
ddspth spelled out, 当月第几天全写 显示值：几号, 若是英文版,显示seventh

Hour:
hh two digits 12小时进制 显示值:01
hh24 two digits 24小时进制 显示值:13

Minute:
mi two digits 60进制 显示值:45

Second:
ss two digits 60进制 显示值:25

其它
Q digit 季度 显示值:4
WW digit 当年第几周 显示值:44
W digit 当月第几周 显示值:1

24小时格式下时间范围为： 0:00:00 - 23:59:59….
12小时格式下时间范围为： 1:00:00 - 12:59:59 ….

–日期和字符转换函数用法（to_date,to_char）
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as nowTime from dual; //日期转化为字符串
select to_char(sysdate,’yyyy’) as nowYear from dual; //获取时间的年
select to_char(sysdate,’mm’) as nowMonth from dual; //获取时间的月
select to_char(sysdate,’dd’) as nowDay from dual; //获取时间的日
select to_char(sysdate,’hh24’) as nowHour from dual; //获取时间的时
select to_char(sysdate,’mi’) as nowMinute from dual; //获取时间的分
select to_char(sysdate,’ss’) as nowSecond from dual; //获取时间的秒
select to_date(‘2004-05-07 13:23:44’,’yyyy-mm-dd hh24:mi:ss’) from dual//

将语言改为英语：SQL> ALTER SESSION SET NLS_LANGUAGE=’AMERICAN’;
将语言改回中文：SQL> ALTER SESSION SET NLS_LANGUAGE=’SIMPLIFIED CHINESE’;

–SYSDATE
用法：返回数据库系统的当前日期时间。

–SYSTIMESTAMP
用法：返回数据库系统的当前日期时间，包括小数秒和时区信息。

–ADD_MONTHS ( date,integer )
用法：返回date加上integer个月后的日期。一个月是由会话(session)参数NLS_CALENDAR来定义的。
参数：date是一个日期时间值或可以隐式转化为DATE的其他值。
integer是一个整数或其他可以转化为整数的值。
说明：返回值永远是DATE数据类型。
如果date是一个月的最后一天，或者得到结果的月份的天数比date所在月份的天数多时，返回结果是结果月份的最后一天。

–MONTHS_BETWEEN ( date1,date2 )
用法：返回date1和date2之间的月份数。如果date1比date2晚，结果为正数；如果date1比date2早，结果为负数。如果date1和date2是该月中的相同日期，或者是该月中的最后一天，那么结果是整数，否则结果具有小数部分。

–NEXT_DAY ( date,char )
用法：返回date之后的第一个由char表示的星期几。返回值类型是DATE。

–LAST_DAY ( date )
用法：返回date所在月份的最后一天。

–round舍入到最接近的日期
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,’year’) YEAR,
round(sysdate,’month’) MONTH ,
round(sysdate,’day’) DAY from dual

–trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒
trunc(sysdate,’year’) YEAR, //返回当前年的1月1日,无时分秒
trunc(sysdate,’month’) MONTH , //返回当前月的1日,无时分秒
trunc(sysdate,’day’) DAY //返回当前星期的星期天,无时分秒
from dual

–计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日

  select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual        //时间差-年  select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual        //时间差-月  select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual             //时间差-天  select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual         //时间差-时  select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual    //时间差-分  select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒 

–更新时间
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),to_char(sysdate+n*365,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual //改变时间-年
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),add_months(sysdate,n) as newTime from dual //改变时间-月
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),to_char(sysdate+n,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual //改变时间-日
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),to_char(sysdate+n/24,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual //改变时间-时
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),to_char(sysdate+n/24/60,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual //改变时间-分
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),to_char(sysdate+n/24/60/60,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual //改变时间-秒

–查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, ‘MONTH’) - 1, ‘MONTH’) First_Day_Last_Month,
Trunc(SYSDATE, ‘MONTH’) - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, ‘MONTH’) First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, ‘MONTH’)) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;

–周处理
select trunc(sysdate,’ww’) ,
trunc(sysdate,’iw’) ,
trunc(sysdate,’w’)
from dual;
ww 按年度1月1日的第一天为每周第一天
w 按月份1日的第一天作为每周第一天
iw 每周一

–##空值处理
–NVL ( expr1, expr2 )
用法：如果expr1为NULL，返回expr2，否则返回expr1。
参数：如果expr1和expr2的数据类型不同时，Oracle将进行隐式转化。如果不能转化，Oracle会返回一个错误。

–NVL2 ( expr1, expr2,expr3 )
用法：如果expr1不为null，则返回expr2，否则返回expr3。

–NULLIF ( expr1,expr2 )
用法：比较expr1和expr2，如果相等，返回NULL，否则返回expr1。expr1不能是NULL的字面量。

–COALESCE ( { expr [,expr ] … } )
用法：返回第一个非NULL的值，至少有两个expr。
参数：expr的数据类型必须一致。

–##group by 处理
–GROUPING ( expr )
用法：GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
参数：expr必须是GROUP BY中的表达。如果expr在行中的值为NULL，则返回1，否则返回0。
说明：grouping的基础上进行decode, case等进行美化。

–GROUPING_ID ( expr )
用法：比grouping还强点，可以接收多个列，这几个列都不为空时，返回0，只要有一个为空，则返回1，如果都为空，则返回3。

–ROLLUP
用法：按分组的第一个列进行统计和最后的小计

–ROLLUP另类用法
用法：只显示合计，不显示小计
ROLLUP(a,(b,c))

–CUBE
用法：按分组的所有列的进行统计和最后的小计

–grouping sets
用法：按分组的所有列只进行小计，不显示合计

SQL> select deptno,job ,sum(sal) from emp group by deptno,job;

DEPTNO JOB          SUM(SAL)

---

    20 CLERK            1900    30 SALESMAN         5600    20 MANAGER          2975    30 CLERK             950    10 PRESIDENT        5000    30 MANAGER          2850    10 CLERK            1300    10 MANAGER          2450    20 ANALYST          6000    99 CLERK             800

SQL> select deptno,job ,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB          SUM(SAL)

---

    10 CLERK            1300    10 MANAGER          2450    10 PRESIDENT        5000    10                  8750    20 CLERK            1900    20 ANALYST          6000    20 MANAGER          2975    20                 10875    30 CLERK             950    30 MANAGER          2850    30 SALESMAN         5600    30                  9400    99 CLERK             800    99                   800                       29825

SQL> select deptno,job ,sum(sal) from emp group by cube(deptno,job);

DEPTNO JOB          SUM(SAL)

---

                       29825       CLERK            4950       ANALYST          6000       MANAGER          8275       SALESMAN         5600       PRESIDENT        5000    10                  8750    10 CLERK            1300    10 MANAGER          2450    10 PRESIDENT        5000    20                 10875    20 CLERK            1900    20 ANALYST          6000    20 MANAGER          2975    30                  9400    30 CLERK             950    30 MANAGER          2850    30 SALESMAN         5600    99                   800    99 CLERK             800

SQL> select deptno,job ,sum(sal) from emp group by grouping sets(deptno,job);

DEPTNO JOB          SUM(SAL)

---

       CLERK            4950       SALESMAN         5600       PRESIDENT        5000       MANAGER          8275       ANALYST          6000    30                  9400    20                 10875    99                   800    10                  8750

SQL> select deptno,
2 job,
3 sum(sal)
4 from emp
5 group by rollup((deptno,job));

DEPTNO JOB          SUM(SAL)

---

    10 CLERK            1300    10 MANAGER          2450    10 PRESIDENT        5000    20 CLERK            1900    20 ANALYST          6000    20 MANAGER          2975    30 CLERK             950    30 MANAGER          2850    30 SALESMAN         5600    99 CLERK             800                       29825

SQL> select case when grouping(deptno)=1 then ‘heji’ else to_char(deptno) end “部门”,
2 case when grouping(job)=1 then ‘xiaoji’ else job end “职位”,
3 sum(sal)
4 from emp
5 group by rollup(deptno,job);

部门 职位 SUM(SAL)

---

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 xiaoji 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 xiaoji 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 xiaoji 9400
99 CLERK 800
99 xiaoji 800
heji xiaoji 29825

SQL> select grouping_id(deptno,job) “部门”,
2 deptno,
3 job,
4 sum(sal)
5 from emp
6 group by rollup(deptno,job);

    部门     DEPTNO JOB          SUM(SAL)

---

     0         10 CLERK            1300     0         10 MANAGER          2450     0         10 PRESIDENT        5000     1         10                  8750     0         20 CLERK            1900     0         20 ANALYST          6000     0         20 MANAGER          2975     1         20                 10875     0         30 CLERK             950     0         30 MANAGER          2850     0         30 SALESMAN         5600     1         30                  9400     0         99 CLERK             800     1         99                   800     3                            29825

–##正则
Oracle中支持正则表达式的函数主要有如下几种：
1、regexp_like(x,pattern) 当x能正确匹配字符串时返回true。
2、regexp_instr(x,pattern) 在x中尝试匹配pattern，并返回匹配的位置。
3、regexp_replace(x,pattern,replacestring) 在x中尝试匹配pattern,并将其替换成replacestring。
4、regexp_substr(x,pattern） 返回x中匹配pattern的一个字符串。
5、regexp_count(x,pattern) 返回x中匹配pattern的个数

它们在用法上与 Oracle SQL 函数 LIKE、INSTR、SUBSTR、REPLACE和COUNT 用法类似，
但是它们使用 POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。
POSIX 正则表达式由标准的元字符（metacharacters）所构成：
^ 匹配字符串的开始位置。
匹配字符串的结尾位置。.匹配除换行符之外的任何单字符。?匹配前面的子表达式零次或一次。例如52?oracle只能匹配5oracle,52oracle等等+匹配前面的子表达式一次或多次。例如52+oracle可以匹配52oracle,522oracle,5222oracle等等∗匹配前面的子表达式零次或多次。例如52∗oracle可以匹配5oracle,52oracle,522oracle,5222oracle等等。|指明两项之间的一个选择。例子 ′( [a−z]+|[0−9]+) ’表示所有小写字母或数字组合成的字符串。
( ) 标记一个子表达式的开始和结束位置。
[] 标记一个中括号表达式。
[abc] 匹配括号中的任意一个字符。例如：[ab]bc可以匹配abc和bbc
[a-z] 匹配指定范围内的任意字符串。例如[A-G]hi可以匹配Ahi至Ghi
{n} 匹配一个字符串n次，n为正整数。例如：hel{2}o 所匹配的是hello
{n,m} 匹配一个字符串至少n次，至多m次。其中n和m都是整数
{n,} 表示至少出现n次
\n表示重复n次上次匹配的内容，此处(a)\1表示匹配两个连续的字符’a’。
\转义符

字符簇：
[::]指定一个字符类，可以匹配该类中的任意字符 这里的字符类包括：
[:alpha:] 任何字母。A-Z、a-z
[:digit:] 任何数字。0-9
[:alnum:] 任何字母和数字。0-9、A-Z、a-z
[:space:] 任何白字符。空格或者tab键
[:upper:] 任何大写字母。 A-Z
[:lower:] 任何小写字母。a-z
[:punct:] 任何标点符号。. , ” ‘等标点符号
[:xdigit:] 任何 16 进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级
\转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序

备注：
1）^ 在[] 里表示取反，[^A] 表示非A的字符
^ 在[] 外表示开始，^A 表示已A开头的字符
2）[]中的集合是或者的关系，[,A] 表示 ,或A
3）[0-9] 表示0到9
[a-z] 表示小写a到z
[A-Z] 表示大写A到Z
[a-zA-Z] 表示所有字母，包括大小写，中间无空格

正则分组
（） 中匹配到的内容将会被分为一组，
\n 表示取第n组的内容
SELECT regexp_replace(v.a1, ‘([A-Z])(.)([A-Z])(.)’, ‘\1.\3’) AS sx,
/分拆，显示（1）/
‘|’ || regexp_replace(v.a1, ‘([A-Z])(.)([A-Z])(.)’, ‘\1’) || ‘|’ AS a1,
/分拆，显示（2）两端加竖线是为了显示空格位置/
‘|’ || regexp_replace(v.a1, ‘([A-Z])(.)([A-Z])(.)’, ‘\2’) || ‘|’ AS a2,
/分拆，显示（3）/
‘|’ || regexp_replace(v.a1, ‘([A-Z])(.)([A-Z])(.)’, ‘\3’) || ‘|’ AS a3,
/分拆，显示（4）/
‘|’ || regexp_replace(v.a1, ‘([A-Z])(.)([A-Z])(.)’, ‘\4’) || ‘|’ AS a4
FROM v

1、REGEXP_LIKE(String, pattern [, match_option])
作用：where条件中使用，查找出匹配字符
详解：当源字符串String匹配正则表达式pattern时，返回true。
参数：String - 需要进行正则处理的字符串
pattern - 进行匹配的正则表达式
match_option - 匹配模式
‘c’ 说明在进行匹配时区分大小写（默认选项）
‘i’ 说明在进行匹配时不区分大小写
‘n’ 允许使用可以匹配任意字符的操作符（通常是’.’）
‘m’ 将String作为一个包含多行的字符串
注意：不要混淆LIKE操作符的通配符和正则表达式的语法，也就是说不要再正则表达式中使用LIKE操作符中的通配符，如果这样做会得到未知的结果，因为’_’和’%’会被正则表达式当做普通字符进行匹配。
例子：SELECT * FROM test_reg WHERE REGEXP_LIKE(name, ‘(a)\1’, ‘i’);
出处：http://www.linuxidc.com/Linux/2012-05/60788.htm

2、regexp_instr(String,pattern[,position[,occurrence[,return_option[,match_option]]]])
作用：查找字符串所在的位置
详解：
参数：String - 需要进行正则处理的字符串
pattern - 进行匹配的正则表达式
position - 开始匹配的位置
occurrence - 匹配的次数，如果不指定，默认为1
return_option 0——返回第一个字符出现的位置。1：pattern下一个字符起始位置。
match_option - 匹配模式
‘c’ 说明在进行匹配时区分大小写（默认选项）
‘i’ 说明在进行匹配时不区分大小写
‘n’ 允许使用可以匹配任意字符的操作符（通常是’.’）
‘m’ 将String作为一个包含多行的字符串
注意：
例子：Select regexp_INSTR(‘abvdsdabasdab’,’ab’,1,2,1) FROM DUAL;
出处：http://chicony.iteye.com/blog/460934

3、REGEXP_REPLACE(string, pattern[,replace_string [, position[,occurrence, [match_parameter]]]])
作用：替换字符串
详解：
参数：String - 需要进行正则处理的字符串
pattern - 进行匹配的正则表达式
replace_string - 替换的字符
position - 开始匹配的位置
occurrence - 匹配的次数，如果不指定，默认为1
match_option - 匹配模式
‘c’ 说明在进行匹配时区分大小写（默认选项）
‘i’ 说明在进行匹配时不区分大小写
‘n’ 允许使用可以匹配任意字符的操作符（通常是’.’）
‘m’ 将String作为一个包含多行的字符串
注意：
例子：
出处：

4、REGEXP_SUBSTR(String, pattern, position, occurrence, match_option)
作用：以某个标识拆分字符串 到不同的行中
详解：
参数：String - 需要进行正则处理的字符串
pattern - 进行匹配的正则表达式
position - 开始匹配的位置
occurrence - 匹配的次数，如果不指定，默认为1
match_option - 匹配模式
‘c’ 说明在进行匹配时区分大小写（默认选项）
‘i’ 说明在进行匹配时不区分大小写

注意：
例子：
出处：
模拟：
1).查询使用正则分割后的第一个值，也就是17
SELECT REGEXP_SUBSTR(‘17,20,23’,’[^,]+’,1,1,’i’) AS STR FROM DUAL;

 17

2).查询使用正则分割后的最后一个值，也就是23
SELECT REGEXP_SUBSTR(‘17,20,23’,’[^,]+’,1,3,’i’) AS STR FROM DUAL;

 23

3).获取一个多个数值的列，从而能够让结果以多行的形式展示出来
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;

 1 2 3 4 5 6 7

4).将上面REGEXP_SUBSTR的occurrence关联
SELECT NVL(REGEXP_SUBSTR(‘17,20,23’, ‘[^,]+’, 1, LEVEL, ‘i’), ‘NULLL’) AS STR FROM DUAL CONNECT BY LEVEL <= 7;

 17 20 23 NULL NULL NULL NULL

5).优化上面的SQL语句，让生成的行的数量符合实际情况
SELECT REGEXP_SUBSTR(‘17,20,23’, ‘[^,]+’, 1, LEVEL, ‘i’) AS STR FROM DUAL CONNECT BY LEVEL <= LENGTH(‘17,20,23’) - LENGTH(REGEXP_REPLACE(‘17,20,23’, ‘,’, ”))+1;

 17 20 23              

5、REGEXP_COUNT (String, pattern [, position [, match_param]])
作用：函数统计字符串出现的次数
详解：
参数：String - 需要进行正则处理的字符串
pattern - 进行匹配的正则表达式
position - 开始匹配的位置
match_option - 匹配模式
‘c’ 说明在进行匹配时区分大小写（默认选项）
‘i’ 说明在进行匹配时不区分大小写

注意：
例子：
出处：
模拟：

–##树形查询
–树形查询语句
Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition

准备数据
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(256),
UPPERDEPID number(10)
);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, ‘总经办’, null);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, ‘开发部’, 0);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, ‘测试部’, 0);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, ‘Sever开发部’, 1);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, ‘Client开发部’, 1);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, ‘TA测试部’, 2);
INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, ‘项目测试部’, 2);
commit;
SQL> select * from dep;
DEPID DEPNAME UPPERDEPID

---

      0 总经办                            1 开发部                     0      2 测试部                     0      3 Sever开发部                1      4 Client开发部               1      5 TA测试部                   2      6 项目测试部                 2

查询数据
SQL> SELECT RPAD( ’ ‘, 2*(LEVEL-1), ‘-’ ) || DEPNAME “DEPNAME”,
2 CONNECT_BY_ROOT DEPNAME “ROOT”,
3 CONNECT_BY_ISLEAF “ISLEAF”,
4 LEVEL ,
5 SYS_CONNECT_BY_PATH(DEPNAME, ‘/’) “PATH”
6 FROM DEP
7 START WITH UPPERDEPID IS NULL
8 CONNECT BY PRIOR DEPID = UPPERDEPID;

DEPNAME ROOT ISLEAF LEVEL PATH

---

总经办 总经办 0 1 /总经办
-开发部 总经办 0 2 /总经办/开发部
—Sever开发部 总经办 1 3 /总经办/开发部/Sever开发部
—Client开发部 总经办 1 3 /总经办/开发部/Client开发部
-测试部 总经办 0 2 /总经办/测试部
—TA测试部 总经办 1 3 /总经办/测试部/TA测试部
—项目测试部 总经办 1 3 /总经办/测试部/项目测试部

说明：
1、CONNECT_BY_ROOT返回当前节点的最顶端节点
2、CONNECT_BY_ISLEAF判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3、LEVEL伪列表示节点深度
4、SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

connect by是结构化查询中用到的，其基本语法是：
select … from tablename
start with cond1
connect by cond2
where cond3
简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段(如emp表中的empno和mgr字段):empno, mgr那么通过表示每一条记录的mgr是谁，就可以形成一个树状结构。

其中：
cond1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
cond2是连接条件，其中用prior表示上一条记录，比如connect by prior id=praentid就是说上一条记录的id是本条记录的praentid，即本记录的父亲是上一条记录。
cond3是过滤条件，用于对返回的所有记录进行过滤。根据“connect by [条件2] start with [条件3]”择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构；
prior和start with关键字是可选项
prior运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，prior运算符所在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。
在连接关系中，除了可以使用列名外，还允许使用列表达式。
prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。
要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。
start with子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
SQL> –向下递归遍历
SQL> select * from emp connect by mgr= prior empno start with empno=7698;

 EMPNO ENAME      JOB               MGR HIREDATE           SAL       COMM     DEPTNO DNAME

---

  7698 BLAKE      MANAGER          7839 1981/5/1          2850                    30 noname  7499 ALLEN      SALESMAN         7698 1981/2/20         1600        300         30 noname  7521 WARD       SALESMAN         7698 1981/2/22         1250        500         30 noname  7654 MARTIN     SALESMAN         7698 1981/9/28         1250       1400         30 noname  7844 TURNER     SALESMAN         7698 1981/9/8          1500          0         30 noname  7900 JAMES      CLERK  

SQL> –向上递归遍历
SQL> select * from emp connect by prior mgr=empno start with empno=7698;

 EMPNO ENAME      JOB               MGR HIREDATE           SAL       COMM     DEPTNO DNAME

---

  7698 BLAKE      MANAGER          7839 1981/5/1          2850                    30 noname  7839 KING       PRESIDENT             1981/11/17        5000                    10 noname

–##行列转换
http://blog.csdn.net/tianlesoftware/article/details/4704858
行列转换包括以下六种情况：
1. 列转行
2. 行转列
3. 多列转换成字符串
4. 多行转换成字符串
5. 字符串转换成多列
6. 字符串转换成多行

–##其他函数
–user
返回登录的用户名称
select user from dual;

–listagg 函数
Oracle行专列函数Listagg()
with temp as(
select ‘China’ nation ,’Guangzhou’ city from dual union all
select ‘China’ nation ,’Shanghai’ city from dual union all
select ‘China’ nation ,’Beijing’ city from dual union all
select ‘USA’ nation ,’New York’ city from dual union all
select ‘USA’ nation ,’Bostom’ city from dual union all
select ‘Japan’ nation ,’Tokyo’ city from dual
)
select nation,listagg(city,’,’) within GROUP (order by city)
from temp
group by nation
这是最基础的用法：

LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。非常方便。

同样是聚合函数，还有一个高级用法：

就是over(partition by XXX)

也就是说，在你不实用Group by语句时候，也可以使用LISTAGG函数：

Sql代码
with temp as(
select 500 population, ‘China’ nation ,’Guangzhou’ city from dual union all
select 1500 population, ‘China’ nation ,’Shanghai’ city from dual union all
select 500 population, ‘China’ nation ,’Beijing’ city from dual union all
select 1000 population, ‘USA’ nation ,’New York’ city from dual union all
select 500 population, ‘USA’ nation ,’Bostom’ city from dual union all
select 500 population, ‘Japan’ nation ,’Tokyo’ city from dual
)
select population,
nation,
city,
listagg(city,’,’) within GROUP (order by city) over (partition by nation) rank
from temp

总结：LISTAGG()把它当作SUM()函数来使用就可以了。