Oracle常用函数

Oracle 常用函数

Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。
函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：
1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，
比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。

常用的单行函数有：

• 字符函数：对字符串操作。
• 数字函数：对数字进行计算，返回一个数字。
• 转换函数：可以将一种数据类型转换为另外一种数据类型。
• 日期函数：对日期和时间进行处理。

2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。

字符函数

函数 说明

ASCII(x) 返回字符X的ASCII码；

CONCAT(x,y) 连接字符串X和Y;

INSTR(x, str [,start] [,n) 在x中查找str，可以指定从start开始，也可以指定从第n次开始。

LENGTH(x) 返回x的长度。

LOWER(x) x转换为小写。

UPPER(x) x转换为大写。

LTRIM(x[,trim_str]) 把x的左边截去trim_str字符串，缺省截去空格。

RTRIM(x[,trim_str]) 把x的右边截去trim_str字符串，缺省截去空格。

TRIM([trim_str FROM] x) 把x的两边截去trim_str字符串，缺省截去空格。

REPLACE(x,old,new) 在x中查找old，并替换为new。

SUBSTR(x,start[,length]) 返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。

数字函数

函数 说明 示例

ABS(x) x绝对值 ABS(-3)=3

ACOS(x) x的反余弦 ACOS(1)=0

COS(x) 余弦 COS(1)=1.57079633

CEIL(x) 大于或等于x的最小值 CEIL(5.4)=6

FLOOR(x) 小于或等于x的最大值 FLOOR(5.8)=5

LOG(x,y) x为底y的对数 LOG(2,4)=2

MOD(x,y) x除以y的余数 MOD(8,3)=2

POWER(x,y) x的y次幂 POWER(2,3)=8

ROUND(x[,y]) x在第y位四舍五入 ROUND(3.456,2)=3.46

SQRT(x) x的平方根 SQRT(4)=2

TRUNC(x[,y]) x在第y位截断 TRUNC(3.456,2)=3.45

ROUND(X,Y)和TRUNC(X,Y)的注意事项:

1. ROUND(X[,Y])，四舍五入。
   在缺省y时，默认y=0；比如：ROUND(3.56)=4。
   y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。
   y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

2. TRUNC(x[,y])，直接截取，不四舍五入。
   在缺省y时，默认y=0；比如：TRUNC (3.56)=3。
   y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。
   y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

日期函数

1.ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
select add_months(sysdate, 12) from dual;

2.LAST_DAY(d)，返回指定日期当月的最后一天。
select LAST_DAY(sysdate) from dual;

3.ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。

1. 如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年
2. 如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
3. 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
4. 如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

SELECT SYSDATE, ROUND(SYSDATE), 
                	ROUND(SYSDATE, 'YEAR'), 
                	ROUND(SYSDATE, 'MONTH'),
                	ROUND(SYSDATE, 'DDD'),
                	ROUND(SYSDATE, 'DAY') FROM DUAL;

4. EXTRACT(fmt FROM d)，提取日期中的特定部分。
   fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。
   其中YEAR、MONTH、DAY可以和DATE类型匹配，也可以与TIMESTAMP类型匹配；
   但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。

SELECT EXTRACT(YEAR FROM SYSDATE),
           EXTRACT(MONTH FROM SYSDATE) ,
           EXTRACT(DAY FROM SYSDATE) ,
           EXTRACT(HOUR FROM SYSTIMESTAMP) ,
           EXTRACT(MINUTE FROM SYSTIMESTAMP) ,
           EXTRACT(SECOND FROM SYSTIMESTAMP)  FROM DUAL;

5. 5.日期转换函数
   • to_date : 将字符串转换成日期
   • to_char : 将日期转换成字符串
     注意:
     yyyy表示四位年份
     mm表示两位月份
     dd表示两位天数
     hh24表示24小时，hh12表示12小时
     mi表示分钟
     ss表示秒钟

SELECT SYSDATE,
			to_char(SYSDATE,'yyyy/mm/dd hh:mi:ss'),
            to_char(SYSDATE, 'yyyy-mm-dd'),
            to_char(SYSDATE, 'yyyy-mm'),
            to_char(SYSDATE, 'yyyy')  
            FROM dual;

 SELECT * FROM t_student; 
 UPDATE t_student SET sbirthday = to_date('1990-01-02','yyyy-mm-dd') WHERE sname = '马云';

其他函数

• nvl(column, value) 如果column为空，返回value，否则返回column。

  `select sid,sname,nvl(sage, 0) from t_student;`
  

• decode: 类似于Java当中 if 、if else、if else if else

  UPDATE t_student SET sgender = '1' WHERE sgender = '男';
  UPDATE t_student SET sgender = '2' WHERE sgender = '女';
  SELECT sid,sname,sgender,
   		//如果sgender的值是1，结果为男。
   		decode(sgender, 1, '男'), 
   		//如果sgender的值是1，结果为男；是2，结果为女。
          decode(sgender, 1, '男', 2, '女'),
          //如果sgender的值是1，结果为男；是2，结果为女;都不是，结果为未知。
          decode(sgender, 1, '男', 2, '女','未知')        
          FROM t_student;
  

---

以上
@Fzxey