Oracle-单行函数

原文链接： https://www.cnblogs.com/mchina/archive/2012/09/07/2649975.html

 

此为笔记自用，如有侵权请联系我删除。

/*
    以下SQL语句所用表，为Oracle 中 Scott用户下自带的 emp、dept表
*/

--单行函数
--          主要分为以下五类：字符函数、数字函数、日期函数、转换函数、通用函数；

-----------------------------------------------------------------------------------------
-- 一、字符函数
SELECT * FROM DUAL;

SELECT UPPER('Hello') FROM DUAL;

SELECT * FROM EMP WHERE ENAME = '&str';

SELECT * FROM EMP WHERE ENAME = UPPER('&str');

--将所有雇员姓名按照小写字母返回
SELECT LOWER(ENAME) FROM EMP;

--将每个雇员姓名的开头首字母大写
SELECT INITCAP(ENAME) FROM EMP;

--查询每个雇员姓名的长度
SELECT ENAME, LENGTH(ENAME) FROM EMP;

--查出姓名长度正好是5的雇员信息
SELECT ENAME, LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME) = 5;

--使用字母“_”替换掉ename中的所有字母“A”
SELECT REPLACE(ename,'A','_') FROM emp;

SELECT REPLACE(ename,'_','A') FROM emp;

--字符串截取 语法一
SELECT ename,SUBSTR(ename,3) FROM emp; --从左往右，从1开始数，截得345...

--字符串截取 语法二
SELECT ename,SUBSTR(ename,0,4) FROM emp;  --Oracle数据库之中，SUBSTR()函数从0或1开始都是一样的!

SELECT ename,SUBSTR(ename,1,3) FROM emp;  -- SUBSTR()也可以设置为负数，表示由后指定截取的开始点

-----------------------------------------------------------------------------------------
--二、数字函数

/*
  ROUND(数字 | 列 [,保留小数的位数])：四舍五入的操作；
  TRUNC(数字 | 列 [,保留小数的位数])：舍弃指定位置的内容；
  MOD(数字 1，数字2)：取模，取余数；
*/

--round函数
SELECT ROUND(903.323) ,ROUND(-903.323),ROUND(-903.323,1) FROM dual;

--trunc函数
SELECT TRUNC(123.456) FROM dual;

SELECT TRUNC(123.456), TRUNC(123.455,2) FROM dual;

--mod 取模(余数)
SELECT MOD(100,33) FROM dual;

-----------------------------------------------------------------------------------------
--三、日期函数

--使用 sysdate 获取当前日期
SELECT SYSDATE FROM dual;               --2021/2/23 15:44:55

--日期计算  日期+数字=日期    若干台后的日期
SELECT SYSDATE+2,SYSDATE+100 FROM dual;  --1	2021/2/25 15:46:16	2021/6/3 15:46:16

--日期计算  日期-数字=日期    若干天前的日期
SELECT SYSDATE-2,SYSDATE-1000 FROM dual;  ---1	2021/2/21 15:47:36	2018/5/30 15:47:36

--日期计算 日期-日期=数字（必须是大日期—小日期）
SELECT ename,hiredate,SYSDATE-hiredate FROM emp;          --1	SMITH	1980/12/17	14678.6609143519

SELECT ename,hiredate,hiredate-SYSDATE FROM emp;          --1	SMITH	1980/12/17	-14678.662337963

--last_day(日期) : 求出指定日期的最后一天
SELECT last_day(SYSDATE) FROM dual;    --求出本月的最后一天日期

--next_day(日期，星期几) : 求出下一个指定星期 X 的日期
SELECT next_day(SYSDATE,'星期一') FROM dual;   --1	2021/3/1 15:56:47  此为当前日期的下一个星期一的日期

--add_months(日期，几个月) ： 该日期后几个月的日期
SELECT add_months(SYSDATE,10) FROM dual;    --1	2021/12/23 16:06:41  此为十个月后的日期

--months_between(日期1，日期2) ：求出两个日期之差的月份数
SELECT ename,hiredate,TRUNC(months_between(SYSDATE,hiredate)) FROM emp; 

--以上这些日期函数可以避免闰年问题

-----------------------------------------------------------------------------------------
--四、转换函数
/*
  现在已经接触到了Oracle数据库之中的
  三种数据：数字（NUMBER）、字符串（VARCHAR2）、日期（DATE），
  
  转换函数的主要功能是完成这几种数据间的互相转换操作，一共有三种转换函数：

TO_CHAR(字符串 | 列，格式字符串)：将日期或者是数字变为字符串显示；
TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示；
TO_NUMBER(字符串)：将字符串变为数字显示；
*/

--to_char 
SELECT SYSDATE FROM dual;  --1	2021/2/23 16:19:08
SELECT to_char(SYSDATE,'yyyy-mm-dd'),to_char(SYSDATE,'yyyy') YEAR FROM dual; 
                           --1	2021-02-23	2021
--to_date
SELECT SYSDATE, to_date('2022-02-04','yyyy-mm-dd') to_date FROM dual;  --1	2021/2/23 16:29:30	2022/2/4

--to_number
SELECT SYSDATE,to_number('2021') FROM dual; --1	2021/2/23 16:31:18	2021

-----------------------------------------------------------------------------------------
--五、通用函数

--NVL ,处理null
SELECT ename,sal,comm,(sal+comm)*12 FROM emp;   
            --1	SMITH	800.00		  有的人没有comm（津贴），导致算出来年薪也为null

SELECT ename,sal,comm,NVL(comm,0),(sal+NVL(comm,0))*12 SUM FROM emp;  
             --1	SMITH	800.00		0	9600    comm为null的被转换成0了

--DECODE ： 多数值判断                                       --重要函数！！！
--    DECODE()函数非常类似于程序中的if…else…语句，
--    唯一不同的是DECODE()函数判断的是数值，而不是逻辑条件。

SELECT EMPNO,
       ENAME,
       JOB,
       DECODE(JOB,
              'CLERK',
              '办事员',
              'SALESMAN',
              '销售人员',
              'MANAGER',
              '经理',
              'ANALYST',
              '分析员',
              'PRESIDENT',
              '总裁')
  FROM EMP;
/*
1	7369	SMITH	CLERK	办事员
2	7499	ALLEN	SALESMAN	销售人员
*/