Oracle常用内置函数介绍及用法(一)

lower(str):将字符串转化为小写;

upper(str):将字符串转化为大写;

initcap(str):将字符串首字母转化为大写;

select lower('AaBb') as aaa, upper('AaBb') as AAA, initcap('AaBb') as Aaa from dual;

length(str):返回字符串的长度

lengthb(str):返回字符串的长度(按照字节)

select  length('Mart') name, length('烟台') addr, lengthb('烟台') addrb from dual;

lpad rpad

lpad(char1,n,char2):

在字符串char1的左端填充字符串char2，直至字符串总长度为n,

char2的默认值为空格，如果char1长度大于n，则该函数返回char1左端的前n个字符。

如果输入参数值存在NULL，则返回NULL

select lpad(rpad('gao', 10, '$'), 17, '#') from dual; --#######gao$$$$$$$

select lpad(rpad('gao', 10, '$'), 15, '#') from dual; --#####gao$$$$$$$

select lpad(rpad('gao', 10, '$'), 10, '#') from dual; --gao$$$$$$$

select lpad(rpad('gao', 10, '$'), 8, '#') from dual; --gao$$$$$

select lpad(rpad('gao', 5, '$'), 17, '#') from dual; --############gao$$

select lpad(rpad('gao', 2, '$'), 17, '#') from dual; --###############ga

-- trim ltrim rtrim

select rtrim('  abce  '), ltrim('  abce  '), trim('  abce  ') from dual;

select trim('s' from 'string'), trim('g' from 'string'), trim('r' from 'string') from dual; --tring

select trim (both from ' DWEYE ') "TRIM e.g." from dual;

select trim (trailing from ' DWEYE ') "TRIM e.g." from dual; --  DWEYE

select trim (leading from ' DWEYE ') "TRIM e.g." from dual; --DWEYE  

select trim ('x' from 'xxxxDWEYExxxx') "TRIM e.g." from dual; --DWEYE

select trim (both 'x' from 'xxxxDWEYExxxx') "TRIM e.g." from dual; --DWEYE

select trim (trailing 'x' from 'xxxxDWEYExxxx') "TRIM e.g." from dual; --xxxxDWEYE

select trim (leading 'x' from 'xxxxDWEYExxxx') "TRIM e.g." from dual; --DWEYExxxx

-- 'both' 参数表示同时去除字符串前后所指定的内容(默认情况下删除空格)

-- 'trailing' 参数表示去除字符串后所指定的内容(默认情况下删除空格)

-- 'leading' 参数表示去除字符串前所指定的内容(默认情况下删除空格)

-- 注意：

trim 删除指定字符的功能中，参数只允许包含一个字符，不支持多字符。可用ltrim rtrim 来实现相应的功能

select ltrim(rtrim('   Mart Li   ', ' '), ' ') from dual; --Mart Li

select ltrim(rtrim('xxxMart Lizzz', 'z'), 'x') from dual; --Mart Li

select rtrim('xyxxDWEYExyyx','xy') "e.g." from dual; --xyxxDWEYE

select ltrim('xyxxDWEYExyyx','xy') "e.g." from dual; --DWEYExyyx

-- 使用RTRIM和LTRIM函数时的注意事项：

-- “xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作

-- substr(src,start,length) 截取字符串,下标从1开始

select substr('1234567890', 3, 5) aaa from dual; --34567

select substr('1234567890', 0, length('1234567890')-1) from dual; --123456789

select substr('1234567890', 1, length('1234567890')-1) from dual; --123456789

-- replace('String','oldstr','newstr') 字符串替换

String   用于指定字符串 

oldstr   用于指定要被替换的子串

newstr   用于指定替换后的子串

如果 oldstr 为 NULL，则返回原有字符串，如果 newstr 为 NULL，则会去掉指定子串。

-- count(column_name)  统计一个表中 column_name 列的数量

select count(column_name) from authors;

-- 如果相同的 column_name 出现了不止一次，则会被计算多次。

可以通过关键字 distinct 得到不同 column_name 的数目：

select count(distinct column_name) from authors

-- 如果相同的column出现了不止一次，它将只被计算一次。

count(column_name),如果某一行的 column_name 是null，那么这一行将不计算，所以count(column_name)是计算所有值不为NULL的数据。

即count(column_name) 就只会统计非空行. 

count(*) 和count('x')，count('y'),count(1),count(2) 这3个都代表所有行,因为每行数据都不为null

补充，在表的主键不同情况下，count(*)和count(数字)的执行效率是不同的:

count(字段)时，如果字段为主键，则效率最高，count(1)次之，count(*)最慢;

如果字段不为主键，则count(1)最快，count(*)次之，count(字段)最慢;

-- abs(n)

用于返回数字n的绝对值，如果输入为NULL，则返回值也是NULL

select abs(100), abs(-100) from dual; --100 100

-- ceil(n) 

返回大于等于数字n的最小整数，若输入NULL，则返回为NULL

select ceil(3.1415927) from dual; --4

-- floor(n)

返回小于等于数字n的最大整数，若输入NULL，则返回NULL

select floor(2345.67) from dual; --2345

-- sign(n) 用于检测数字的正负

  若n<0; 则返回-1;

  若n=0; 则返回0;

  若n>0; 则返回1;

  若输入值为NULL，则返回NULL

select sign(123), sign(-100), sign(0) from dual; -- 1 -1 0

-- mod(n1,n2)

返回n1除以n2的余数

select mod(10, 3), mod(3, 3), mod(2, 3) from dual; --1 0 2

-- roundn(n[,m]) truncn(n[,m]) cut

round(n[,m])：返回n的四舍五入值,其中n可为任意数字，m必须为整数。

  若省略m或者m=0,则四舍五入到整数位;

  若m<0, 则四舍五入到小数点前的第m位;

  若m>0, 则四舍五入到小数点后的第m位;

  若输入NULL, 则输出NULL.

trunc(n[,m])：该函数用于截取数字,其中n可以是任意数字, m必须是整数。

  若m省略，则会将数字n的小数部分截去;

  若m>0, 则将数字n截取至小数点后的第m位;

  若m<0, 则将数字n截取至小数点前的第m位.

select round(55.6), round(-55.4), trunc(55.6), trunc(-55.4) from dual; --56 -55 55 -55

round(d[,fmt]):返回日期时间的四舍五入结果。

  参数d用于指定日期时间值，参数fmt用于指定四舍五入的方式。

  如果设置fmt为YEAR，则7月1日为分界线；

  如果设置fmt为MONTH，则16日为分界线；

  如果设置fmt为DAY，则中午12：00时为分界线。

trunc(d[,fmt])：用于截断日期时间数据。fmt用于指定截断日期时间数据的方法。

  如果设置fmt为YEAR，则结果为本年度的1月1日；

  如果设置fmt为MONTH，则结果为本月1日。

select round(sysdate,'YEAR') from dual; --2012-1-1 (today is 2011-07-01)

select round(sysdate,'YEAR')-1 from dual; --2011-12-31(today is 2011-07-01)

select trunc(sysdate,'YEAR') from dual; --2011-1-1(today is 2011-07-27)

select trunc(124.1666, -2) trunc1, trunc(124.16666, -1) trunc2 from dual; --100 120

select trunc(124.1666, 0) trunc1, trunc(124.16666, 2) trunc2 from dual; --124 124.16

-- add_months(d,n)

返回特定日期时间之后或之前的几个月所对应的日期时间

d:给定的日期时间;

n:可以是任意整数; 当n<0时,返回d之前n个月对应的日期时间;当n>0时,返回d之后n个月对应的日期时间.

select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

select to_char(add_months(to_date('2010-12', 'yyyy-mm'), 2), 'yyyy-mm')

  from dual; --2011-02

select to_char(add_months(to_date('2010-12', 'yyyy-mm'), -2), 'yyyy-mm')

  from dual; --2010-10

-- last_day(d)

返回特定日期所在月份的最后一天，参数d表示给定日期。

select to_char(sysdate, 'yyyy-mm-dd'), to_char((sysdate) + 1, 'yyyy-mm-dd') from dual; --2011-07-01 2011-07-02

select to_char(last_day(sysdate), 'yyyy-mm-dd') from dual; --2011-07-31

-- months_between(d1,d2) 返回日期d1和d2之间相差的月数;

  如果d1小于d2,则返回负数;

  如果d1和d2的天数相同或都是月底,则返回整数;

  否则Oracle以每月31天为准，计算结果的小数部分。

select months_between('19-12月-2010', '19-3月-2010') mon_between from dual; --9

select months_between(to_date('2010-03-19', 'yyyy-mm-dd'), to_date('2010-12-19', 'yyyy-mm-dd')) mon_betw from dual; -- -9

select months_between(to_date('2005.05.20', 'yyyy.mm.dd'), to_date('2010.05.20', 'yyyy.mm.dd')) mon_betw from dual; -- -60

-- new_time(date,zone1,zone2)

select to_char(sysdate, 'yyyy.mm.dd hh24:mi:ss') bj_time,

       to_char(new_time(sysdate, 'PDT', 'GMT'), 'yyyy.mm.dd hh24:mi:ss') los_angles

  from dual; --2011.07.01 14:45:31 2011.07.01 21:45:31

-- next_day(date,char) 返回特定日期之后的第一个工作日所对应的日期。

  date表示给定日期时间;

  char用于指定工作日,工作日必须与日期语言匹配。

select next_day('18-5月-2010', '星期五') next_day from dual; --2010-5-21

select next_day(to_date('2011-07-01', 'yyyy-mm-dd'), '星期一') next_day from dual; --2011-7-4

select next_day(to_date('2011-07-01', 'yyyy-mm-dd'), '星期二') next_day from dual; --2011-7-5

-- sysdate current_date getdate()

sysdate: Oracle数据库 获得系统当前时间的函数

current_date: Oracle数据库 获得当前会话时区所对应的日期时间的函数

getdate():SQLServer数据库 获得当前的日期和时间的函数

select getdate() from dual; --NOV 30 1997 3:29AM

select to_char(sysdate, 'yyyy-mm-dd day') from dual; --2011-07-01 星期五

-- extract() 从给定的日期时间值中获取所需要的特定数据

select extract(year from sysdate) from dual; --2011

-- greatest least

greatest:返回一组表达式中的最大值,即比较字符的编码大小.

least:返回一组表达式中的最小值.

select greatest('AA','AB','AC') gtc from dual; --AC

select greatest('啊','安','天') gtc from dual; --天

select least('啊','安','天') ltc from dual; --啊

-- avg(distinct|all) 函数返回数值列的平均值

all:表示对所有的值求平均值;

distinct:只对不同的值求平均值

NULL 值不包括在计算中。

select avg(distinct t.data_length) from user_tab_cols t; --323.2325

select avg(all t.data_length) from user_tab_cols t; --48.39710

-- max min

函数MAX（）返回一个数值型字段的所有值中的最大值。

函数MIN（）返回一个数值型字段的所有值中的最小值。

如果字段是空的，MAX() MIN()返回空值

select max(all t.data_length) from user_tab_cols t; --4000

select min(all t.data_length) from user_tab_cols t; --0

-- decode(condition, case1, value1, [case2, value2, ...,] default_value)

-- 条件判断函数

select decode('red','red','红灯','green','绿灯','黄灯') color from dual; --红灯

select decode('green','red','红灯','green','绿灯','黄灯') color from dual; --绿灯

select decode('yellow','red','红灯','green','绿灯','黄灯') color from dual; --黄灯

-- NVL(expr1, expr2) 

若expr1为NULL，返回expr2;

若expr1不为NULL，返回expr1;

注意两者的类型要一致 

常用于 存储过程中 条件的判断

select nvl('abc','def') from dual; -- abc

select nvl(null,'Replace Null Column') ideacol from dual; --Replace Null Column

-- NVL2 (expr1, expr2, expr3) 

若expr1不为NULL，返回expr2;

若expr1为NULL，返回expr3;

expr2和expr3类型不同的话，expr3会转换为expr2的类型

-- NULLIF (expr1, expr2)

相等返回NULL，不等返回expr1

-- sum(colname)  计算字段值的和

select sum(null) from dual; --(空)

select sum(t.num_rows) from user_tables t; --598441

函数SUM（）的返回值代表字段中所有值的和。字段的数据类型也许是MONEY型，但你也可以对其它数值型字段使用函数SUM（）。

-- to_char(n[,fmt]) 数字转换成字符

其实：to_number,to_char,to_date等转换函数都可以在很多数据类型之间进行转换,

to_lob一般只能将long、long raw转换为clob、blob、nclob类型

-- like 模糊查询

用到like关键字和通配符，有时候还得用到转义字符:

其中通配符有两个："_"代表一个字符，"%"代表零个或多个字符;

如果一个字符串中本身含有 "%"或"_",那么我们就要用转义字符, ' \ ' 就是一个转义字符，我们还可以自己定义一个转移字符：

select ename from emp where ename like '%$%%' escape '$';

其中escape将指明$符号为转移字符，默认情况下oracle把 ' \ ' 作为转移字符.

-- rownum rowid

Oracle中有两个伪列叫做：rownum和rowid。

rowid是每条记录的唯一标示，而rownum则是每条记录在表中从第一条记录到最后的一个排序标号;

可以通过 rownum 选出某个表中前几条记录入：

select * from emp where rownum < 5 则可以选出前五行记录

但是不能用 rownum 选出后几条记录来,如：

select * from emp where rownum > 10 后十条记录：这是错误的方法。

凡是大于或等于的都选不出来。

rownum 本身不显示在表中，它是隐藏着的，只有通过select语句显示的在select列表中注明rownum字段才能被选出来展示，

如子查询选出的一张新表，它同时也具有了一个伪列

--扩展: Oracle 分页的实现：

select ename, sal 

  from (select ename, sal, rownum 

      from (select ename, sal from emp order by sal desc ))

 where rownum >= 6 

   and rownum <=10;

最后总结一句话就是rownum是oracle加在你最后取出来的结果集上的一个伪字段，它记录着每一行的行号，

而这个行号只能和小于、小于等于使用,不能和等于、大于或大于等于一起使用,

要强行使用的话只能用子查询解决。