ORACLE数据库-函数

函数

单行函数：

从表中查询的每一行只返回一个值，可出现在select子句，where子句中

SELECT upper('smith') FROM DUALSELECT lower('HELLO WORLD') FROM DUALSELECT INITCAP('hello world') FROM DUAL--单词首字母大写/*LPAD *//*LTRIM */UPDATE PVDTA.F4095 SET MLMCU=LPAD(TRIM(MLMCU),12,' ') 

日期函数

日期函数
数字函数

SELECT ROUND(34.6) FROM DUAL--四舍五入SELECT TRUNC(34.6) FROM DUAL--截断小数

字符函数

SELECT SUBSTR('abcdefg',0,3) from dual/*从第0位开始，截取3位*/select REPLACE('abcdefghicde','cde','xxx') from DUALSELECT MLMCU,LENGTH(MLMCU) FROM F4095--获取字段的长度SELECT LTRIM(' ADD ')FROM DUAL--左去空格SELECT RTRIM(' ADD ')FROM DUAL--右去空格SELECT TRIM(' ADD ')FROM DUAL--左右去空格SELECT LpaD('55',5,'0') from dual;--左补select rpad('55',5,'0') FROM DUAL;--右补/*第三个参数缺省时默认为是补充空格*/select lpad('55',5) FROM DUAL

转换函数：

ToChar(),ToDate(),ToNumber()

其他函数:

Nvl(exp1,exp2):表达式一为null时，返回表达式二
Nvl2(exp1,exp2,exp3):表达式一为null时返回表达式三，否则返回表达式二
Nullif(exp1,exp2):两表达式相等时，返回null，否则返回表达式一

分组函数：基于一组行来返回

Avg,Min,Max,Sum,Count
Group by,having

分析函数

Row_number,rank,dense_rank
示例：

CREATE TABLE FA (AID NUMBER ,SCHOOL NUMBER,FACILITY NUMBER,QUALITY NUMBER)ALTER TABLE FA ADD PRIMARY KEY (AID)ALTER TABLE FA ADD DES NCHAR(20)SELECT * FROM FA FOR UPDATE/*1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)*/SELECT * FROM (SELECT AID,SCHOOL,DES,FACILITY,QUALITY,QUALITY_EX,ROW_NUMBER() OVER (PARTITION BY SCHOOL ORDER BY FACILITY) RN FROM FA )

SELECT * FROM (SELECT AID,SCHOOL,DES,FACILITY,QUALITY,QUALITY_EX,ROW_NUMBER() OVER (PARTITION BY SCHOOL ORDER BY FACILITY) RN FROM FA ) where RN=1/*对于筛选某些字段重复的记录可用此方法*//*2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)跳跃排序*/SELECT * FROM (SELECT AID,SCHOOL,DES,FACILITY,QUALITY,QUALITY_EX,RANK() OVER (PARTITION BY SCHOOL ORDER BY QUALITY) RN FROM FA )

/*由查询结果可知，相同的并列，下一个则跳跃到并列所替的序列后：如有两个并列2，那么下一个则直接排为4，跳过3；*/

/*3.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)跳跃排序*/SELECT * FROM (SELECT AID,SCHOOL,DES,FACILITY,QUALITY,QUALITY_EX,DENSE_RANK() OVER (PARTITION BY SCHOOL ORDER BY QUALITY) RN FROM FA )

--------由查询结果可知，当两个并列为2时，下一个仍连续有序为3，不跳跃到4