Oracle中函数用法，遇见一个学习一个会继续更新

• TO_NUMBER()函数的用法

TO_NUMBER函数()是Oracle中常用的类型转换函数之一，主要是将字符串转换为数值型的格式，与TO_CHAR()函数的作用正好相反。

to_number函数的格式如下：

to_number（varchar2 or char,’format model’）

SQL语句中使用方法：

SQL>select to_number(type) from table;

SQL>select type from table order by to_number(type) desc;

用法陷阱：
有的时候你会发现，使用了TO_NUMBER()函数并且语法正确，但是Oracle却报“invalid number”的错误，而你在一遍又一遍认认真真检查并确定语句无误之后大呼惊奇，以为TO_NUMBER()函数还有什么可能不知道的用法。其实这很可能是你所查询的数据出现了问题，而非SQL。使用TO_NUMBER()函数的时候，一定要确保所转换字段是可转换为数字的，比如type为字符串“123”是可以转换为数字123的，但是字符串“1-2-3”或者是"男人/女人"汉字时不可以。如果你的字段中包含了字符串“1-2-3”或者汉字，而你还直接使用了TO_NUMBER()函数进行操作的话就会报“invalid number”的错。

解决办法：

用REGEXP_SUBSTR()函数进行截取

SQL>select type from table order by to_number(regexp_substr(type,'[0-9]*[0-9]',1)) desc;

• REGEXP_SUBSTR()函数的用法

regexp_substr 为截取方法，通过允许您在字符串中搜索正则表达式模式来扩展功能的功能。它也类似于REGEXP_INSTR，但不是返回子字符串的位置，而是返回子字符串本身。如果您需要匹配字符串的内容，而不需要它在源字符串中的位置，则此功能很有用。该函数返回的字符串为VARCHAR2或，CLOB数据的字符集与相同source_char。

to_number函数的格式如下：

REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__string    ：需要进行正则处理的字符串

__pattern    ：进行匹配的正则表达式

__position   ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier   ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

SQL语句中使用方法：

SQL>select regexp_substr('123,456,7','[0-9]*[0-9]',1) from table;

SQL>select regexp_substr(type,'[0-9]*[0-9]',1) from table;

• DECODE()函数的用法

类似于if,else if,else if,else 判断语句函数

decode函数的格式如下：

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

SQL语句中使用方法：

SQL>select t.id, t.name, t.age, decode(t.sex, '1', '男生', '2', '女生', '其他') as sex from table t

解释：如果sex的值为'1'则显示'男生'，如果set的值为'2'则显示为'女生'，否则显示为'其他'

• Start With ... CONNECT BY PRIOR关键字

在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下:

ID, DSC, PID;

三个字段, 分别表示 当前标识的 ID(主键), DSC 当前标识的描述, PID 其父级ID, 比较典型的例子 是 国家, 省, 市 这种层级结构;

省份归属于国家, 因此 PID 为 国家的 ID, 以此类推;

此时需要用到递归查询来查询层级结构的数据

start with关键字语法如下：

SELECT ... FROM + 表名

WHERE + 条件3

START WITH + 条件1

CONNECT BY PRIOR + 条件2

条件1: 表示从哪个节点开始查找, 也就是通过条件1 查询到的数据, 作为后续查询的起始节点(参数).

当然可以放宽限定条件，如 ID in ('00001', '00011')以取得多个根节点，也就是多棵树；在连接关系中，除了可以使用列明外，还允许使用列表达式。

如果省略Start With

就默认把所有满足查询条件的Tree整个表中的数据从头到尾遍历一次,每一个数据做一次根,然后遍历树中其他节点信息.

条件2: 是连接条件，其中用PRIOR表示上一条记录，例如CONNECT BY PRIOR ID = PID，意思就是上一条记录的ID是本条记录的PID，即本记录的父亲是上一条记录。CONNECT BY子句说明每行数据将是按照层次顺序检索，并规定将表中的数据连入树形结构的关系中。

Prior 在父节点的一侧表示, 自底向上查, 在 子节点的一侧表示 自上向下查询;

条件3: 不能用在 Connect By 后, 这里的条件判断, 等价于 在最后查询出结果列表之后, 再进行条件筛选; 并非 删除掉 节点及子节点;

create table DEMO (
    ID varchar2(10) primary key,
    DSC varchar2(100),
    PID varchar2(10)
)
--插入几条数据

Insert Into DEMO values ('00001', '中国', '-1');
Insert Into DEMO values ('00011', '陕西', '00001');
Insert Into DEMO values ('00012', '贵州', '00001');
Insert Into DEMO values ('00013', '河南', '00001');
Insert Into DEMO values ('00111', '西安', '00011');
Insert Into DEMO values ('00112', '咸阳', '00011');
Insert Into DEMO values ('00113', '延安', '00011');


--自底向上
Select * From DEMO
Start With ID = '00113'
Connect By  Prior PID = ID

--结果
00113   延安  00011
00011   陕西  00001
00001   中国  -1

--自上向下
Select * From DEMO
Start With ID = '00001'
--用 Start Wiht PID = '-1' 结果不变
Connect By  Prior ID = PID

--结果
00001   中国  -1
00011   陕西  00001
00111   西安  00011
00112   咸阳  00011
00113   延安  00011
00012   贵州  00001
00013   河南  00001

--Where 删除
Select ID, PID, DSC
From DEMO
WHERE ID <> '00011'
Start With ID = '00001'
Connect By Prior ID =  PID

--结果
00001   -1      中国
00111   00011   西安
00112   00011   咸阳
00113   00011   延安
00012   00001   贵州
00013   00001   河南

下面是几条关键字特殊点:

nocycle关键字, 有时候数据本身 不合理会导致出现循环的问题, 如 将上述的 ID '00001' 记录的 'PID' 也改为 '00001', 会出现循环的问题, 这是, 需要用到 nocycle 即可消除循环;

Connect By nocycle Prior ID = PID 即可.

connect_by_isleaf 表示当前节点是否是叶子节点

level 表示当前节点所处层级, 这里的层级指的是 从 start with 查询到的节点开始往下算起, 当前属于第几层级

Select ID, PID, DSC,
connect_by_isleaf isLeaf,
LEVEL
From DEMO
Connect By nocycle Prior ID = PID
Start With ID = '00001';

--结果
ID      PID     DSC  isLeaf     LEVEL
00001   00001   中国  0           0
00011   00001   陕西  0           1
00111   00011   西安  1           2
00112   00011   咸阳  1           2
00113   00011   延安  1           2
00012   00001   贵州  1           1
00013   00001   河南  1           1

这里需要注意的一个点, 如果采用的是 自底向上的 方式查询, 则 LEVEL 的 层级 同样是 从底向上, 如 00113 LEVEL 1 00011 LEVEL 2 00001 LEVEL 3.

另外一点: 如果在查询语句中 Select ID, PID, DSC, connect_by_isleaf isLeaf, LEVEL - 1 LEVEL 这种查询方式的话, 在 WHERE 判断条件中, 只需要判断 LEVEL = 1, 就可以取出 当前查询节点的 子节点(由于LEVEL 也是 伪列, 需要用子查询的方式);

SIBLINGS关键字:它会保护层次，并且在每个等级中按expre排序。

Select ID, PID, DSC,
connect_by_isleaf,
LEVEL
From DEMO
Start With ID = '00001'
Connect By nocycle Prior ID =  PID
ORDER By DSC

--结果, 仅贴出部分数据(层级结构被破坏了)
00012   00001   贵州  1   2
00013   00001   河南  1   2
00011   00001   陕西  0   2
00111   00011   西安  1   3
00112   00011   咸阳  1   3
00113   00011   延安  1   3
00001   -1      中国  0   1

--ORDER SIBLINGS  By DSC
Select ID, PID, DSC,
connect_by_isleaf,
LEVEL
From DEMO
Start With ID = '00001'
Connect By nocycle Prior ID =  PID
ORDER SIBLINGS  By DSC

--结果(Level 层级不变)
00001   -1      中国  0   1
00012   00001   贵州  1   2
00013   00001   河南  1   2
00011   00001   陕西  0   2
00111   00011   西安  1   3
00112   00011   咸阳  1   3
00113   00011   延安  1   3

connect_by_iscycle:存在循环，将返回1，否则返回0