一、简单函数
1.lower():转为小写 select lower('ABCdef') from dual; 2.upper():转大写 select upper('Ttttttt') from dual; 3.initcap(x) 单词首字母转大写 select INITCAP('fanghuafeng') FROM dual; 4.length():字符长度 select length(ename) leng ,ename from scott.emp; 5.lengthb():字节长度，一个汉字占两字节，用于判断是否含有中文 select length('国家'),lengthb('国家') from dual; 6.VSIZE(c) 返回c的字节数。 select length('国家'),lengthb('国家),vsize('国家') from dual; 7.mod(a,b):取模,正负取被除数的符号 select mod(5,-2),mod(5,0),mod(-5,2) from dual;1 8.ROUND(a,n):对a从n位开始四舍五入,默认为取整 select ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1),ROUND(23.56,-2) FROM DUAL; 9.TRUNC(a,n):对a从n位开始截取小数点右边直接截取，左边截取后给0,默认从小数点前截取 select TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; 10.CEIL(n) 返回大于或等于n的最小的整数值 select ceil(3.16),ceil(-3.16) from dual;--4,-3 11.FLOOR(n) 返回小于等于n的最大整数值 select floor(3.76),floor(-3.16) from dual; --3,-4 12.GREATEST(n1,n2,...n) 返回序列中的最大值 select GREATEST(15,5,75,8) "Greatest",greatest('方','华','峰') FROM DUAL; 13.LEAST(n1,n2,n3..n)返回最小值 select LEAST(15,5,75,8) LEAST,least('方','华','峰') FROM DUAL; 14.随机函数dbms_random，返回随机数和随机字符串 小树点后38位长度的随机数，默认为0到1之间，可以指定范围 select dbms_random.value(),dbms_random.value(10,20) FROM dual; 返回指定格式的随机字符串string('parameter',length)，字母：'a'不分大小写'u'大写'l'小写'x'大写加数字'p'所有字符 select dbms_random.string('p',5),dbms_random.string('a',5),dbms_random.string('x',5) from dual; 15.trim函数 select trim(' aaaa ') aaa,ltrim(',aaabcd',',') bb from dual; 16.LPAD(c1,n[,c2]):n为负值为空，给c1左边补c2使得字符串长等于n,默认补空格,有截取子串的功能 select LPAD('WhaT',5),LPAD('WhaT',25),LPAD('WhaT',25,'-'),LPAD('WhaT',-4,'-') FROM DUAL; 17.RPAD(c1,n[,c2]):右补 select RPAD('WhaT',5),RPAD('WhaT',25),RPAD('WhaT',25,'-'),LPAD('WhaT',-4,'-') FROM DUAL; 18.NULLIF(c1,c2):逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END select NULLIF('a','b'),NULLIF('a','a') FROM DUAL; 19.返回第一个不为空的值，如果所有的都为空，则返回NULL。 select COALESCE(null,'','1','a') from dual; 20.CAST(c as newtype) 将指定字串转换为指定类型，基本只对字符类型有效 select cast('1001' as number(5,1)) from dual; --1001 21.DUMP:返回存储信息 select dump('abcd') from dual; --Typ=96 Len=4: 97,98,99,100 22.查询本地语言，会话的SID select userenv('language'),userenv('sid') from dual; 23.user 当前回话的用户 select user from dual; 24.define返回数据库信息，用户均可执行sqlplus命令 define 25.英文字符集下的逆序函数 REVERSE() select reverse('abcd'),reverse('123456') from dual; 二、字符串函数 1.substr(str,n1,l) 将str从n1位置开始，截取l长度的子串 2.instr(str,str1,n1,n2)返回str1在str中从n1开始，第n2次出现的位置，可用于判断字符包含性等同于like 3.replace(str,str1,str2)字符串级别的替换，将str中额str1替换为str2 4.translate(str,str1,str2)字符级别的替换，对应不上的替换为空，str1,str2任何一个为null结果就为null select translate('abcb3add5esa6d','_1234567890','_') from dual;--计算长度判断是否含有指定字符 5.反向输出 中文外逆序系统函数 REVERSE() 中文： create or replace function my_Reverse(s varchar2) return varchar2 is my_Result varchar2(1000); v_s varchar2(1000); begin v_s:=substr(s,1,1); for i in 2..length(s) loop v_s:=substr(s,i,1)||v_s; end loop; my_Result:=v_s; return(my_Result); end; 三、格式转换函数 1.to_char(date,'format'):时间格式化输出显示，to_char(number,'xxxx')10进制转为16进制 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select to_char(15,'xxxx') FROM dual; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; insert into emp_a values(7777,'FHF','IT',7566,'1989-01-23 15:30:20',30,null,null); 2.to_date(string,'format'):将字符串转化为日期型，受客户端影响不一定可以格式化 select to_date ('2014-10-03 9:51:38','yyyy-mm-dd hh:mi:ss') from dual; select to_date ('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual; 3.TO_NUMBER(str,format)明确的字符转数字，16进制转为10进制 select TO_NUMBER('-100.00') FROM DUAL; select to_number('19f','xxxx') FROM dual; 4.nvl(string1,string2):string1为null则替换显示string2 select t.*,nvl(comm,0) new_comm from scott.emp t; 5.nvl2(string1,string2,string3):string1为null则取string3否则取string2 select t.*,nvl2(comm,comm+100,0) new_comm from scott.emp t; 6.DECODE(exp,s1,r1,s2,r2..s,r[,def]) select decode('a2','a1','true1','a2','true2','default') from dual; 7.case函数 case col when value1 then value1_1 when value2 then value2_2 else value3_3 end; 8.bin_to_num 2进制转换为10进制: select bin_to_num(1,0,0,1),bin_to_num(1,1,0,1) from dual; 9.10进制转化为2进制 CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER) RETURN VARCHAR IS V_RTN VARCHAR(8); --注意返回列长度 V_N1 NUMBER; V_N2 NUMBER; BEGIN V_N1 := V_NUM; LOOP V_N2 := MOD(V_N1, 2); V_N1 := ABS(TRUNC(V_N1 / 2)); V_RTN := TO_CHAR(V_N2) || V_RTN; EXIT WHEN V_N1 = 0; END LOOP; --返回二进制长度 SELECT LPAD(V_RTN, 8, 0) INTO V_RTN FROM DUAL; RETURN V_RTN; END; 四、日期函数 1.ADD_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。时间的月份计算，只改变月份，其他时间同当 前年的改变可以通过月份*12运算 select ADD_MONTHS(sysdate,1),ADD_MONTHS(sysdate,-12) FROM DUAL; 2.current_date:当前系统时间，设置会话的日期格式 alter session set nls_date_format='yyyy-mm-dd'; select SYSDATE,CURRENT_DATE FROM DUAL;--CURRENT_DATE比sysdate快1秒 3.LAST_DAY(d) 返回指定时间所在“月”的最后一天,时分秒对应为当前时间 select last_day(SYSDATE) FROM DUAL; select last_day(add_months(sysdate,-1)) FROM DUAL; 4.next_day(d,n):返回指定日期后最近的星期n日期 select next_day(sysdate,1) from dual;--1..7代表周日..周六 select NEXT_DAY(SYSDATE,'星期四') FROM DUAL; 5.MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0,d1-d2 select months_between(sysdate,add_months(sysdate,-3)) from dual; 6.ROUND(d[,fmt]):year按月，month按日，ddd按上下半天，day按周四舍五入，不能为时分秒，判断时间区间 select round(sysdate,'year'),round(sysdate,'month'),round(sysdate,'ddd'),round(sysdate,'day') from dual; 7.trunc(d,format):yyyy,Iy,month,q,WW,day,hh,hh24，找开始时间 select trunc(to_date('20080702 08:05:21','YYYYMMDD HH24:MI:SS'),'YYYY') FROM dual; --当年第一天 select trunc(to_date('20080702 08:05:21','YYYYMMDD HH24:MI:SS'),'IY') FROM dual;--去年年底 select trunc(sysdate,'Q') FROM dual--季度第一天 select trunc(sysdate,'month') FROM dual--月份第一天 select trunc(sysdate,'WW') FROM dual--周的第一天，不是完整的周*********** 8.EXTRACT(fmt FROM d)：提取日期中的特定部分 select sysdate,extract(year from sysdate) year,extract(month from sysdate) months, extract(day from sysdate) day,extract(hour from systimestamp ) hour,extract(minute from systimestamp ) minute, extract(second from systimestamp ) second from dual; select sysdate,extract(year from to_date('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss')) year, extract(month from to_date('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss')) months, extract(day from to_date('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss')) day, extract(hour from to_timestamp('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss') ) hour, extract(minute from to_timestamp('2014-10-11 19:44:35','yyyy-mm-dd hh24:mi:ss') ) minute, extract(second from systimestamp ) second from dual; select to_char(SYSDATE,'yyyy'),to_char(SYSDATE,'mm'),to_char(SYSDATE,'dd') FROM dual; 9.获得两个时间的相差单位时间，获得小时，分钟，秒，毫秒通过时间换算即可； select CEIL (SYSDATE-to_date('2014-10-30 14:20:50','yyyy-mm-dd hh24:mi:ss')) FROM dual;--1天 select SYSDATE+1 FROM dual;--sysdate+n运算单位是天 五、分析聚合函数 1.开窗函数dense_rank(),rank(),row_number() over(partition by col1 order by col2)分类排序必须要有order by字句 select empno,deptno,ename,sal, dense_rank() over(partition by deptno order by sal desc) num from scott.emp; select empno,deptno,ename,sal, rank() over (partition by deptno order by sal desc ) num from scott.emp; select empno,deptno, ename, sal, row_number() over (partition by deptno order by sal desc ) num from scott.emp order by deptno; 2.聚合分析，可以给表中记录均带上聚合值，同时可以显示未分组的列 a.聚合函数 SELECT deptno, AVG(sal) FROM scott.emp GROUP BY deptno ORDER BY 1; b.聚合分析函数 SELECT deptno,ename,job, AVG(sal) OVER(PARTITION BY deptno ) num FROM scott.emp ORDER BY deptno; 3.指定字段排序累计，相同字段值累计值相同 select empno,ename,sal,sum(sal) over(order by sal desc) from emp; select empno,ename,sal,sum(sal) over (order by deptno) from emp; 六、正则表达式 1.REGEXP_LIKE --与LIKE的功能相似 2.REGEXP_INSTR --与INSTR的功能相似 3.REGEXP_SUBSTR --与SUBSTR的功能相似 4.REGEXP_REPLACE --与REPLACE的功能相似七、利用系统函数搭建测试数据
通过一条 SQL快速生成大量的测试数据 create table myTestTable as select rownum as id,to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime, trunc(dbms_random.value(0, 100)) as random_id,dbms_random.string('x', 20) random_string from dual connect by level <= 100000;