一、to_char函数
1、用作日期转换
to_char(date,'格式');
select to_date('2005-01-01 ','yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
2、处理数字:
to_char(number,'格式');
select to_char(1234567890,'999,999,999,999.9999') from dual;
3、to_char(salary,'$99,99');
select TO_CHAR(123,'$99,999.9') from dual;
4、用于进制转换:将10进制转换为16进制;
select to_char(4567,'xxxx') from dual;
二:to_number函数
1、char或varchar2类型的string转换为一个number类型的数值
select to_number('000012134') from dual;
2、可以用来实现进制转换;16进制转换为10进制:
select to_number('19f','xxx') from dual;
select to_number('f','xx') from dual;
三:connect_by函数
1、从根节点查找叶子节点
select t.*, level, CONNECT_BY_ROOT(id)
from tab_test t
start with t.id = 0
connect by prior t.id = t.fid;
2、从叶子节点查找上层节点
--第一种,修改prior关键字位置
select t.*, level, CONNECT_BY_ROOT(id)
from tab_test t
start with t.id = 4
connect by t.id = prior t.fid;
--第二种,prior关键字不动 调换后面的id=fid逻辑关系的顺序
select t.*, level, CONNECT_BY_ROOT(id)
from tab_test t
start with t.id = 4
connect by prior t.fid = t.id;
3、生成数字序列结果集
select rownum from dual connect by rownum<=10;
4、字符串分割,由一行变为多行
- 比如说分割01#02#03#04这种有规律的字符串
select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport
from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');
5、省略prior关键字时数据的返回策略
构造一个结果集,其中包含两条数据;然后查询level为1,2,3层的数据
select t.*, level
from (select 1 as num from dual
union
select 2 as num from dual
) t
connect by level <= 3;
作者:scgyus