Oracle SQL 函数详解-优快云博客

本文详细介绍了 Oracle SQL 中常用的函数，包括日期处理、数值运算、字符串操作等，并提供了实际应用场景示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

(转载：http://www.blogjava.net/decode360/archive/2008/11/29/286840.html）

一、to_char函数
Parameter      Explanation
YEAR           Year spelled out
YYYY           4 digits of year
YYY            3 digits of year
YY             2 digits of year
Y              1 digit of year
IYYY           4digits year based on the ISO standard

IYY            3 digits of ISO year
IY             2 digits of ISO year
I              1 digit of ISO year
Q              Quarter of year (1 .. 4)
MM             Month (01 ..12)
MON            Abbreviated name of month
MONTH          Name of month, padded with blanks to length of 9 characters.
RM             Roman numeral month (I .. XII)
WW             Week of year (1-53) where 7 days 1 week (与星期几无关)
W              Week of month (1-5) where 7 days 1 week (与星期几无关)
IW             Week of year (1-52 or 1-53) based on the ISO standard. (周一到周日为一周，若1日为周五-周日，则为上年最后一周)
D              Day of week (周日1 .. 周六7)

DY             Abbreviated name of day.
DAY            Name of day
DD             Day of month (1-31)

DDTH           Day of month (1-31)
DDD            Day of year (1-366)
J              Julian day；the number of days since January 1, 4712 BC.
HH             Hour of day (1-12).
HH12           Hour of day (1-12).
HH24           Hour of day (0-23).
MI             Minute (0-59).
SS             Second (0-59).
SSSSS          Seconds past midnight (0-86399).
FF             Fractional seconds.

XXXXX 转换为8进制

to_char(1210.73, '9999.9')       would return '1210.7'
to_char(1210.73, '9,999.99')    would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73' --($:放置一个$符; L:放置一个浮动本地货币符)
to_char(21, '000099')          would return '000021'

to_char(21, '999999') would return ' 21'

to_char(21, 'FM999999') would return '21'

to_char(sysdate, 'FMYYY') would return '8' --FM表示去掉0或空格 (当前为2008年)

to_char(125, 'XXXXX') would return '7D'

to_number('7D','XXXXX') would return '125'

另注：trunc与to_char的比较

trunc原意为截去数据小数部分，例如：

trunc(23.48429387) 返回23

trunc(23.48429387,3) 返回23.484

trunc(-1.443432) 返回-1

但trunc(date) 具有与to_char(date) 相似的功能，但有区别：

trunc(sysdate,'cc') 取当世纪的第一天　　to_char(sysdate,'cc') 取当世纪数值

trunc(sysdate,'yyyy') 取当年的第一天 to_char(sysdate,'yyyy') 取当年数值

trunc(sysdate,'iyyy') 取上年的最后一天　　to_char(sysdate,'iyyy') 取当年数值

trunc(sysdate,'q') 取当季第一天　 to_char(sysdate,'iyyy') 取当季数值

trunc(sysdate,'mm') 取当月第一天 to_char(sysdate,'mm') 取当月数值

trunc(sysdate,'ww') 取当周第一天(周二)　 to_char(sysdate,'ww') 取当周数值(第几周)

trunc(sysdate,'iw') 取当周第一天(周一)　 to_char(sysdate,'iw') 取当周数值(第几周)

总结：trunc对日期的截取由后面参数决定位置后将之后所有数值为默认初始值！

二、order by函数

--自动将结果列表按字段顺序对应排序

order by1,2,3

--可对字段decode后再排序，下例为将2222、1111排在前两位，其他按顺序排列

select a,b,c from t1

order by decode(a,'2222',1,'1111',2,a)

--如遇到空值时，order by默认将空值排在最下面，如要排在最上面，则：

order by nulls first

三、取整类函数整理

ceil：取整(大)

select ceil (-1.001) value from dual /-1

floor：取整（小）

select floor(-1.001) value from dual /-2

trunc：取整（截取）

select trunc(-1.001) value from dual /-1

round：取整(舍入)

select round(-1.001) value from dual /-1

应用举例：(根据时间算年龄)

trunc(months_between(sysdate,birthday)/12) Age

四、LPAD与RPAD的用法：

比较：select LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') from dual;

|WhaT| WhaT is tHis| -------------WhaT is tHis

select RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') from dual;

|WhaT| WhaT is tHis | WhaT is tHis-------------

作用：作为调整格式的输出，例：

with x as

(select'aa' chr from dual

union all

select'bb' chr from dual)

select level,chr,lpad(' ',(level-1)*5,'-')||chr other from x connectbylevel<=3

说明：若LPAD对空字符串操作无效，因此至少必须有' '空格符!

LPAD的实际应用：

select distinct lpad(selltype,2,'0') from lccont;

由于系统中其他的selltype字段均为01、02等2位，但出现7，另有null值

所以使用 lpad(selltype,2,'0') 可以即保存null值又将7更新为07

五、rank() order by()和row_number() order by()的区别：

with t as (

select1 afrom dual

unionall

select2 afrom dual

unionall

select1 afrom dual

)

select a,rank() over(orderby a) rank,row_number() over(orderby a) num from t;

六、translate和replace的区别：

select translate('What is this','ait','-*%') from dual;---Wh-% *s %h*s

select replace('What is this','ait','-*%') from dual;-----What is this

select replace('What is this','hat','-*%') from dual;-----W-*% is this

translate的实际应用：

select translate('12XXX5869XXXX','0123456789'||'12XXX5869XXXX','0123456789')from dual;

<取字符串中的所有数字>

七、sysdate与current_date的差别：

select sysdate,current_date from dual;

某些情况下current_date会比sysdate快一秒。

我们认为current_date是将current_timestamp中毫秒四舍五入后的返回

虽然没有找到文档支持，但是想来应该八九不离十。

八、一些有用的时间函数：

select NEXT_DAY(sysdate,5) from dual;--下一个星期四(不算今天)

select NEXT_DAY(sysdate,'星期三') from dual;--下一个星期一(大小写都可)

select LAST_DAY(sysdate) from dual;--当月最后一天

九、一些有用的数字/字符函数：

select GREATEST(a,b) Greatest from t2;----------求最大值

select LEAST(a,b) LEAST from t2;-----------------求最小值

select NULLIF('a','b'),NULLIF('a','a') from dual;-----a=b则返回null；a<>b则返回a

select nvl(null,'a'),nvl('1','a') from dual;------------为null时返回a，不会null返回原值

select nvl2(null,'a','b'),nvl2('1','a','b') from dual;---为null时返回b，不为null返回a

selectCOALESCE(null,5,6,null,9) from dual;--------返回第一个非空值

select POWER(2.2,2.2) from dual; ----------------a的b次方

十、一些有用的字符串操作函数：

select CHR(95) from dual;----------------------------------------ASCII码对应字符

select ASCII('_') from dual;-----------------------------------字符对应ASCII码

select concat('aa','bb') from dual;------------------------------等同于||

select INITCAP('whaT is this') from dual;--------------------------首字母大写，其余小写

select TO_MULTI_BYTE('ABC abc　中华') from dual;-----------半角变全角

select TO_SINGLE_BYTE('ＡＢＣ　ａｂｃ中华') from dual;-------全角变半角

select VSIZE('abc中华') from dual;---------------------------返回字节数

select INSTR('CORPORATE FLOOR','OR',3,2) from dual;---------从第3位开始查找第2个'OR'

十一、WMSYS.WM_CONCAT函数应用：

此函数作用在于将某字段所有值列出到一个单元格中

selectreplace(WMSYS.WM_CONCAT(num),',',' ') from t1;

行列转换中最简单的一种方法。

十二、单元格内文本换行的方法：

Tab键 chr(9)

换行符 chr(10)

回车符 chr(13)

空格符 chr(32)

select'a'||chr(9)||'b'from dual;

select'a'||chr(13)||'b'from dual;

注：须在SQLPlus中查看结果，PL/SQL Developer中无法显示换行

十三、DECODE

DECODE函数相当于一条件语句,语法如下：
  DECODE(input_value, value, result [, value, result...] [, default_value]);
    input_value: 试图处理的数值;
    value: 与input_value是否匹配,匹配的话则返回result;不匹配的话,若定义了default_value,则返回default_value;没有定义default_value,
           返回空值,与null对应
e.g.
SELETE checkup_type, DECODE(blood_test_flag, 'Y', 'Yes', 'N', 'No', NULL, 'None', 'Invalid') FROM checkup;

十四、NVL()

从两个表达式中返回一个非NULL的值.语法如下:
  NVL(exp1, exp2)
    如果exp1结果为NULL,则返回exp2; exp1计算结果不为NULL,返回exp1;
    如果exp1结果为NULL,且exp2为NULL,则返回NULL.