Oracle数据库(五)——函数下

日期型函数

系统日期，时间函数

SYSDATE函数。该函数没有参数，可以得到系统日期，下面示例将得到的时间格式化：

SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) FROM DUAL;

SYSTIMESTAMP函数。该函数没有参数，返回系统时间，该时间包含时区信息，精确到微秒。返回类型为带时区的TIMESTAMP类型

得到数据库时区函数： DBTIMEZONE

为日期加上指定月份函数： ADD_MONTHS(date,integer)函数

返回当前会话的时区： SESSIONTIMEZONE函数

返回指定月份最后一天：LAST_DAY(date)

返回指定日期后一周的日期函数：NEXT_DAY(date,char) char表示星期几

返回会话所在时区当前的日期：CURRENT_DATE

提取指定日期特定部分的函数：EXTRACT(datetime)

SELECT 
EXTRACT(YEAR FROM SYSDATE) YEAR,
EXTRACT(MINUTE FROM TIMESTAMP '2010-6-18 12:23:10') MIN,
EXTRACT(SECOND FROM TIMESTAMP '2010-6-18 12:23:10') SEC,
FROM DUAL;

得到两个日期之间的月份数：MONTHS_BETWEEN(date1,date2),函数两个参数都是日期型数据，当date1>date2，如果两个参数表示日期是某月的同一天，或它们都是某月中的最后一天，则该函数返回一整函数，否则返回小数。当date1大于date2时，返回一个负值

时区时间转化函数 NEW_TIME(date,timezone1,timezone2)函数，该函数将返回时间date在时区timezone1转化到时区timezone2

日期四舍五入，截取函数：ROUND(date[,fmt]) 将date舍入到fmt指定的形式，如果fmt被省略，则date将被处理到最近的一天

TRUNC(date[,fmt]) 将date舍入到fmt指定的形式，如果fmt被省略，则date将被处理到最近的一天

select TO_CHAR(ROUND(TO_DATE('2010-5-1 09:00:00','YYYY-MM-DD HH 24:MI:SS'),'YYYY-MM-DD HH 24:MI:SS')) FROM DUAL;
select TO_CHAR(TRUNC(TO_DATE('2010-5-1 09:00:00','YYYY-MM-DD HH 24:MI:SS'),'YYYY-MM-DD HH 24:MI:SS')) FROM DUAL;

转换函数

字符串转ASCII类型字符串 ASCIISTR(char)

二进制转换为十进制 BIN_TO_NUM(data[,data…]) 用，分开位数

数据类型转换 CAST(expr as type_name) 把expr参数转化为type_name类型 基本上用于数字和字符之间和字符与日期类型之间的转换

字符串和ROWID(伪列)相互转换 ROWID格式必须长度为18，每一条记录都由一个rowid，rowid在数据库中唯一，可以利用select查询该字段

把字符串转为ROWID类型:CHARTOROWID(char)

把ROWID类型转换为字符类型 ROWIDTOCHAR(rowid)

把字符集转到另一个字符集 CONVERT(char,dest_char_sest[,source_char_set]),

char 等待转换的字符 dest_char_sest 转变后的字符集 source_char_set 原字符集，如果没有该参数，则默认数据库实例字符集

十六进制字符串转换为RAW类型 HEXTORAW(char)

RAW类型转换为十六进制字符串 RAWTOHEX(raw)

数值到字符串 TO_CHAR(number) TO_CHAR(n[,fmt[,nlsqaram]])

日期类型到字符串 TO_CHAR(date) TO_CHAR(n,[,fmt[,nlsparam]])

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

字符串转数字函数 TO_NUMBER(expr[,fmt[,nlsparam]])

全角转半角函数 TO_SINGLE_BYTE(char)

NULL函数

返回列表第一个不为NULL的表达式，如果都是NULL，则返回一个NULL: COALESCE(expr)

排除指定条件 LNNVL(condition) ,包含NULL的条件

SELECT* FROM PRODUCTINFO WHERE LNNVL(QUANTITY>=70);
--数量低于70的产品，包括为NULL的

替换NULL值函数

NVL(expr1,expr2)函数，替换NULL值，表示如果expr1为NULL值，则返回expr2的值，否则返回expr1的值。该函数要求两个参数类型一致，至少相互间能进行隐式的转换，否则会提示出错

NVL2(expr1,expr2,expr3),expr1为NULL时，返回expr3，当不为空时，则返回expr2的值

集合函数

AVG([distinct|all]expr) 求某组的平均值，返回数值类型

distinct 去重 all 所有值 expr 表达式

select avg(all productprice) from productinfo group by category
--查询各类产品的平均价格

求记录数量函数：

COUNT(*|[distinct][all]expr) 
--查询productinfo表中的productprice字段低于3000的不重复的记录数
SELECT COUNT(DISTINCT PRODUCTPRICE) FROM PRODUCTINFO WHERE PRODUCTPRICE < 3000;

返回最大最小值函数

MAX([distinct|all]expr) --返回指定列的最大值
MIN([distinct|all]expr)

求和函数

SUM([distinct|all]expr) 

其他函数

返回登录名 ： USER

返回会话以及上下文信息函数：

USERENV(parameter)  --返回当前会话信息
SELECT USERENV('ISDBA') FROM DUAL;
SYS_CONTEXT(namespace,parameter) --可以得到Oracle已经创建的context，名为userenv的属性对应值
SELECT SYS_CONTEXT('USERENV','SESSION_USER') SESSION_USER FROM DUAL;--得到当前会话对应的用户名

表达式匹配函数

DECODE(expr,search,result[,search1,result1][,default])
--改函数的执行结果是，当expr符合条件search时就返回result的值，该过程可以重复多个，如果最后没有匹配结果，可以返回默认值default，注意它是一对一的匹配过程
--下面的事例将演示PRODUCTINFO中产品数量大于100显示充足，少于或等于100则显示不足
SELECT PRODUCTNAME,QUANTITY,DECODE(SIGN(QUANTITY-100),1，'充足',0,'不足') FROM PRODUCTINFO