Oracle函数语法（二）

一、前言

Oracle 提供一系列用于执行特定操作的函数，SQL 函数带有一个或多个参数并返回一个值

以下是SQL函数的分类：

二、单行函数

单行函数对于从表中查询的每一行只返回一个值，可以出现在 SELECT 子句中和 WHERE 子句中

单行函数可以大致划分为：            日期函数            数字函数            字符函数            转换函数            其他函数

三、日期函数

日期函数对日期值进行运算，并生成日期

数据类型或数值类型的结果 日期函数包括：             ADD_MONTHS – MONTHS_BETWEEN             LAST_DAY             ROUND             NEXT_DAY             TRUNC             EXTRACT

例如：

系统时间向后推一个月

select add_months(sysdate,1) from dual;

系统日期与指定日期间相差的月份

select months_between(sysdate,date'2015-01-01') from dual;

取指定日期的最后一天

select last_day(sysdate) from dual;

将指定时间舍入到指定的单位:yyyy,mm,dd,q

select round(sysdate,'mm') from dual;

紧邻指定日期最近的下一个星期几

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

按单位对指定日期截取：yyyy,mm,dd,d,q 返回指定单位的第一天

select trunc(sysdate,'q') from dual;

截取指定日期中的某部分

EXTRACT

四、字符函数

字符函数接受字符输入并返回字符或数值

函数输入输出Initcap(char)Select initcap(‘hello’) from dual;HelloLower(char)Select lower(‘FUN’) from dual;funUpper(char)Select upper(‘sun’) from dual;SUNLtrim(char,set)Select ltrim( ‘xyzadams’,’xyz’) from dual;adamsRtrim(char,set)Select rtrim(‘xyzadams’,’ams’) from dual;xyzadTranslate(char, from, to)SELECT TRANSLATE(‘jacjk’,‘j’ ,‘b’)FROM DUAL;backReplace(char,searchstring,[rep string])Select replace(‘jack and jue’ ,‘j’,‘bl’) fromdual;black and blueInstr (char, m, n)Select instr (‘worldwide’,‘d’) from dual;5Substr (char, m, n)Select substr(‘abcdefg’,3,2) from dual;cdConcat (expr1, expr2)Select concat (‘Hello’,’ world’) from dual;Hello world

以下是一些其它的字符函数 CHR和ASCII LPAD和RPAD TRIM(char FROM set) LENGTH

例如：

SELECT CHR(67) FROM dual;

SELECT LPAD(‘function’,15,’=’) FROM dual;

SELECT LENGTH('frances') FROM dual;

SELECT TRIM(9 from 9999876789999) FROM dual;

在select中实现 if else

decode(表达式,值1,结果1,[值2,结果2…][,否则] sign(数值表达式)   返回表达式的结果是正、负、零、空值   1 正   -1负   0 零或相等   NULL

五、数字函数

数字函数接受数字输入并返回数值结果

函数输入输出Abs(n)Select abs(-15) from dual;15Ceil(n)Select ceil(44.778) from dual;45Floor(n)Select floor(100.2) from dual;100Power(m,n)Select power(4,2) from dual;16Mod(m,n)Select mod(10,3) from dual;1Round(m,n)Select round(100.256,2) from dual;100.26Trunc(m,n)Select trunc(100.256,2) from dual;100.25Sqrt(n)Select sqrt(4) from dual;2Sign(n)Select sign(-30) from dual;-1 六、转换函数

转换函数将值从一种数据类型转换为另一种数据类型

常用的转换函数 TO_CHAR TO_DATE TO_NUMBER

例如：

SELECT TO_DATE('2005-12-06' ,'yyyy-mm-dd') FROM dual;

SELECT TO_NUMBER('100') FROM dual;

SELECT TO_CHAR(sysdate,'YYYY年MM月DD日 HH24:MI:SS')FROM dual;　

  TO_CHAR

例如： –取指定日期是一周中的第几天(可计算周几)

select to_char(sysdate,'d') from dual;

–取指定日期是一年中的第几天

select to_char(sysdate,'ddd') from dual;

–取指定日期是一年中的第几周

select to_char(sysdate,'ww') from dual;

to_char(148.5,‘999.999’) ’ 148.500’

to_char(1485,‘9,999’) ’ 1,485’

to_char(148.5,‘999D999’) ’ 148.500’

七、分组函数

分组函数基于一组行来返回结果为每一组行返回一个值 例如：

SELECT  AVG(re_level) FROM itemfile WHERE p_category='accessories';

SELECT MAX(max_level) FROM itemfile;

SELECT SUM(itemrate*max_level) FROM itemfile;

SELECT MAX(max_level) FROM itemfile;

SELECT SUM(itemrate*max_level) FROM itemfile;

SELECT COUNT(*) FROM itemfile;

  GROUP BY和HAVING子句

GROUP BY子句    用于将信息划分为更小的组    每一组行返回针对该组的单个结果

HAVING子句    用于指定 GROUP BY 子句检索行的条件

例如：

SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;

SELECT p_category, MAX(itemrate) FROM itemfile 
GROUP BY p_category 
HAVING p_category NOT IN ('accessories');

八、其它函数

以下是几个用来转换空值的函数： NVL NVL2 NULLIF

例如：

SELECT itemdesc, NVL(re_level,0) FROM itemfile;

SELECT itemdesc, NVL2(re_level,re_level,max_level)
FROM itemfile;
SELECT itemdesc, NULLIF(re_level,max_level)
FROM itemfile;