【Oracle学习笔记】单行函数

写在前面：

单行函数的概念：

单行函数：接受一个数据，输出一个数据；
多行函数：聚合函数，分组函数。接受一组数据，输出一个数据。

常见字符函数

大小写相关： LOWER , UPPER , INITCAP
其他：TRIM , LTRIM , RTRIM , LPAD , PPAD , INSTR , SUBSTR , LENGTH , CONCAT , REPLACE

• LOWER会将所有字符转换为小写
• UPPER会将所有字符转换为大写
• INITCAP会将首字母转换为大写

example：

SELECT lower('Hello'),upper('Hello'),initcap('Hello')
FROM dual;

• TRIM从字符串的首尾去掉特定字符
• LTRIM从字符串的首部去掉特定字符
• RTRIM从字符串的尾部去掉特定字符

example:

SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'),rtrim('HelloHH', 'H')
FROM dual;

• LPAD向左补齐
• PPAD向右补齐

grammar : lpad(数值,多少位,填充内容) rpad(数值,多少位,填充内容)

example :

SELECT lpad(1000,7,'*'),rpad(1000,7,'*')
FROM dual;

• INSTR 查找字符串的位置（注意：在Oracle中，字符串的位置是从1开始的）

grammar : instr(原来的字符串,要查找位置的字符串)

example :

SELECT instr('hello tom','tom')
 FROM dual;

• SUBSTR 求字串，从某个位置到开始截取一定长度

grammar : substr(字符串,从哪个位置开始截取,截取多少个字符[不写则截取到最后一个])

example:

SELECT substr('hello',3,2)
FROM dual;

• LENGTH 求字符串的长度

example :

SELECT length('tom')
FROM dual;

• CONCAT 连接两个字符串

example :

SELECT concat('tom','my')
FROM dual;

• REPLACE替换字符串

example:

SELECT replace('hello','h','xxx')
FROM dual;

常用字符函数例子整合：

SELECT lower('Hello'),upper('Hello'),initcap('Hello')
FROM dual;

SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'),rtrim('HelloHH', 'H')
FROM dual;

SELECT lpad(1000,7,'*'),rpad(1000,7,'*')
FROM dual;

SELECT instr('hello tom','tom')
FROM dual;

SELECT substr('hello',3,2)
FROM dual;

SELECT length('tom')
FROM dual;

SELECT concat('tom','my')
FROM dual;

SELECT replace('hello','h','xxx')
FROM dual;

常见数值函数

• ROUND四舍五入

example :

SELECT round(123.456,2),round(123.456,-2),round(123.45,0),round(123.456)
FROM dual;

round(123.456,2)小数点两位后四舍五入
round(123.456,-2)小数点前两位四舍五入
round(123.45,0)按照小数点四舍五入
round(123.456)与round(123.456,0)相同

• TRUNC去尾

example :

SELECT trunc(12.456,2),trunc(123.456,-2),trunc(123.45,0),trunc(123.456)
FROM dual;

trunc(12.456,2)小数点后保留两位
trunc(123.456,-2)小数点前保留两位
trunc(123.45,0)按照小数点保留
trunc(123.456)与trunc(123.456,0)相同

• MOD求模

example :

SELECT mod(12,5)
FROM dual;

常用数值函数例子整合：

SELECT round(123.456,2),round(123.456,-2),round(123.45,0),round(123.456)
FROM dual;

SELECT trunc(12.456,2),trunc(123.456,-2),trunc(123.45,0),trunc(123.456)
FROM dual;

SELECT mod(12,5)
FROM dual;

常见日期函数

• SYSDATE获取系统当前日期

example :

SELECT sysdate
FROM dual;

• MONTHS_BETWEEN获取两个日期间的相差月数

example : 获取emp表中员工的入职日期到今天相差的月数

SELECT ename,months_between(sysdate,hiredate)
FROM emp;

• ADD_MONTH获取几个月后的日期

exmaple : 获取三个月以后的日期

SELECT add_months(sysdate,3)
FROM dual;

• LAST_DAY获取本月最后一天的日期

example :

SELECT last_day(sysdate)
FROM dual;

• EXTRACT从日期中获取某一部分值

example : 获取当前日期的年份

SELECT extract(YEAR FROM sysdate)
FROM dual;

example : 获取当前日期的月份

SELECT extract(MONTH FROM sysdate)
FROM dual;

example : 获取当前日期的号数

SELECT extract(DAY FROM sysdate)
FROM dual;

常见日期函数例子整合：

SELECT sysdate
FROM dual;

SELECT ename,months_between(sysdate,hiredate)
FROM emp;

SELECT add_months(sysdate,3)
FROM dual;

SELECT last_day(sysdate)
FROM dual;

SELECT extract(YEAR FROM sysdate)
FROM dual;

SELECT extract(MONTH FROM sysdate)
FROM dual;

SELECT extract(DAY FROM sysdate)
FROM dual;

常见转换函数

• TO_CHAR将日期或数值转换为指定格式的字符

example : 将日期转换为指定格式的字符

SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MM:SS')
FROM dual;

example : 将数值转换为指定格式的字符

SELECT to_char(123.45,'L9,999,999.00')
FROM dual;

其中L表示当地字符，00 表示小数点后如果没有两位小数，则用0填充

• TO_DATE将指定格式字符转换为日期

example :

SELECT to_date('1980-01-01','YYYY-MM-DD')
FROM dual;

• TO_NUMBER将指定格式字符串转换为数值

example :

SELECT to_number('$1,222.34','$9,999.99')
FROM dual;

常见转换函数例子整合：

SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MM:SS')
FROM dual;

SELECT to_char(123.45,'L9,999,999.00')
FROM dual;

SELECT to_date('1980-01-01','YYYY-MM-DD')
FROM dual;

SELECT to_number('$1,222.34','$9,999.99')
FROM dual;

其他函数

空值转换函数：

• NVL字段为空，则用相同类型替代

example :

SELECT ename,comm,nvl(comm,0)
FROM emp;

如果字段comm为空，则用与comm数值类型相同的0代替

• NVL2如果字段为空，则返回第二个参数，反之则返回第三个参数

example :

SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;

如果comm字段为空，则用0代替，如果不为空，则用sal代替

• NULLIF两个字段相同，则为空，否则返回第一个参数的值

exmaple :

SELECT nullif('123','123')
FROM dual;

控制转换函数例子整合：

SELECT ename,comm,nvl(comm,0)
FROM emp;

SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;

SELECT nullif('123','123')
FROM dual;

条件分支函数

• CASE

example :

SELECT ename, deptno, sal,
            CASE deptno
            WHEN 10 THEN sal + 100
            WHEN 20 THEN sal + 200
            WHEN 30 THEN sal + 300
            ELSE sal END AS salary
FROM emp;

• DECODE

example : 按照部门号码进行薪资的增加，部门号为10，薪资增加100，部门号为20薪资增加200，部门号为30，薪资增加300，其他则没有变化

SELECT ename, deptno, sal,
        decode(deptno,
                10,sal + 100,
                20,sal + 200,
                30,sal + 300,
                sal) AS salary
    FROM emp;

SELECT ename, deptno, sal,
        CASE 
        WHEN deptno = 10 THEN sal +100
        WHEN deptno = 20 THEN sal +200
        WHEN deptno = 30 THEN sal +300
        ELSE sal END AS salary
FROM emp;

decode是一个函数， case是一个表达式
但是case有更强大的能力

条件分支函数例子整合：

SELECT ename, deptno, sal,
    decode(deptno,
            10,sal + 100,
            20,sal + 200,
            30,sal + 300,
            sal) AS salary
FROM emp;            

SELECT ename, deptno, sal,
        CASE deptno
        WHEN 10 THEN sal + 100
        WHEN 20 THEN sal + 200
        WHEN 30 THEN sal + 300
        ELSE sal END AS salary
FROM emp;

SELECT ename, deptno, sal,
        CASE 
        WHEN deptno = 10 THEN sal +100
        WHEN deptno = 20 THEN sal +200
        WHEN deptno = 30 THEN sal +300
        ELSE sal END AS salary
FROM emp;