完全掌握Oracle使用資料操作函數

這篇文章為大家帶來了關於Oracle的相關知識，其中主要介紹了使用資料操作函數的相關問題，像幾乎所有其他的電腦語言一樣，SQL支援使用函數來操作數據，函數通常用於對數據執行操作，以便進行轉換和操作，下面一起來看一下，希望對大家有幫助。

推薦教學：《Oracle影片教學》

一、概述​​：

像幾乎所有其他的電腦語言一樣，SQL支援使用函數來操作數據，函數通常用於對數據執行操作，以便進行轉換和操作。

二、函數分類

• 文字函數：用於操作文字字串（例如，修剪或填滿值，以及把值轉換成大小寫形式）。
• 數值函數：用於對數值型資料執行數學運算（例如：傳回絕對值以及執行代數計算）。
• 日期和時間函數：用於操作日期和時間值，以及從這些值中提取特定的部分（例如：傳回日期之間的差異以及檢查日期有效性） 。
• 系統函數：傳回特定於所有的DBMS的資訊（例如：傳回使用者登入資訊或檢查版本的詳細資訊）。

1.文字運算函數

範例：

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

結果：

##可以看出，Upper()把文字轉換成大寫形式。下表列舉了一些常用的文字操作函數。

函數描述#Length()傳回字串的長度Lower()把字串轉換成小寫形式LPad()把字串左邊填入空格LTrim()從字串左邊修剪空白RPad ()在字串右邊填滿空格RTrim()從字串右邊邊修剪空白#Soundex()傳回字串的SOUNDEX值#SubString()傳回字串內的字元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.日期和时间操作函数

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

函数	描述
Add_Mounth()	给日期添加月份（也可以减去月份）
Extract()	从日期和时间中减去年，月，日，时，分或秒
Last_Day()	返回月份的最后一天
Months_Between()	返回两个月份之间的月数
Next_Day()	返回指定日期后面的那一天
Sysdate()	返回当前日期和时间
To_Date()	把字符串转换成日期

--使用案例；
--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.数值操作函数

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

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

函数	描述
Abs()	返回数字的绝对值
Cos()	返回指定角度的三角余弦值
exp()	返回指定数字的指数值
mod()	返回除法运算的余数
sin()	返回指定角度的正弦值
sqrt()	返回指定数字的平方根
tan()	返回指定角度的三角正切值

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

以上是完全掌握Oracle使用資料操作函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！