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'条件成立 |
9.其他
奇偶数判断
奇数：
数字字段%2=1 或mod(数字字段, 2)=1
偶数：
数字字段%2=0 或mod(数字字段, 2)=0
扫一扫
819
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
