oracle数据库学习之单行函数

sql函数

单行函数

单行函数:
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值

单行函数的分类

字符函数

大小写控制函数

-- 全部小写
select Lower('Company') from dual;
-- 全部大写
select Upper('Company') from dual;
-- 首字母大写
select initcap('companyinit') from dual;

字符控制函数

select concat('hello ','world'),substr('helloworld',2,4),length('helloworld') from dual;

-- concat 拼接两个字符串
-- substr 截取字符串 oracle脚标默认为1，第二个字符开始，往后到第四个字符截止
-- length 字符串的长度


select instr('helloworld','w')  from dual;

-- instr 后面的字符在第一个字符中首次出现的位置
-- 如果不存在，则返回0

select lpad('12345',10,'*')  from dual;

select rpad('12345',10,'*')  from dual;

select TRIM('H' FROM 'HHHHelloHHHWorldHHH') from dual;

select replace('bbabcdebb','b','m') from dual;

-- lpad 左补齐，使用* 补齐第一个字符到10位长度
-- lpad 右补齐，使用* 补齐第一个字符到10位长度
-- TRIM 在param2中首尾出现的param1移除
-- replace 将param1中的param2，替换为param3

数字函数

select ROUND(45.926, 2),trunc(45.926, 2),mod(1600,300) from dual;

-- ROUND 四舍五入
-- trunc    截取
-- mod     求余数

日期函数

在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
日期不允许做加法运算，无意义
可以用数字除24来向日期中加上或减去天数。

select sysdate from dual;
select sysdate+1 from dual;
select sysdate-1 from dual;
select sysdate-to_date('2020-05-21','yyyy-mm-dd') from dual;
select 58/24 from dual;

select months_between(to_date('2022-05-21', 'yyyy-mm-dd'),
                      to_date('2022-04-21', 'yyyy-mm-dd')),
            ADD_MONTHS(sysdate,1),
            NEXT_DAY(sysdate,'FRIDAY'), 
            last_day(sysdate)         
  from dual;

-- months_between:两个日期之间相差几个月
-- ADD_MONTHS：在原来的日期上加上几个月
-- NEXT_DAY：指定日期的下一个星期 * 对应的日期
-- last_day：本月的最后一天

转换函数

SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
SELECT TO_CHAR(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') FROM dual;

字符函数转数字函数

select to_char(199)from dual;
select to_char(12345678.99,'999,999,99')from dual;
select to_char(12345678.99,'$999,999,99')from dual;
select to_number('$123,456,79','$999,999,99')from dual;

通用函数

-- 将空值转换成一个已知的值：
-- 可以使用的数据类型有日期、字符、数字。
-- 函数的一般形式:
-- NVL(commission_pct,0)
-- NVL(hire_date,'01-JAN-97')
-- NVL(job_id,'No Job Yet')

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

-- NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
SELECT last_name,  salary, commission_pct,
       NVL2(commission_pct, 
            'SAL+COMM', 'SAL') income
FROM   employees WHERE department_id IN (50, 80);

-- NULLIF (expr1, expr2) :  相等返回NULL，不等返回expr1 
SELECT first_name, LENGTH(first_name) "expr1", 
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

使用 COALESCE 函数

COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

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

条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
CASE 表达式
DECODE 函数

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 函数

在需要使用 IF-THEN-ELSE 逻辑时:
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;

SELECT last_name, salary,
       DECODE (TRUNC(salary/2000, 0),
                         0, 0.00,
                         1, 0.09,
                         2, 0.20,
                         3, 0.30,
                         4, 0.40,
                         5, 0.42,
                         6, 0.44,
                            0.45) TAX_RATE
FROM   employees
WHERE  department_id = 80;