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

ゝ一纸荒年。 2021-12-09 07:53 321阅读 0赞

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加在你最后取出来的结果集上的一个伪字段，它记录着每一行的行号，

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

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

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Python内置函数用法介绍

相关 SqlServer 常用内置函数及用法

相关 Python常用内置函数

相关 Mysql常用内置函数

相关 MySQL常用内置函数

相关 MySQL 常用内置函数与所有内置函数

相关 Oracle常用内置函数

相关 python常用内置函数（一）

相关 Oracle常用内置函数介绍及用法(二)

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