oracle函数大全的应用

wounler

已于 2022-04-24 18:17:05 修改

阅读量499

点赞数

文章标签： sql oracle

于 2022-03-21 16:54:29 首次发布

本文链接：https://blog.csdn.net/wounler/article/details/123639031

版权

使用dual表

dual表是oracle系统中对所有用户可用的一个实际存在的表，这个表不能用来存储信息，在实际应用过程中仅用来执行select语句。dual表是一个1行1列的表，不能向dual表中执行insert、delete和truncate语句。可以使用daul表用来查询系统的信息。

order by 子句里含有decode函数的说明

语句说明：
order by 子句里含有decode函数时，decode函数里的第一个参数，是order by排序时要针对的字段（对象，对哪些字段进行排序操作），而decode函数则是影响order by排序时数据行的顺序。例如，
表A的内容如下：

Aa          Ab
--------    ----------
1            19
null        2
4            10
3             1

SQL>select * from A order by Aa;
Aa          Ab
--------    ----------
1            19
3             1
4            10
null        2

SQL>select * from A  ORDER BY DECODE(Aa, NULL,Ab);
Aa          Ab
--------    ----------
1            19
null       2
3             1
4           10

上述两个例子里order by排序针对的字段都是Aa，而带有decode函数的语句排序时发现某行的里Aa字段为null时就会用该行的Ab字段的值赋予 Aa字段，故而Aa字段以此值来参加排序。

1.decode函数的两种形式

第一种形式

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

第二种形式

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

sign（）函数

解释：
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

用法示例：

select id,sign(id-2) from t_decode;

截图效果：
在这里插入图片描述

lpad（）函数

在字段id前边补字段0 长度为2

select lpad(id,2,0) from t_decode；

在这里插入图片描述

函数	功能	实例
lower(str)	将字符串转换为小写	Select lower(‘Hello World’) from dual; --返回结果：hello world
upper(str)	将字符串转换为大写	Select upper(‘hello world’) from dual; --返回结果：HELLO WORLD
initcap(str)	将每个单词首字母大写，其它字母小写	Select initcap(‘hello world’) from dual; --返回结果：Hello World Select initcap(‘HELLO WORLD’) from dual； --返回结果：Hello World
nls_initcap(str)	与initcap函数相同，它可以使用nlssort指定的分类方法
nls_lower(str)	与lower函数相同，它可以使用nlssort指定的分类方法
nls_upper(str)	与upper函数相同，它可以使用nlssort指定的分类方法
concat(str1,str2)	字符串拼接函数	Select concat(‘hello’,’world’) from dual; --返回结果：helloworld 等价于 Select ‘hello’ \|\| ‘world’ from dual; --返回结果：helloworld
instr(str,find_string,[start],[occurrence])	返回指定字符串在某字符串中的位置，可以指定搜索的开始位置和返回第几次搜索出来的结果（这个位置是从1开始算起）。start：此参数可选，如果省略默认为1。字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左开始检索，返回要查找的字符串在源字符串中的开始索引。occurrence：此参数可选，如果省略，默认为1，如果为负数系统会报错	Select instr(‘Hello World’,’o’) from dual; --返回结果：5 Select instr(‘Hello World’,’o’,6) from dual; --返回结果：8 Select instr(‘Hello World’,’o’,1,2) from dual; --返回结果：8 Select instr(‘Hello World’,’c’) from dual; --返回结果：0 select instr('helloworld','l',-1,1) from dual; --返回结果：9 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
length(str)	返回表达式中的字符数	Select length(‘abc’) from dual; --返回结果：3
lengthb(str)	与length函数相同，对于单字节字符系统，返回以字字节位单位	Select lengthb(‘字长’) from dual; --返回结果：4
lpad(str,width,[pad_string])	当字符串长度不够时，左填充补齐，可以指定补齐时用什么字符补齐，若不指定，则以空格补齐	Selece lpad(‘hello world’,20,'') from dual; --返回结果：********hello word
rpad(str,width,[pad_string]) 同上	当字符串长度不够时，右填充补齐，可以指定补齐时用什么字符补齐，若不指定，则以空格补齐	Selece rpad(‘hello world’,20) from dual; --返回结果：hello word*********
ltrim(str,[trim_string])	从字符串左侧去除指定的所有字符串，若没有指定去除的字符串，则默认除去左侧空白符	Select ltrim(‘*hello world‘,'') from dual; --返回结果：hello world***
rtrim(x,[trim_string])	从字符串右侧去除指定的所有字符串，原理同ltrim()	Select rtrim(‘*hello world‘,'') from dual; --返回结果：***hello world
trim(str)	去除两边的空格	Select rtrim(‘ hello world ‘) from dual; --返回结果：hello world
replace(x,y,z)	从字符串x中搜索y字符串，并使用z字符串替换，并不会修改数据库中的原始值	Select replace(‘hello world’,’hello’,’hi’) from dual; --返回结果：hi world
translate	同replace，作用与字符基础上而非非字符串基础上
substr(x,start,[length])	返回字符串中指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起，如果length省略，则将返回一直到字符串末尾的所有字符	Select substr(‘hello world’,3) from dual; --返回结果：llo world Select substr(‘hello world’,-3) from dual; --返回结果：rld Select substr(‘hello world’,3,2) from dual; --返回结果：ll Select substr(‘hello world’,-7,4) from dual; --返回结果：o wo
substrb	同substr，以字节数而非字符数返回字符串的一部分
soundex	返回字符串的语言提示	select soundex('a') from dual; --返回结果：A000

round(x,y)	四舍五入x的值位y位小数位，如果y忽略，则无小数位，如果y为负数，则小数位左边的数被四舍五入	select round(45.927,2) from dual; --返回结果：45.93 select round(45.923,0) from dual; --返回结果：46 select round(45.923,-1) from dual; --返回结果：50
trunc(value,n)	对value进行截断，如果n>0,保留n位小数；n<0，则保留-n 位整数位; n=0,则去掉小数部分	Select trunc(555.666) from dual; --返回结果：555 Select trunc(555.666,2) from dual; --返回结果：555.66; Select trunc(555.666,-2) from dual; --返回结果：500
mod(x,y)	返回x除以y的余数	select mod(45,12) from dual; --返回结果：9
avg(x)	返回x的平均值	Select avg(age) from sc;
abs(value)	返回value的绝对值	Select abs(-10) from dual; --返回结果：10
ceil(value)	返回大于等于value的最小值	Select ceil(2.3) from dual; --返回结果：3
floor(value)	返回小于等于value的最大整数	Select floor(2,3) from dual; --返回结果：2
max(x)	返回x的最大值	Select max(grade) from sc;
min(x)	返回x的最小值	Select min(grade) from sc;
sum(x)	返回x的总计值	Select sum(grade) from sc;
count(x)	返回统计的行数	Select count(name) from sc;
nvl(x,value)	将一个null转换为另一个值，如果x的值为null，则返回value，否则返回x值本身	Select nvl(null,’value’) from dual; --返回结果：value
nvl2(x,value1,value2)	如果x不为空，返回value1，否则返回value2	Select nvl(null,’value1’,'value2') from dual; --返回结果：value2
to_char(x,[format])	将x转换为字符串，format为转换的格式，可以为数字格式或日期格式	Select to_char(‘1234.56’) from dual; --返回结果：1234.56 Select to_char(‘12345.67’,’99,999.99’) from dual; --返回结果：12,345.67
to_number(x,[format])：	将x转换为数字，可以指定format格式	Select to_number(‘970.13’)+25.5 from dual; --返回结果：995.63 Select to_number(‘-$12,345.67’,’$99999.99’) from dual; --返回结果： -12345.67
cast(x as type)：	将x转换为指定的兼容的数据库类型	select cast(12345.67 as varchar2(10)),cast('05-7月-07' as date), cast(12345.678 as number(10,2)) from dual;--返回结果： 12345.67 2007/7/5 12345.68
to_date(x,[format])：	将x字符串转换为日期	Select to_date(‘2018/02/15 11:10:30’,’yyyy/MM/dd hh24:mi:ss’) from dual;--返回结果：2018/2/15 11:10:30
decode用法	decode(字段或字段的运算，值1，值2，值3）	这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3 当然值1，值2，值3也可以是表达式。
sign用法	sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1	select sign(-2) from dual;--返回结果：-1
months_between (date1,date2)	计算date1和date2之间的月数，其结果可以是正的也可以是负的。如果date1大于date2，则结果是正的，反之，结果是负的。结果的小数部分表示月的一部分	select months_between(to_date('2022/03/20','yyyy/MM/dd'))
to_blob	将指定的值转换为blob类型的值
to_clob	将指定的值转换为clob类型的值
to_number	将char或varchar2字符串强制转换为number值
to_date	将char或varchar2字符串强制转换为日期值
to_char	将日期型或数字型的值转换为varchar2类型的值

SQL中的单记录函数

1.ASCII 返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

2.CHR 给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;

3.CONCAT 连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;

4.INITCAP 返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;

5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
SQL> select instr('oracle traning','ra',1,2) instring from dual;

6.LENGTH 返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal))

7.LOWER 返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

8.UPPER 返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;

9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;

12.REPLACE('string','s1','s2')
string   希望被替换的字符或变量
s1       被替换的字符串
s2       要替换的字符串
SQL> select replace('he love you','he','i') from dual;

13.SOUNDEX 返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

14.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符

15.ABS 返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;

16.ACOS 给出反余弦的值
SQL> select acos(-1) from dual;

17.ASIN 给出反正弦的值
SQL> select asin(0.5) from dual;

18.ATAN 返回一个数字的反正切值
SQL> select atan(1) from dual;

19.CEIL 返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;

20.COS 返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;

21.COSH 返回一个数字反余弦值
SQL> select cosh(20) from dual;

22.EXP 返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;

23.FLOOR 对给定的数字取整数
SQL> select floor(2345.67) from dual;

24.LN 返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;

25.LOG(n1,n2) 返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;

26.MOD(n1,n2) 返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

27.POWER 返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;

28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

29.SIGN 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;

30.SIN 返回一个数字的正弦值
SQL> select sin(1.57079) from dual;

31.SIGH 返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;

32.SQRT 返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;

33.TAN 返回数字的正切值
SQL> select tan(20),tan(10) from dual;

34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;

35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

SQL> select last_day(sysdate) from dual;

38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual;

39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;

41.SYSDATE 用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

42.CHARTOROWID 将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

44.HEXTORAW 将一个十六进制构成的字符串转换为二进制

45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制

46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型

47.TO_CHAR(date,'format')，格式不区分大小写，底层都会转化成大写
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

日期以20220401 13:45:25为例

	格式	描述	结果
year	y	一位年	2
	yy	两位年	22
	yyy	三位年	022
	yyyy	四位年	2022
Month	mm	两位月	显示值:04
	mon	字符集表示	显示值:04月,若是英文版,显示apr
	month	字符集表示	显示值:04月,若是英文版,显示april
Day	d	当星期第几天（从星期日开始算）	6
	dd	当月第几天	01
	ddd	当年第几天	091
	dy	当周第几天简写	fri
	day	当周第几天全写	friday
Hour	hh	2小时进制	01
Hour	hh24	24小时进制	13
Minute	mi	60进制	45
Second	ss	60进制	25
Q	Q	季度	2
WW	WW	当年第几周	13
W	W	当月第几周	1

48.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期

49.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;

50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;

51.BFILENAME(dir,file)指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

52.CONVERT('x','desc','source') 将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,2 0,'none',3 2,'insert',4 3,5 'select',6 6,'update',7 7,'delete',8 8,'drop',9 'other') cmd from v$session where type!='background';

53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数

55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;

SQL> select greatest('啊','安','天') from dual;

56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;

57.UID
返回标识当前用户的唯一整数
SQL> show user
SQL> select username,user_id from dba_users where user_id=uid;

58.USER
返回当前用户的名字
SQL> select user from dual;

59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;

SQL> select userenv('isdba') from dual;

60.userenv返回会话标志
SQL> select userenv('sessionid') from dual;

61.userenv返回会话人口标志
SQL> select userenv('entryid') from dual;

62.userenv返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;

63.userenv返回当前环境变量
SQL> select userenv('language') from dual;

64.userenv返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;

65.userenv返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;

66.vsize返回X的大小(字节)数
SQL> select vsize(user),user from dual;