一.数值型函数

1.绝对值，取余，正负判断函数

   绝对值：ABS(n),

   取余：MOD(n2,n1),

   正负判断：SIGN(n),

2.指数函数

   平方根：SQRT(n)

   幂函数：POWER(n1,n2)

   对数：LOG(n1,n2)

3.四舍五入截取函数

   四舍五入：ROUND(n,integer)

   取真值，无关四舍五入：TRUNC(n,integer)

二.字符型函数

1.ASCII码与字符转换函数：
   ASCII转换成字符：CHR(n)
   字符转换成ASCII码：ASCII(char)
2.获取字符串长度函数
   LENGTH(char)
   The LENGTH functionsreturn the length of char. LENGTH  calculates length usingcharacters as defined by the input character set.--返回以字符为单位的长度.
   LENGTHB usesbytes instead of characters.--返回以字节为单位的长度.
   LENGTHC usesUnicode complete characters.--返回以Unicode完全字符为单位的长度.
   LENGTH2 usesUCS2 code points.--返回以UCS2代码点为单位的长度.
   LENGTH4 usesUCS4 code points.--返回以UCS4代码点为单位的长度.
3.字符串截取函数
   SUBSTR(char,position,substring_length)
   SUBSTR返回以字符为单位的长度.
   SUBSTRB返回以字节为单位的长度.
   SUBSTRC返回以Unicode完全字符为单位的长度.
   SUBSTR2返回以UCS2代码点为单位的长度.
   SUBSTR4返回以UCS4代码点为单位的长度.
4.字符串连接函数
   CONCAT(char1,char2)
5.字符串搜索函数
   INSTR(string,position,occurrence)
6.字母大小写转换函数
   指定参数全部转换成大写字母：UPPER(char)
   指定参数全部转换成小写字母：LOWER(char)
   所有单词的首字母转化成大写字母：INITCAP(char)
7.带排序参数的字母大小写转换函数
   NLS_INITCAP(char[,nlsparam]) 将指定参数的第一个字母转换成写。

   nlsparam: 

   NLS_SORT = SCHINESE_STROKE_M 按笔画，部首排序

   NLS_SORT = SCHINESE_PINYIN_M 按拼音排序

   NLS_SORT = SCHINESE_RADICAL_M 按照部首，笔划排
8.为指定参数排序函数
   NLSSORT(char[,nlsparam])

9.替换字符串函数
   REPLACE(char,search_string[,replacement_string])
10.字符串填充函数
   RPAD(expr1,n[,expr2])  右方向填充
   LPAD(expr1,n[,expr2])   左方向填充
   注：expr2总是从左到右填充。

11.删除字符串首届指定字符的函数
   TRIM([LEADING|TRAILING|BOTH][trim_character FROM] trim_source)
   leading:删除trim_source的前缀字符
   trailing:删除trim_source的后缀字符
   both:删除trim_source的前后缀字符
   trim_character:删除的指定字段，默认删除空格
   trim_source:被操作的字符串。
例：select trim(leading 't' from 'test') from dual;
    RTRIM(char[,set])
    LTRIM(char[,set])
例：select rtrim('lentimdgsdgfgs','dgfs*') from dual
12.字符集名称和ID互换函数
    NLS_CHARSET_ID(sring)

    NLS_CHARSET_NAME(number)

三、日期型函数 

1.SYSDATE  系统时间函数

    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

2.systimestamp  带时区的系统时间，精确到微秒。

3.dbtimezone 数据库时区

4.ADD_MONTHS(date,integer)为日期加上指定的月份函数。

 Select to_char(add_months(to_date('2015-1-30','YYYY-MM-DD'),'1'),'YYYY-MM-DD');

5.LAST_DAY(date) 返回指定月份最后一天函数

    select last_day(to_char(add_months(sysdate,3))) from dualselect last_day(to_char(add_months(sysdate,3))) from dual;

6.NEST_DAY(date,char)返回指定日期后一周的日期函数

    select sysdate,next_day(sysdate,'星期一') from dual

7.CURRENT_DATE 返回会话所在时区当前日期函数

    select sessiontimezone,to_char(current_date,'YYYY-MM-DD HH24:MI:SS') from dual;

8.extract(datetime) 提取指定日期特定部分的函数

select  extract(year from sysdate) year,

       extract (minute from timestamp ' 2015-05-04 23:15:23 ') min,

       extract (second from timestamp ' 2015-05-12 23:15:23 ') sec 

       from dual;

9.MONTHS_BETWEEN(date1,date2) 得到两个日期之间的份数

select months_between(to_date('2015-05-06', 'YYYY-MM-DD'), to_date('2015-06-06', 'YYYY-MM-DD')) one,

      months_between(to_date('2015-05-31', 'YYYY-MM-DD'), to_date('2015-06-30', 'YYYY-MM-DD')) two,

      months_between(to_date('2015-05-06','YYYY-MM-DD') ,to_date('2015-08-06','YYYY-MM-DD')) three 

from dual

10.NEW_TIME(date,timezone1,timezone2) 时区时间转换函数

11.ROUND(date[,fmt]) 日期四舍五入

    TRUNC(date[,fmt]) 日期截取函数

四、转换函数

1.ASCIISTR(char) 字符串转ASCII类型字符串函数

2.BIN_TO_NUM(data[,data...]) 二进制转十进制函数

3.CAST(expr as type_name) 数据类型转换函数

4.字符串和rowid互相转换函数

    chartorowid(char)字符串转rowid

    rowidtochar(rowid)rowid转字符串

    rowidtonchar(rowid)rowid转成字符串，返回类型为nvarchar2。

5.convert(char,转变后的字符,转变前的字符)

6.十六进制字符串与RAW类型相互转换函数

    hextoraw(char) 十六进制字符串转换成raw类型的数据

    rawtohex(raw) raw类型字符转换成十六进制字符串，返回varchar2类型

    rawtonhex(raw)raw类型字符转换成十六进制字符串，返回nvarchar2类型

7.数值转换成字符型函数

    to_char(number[,fmt[,nlsparam]]) 数值转换成字符

    to_char(date[,fmt[,nlsparam]]) 日期转换成字符

    例： select to_char(44,'99.9') from dual;

            select to_char(sysdate,'YYYY-MM-DD') ,

               to_char(sysdate,'HH:MI:ss') ,

               to_char(sysdate,'month','nls_date_language=English')  

           from dual;

8.to_date(char[,fmt[,nlsparam]]) 字符转日期型函数

   例：select to_char(to_date('2015-05-06','YYYY-MM-DD'),'month') from dual;

9.to_number(expr[,fmt[,nlsparam]]) 字符串转数字函数

10.to_single_byte(char) 将函数从全角转成半角。

五、NULL函数

1.coalesce(expr) 返回列表中第一个不为null的函数

    例：select coalesce(null,9-7,null,9);

2.lnnvl(condition)排除指定条件函数

    例：select * from table_name where lnnvl(condition [as column_name>10]);

3.nvl(expr1,expr2) 表示如果expr1为null值时，则返回expr2的值，否则返回expr1的值。

    nvl2(expr1,expr2,expr3) 同nvl类似，不同的是当expr1为null时，函数返回expr3的值，当expr1不为空时，返回expr2的值。

六、集合函数

1.avg([distinct|all]expr) 平均数函数

    dustinct:去除重复的值

    all:表示所有的值，包括重复的值，也是默认值。

    expr:表达式。只能是数值类型。

2.count(*|[distinct][all]expr) 该函数用来计算记录的数量或某列的个数。

3.max([distinct|all]expr) 返回最大值函数。

    min([distinct|all]expr) 返回最小值函数。

4.sum([distinct|all]expr) 求和函数

七、其他函数

1.user 返回登录名函数

例：select user from dual;

2.userenv(parameter) 返回当前会话的信息。

parameter:参数。（当参数为Language时返回语言，字符集;sessionid返回会话id;isdba返回当前用户是否dba）

例：select userenv('isdba') from dual;

3.sys_context(namespace,parameter) 上下文信息函数

例：select
 SYS_CONTEXT('USERENV','TERMINAL') terminal,
 SYS_CONTEXT('USERENV','LANGUAGE') language,
 SYS_CONTEXT('USERENV','SESSIONID') sessionid,
 SYS_CONTEXT('USERENV','INSTANCE') instance,
 SYS_CONTEXT('USERENV','ENTRYID') entryid,
 SYS_CONTEXT('USERENV','ISDBA') isdba,
 SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
 SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
 SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
 SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
 SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
 SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
 SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
 SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
 SYS_CONTEXT('USERENV','SESSION_USER') session_user,
 SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
 SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
 SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
 SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
 SYS_CONTEXT('USERENV','DB_NAME') db_name,
 SYS_CONTEXT('USERENV','HOST') host,
 SYS_CONTEXT('USERENV','OS_USER') os_user,
 SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
 SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
 SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
 SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
 SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
 SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')  authentication_type,
 SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
 from dual;

4.decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 表达式匹配函数

例：比如我要查询某班男生和女生的数量分别是多少?

通常我们这么写:

select count(*) from 表 where 性别 ＝ 男；

select count(*) from 表 where 性别 ＝ 女；

要想显示到一起还要union一下，太麻烦了

用decode呢，只需要一句话

select decode(性别，男，1，0），decode(性别，女，1，0） from 表

使用方法：

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

2、此函数用在SQL语句中，功能介绍如下：

Decode函数与一系列嵌套的 IF-THEN-ELSE语句相似。base_exp与compare1,compare2等等依次进行比较。如果base_exp和 第i 个compare项匹配，就返回第i 个对应的value 。如果base_exp与任何的compare值都不匹配，则返回default。每个compare值顺次求值，如果发现一个匹配，则剩下的compare值（如果还有的话）就都不再求值。一个为NULL的base_exp被认为和NULL compare值等价。如果需要的话，每一个compare值都被转换成和第一个compare 值相同的数据类型，这个数据类型也是返回值的类型。

Decode函数在实际开发中非常的有用

结合Lpad函数，如何使主键的值自动加1并在前面补0

select LPAD(decode(count(记录编号),0,1,max(to_number(记录编号)+1)),14,'0') 记录编号 from tetdmis

eg:

select decode(dir,1,0,1) from a1_interval

dir 的值是1变为0，是0则变为1

比如我要查询某班男生和女生的数量分别是多少?

通常我们这么写:

select count(*) from 表 where 性别 ＝ 男；

select count(*) from 表 where 性别 ＝ 女；

要想显示到一起还要union一下，太麻烦了

用decode呢，只需要一句话

select decode(性别，男，1，0），decode(性别，女，1，0） from 表

3，order by对字符列进行特定的排序

大家还可以在Order by中使用Decode。

例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用Decode完成要求了。

select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)