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;	Hello
Lower(char)	Select lower(‘FUN’) from dual;	fun
Upper(char)	Select upper(‘sun’) from dual;	SUN
Ltrim(char,set)	Select ltrim( ‘xyzadams’,’xyz’) from dual;	adams
Rtrim(char,set)	Select rtrim(‘xyzadams’,’ams’) from dual;	xyzad
Translate(char, from, to)	SELECT TRANSLATE(‘jacjk’,‘j’ ,‘b’)FROM DUAL;	back
Replace(char,searchstring,[rep string])	Select replace(‘jack and jue’ ,‘j’,‘bl’) fromdual;	black and blue
Instr (char, m, n)	Select instr (‘worldwide’,‘d’) from dual;	5
Substr (char, m, n)	Select substr(‘abcdefg’,3,2) from dual;	cd
Concat (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;	15
Ceil(n)	Select ceil(44.778) from dual;	45
Floor(n)	Select floor(100.2) from dual;	100
Power(m,n)	Select power(4,2) from dual;	16
Mod(m,n)	Select mod(10,3) from dual;	1
Round(m,n)	Select round(100.256,2) from dual;	100.26
Trunc(m,n)	Select trunc(100.256,2) from dual;	100.25
Sqrt(n)	Select sqrt(4) from dual;	2
Sign(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;

---