Oracle

1、SQL的函数类型

单行函数和多行函数

2、单行函数的函数类型

1.字符函数

lower  —— 小写
upper   —— 大写
initcap  —— 将字符串首每个单词字母变成大写
   函数名(表达式)
concat —— 字符串拼接
    concat(str1,str2)   ——只支持两个参数
    如果字符串过多，拼接推荐用 ||
    select 'hello ' || 'world '|| '1234' || 123 from dual;  --123 做了隐式转换
substr —— 取子串
    substr(str , pos , len)  --在str里边第pos个字符取子串，长度是len，len可以省略
    pos 可以是负数，相当于从字符串后边往前面数
    select substr('hello world',-3 ) from dual;   --rld
instr —— 判断某个字符串在另一个字符串的哪个位置
    instr (str , substr )   --如果找不到，就返回0，找到则返回向相应位置（第几个字符）
lpad,rpad —— 左填充，右填充
    lpad ( str , len , char) -- 往str左边填充char字符到len长度
    select lpad('hello', 10 , '#') from dual;
    select lpad('hello', 3 , '#') from dual;   --如果字符串长度比len长，就截取
trim —— 去除两边空格 
    select trim ( '     Hello world   ') from dual;
    select trim ( 'H' from 'Hello worldH') from dual;   --去除两边的H
replace —— 字符串替换
    replace(str , substr , replaceString) --从str里边找到substr，替换成 replaceString

A、大小写转换函数

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';

B、字符串操作函数

SELECT employee_id, 
    CONCAT(first_name, last_name) NAME,
    job_id, 
    LENGTH (last_name),
    INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';

2.数值操作函数

round   ——  四舍五入
trunc   ——  截取
ceil、floor  —— 向上取整、向下取整
mod   —— % 取模
select  
        round(45.926) 四舍五入,
    round(45.926,1) 四舍五入保留一位小数,
    trunc(45.926) 截取,
    trunc(45.926,1) 截取并保留一位小数,
    ceil(45.926) 向上取整,
    floor(45.926) 向下取整,
    mod(1000,600) 取模
from dual

A、ROUND

SELECT 
  ROUND(45.923,2), 
  ROUND(45.923,0), 
  ROUND(45.923,-1) 
FROM DUAL;

B、TRUNC

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) 
FROM DUAL;

C、MOD

SELECT last_name, salary, MOD(salary, 5000) 
FROM employees
WHERE job_id = 'SA_REP';

3.日期操作函数

sysdate —获取当前系统的时间

sysdate 不仅包含日期，还包含时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

1、显示昨天、今天、明天

日期类型的计算 +1 -1 单位是天

select sysdate -1 昨天 , sysdate 今天 , sysdate +1 明天 from dual;

2、计算员工工龄，按照日、周、月、年显示

日期相减，得到两个日期相隔多少天

select ename ,hiredate,
   sysdate- hiredate "天",
   (sysdate - hiredate) / 7 "周",
   (sysdate - hiredate) / 30 "月",
   (sysdate - hiredate) / 365 "年"
from emp

months_between

months_between(date1 , date2 ) —算出两个时间相差多少个月——精确值

select  ename ,hiredate,
    (sysdate - hiredate) / 30 "月",
     months_between(sysdate,hiredate) "精确月"
from emp

add_months

add_months(date, n) 在date上添加n个月

计算明年今日

select add_months(sysdate,12) "明年今日" from dual;

last_day

last_day(date) —返回date 所在 月的最后一天

select last_day(sysdate) from dual;

next_day

next_day(sysdate,'星期三') — 返回下一个星期几是哪一天

select next_day(sysdate,'星期三') from dual;

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS, sysdate+1 as
tomorrow , hire_date + 8/24
FROM employees
WHERE department_id = 90;

4.类型转换函数

数字转字符串 to_char
    to_char(数字)
  to_char(数字,格式字符串)
将薪水转化为本地货币字符型
    select to_char(sal,'L9999') from emp;    -L 代表本地货币符号 ，9代表一位数
字符串转数字 to_number
    select to_number('1234') from dual;
    select to_number('￥950','L9999') from dual;
用什么格式转到数字类型的，就用什么格式转回去字符串
日期转字符串 to_char
    hiredate 转字符串
    select hiredate ,to_char(hiredate , 'dd-mm-yyyy day')  from emp;
字符串转日期 to_date
    select to_date( '17-12-1980 星期三' , 'dd-mm-yyyy day') from dual;

A、隐式转换规则

Oracle 数据类型的隐私转换规则：
对于赋值操作可以：

对于表达式比较操作仅可以：

B、显式转换规则

TO_CHAR() 函数：日期到字符串的转换

SELECT last_name, TO_CHAR(hire_date, 'fmDD "of" Month YYYY') 
AS HIREDATE
FROM employees;

TO_CHAR() 函数：数字到字符串的转换

SELECT TO_CHAR(salary, 'L99,999.00') SALARY 
FROM employees
WHERE last_name = 'Ernst';

TO_NUMBER() 函数：字符串到数字的转换

TO_DATE() 函数：字符串到日期的转换

TO_DATE() 函数：日期转换时使用RR格式的注意事项

C、函数嵌套

单行函数可以被无限层的嵌套，计算时先计算里层，再计算外层

SELECT last_name, NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;

5.其他常用单行函数

nvl  
   nvl(exp,retval) 如果exp是空，就返回retval，否则返回exp
nvl2
   nvl2(exp , val1 , val2)   如果exp不为空 ，返回val1， 否则返回val2
   查询员工信息，有奖金就显示'有奖金'，没奖金就显示'没奖金'

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees 
WHERE department_id 
IN (50, 80);

SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;

SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;

SELECT last_name, COALESCE(commission_pct, salary, 10) comm
FROM employees 
ORDER BY commission_pct;

3、条件表达式

条件表达式：
实现方法：
CASE 语句或者DECODE函数，两者均可实现 IF-THEN-ELSE 的逻辑，相比较 而言，DECODE 更加简洁。

CASE 语句

CASE expr 
        WHEN comparison_expr1 THEN return_expr1
        [WHEN comparison_expr2 THEN return_expr2
        WHEN comparison_exprn THEN return_exprn
    ELSE else_expr]
END

SELECT last_name, job_id, salary,
    CASE job_id
        WHEN 'IT_PROG' THEN 1.10*salary
        WHEN 'ST_CLERK' THEN 1.15*salary
        WHEN 'SA_REP' THEN 1.20*salary
    ELSE salary
    END "REVISED_SALARY"
FROM employees;

DECODE函数

DECODE(col|expression, search1, result1 [, search2, result2,...,]
[, default])

SELECT last_name, job_id, salary,
 DECODE(job_id, 
 'IT_PROG', 1.10*salary,
 'ST_CLERK', 1.15*salary,
 'SA_REP', 1.20*salary,
 salary) REVISED_SALARY
FROM employees;