完全掌握Oracle使用数据操作函数

推荐教程：《Oracle视频教程》

一、概述：

像几乎所有其他的计算机语言一样，SQL支持使用函数来操作数据，函数通常用于对数据执行操作，以便进行转换和操作。

二、函数分类

• 文本函数：用于操作文本字符串（例如，修剪或填充值，以及把值转换成大小写形式）。
• 数值函数：用于对数值型数据执行数学运算（例如：返回绝对值以及执行代数计算）。
• 日期和时间函数：用于操作日期和时间值，以及从这些值中提取特定的部分（例如：返回日期之间的差值以及检查日期有效性）。
• 系统函数：返回特定于所有的DBMS的信息（例如：返回用户登录信息或者检查版本的详细信息）。

1.文本操作函数

示例：

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

结果：

可以看出，Upper()把文本转换成大写形式。下表列举了一些常用的文本操作函数。

--1、查询字符长度位6的数:length()
select * from table_name where length(column_name) = 6;

--2、把字符串转换成小写形式：lower()
select lower(column_name) from table_name;

--3、将字符串左边填充：lpad(String ,截取长度，添加的字符串)。说是添加字符串也不准确，比较准确的说法是对String进行截取字符串，如果截取长度大于String的长度，则在String的左侧添加字符串进行填补，如果第三个参数未指定，则用空格进行填补。
select lpad('test',10,'ee') from dual;
--结果：
    lpad('test',10,'ee')
----------------------------
eeeeeetest
————————————————------------

--4、从字符串左边修剪空白： LTRIM(c1,[,c2])

【功能】删除左边出现的字符串

【参数】C1 字符串，c2 追加字符串，默认为空格

【返回】字符型

select ltrim('abcddee','abc') from dual;
--结果：
    ltrim('abcddee','abc')
---------------------------
ddee
————————————————-----------

--5、将字符串右边填充：rpad(string, c1, [c2])
【功能】将字符串右边填充
【参数】string：需要操作的字符串 c1：操作后的字符串长度 c2：用于填充的操作符，默认为空格

--6、从字符串右边修剪空白： RTRIM(c1,[,c2])
【功能】删除右边出现的字符串
【参数】c1：字符串 c2：需要删除的字符串
【返回】字符型
select rtrim('abcddee','ddee') from dual;
--结果：
    ltrim('abcddee','ddee')
---------------------------
abc
————————————————-----------

--7、返回字符串内的子字符：substring(string, index1, [,length1])
【功能】返回字符串内的子串
【参数】string：操作的源字符串 index1：子串在源字符串的起始位置 length1：子串的长度
【返回】字符型
select substring('string', 0, 3) from dual;
--结果：
    substr('string', 0, 3)
---------------------------
str
————————————————-----------

--8、返回字符串的大写形式：upper(string)
【功能】返回字符串的大写形式
【参数】：string：需要操作的源字符串
【返回】字符型
select upper('string') from dual;
--结果：
    upper('string')
---------------------------
STRING
————————————————-----------

注意：

SOUNDEX是一个算法，用于把任何文本字符串转换成一种字母数字模式，描述该文本的语音表示。SOUNDEX考虑了类似的发音字符和音节，允许按字符串的发音（而不是按它们是如何输入的）来比较它们。尽管SOUNDEX不是一个SQL概念，Oracle（像许多其他的DBMS一样）还是提供了对SOUNDEX的支持。

下面给出了一个使用Soundex()函数的示例。customers表中有一位Coyote Inc.的顾客，联系名为Y. Lee。但是，如果出现输入错误，而联系人实际上是Y. Lie，则会如何？显然，按正确的联系人名字执行搜索将不会返回任何数据，如下所示：

SELECT cust_name, cust_contact
FROM customers
where cust_contact = 'Y. Lie'

现在尝试使用Soundex()函数执行相同的搜索，以匹配发音类似于Y. Lie的所有联系人名字。

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

在这个示例中，WHERE子句使用Soundex()函数把cust_contact列值和搜索字符串转换成它们的SOUNDEX值。由于Y. Lee和Y. Lie发音相似，它们的SOUNDEX值将会匹配，因此WHERE子句将正确地过滤出想要的数据。

2.日期和时间操作函数

下表列出了一些常用的日期和时间操作函数

--使用案例；
--1、查询当前系统时间加3月后的时间：add_month()
select add_month(sysdate,3) from dual;

--2、查询6月分数据：extract()
select * from extract(month from date1);

--3、查询月份的最后一天:last_day()  假设位2022/6
select last_day(sysdate) from dual;
-->结果：2022/06/30

--4、查询两个时间之间相差的月份数：months_between()
select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months from dual;
 
    MONTHS
----------
2.3548387
————————————————

--5、返回指定日期后面的一天:next_date()
select sysdate from dual;

SYSDATE
-------------------
2022-07-13 19:30:26

--6、返回当前日期和时间:sysdate()
select sysdate from dual;

--7、把字符串转换成日期:to_date()
select * from table_name where date1 = to_date('2022/07/13', 'yyyy/mm/dd');

注意：最重要的Extract()函数

要记住一件事：数据格式化可能比较棘手。毕竟，”2022-03-04“是指那一天？03是月份以及04是天吗？或者反之亦然？因此，无论何时把一个日期提供给Oracle，都必须明确的说明它是如何格式化的。

SELECT cust_id, order_num
FROM orders
WHERE order_date = TO_DATE('2015-02-01', 'yyyy-mm-dd');

更灵活的日期运算需要能够提取日期或时间的特定部分。此时，Extract()函数就派上用场了。顾名思义，Extract()用于提取日期和时间的某些部分，允许只处理YEAR、MONTH、DAY、HOUR、MINUTE和SECOND。

下面给出了前面问题的另一种解决方案（该解决方案不需要你弄清楚每个月有多少天，或者担心闰年中的2月）：

SELECT cust_id, order_num
FROM orders
WHERE Extract(Year FROM order_date) = 2015
   AND Extract(Month FROM order_date) = 2

分析：

Extract(Year)返回日期中的年份。类似地，Extract(Month)返回日期中的月份。因此，WHERE Extract(Year FROM order_date) = 2015 AND Extract(Month FROM order_date) = 2将检索order_date在2015年和2月的所有行。

3.数值操作函数

数值操作函数当然用于操作数值型数据。这些函数只要用于代数，三角或者几何运算，因此不像字符串或者日期和时间操作函数那样常用。

下表列出常用的数值操作函数：

推荐教程：《Oracle视频教程》

以上就是完全掌握Oracle使用数据操作函数的详细内容，转载自php中文网