服务热线:13616026886

技术文档 欢迎使用技术文档,我们为你提供从新手到专业开发者的所有资源,你也可以通过它日益精进

位置:首页 > 技术文档 > 数据库技术 > Oracle技术 > Oracle开发 > 查看文档

带你深入了解oracle中几个相对特殊的函数

1、全角转半角函数 to_single_byte

sql> select to_single_byte('oracle') from dual;

to_single_byte('oracle')
------------------------------
oracle

2、数肿?⑽模???o_char、to_date

sql> select to_char(to_date('12345','j'),'jsp') en from dual;

en
----------------------------------------
twelve thousand three hundred forty-five

不过有限制:一是长度的限制,二是不能转换带小数的

sql> select to_char(to_date('88888882345','j'),'jsp') from dual;

select to_char(to_date('88888882345','j'),'jsp') from dual

ora-01854: julian 日期必须介于 1 和 5373484 之间 

julian date指的是公元前4712年1月1日起经过的天数.

the inner to_char simply converts the number 
(which would probably be a numeric variable in practice) 
to char so some magic can happen ... 

the to_date converts the char using the j (julian day) 
format. (the julian day is the number of days since 
january 1, 4712bc, which is when sql*plus was invented), 

having established the date value, we then convert that 
date back to a julian day. because the to_char in this 
case is used in date context, we can use the j mask to 
duplicate the original value, and append the sp (spell) 
format mask. 'spell" does exactly that - it converts the 
number to words, hence the string value above. 
sp can be used in a number of situations. for example, 
if sysdate is 26-aug-98, then : 

select to_char ( sysdate, 'ddsp') from dual; -- spells 
the day as twenty-six, 
and 
select to_char ( sysdate, 'ddspth') from dual; 
--returns twenty-sixth

3、sys_guid()

oracle数据库中有一个sys_id函数,可以产生guid,但是返回的是32字节的字符串,但是如果用字符串作为主键的话效率将会不高。

扫描关注微信公众号