Oracle数据库中常用函数：字符、数字、日期、转换、通用

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

1。字符函数

字符函数的作用主要是操作字符串数据。更多字符函数如下：
•upper(char)：将输入字符串改为大写并返回；
•lower(char)：将输入字符串返回小写；
•initcap(char)：开头使用大写字母；
•length(char)：求字符串的长度；
•replace（char，search_string，replace_string）：替换；
•substr(char, m, n)：截取字符串的子串

Oracle 比较麻烦一点。即使要验证字符串，也必须写出完整的SQL语句。因此，在Oracle数据库中，为了方便用户查询，专门提供了“双”虚拟表。

1.1.上例：观察大写函数
SELECT UPPER('hello') FROM Dual;转换为小写操作，返回所有员工姓名小写
SELECT LOWER(ename) FROM emp;

1.3.initcap 示例：将每个员工姓名大写 SELECT INITCAP(ename) FROM em;示例：请求请求姓名长度恰好为 5
的员工信息 SELECT ename,LENGTH(ename) FROM emp
WHERE LENGTH(ename)=5;

1.5.使用字母“_”替换名称中所有字母“A”
SELECT REPLACE(ename,'A','_') FROM emp;

1.6.substring字符串截取操作有两种语法：
语法一​​：SUBSTR(string|column,startPoint)，表示从起点到终点截取；
SELECT ename,SUBSTR(ename,3) FROM emp;

语法二：SUBSTR(string | column, startpoint, endpoint)，表示从起点到终点截断，截断一部分内容；
SELECT ename,SUBSTR(ename,0,3) FROM emp;
SELECT ename,SUBSTR(ename,1,3) FROM emp;

示例：❀需要每个员工姓名的最后三个字母
•普通思路：确定长度为length-2的起点
SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;
•新思路：输入负数指定截距从末尾算起的位置；
SELECT ename,SUBSTR(ename,-3) FROM emp;

1.7。面试问题：SUBSTR()函数拦截时，sink是从0开始还是从1开始？
•在Oracle数据库中，SUBSTR()函数从0到1都是一样的；参考substr方法
•SUBSTR()也可以设置为负数，表示由截取起始点指定；

2。数值函数

数学函数的输入参数和返回值的数据类型都是数值类型。数学函数包括 cos、cosh、exp、ln、log、sin、sinh、sqrt、tan、tanh、acos、asin、atan、round 等：
•round（number，[number小数位数])：四舍五入运算；
•trunc(number, [小数位数])：截断数字；
•mod（数字1，数字2）：取模，取余；

•floor(n) 返回小于或等于n 的最大整数
•roof(n) 返回大于或等于n 的最小整数
•abs(n) 返回数字的绝对值1 为整数。
如果 m 为正数，则四舍五入到小数点后 m 位。
如果 m 为负数，则四舍五入到小数点前 m 位。
例如从双中选择轮(23.75123)； --返回 24
SELECT round(23.75123, -1) FROM Dual; --返回 20
SELECT round(27.75123, -1) FROM Dual; - -返回 30
SELECT round(23.75123, -3) FROM Dual; --返回 0
SELECT round(23.75123, 1) FROM Dual; --返回 23.8
SELECT round(23.75123, 2) FROM Dual ; --返回 23.75
SELECT round(23.75123, 3) FROM Dual; --Return 23.751

2.2, trunc
trunc(n,[m]) 该函数用于截断数字。
如果省略 m，则小数部分将被截去，
如果 m 是正数，c 将是小数，
如果米为负数 ，则捕获小数点前 m 位。
例如从双中选择 trunc(23.75123)； --返回 23
SELECT trunc(23.75123, -1) FROM Dual; --返回 20
SELECT trunc(23.75123, -3) FROM Dual; --Return 0
SELECT trunc(23.75123, 1) FROM Dual; --返回 23.7
SELECT trunc(23.75123, 2) FROM Dual; --返回 23.75
SELECT trunc(23.75123, 3) FROM Dual; --返回23,751

2.3.mod取模运算
SELECT MOD(10,3) FROM Dual; --Return 1
2.4, storey(n) 返回小于或等于 n 的最大值 整数
2.5, ceil(n) 返回大于或等于 n 的最小整数 f .ex 。 , 从对偶中选择 ceil(24.56); --return 25
从双中选择楼层(24.56); --return 24
2.6,abs(n) 返回数字n的绝对值
对数字的处理多用于金融系统或银行系统。不同的处理方法给出不同的核算结果。

3.日期函数

如果现在要进行日期操作，首先需要解决一个问题，就是如何获取当前日期。这个当前日期可以通过使用“SYSDATE”来获取，代码如下：
SELECT SYSDATE FROM Dual；

除了上面的当前日期外，还可以对日期进行几种计算：
• 日期+数字=日期，表示几天后的日期；
SELECT SYSDATE + 3,SYSDATE + 300 FROM Dual;

•Date – number = date，表示几天前的日期；
SELECT SYSDATE - 3,SYSDATE - 300 FROM Dual;

•日期 – 日期 = 数字代表两个日期之间的天数，但必须是大日期 – 小日期；

4.1。示例：查找截至今天每位员工的工作日数
SELECTename,hiredate,SYSDATE-hiredate FROM emp;
而且很多编程语言还提出了日期可以用数字表示的概念

除了上面三个公式之外，还提供了下面这个。四种操作功能：
4.2。 • LAST_DAY（日期）：查找给定日期的最后一天；
示例：查找本月的最后一天
SELECT LAST_DAY(SYSDATE) FROM Dual;

4.3.•NEXT_DAY（日期，周数）：查找下一个指定周的日期 ❀❓4.• .• ADD_MONTHS（日期，数字）：查找几个月后的日期；
示例：查找四个月后的日期
SELECT ADD_MONTHS(SYSDATE,4) FROM Dual;

4.5 .•MONTHS_BETWEEN(Date 1, Date 2)：查找两个月之间的日期；
示例：查找每个员工截至今天的入职月份
SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN (SYSDATE,hiredate)) FROM emp;

在所有开发中，如果有日期操作 建议使用以上函数，因为这些函数可以避免闰年问题。

4。转换功能

现在您已经接触到了Oracle数据库中的三种类型的数据：数字（NUMBER）、字符串（VARCHAR2）和日期（DATE）。转换函数的主要作用就是完成这几种数据的转换。字符串之间的转换操作一共有三个转换函数： 将字符串转换为DATE数据视图；
•TO_NUMBER（字符串）：将字符串转换为数字显示；

4.1.TO_CHAR() 函数
在当前系统日期和时间之前提示：
SELECT SYSDATE FROM Dual；
以“d.-year”格式显示。显然，这种显示格式不符合通常的想法。通常是“年-月-日”，所以在这种情况下可以使用 TO_CHAR() 函数，但使用该函数需要一些格式字符串：年 (yyyy)、月 (mm)、日 (dd)。
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') 年,TO_CHAR(SYSDATE,'mm') 月,TO_CHAR(SYSDATE,'dd') 天 FROM 双;
结果：
TO_CHAR(SY YEAR MO DA
---------- ---- -- --
2012-08-12 2012 08 12

但这它可以发现显示的数据中会有前导0。如果想消除这个0，可以加一个“fm”。
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') day FROM Dual;
结果：
DAY
----------❀--20

一般人加0，所以你只需要知道这个标记就可以了。然而，在Oracle中，DATE包含时间，但前面的代码没有显示时间。要显示时间，请添加标签：
SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM Dual;
结果
♹♹ -- -- ---------- ---
2012-8-12 16:13:38

请注意，使用 TO_CHAR() 函数后，所有内容都是字符串，不再是以前的了DATE 类型的数据， TO_CHAR() 函数也可用于格式化数字。此时，每个“9”代表的是数字的概念，而不是数字9的概念。
SELECT TO_CHAR(89078907890,'L999,999,999,999,999') FROM Dual;❀❀❀ ❀❀89078907890,'L999,999,
---------------- ------------------
￥89,078,907,890
字母“L”代表“本地”的意思，即：当前语言环境 货币符号

4.2.TO_DATE() 函数
该函数的主要功能就是将字符串转换为DATE类型数据。
SELECT TO_DATE('1989-09-12','yyyy-mm-dd') FROM Dual;
结果：
TO_DATE('1989- --------- --------- -----
December-September-89
通常这个函数在更新数据库的时候用的比较多；一眼就可以看到函数转换字符串转数字：
SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM Dual;
但在 Oracle 中却很巧妙，所以不用使用 TO_NUMBER() 也能完成上面的功能：
SELECT ' 1' + '2' FROM Dual;

例如：SQL> SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM Dual;
结果：
TO_NUMBER('1') )+TO_NUMBER ('2')
---------------------------------- - -
3

SQL> SELECT '1' + '2' FROM Dual;

'1'+'2'
----------
3

然后是 TO_NUMBER ()函数基本不再考虑。关键函数是TO_CHAR()，其次是TO_DATE()函数。

5. 通用函数

通用函数主要有两个：NVL() 和 DECODE()。这两个函数是Oracle自带的特殊函数；
5.1.NVL()函数，处理null
示例：请求查询每个员工的全部年薪
SQL> SELECT ename,sal,comm)*12+comm emp;
结果：
ENAME      SAL  COMM (SAL+COMM)*12
---------- ---------- -------- --- - --------- --
MARTIN 1250 1400 31800
SCOTT 800
TURNER 1500 0 18000❀♿ ADA这样t 一些员工的 110 年工资将为零，并且钥匙这个问题就是Comm字段为零，所以要解决这个问题，需要做一个过程：将零变为0，这就是NVL( )函数的作用。？ COMM,0))*12 NVL(COMM,0)
---------- ---------- ---------- ---- -------------------------
马丁 1250 1400 31800 1400
斯科特 800 9600 0
特纳 1500 0 18000 0 0
ADAMS 1100 13200 0

5.2.DECODE() 函数：多值判断
Decode() 函数与 if...else... 语句非常相似，唯一的区别是函数 DECODE () 计算的是数值，而不是逻辑条件。？分析师：分析师；
•总统：总统；
这个评估必须逐行进行，所以此时必须使用 DECODE()，该函数的语法如下：
DECODE(数值 | 列，评估值 1，显示值 1 ，评估值2，显示值2，评估值3，显示值3，...)
示例：实现所示操作函数
SQL> SELECT empno,ename,job, DECODE(job,'CLERK' ,'业务员','销售员','销售员','经理','经理','分析师','分析师','总裁','总裁')
FROM emp;
结果:
EMPNO ENAME 作业解码（J
--------- --------- --------- -- ------
7369 SMITH CLERK 文员
7499 ALLEN SALESMAN 销售人员
7566 JONES MANAGER 经理
7654 MART IN SALESMAN 推销员8 BLA 7839 KING PRESIDENT 总裁
78 44 TURNER SALGER 销售员
7876 ADAMS CLERK 职员
7902 福特分析师Analyst
DECODE()函数是Oracle中最具特色的函数，一定要掌握。列出所有职员 (CLERK) 的姓名、号码和部门号码。
SELECT empno、ename、deptno FROM emp WHERE job='CLERK';

3.查找佣金高于工资的员工。
SELECT * FROM emp WHERE comm>sal;

4。找出 60% 的佣金高于工资的员工。
从 emp 中选择 *，其中 comm>sal*0.6；

5。查找部门 10 中所有经理 (LEDER) 和部门 20 中所有职员 (CLERK) 的详细信息。
SELECT * FROM emp
WHERE (job='LEDER' AND deptnr=10) OR (job='CLERK ' 且部门号 = 20);

6。查找部门10的所有经理（MANAGER），部门20的所有文员（CLERK），所有既不是经理也不是文员，但工资大于等于2000的员工的详细信息。
SELECT * FROM emp
WHERE (job='MANAGER' AND dept.no.=10) OR (job='clerk' AND dept.no.=20)
OR (job NOT'','CLERKER ) AND sal>=2000) ;

7.了解领取佣金的员工的不同工作。
从 emp 中选择不同的作业，其中 comm 不为空；

8。查找不需要佣金或佣金低于 100 的员工。
SELECT * FROM emp WHERE comm IS NULL OR comm12;

11。以大写字母显示所有员工的姓名。
从 emp 中选择 INITCAP(ename)；

12。显示恰好 5 个字符的员工姓名。
从 emp 中选择 ename，其中 LENGTH(ename)=5；

13。显示不带“R”的员工姓名。
从 emp 中选择 ename，其中 ename 不喜欢“%R%”；

14。显示所有员工姓名的前三个字符。
从 emp 中选择 SUBSTR(ename,0,3)；

15。显示所有员工的姓名并将所有“A”替换为“a”。
SELECT REPLACE(ename,'A','a') FROM emp;

16。显示工龄 10 年雇员的姓名和受雇日期。
选择 ename、hiredate FROM emp
WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>10;

17。查看员工详细信息，按姓名排序。
SELECT * FROM emp ORDER BY ename;

18。显示员工的姓名和雇用日期，并根据服务年限对最年长的员工进行排序。
SELECT ename,hiredate FROM emp ORDER BYhiredate;

19.显示所有员工的姓名、职位和薪资，按职位降序排列，如果职位相同，按薪资排序。
SELECT ename, job, sal FROM emp ORDER BY job DESC,sal;

20。显示所有员工的姓名、加入公司的年份和月份，按入职日期的所有月份排序，如果月份相同，则最早的员工排在队列的最前面。
该程序必须从日期中提取年份和月份，并使用 TO_CHAR() 函数来完成。
SELECT ename,TO_CHAR(hiredate,'yyyy') 年,TO_CHAR(hiredate,'mm') 月
FROM emp
按月、年排序；❀。显示在一个月 30 天的情况下所有员工的日薪，而其余的则忽略。
从 emp 中选择 ename、sal、TRUNC(sal/30)；

22。查找 2 月份（每年）雇用的所有员工。
SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')=2;

23。显示每位员工加入公司以来的天数。
SELECT ename,SYSDATE-hiredate FROM emp;

24。显示姓名字段中任意位置包含“A”的所有员工的姓名。
从 emp 中选择 ename，其中 ename LIKE '%A%'；

25。以年、月、日的形式显示所有员工的工作年限。
第一步：求每位员工的工作年限：工作总月数/12 = 年数；
SELECT ename、hiredate、
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年
FROM emp;
步骤 2：查找月数。上述计算中忽略的小数点实际上是月份，所以只取余数；
SELECT ename，hiredate，
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年，
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE))
步骤 3：找到最准确的天数最好的办法是在不超过30天的范围内查找；
现在我们知道当前时间是用SYSDATE提取的，租赁日期是用租赁日期提取的，但是租赁日期之间的差距而且SYSDATE太大了，所以肯定会是一个bug，那么就得想办法把雇佣日期增加到SYSDATE的30天内。

之前学过两个函数：
•MONTHS_BETWEEN()：求两个日期之间的月数，如果有：MONTHS_BETWEEN(SYSDATE,hiredate)求从入职日到今天的入职月份； •ADD_MONTHS() ：将指定月份之后的日期添加到日期中。如果租赁日期 + 从今天算起的月数 = 新日期，并且新日期距 SYSDATE 不得超过 30 天。
SELECT ename，hiredate，
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 年，
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,SYATE)),C - add_months (hiredate,months_ Between (sysdate,hiredate) ))) day
from emp;

以上程序是日期函数的广泛应用

以上都是Oracle数据库的常用函数。