oracle中常用函数大全
1、数值型函数
|
函数 |
说明 |
样例 |
显示 |
|
ceil(n) |
大于或等于数值n的最小整数 |
select ceil(10.6) from dual; |
11 |
|
floor(n) |
小于等于数值n的最大整数 |
select ceil(10.6) from dual; |
10 |
|
mod(m,n) |
m除以n的余数,若n=0,则返回m |
select mod(7,5) from dual; |
2 |
|
power(m,n) |
m的n次方 |
select power(3,2) from dual; |
9 |
|
round(n,m) |
将n四舍五入,保留小数点后m位 |
select round(1234.5678,2) from dual; |
1234.57 |
|
sign(n) |
若n=0,则返回0,若n>0,则返回1,若n<0则返回-1 |
select sign(12) from dual; |
1 |
|
sqrt(n) |
n的平方根 |
select sqrt(25) from dual ; |
5 |
|
to_number(x,[,fmt]) |
把一个字符串以fmt格式转换为一个数字 |
select to_number('88877') from dual; SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual; |
88877 |
|
abs(x) |
X的绝对值 |
ABS(-3)=3 |
|
|
acos(x) |
X的反余弦 |
ACOS(1)=0 |
|
|
cos(x) |
余弦 |
COS(1)=0.54030230586814 |
|
|
log(x,y) |
X为底Y的对数 |
LOG(2，4)=2 |
|
|
mod(x,y) |
X除以Y的余数 |
MOD(8，3)=2 |
|
|
TRUNC(X[,Y]) |
X在第Y位截断，直接截取，不四舍五入。 |
TRUNC(3.456，2)=3.45 |
|
2、字符函数
|
函数 |
说明 |
样例 |
显示 |
|
initcap(char) |
把每个字符串的第一个字符换成大写 |
select initicap('mr.ecop') from dual; |
Mr.Ecop |
|
upper(char) |
把字符串换成大写 |
select Upper ('ddd') from dual; |
|
|
lower(char) |
整个字符串换成小写 |
select lower('MR.ecop') from dual; |
mr.ecop |
|
replace(char,str1,str2) |
字符串中所有str1换成str2 |
select replace('Scott','s','Boy') from dual; |
Boycott |
|
substr(char,m,n) |
取出从m字符开始的n个字符的子串 |
select substr('ABCDEF',2,2) from dual; |
CD |
|
length(char) |
求字符串的长度 |
select length('ACD') from dual; |
3 |
|
|| |
并置运算符 |
select 'ABCD'||'EFGH' from dual; |
ABCDEFGH |
|
ascii(x) |
返回字符X的ASCII码 |
|
|
|
concat(x,y) |
连接字符串X和Y |
|
|
|
instr(x,str[,start][,n) |
从X中查找str，可以指定从start开始，也可以指定str出现的次数从n开始查找 |
|
|
|
length(x) |
返回X的长度 |
|
|
|
ltrim(x[,trim_str]) |
把X的左边截去trim_str字符串，缺省截去空格 |
|
|
|
rtrim(x[,trim_str]) |
把X的右边截去trim_str字符串，缺省截去空格 |
|
|
|
trim([trim_str from]x) |
把X的两边截去trim_str字符串，缺省截去空格 |
|
|
|
replace(x,old,new) |
在X中查找old，并替换成new |
|
|
|
substr(x,start[,length]) |
返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾 |
|
|
|
to_char(n[,f]) |
将数字n转换为字符串,f为可选的格式化参数， 格式化字符有： 9指定位置处显示数字 9.9指定位置返回小数点 99,99指定位置返回一个逗号 $999数字开头返回一个美元符号 9.99EEEE科学计数法表示 L999数字前加一个本地货币符号 999PR 如果数字式负数则用尖括号进行表示 |
select to_char(88877) from dual; SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;
|
'88877 |
|
lpad(char1,n [,char2]) |
返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。 |
示例 SELECT LPAD(ename,15,'*') FROM emp; |
|
|
rpad(char1, n [,char2]) |
返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。 |
示例 SELECT RPAD(ename,15,'*') FROM emp; |
|
|
translate (string, if, then) |
“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。 |
SELECT TRANSLATE(ename,'AEIOU', 'XXXXX') FROM emp; |
|
|
to_date(x,[,fmt]) |
把一个字符串以fmt格式转换成一个日期类型 |
|
|
3、日期型函数
|
函数 |
说明 |
样例 |
显示 |
|
sysdate |
当前日期和时间 |
select sysdate from dual; |
|
|
last_day |
本月最后一天 |
select last_day(sysdate) from dual; |
|
|
add_months(d,n) |
当前日期d后推n个月 |
select add_months(sysdate,2) from dual; |
|
|
months_between(d,n) |
日期d和n相差月数 |
select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual; |
|
|
next_day(d,day) |
d后第一周指定day的日期， day 格式有：'Monday' 星期一，'Tuesday' 星期二，'wednesday' 星期三， 'Thursday' 星期四， 'Friday' 星期五，'Saturday' 星期六 ，'Sunday' 星期日 |
select next_day(sysdate,'Monday') from dual; |
|
|
to_char(sysdate,n) |
将时间转换为字符串，n格式字符，格式有： |
select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dua; |
|
|
1.Y或YY或YYY 年的最后一位，两位，三位 |
select to_char(sysdate,'YYY') from dual; |
|
|
|
2.Q 季度,1-3月为第一季度 |
select to_char(sysdate,'Q') from dual; |
|
|
|
3.MM 月份数 |
select to_char(sysdate,'MM') from dual; |
|
|
|
4.RM 月份的罗马表示 |
select to_char(sysdate,'RM') from dual; |
IV |
|
|
5.month 用9个字符表示的月份名 |
select to_char(sysdate,'month') from dual; |
|
|
|
6.ww 当年第几周 |
select to_char(sysdate,'ww') from dual; |
|
|
|
7.DDD 当年第几天,一月一日为001 ,二月一日032 |
select to_char(sysdate,'DDD') from dual; |
|
|
|
8. DD 当月第几天 |
select to_char(sysdate,'DD') from dual; |
|
|
|
9. D 周内第几天 |
select to_char(sysdate,'D') from dual; 如 sunday |
|
|
|
10. DY 周内第几天缩写 |
select to_char(sysdate,'DY') from dual; 如 sun |
|
|
|
11. hh12 12小时制小时数 |
select to_char(sysdate,'hh12') from dual; |
|
|
|
12. hh24 24小时制小时数 |
select to_char(sysdate,'hh24') from dual; |
|
|
|
13. Mi 分钟数 |
select to_char(sysdate,'Mi') from dual; |
|
|
|
14.ss 秒数 |
select to_char(sysdate,'ss') from dual; |
|
|
|
round(d[,fmt]) |
返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式 模型。默认 fmt 为 DDD，即月中的某一天。 Ø ① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。 Ø ② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。 Ø ③ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。 Ø ④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。 与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。 |
例：SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'), ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;
|
|
|
extract(fmt from d) |
提取日期中的特定部分。 fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。 HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。 |
例：SELECT SYSDATE "date", EXTRACT(YEAR FROM SYSDATE)"year", EXTRACT(MONTH FROM SYSDATE)"month", EXTRACT(DAY FROM SYSDATE)"day", EXTRACT(HOUR FROM SYSTIMESTAMP)"hour", EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute", EXTRACT(SECOND FROM SYSTIMESTAMP)"second" FROM dual; |
|
4、表函数
|
函数 |
说明 |
样例 |
显示 |
|
table( 函数名称() ); |
把返回结果集合的函数返回的结果， 以表的形式， 进行返回。 |
select sysdate from dual; |
|
5.开窗函数
|
函数 |
说明 |
样例 |
显示 |
|
row_number() over() |
用法1： |
|
|
|
wm_concat |
用于列转行，逗号分隔 |
SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as result FROM m_researcher_stock_rel GROUP BY n_sec_code
|
|
6. 其它单行函数
|
函数 |
说明 |
样例 |
显示 |
|
NVL(X,VALUE) |
如果X为空，返回value，否则返回X |
例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元 代码演示：NVL函数 SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000; |
|
|
rownum |
返回行号 |
|
|
|
rowid |
返回在磁盘的地址 |
|
|
|
nvl2(x,value1,value2) |
如果x非空，返回value1，否则返回value2 |
例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元 代码演示：NVL2函数 SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" FROM EMP WHERE SAL<2000; |
|
7.聚合函数
|
函数 |
说明 |
样例 |
显示 |
|
AVG |
平均值 |
|
|
|
sum(x) |
返回结果集中 x 列的总合。 |
|
|
|
MIN |
最小值 |
|
|
|
MAX |
最大值 |
|
|
|
COUNT |
统计个数 |
|
|
8.查询函数
|
函数 |
说明 |
样例 |
显示 |
|
decode |
使用decode判断字符串是否一样 DECODE(value,if 条件1，then 值1，if 条件2，then 值2，...，else 其他值) |
select aac001,decode(aac001,'0000000001','林海燕','0000000002','陈德财','others') as name from ac01 where rownum<=5;
|
|
|
Case |
1.简单case语句 语法: case exp when comexp then returnvalue ... when comexp then returnvalue else returnvalue end
case到end之间相当于一个具体的值，可以做运算，取别名，嵌套case 等等。 只要把case到end当作一个运算结果的表达式就可以了。
|
select cust_last_name, case credit_limit when 100 then 'low' when 5000 then 'high' else 'medium' end from customers;
|
|
|
2.搜索case语句 语法: case when boolean then return value ... when boolean then return value else retur nvalue end
|
select case when id between 1 and 10 then 'low' when id between 20 and 30 then 'mid' when id between 40 and 50 then 'high' else 'unknow' end from product;
|
|
|
|
3. case中嵌套子查询 Case语句中可以使用子查询，但是必须返回一行，不可以是多行. 如：
|
select case (select count(*) as s1 from t1 where a = 1) when (select count(*) as s2 from t1, t2 where t1.a = t2.a and t2.a = 1) then '相等' else '不相等' end from dual; |
|
|
|
4. --简单case和搜索case之间的区别： 1. 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。 2. searched case可以作为比较条件，那么可以使用like、!=、between ..and、<、=、is null、is not null等，比简单case的使用更加广泛，完全可以替代简单case。 --注意事项: 1.case 表达式返回的是一个确定的value，若前面的都不匹配，则返回else中的项. 2.简单case 中的表达式，when 后面的表达式类型应该全部保持一致. 3.所有的then 后面的return_value类型要保持一致. 4.对于简单case 表达式，也就是case 表达式 when…那么when null 总是取不到。也就是case 后面的表达式如果值为null，不会与when null 匹配，只会与else匹配. 5.对于searched case来说，有自动类型转换，只要条件成立就可以。 如:select case when 1='1' then 1 end from dual; 其中1='1'条件成立
|
|
|