首页 > 代码库 > Oracle常用函数汇总
Oracle常用函数汇总
在Oracle OCP考试中,相当一部分知识点涉及到对于Oracle常见函数的考查。尽管Oracle官方文档SQL Language Reference中Functions一章内列举了所有Oracle自带函数,但如果要系统的看一遍,还是要花费相当的精力,更何况还是英文呢。如果碰到一个不熟悉的,就查一下,不经常用,又很容易遗忘。下面就对Oracle常见函数做个整理,方便以后查询。
一、 大小写转换函数
1. LOWER函数
LOWER函数将字符以小写形式输出。其语法为LOWER(char)。
例如:
SQL> select lower(100+100),lower(sysdate),lower(‘HELLO‘) from dual;
LOW LOWER(SYS LOWER
--- --------- -----
200 25-sep-14 hello
注意:返回的均是字符类型,在执行LOWER函数之前先计算数据和日期表达式,并隐式转换为字符数据。
2. UPPER函数
UPPER函数将字符以大写形式输出。其语法为UPPER(char)。
例如:
SQL> select upper(‘hello world‘) from dual;
UPPER(‘HELL
-----------
HELLO WORLD
3. INITCAP函数
INITCAP函数将字符串中每个单词的第一个字母大写,而该单词的剩余字母小写。
例如:
SQL> select initcap(‘hello WORLD! that is funny!‘) from dual;
INITCAP(‘HELLOWORLD!THATISF
---------------------------
Hello World! That Is Funny!
二、 字符操作函数
1. CONCAT函数
CONCAT函数用于连接两个字符串。有两个参数,语法为CONCAT(s1,s2)。
例如:
SQL> select concat(‘hello‘,‘world‘) from dual;
CONCAT(‘HE
----------
helloworld
SQL> select concat(concat(ename,‘‘‘s job is ‘),job) from emp where empno=7788;
CONCAT(CONCAT(ENAME,‘‘‘SJOBIS
-----------------------------
SCOTT‘s job is ANALYST
2. LENGTH函数
LENGTH函数返回字符串的字符数,包括空格,制表符和特殊字符。其语法为LENGTH(s)。
例如:
SQL> select length(‘hello world!‘) from dual;
LENGTH(‘HELLOWORLD!‘)
---------------------
12
3. LPAD和RPAD函数
LPAD和RPAD函数分别被称为左填充和右填充函数。它们分别在给定字符串左边或右边填充指定数量的字符。其语法为LPAD(s,n,p)和RPAD(s,n,p),其中s表示源字符串,n表示返回字符串的最终长度,p指定用于填充的字符串。注意:如果参数n小于或者等于源字符串s的长度,就不会添加任何字符,此时只返回源字符串s的前n个字符。p默认为空格。
例如:
SQL> select lpad(‘hello‘,10,‘*‘),lpad(‘hello‘,10,‘*#‘),lpad(‘hello‘,10),lpad(‘hello‘,4,‘*‘) from dual;
LPAD(‘HELL LPAD(‘HELL LPAD(‘HELL LPAD
---------- ---------- ---------- ----
*****hello *#*#*hello hello hell
SQL> select rpad(‘hello‘,10,‘*‘),rpad(‘hello‘,10,‘*#‘),rpad(‘hello‘,10),rpad(‘hello‘,4,‘*‘) from dual;
RPAD(‘HELL RPAD(‘HELL RPAD(‘HELL RPAD
---------- ---------- ---------- ----
hello***** hello*#*#* hello hell
4. TRIM函数
TRIM函数从字符串的开头或者结尾删除一些字符。其语法为TRIM([trailing|leading|both] trimstring from s)。其中
TRIM(s)删除输入字符串两边的空格
TRIM(trailing trimstring from s)从字符串s的结尾删除所有trimstring(如果存在的话)。
TRIM(leading trimstring from s)从字符串s的开头删除所有trimstring。
TRIM(both trimstring from s)从字符串s的开头和结尾删除所有trimstring。
SQL> select trim(both ‘*‘ from ‘***hello****‘) from dual;
TRIM(
-----
hello
SQL> select trim(trailing ‘*‘ from ‘***hello****‘) from dual;
TRIM(TRA
--------
***hello
SQL> select trim(leading ‘*‘ from ‘***hello****‘) from dual;
TRIM(LEAD
---------
hello****
注意:trimstring只能是一个字符。
SQL> select trim(leading ‘*!‘ from ‘*!*!hello‘) from dual;
select trim(leading ‘*!‘ from ‘*!*!hello‘) from dual
*
ERROR at line 1:
ORA-30001: trim set should have only one character
5. INSTR函数(In-string)
INSTR函数确定搜索字符串在给定字符串中的位置。它返回数字位置,如果搜索字符串不存在,则该函数返回0。其语法为INSTR(source string,search string,[search start position],[nth occurrence])。其中,后两个参数是可选的,search start position的默认值是1,即source string的开头。nth occurrence的默认值是1,即第一次出现。
例如:
SQL> select instr(‘1*3*5*7*9‘,‘*‘,-3,2) from dual;
INSTR(‘1*3*5*7*9‘,‘*‘,-3,2)
---------------------------
4
SQL> select instr(‘1*3*5*7*9‘,‘*‘) from dual;
INSTR(‘1*3*5*7*9‘,‘*‘)
----------------------
2
SQL> select instr(‘1*3*5*7*9‘,‘*‘,2) from dual;
INSTR(‘1*3*5*7*9‘,‘*‘,2)
------------------------
2
6. SUBSTR函数(Substring)
SUBSTR函数从给定源字符串中给定的位置开始,提取指定长度的子字符串。其语法为SUBSTR(source string,start position,[number of characters to extrace])。最后一个参数可选,倘若没有指定,默认从start position到source string结尾的字符数。
例如:
SQL> select substr(‘1*3*5*7‘,3) from dual;
SUBST
-----
3*5*7
SQL> select substr(‘1*3*5*7‘,3,2) from dual;
SU
--
3*
SQL> select substr(‘1*3*5*7‘,-3,2) from dual;
SU
--
5*
7. REPLACE函数
REPLACE函数用替换项取代源字符串中出现的所有搜索项。其语法为REPLACE(source string,search item,[replacement term])。replacement term默认为空字符。
例如:
SQL> select replace(‘1*3*5*7‘,‘*‘,‘->‘) from dual;
REPLACE(‘1
----------
1->3->5->7
SQL> select replace(‘1*3*5*7‘,‘*‘) from dual;
REPL
----
1357
三、 数字函数
1. 数字round函数
ROUND函数依据指定的小数精度对数值进行四舍五入运算。其语法为ROUND(source number,decimal precision)。decimal precision参数指定舍入的精度。默认是0,即对源数字四舍五入到整数。如果指定的小数精度n为正数,则要舍入的有效数字在小数点右边(n+1)个位置。如果n为负数,则要舍入的有效数字在小数点左边n个位置。
例如:
SQL> select round(145.78,-1) from dual;
ROUND(145.78,-1)
----------------
150
SQL> select round(145.78) from dual;
ROUND(145.78)
-------------
146
SQL> select round(145.78,1) from dual;
ROUND(145.78,1)
---------------
145.8
2. TRUNC函数(Truncate)
TRUNC函数依据指定的小数精度对数值进行截取运算。其语法为TRUNC(source number,decimal precision)。
例如:
SQL> select trunc(145.78,-1) from dual;
TRUNC(145.78,-1)
----------------
140
SQL> select trunc(145.78) from dual;
TRUNC(145.78)
-------------
145
SQL> select trunc(145.78,1) from dual;
TRUNC(145.78,1)
---------------
145.7
3. MOD函数(Modulus)
MOD函数返回除法运算的余数。
例如:
SQL> select mod(5,2) from dual;
MOD(5,2)
----------
1
SQL> select mod(5.2,2.2) from dual;
MOD(5.2,2.2)
------------
.8
四、 日期函数
1. MONTHS_BETWEEN函数
MONTHS_BETWEEN函数返回两个日期参数之间月数的数值。其语法为MONTHS_BETWEEN(date1,date2)。该函数计算date1和date2之间月份的差值(每月31天),即date1-date2。返回值可能由整数和小数部分组成。其中,整数表示这两个日期之间相差的月数。小数部分表示剩余的天数和时间,以31天的月份为基础。
例如:
SQL> select months_between(sysdate,sysdate-31) from dual;
MONTHS_BETWEEN(SYSDATE,SYSDATE-31)
----------------------------------
1
SQL> select months_between(‘2-MAR-2014‘,‘1-FEB-2014‘) from dual;
MONTHS_BETWEEN(‘2-MAR-2014‘,‘1-FEB-2014‘)
-----------------------------------------
1.03225806
SQL> select 1/31 from dual;
1/31
----------
.032258065
--可以看出小数部分是以31天为基础的。
2. ADD_MONTHS函数
ADD_MONTHS函数语法为ADD_MONTHS(date,number of months),即计算指定月份添加到指定date后的日期。月数可以为负。
例如:
SQL> select add_months(‘1-MAR-2014‘,1) from dual;
ADD_MONTH
---------
01-APR-14
SQL> select add_months(‘1-MAR-2014‘,2.9) from dual;
ADD_MONTH
---------
01-MAY-14
SQL> select add_months(‘1-MAR-2014‘,-2.9) from dual;
ADD_MONTH
---------
01-JAN-14
3. NEXT_DAY函数
NEXT_DAY返回在指定日期后下一次出现星期几的日期。其语法为NEXT_DAY(start date,day of the week)。函数计算在start date之后day of the week下一次出现的日期。day of the week可以是字符值或者整数值。其中,字符值至少是星期名称的前三个字符,例如,星期日可以表示为sun,sund,sunda或者Sunday。对于整数,1表示星期日,2表示星期一,以此类推。
例如:19-SEP-2014是周五
SQL> select next_day(‘19-SEP-2014‘,5) from dual;
NEXT_DAY(
---------
25-SEP-14
SQL> select next_day(‘19-SEP-2014‘,‘tue‘) from dual;
NEXT_DAY(
---------
23-SEP-14
SQL> select next_day(‘19-SEP-2014‘,‘SUNDAY‘) from dual;
NEXT_DAY(
---------
21-SEP-14
4. LAST_DAY函数
LAST_DAY函数提取指定日期所属的月,并计算该月最后一天的日期。其语法为LAST_DAY(date)。
例如:
SQL> select last_day(‘19-SEP-2014‘) from dual;
LAST_DAY(
---------
30-SEP-14
5. 日期ROUND函数
日期ROUND函数依据指定的日期精度格式对日期进行舍入运算。其语法为ROUND(source date,[date precision format]).source date表示任意日期。date precision format包括世纪(CC),年(YYYY),季度(Q),月(MM),星期(W),日(DD),时(HH)和分(MI)。
如果日部分大于16,则会入到下一个月,否则舍到当月。如果月在1和6之间,则会舍到当年开头的日期,否则入到下一年开头的日期。
例如:
查询的时间为:2014-09-26 04:44:38
SQL> select round(sysdate) day,round(sysdate,‘w‘)week,round(sysdate,‘month‘)month,round(sysdate,‘year‘)year from dual;
DAY WEEK MONTH YEAR
--------- --------- --------- ---------
26-SEP-14 29-SEP-14 01-OCT-14 01-JAN-15
因为查询时间是4点,没有查过12点,故DAY是当天。因为本月1号是周一,WEEK返回的是查询时间起,下一个周一的日期,即29-SEP-14。因为查询时间是26号,超过16号,故MONTH需入到下一个月。因为查询时间是9月,超过6月,故入到下一年。
6. 日期TRUNC函数
日期TRUNC函数依据指定的日期精度格式对日期进行截取运算。其语法为TRUNC(source date,[date precision format])。date precision format指定截取的精度,默认的截取精度是日。这就意味着将source date的所有时间都设置为00:00:00(00时、00分和00秒)。在月级别上的截取将source date的日期设置为该月的第一天。年级别的截取返回当年开头的日期。
例如:
SQL> select trunc(sysdate) day,trunc(sysdate,‘w‘)week,trunc(sysdate,‘month‘)month,trunc(sysdate,‘year‘)year from dual;
DAY WEEK MONTH YEAR
--------- --------- --------- ---------
26-SEP-14 22-SEP-14 01-SEP-14 01-JAN-14
唯一需要注意的是WEEK,因为本月1号是周一,WEEK返回的是本周周一的日期。
Oracle常用函数汇总