Oracle函数.docx
- 文档编号:13180940
- 上传时间:2023-06-11
- 格式:DOCX
- 页数:26
- 大小:130.26KB
Oracle函数.docx
《Oracle函数.docx》由会员分享,可在线阅读,更多相关《Oracle函数.docx(26页珍藏版)》请在冰点文库上搜索。
Oracle函数
Oracle函数
Oracle函数-1-
NVL函数-1-
IF-THEN-ELSE函数-2-
INSTR函数-2-
ADD_MONTHS函数-3-
SUBSTR函数-4-
TO_NUMBER函数-4-
LPAD函数-4-
DUAL表-5-
CEIL和FLOOR函数-6-
DECODE函数-6-
Trunc()函数的用法-9-
Coalescue函数-10-
Greatest函数-10-
Least函数-10-
ABS函数-11-
Ceil函数-11-
To_char函数-12-
Trim 函数-19-
Upper函数-25-
LOWER函数-25-
NVL函数
NVL(E1,E2)的功能为:
如果E1为NULL,则函数返回E2,但此函数有一定局限,所以就有了NVL2函数。
拓展:
NVL2函数:
Oracle/PLSQL中的一个函数,Oracle在NVL函数的功能上扩展,提供了NVL2函数。
NVL2(E1,E2,E3)的功能为:
如果E1为NULL,则函数返回E3,若E1不为null,则返回E2。
IF-THEN-ELSE函数
在逻辑编程中,经常用到If–Then–Else进行逻辑判断。
在DECODE的语法中,实际上就是这样的逻辑处理过程。
它的语法如下:
DECODE(value,if1,then1,if2,then2,if3,then3,...else)
INSTR函数
instr函数在Oracle/PLSQL中是返回要截取的字符串在源字符串中的位置。
instr是一个非常好用的字符串处理函数,几乎所有的字符串分隔都用到此函数。
instr(string1,string2,start_position,nth_appearance)[1][2]
string1
源字符串,要在此字符串中查找。
string2
要在string1中查找的字符串。
start_position
代表string1的哪个位置开始查找。
此参数可选,如果省略默认为1.字符串索引从1开始。
如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。
nth_appearance
代表要查找第几次出现的string2.此参数可选,如果省略,默认为1.如果为负数系统会报错。
注意:
位置索引号从1开始。
如果String2在String1中没有找到,instr函数返回0。
示例:
SELECTinstr('syranmo','s')FROMdual;--返回1
SELECTinstr('syranmo','ra')FROMdual;--返回3
SELECTinstr('syranmo','a',1,2)FROMdual;--返回0
对于instr函数,我们经常这样使用:
从一个字符串中查找指定子串的位置。
例如:
SQL>selectinstr('abcdefgh','de')positionfromdual;
POSITION
----------
4
从1开始算d排第四所以返回4
ADD_MONTHS函数
格式:
ADD_MONTHS(DATE,NUMBER)即ADD_MONTHS(日期,数字)
ADD_MONTHS函数在输入日期上加上指定的几个月返回一个新的日期。
如果给出一负数,返回值日期之前几个月日期。
ADD_MONTHS(DATE,NUMBER)中的NUMBER应当是整数,给出小数时,正数被截为小于该数的最大整数,负数被截为大于该数的最小整数。
例:
add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99) 返回 28-Feb-95
注:
上例中29调整为28,是因为96年二月份最后一天是29号,而95年二月份最后一天是28号。
add_months(to_date('15-Nov-1961','d-mon-yyyy'),1) 返回 15-Dec-1961
add_months(to_date('30-Nov-1961','d-mon-yyyy'),1) 返回 31-Dec-1961
注:
从30调整为31,为了保持都是对应最后一天。
add_months(to_date('31-Jan-1999','d-mon-yyyy'),1) 返回 28-Feb-1999
注:
函数将31日调为28日,以使结果对应新一月的最后一天,因1999年2月只有28天
SUBSTR函数
substr(字符串,截取开始位置,截取长度)//返回截取的字
substr('HelloWorld',0,1)//返回结果为'H' *从字符串第一个字符开始截取长度为1的字符串
substr('HelloWorld',1,1)//返回结果为'H' *0和1都是表示截取的开始位置为第一个字符
substr('HelloWorld',2,4)//返回结果为'ello'
substr('HelloWorld',-3,3)//返回结果为'rld'*负数(-i)表示截取的开始位置为字符串右端向左数第i个字符
测试:
selectsubstr('HelloWorld',-3,3)valuefromdual;
TO_NUMBER函数
在“转换函数:
TO_CHAR()用法之一将一个数值转化成字符串 ”一文中 TO_CHAR 函数所用格式,都适合本函数。
例:
TO_NUMBER('123.45') 结果 123.45
TO_NUMBER('$123,456.78','$999,999.99') 结果 123,456.78
LPAD和RPAD函数
lpad函数是Oracle数据库函数,lpad函数从左边对字符串使用指定的字符进行填充。
从其字面意思也可以理解,l是left的简写,pad是填充的意思,所以lpad就是从左边填充的意思。
R是right的简写,也就是说rpad就是充右边进行填充。
语法格式如下:
lpad(string,padded_length,[pad_string])
string
准备被填充的字符串;
padded_length
填充之后的字符串长度,也就是该函数返回的字符串长度,如果这个数量比原字符串的长度要短,lpad函数将会把字符串截取成从左到右的n个字符;
pad_string
填充字符串,是个可选参数,这个字符串是要粘贴到string的左边,如果这个参数未写,lpad函数将会在string的左边粘贴空格。
示例1:
SQL>selectlpad('abcde',10,'x')fromdual;
LPAD('ABCDE',10,'X')
--------------------
Xxxxxabcde
DUAL表
1.dual确实是一张表.是一张只有一个字段,一行记录的表.
2.习惯上,我们称之为'伪表'.因为他不存储主题数据.
3.他的存在,是为了操作上的方便.因为select都是要有特定对象的.
如:
select*frommytable;
select*frommyview;
等等.
但如果我们不需要从具体的表来取得表中数据,而是单纯地为了得到一些我们想得到的信息,并要通过select完成时,就要借助一个对象,这个对象,就是dual;
如我们要计算999*999的值,可以用:
select999*999fromdual;
来实现;
要拼接一个电话信息:
selectconcat('010-','88888888')||'转23'高乾竞电话fromdual;
就变成了我们想要的格式输出.
4.当然,我们不一定要dual,也可以这样做.例如:
createtablemydual(dummyvarchar2
(1));
也可以实现和dual同样的效果:
select999*999frommydual;
不过,dual我们都用习惯了,就无谓自己再搞一套了.
CEIL和FLOOR函数
ceil和floor函数在一些业务数据的时候,有时还是很有用的。
ceil(n)取大于等于数值n的最小整数;
floor(n)取小于等于数值n的最大整数
如下例子
SQL>selectceil(9.5)fromdual;
CEIL(9.5)
----------
10
SQL>selectfloor(9.5)fromdual;
FLOOR(9.5)
----------
9
DECODE函数
含义解释:
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下:
IF条件=值1THEN
RETURN(翻译值1)
ELSIF条件=值2THEN
RETURN(翻译值2)
......
ELSIF条件=值nTHEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
ENDIF
decode(字段或字段的运算,值1,值2,值3)
这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3,当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多
使用方法:
1、比较大小
selectdecode(sign(变量1-变量2),-1,变量1,变量2)fromdual;--取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
例如:
变量1=10,变量2=20
则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
2、此函数用在SQL语句中,功能介绍如下:
Decode函数与一系列嵌套的IF-THEN-ELSE语句相似。
base_exp与compare1,compare2等等依次进行比较。
如果base_exp和第i个compare项匹配,就返回第i个对应的value。
如果base_exp与任何的compare值都不匹配,则返回default。
每个compare值顺次求值,如果发现一个匹配,则剩下的compare值(如果还有的话)就都不再求值。
一个为NULL的base_exp被认为和NULLcompare值等价。
如果需要的话,每一个compare值都被转换成和第一个compare值相同的数据类型,这个数据类型也是返回值的类型。
Decode函数在实际开发中非常的有用,结合Lpad函数,如何使主键的值自动加1并在前面补0
selectLPAD(decode(count(记录编号),0,1,max(to_number(记录编号)+1)),14,'0')记录编号fromtetdmis
eg:
selectdecode(dir,1,0,1)froma1_interval
dir的值是1变为0,是0则变为1
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
selectcount(*)from表where性别=男;
selectcount(*)from表where性别=女;
要想显示到一起还要union一下,太麻烦了
用decode呢,只需要一句话
selectdecode(性别,男,1,0),decode(性别,女,1,0)from表
3,orderby对字符列进行特定的排序
大家还可以在Orderby中使用Decode。
例:
表table_subject,有subject_name列。
要求按照:
语、数、外的顺序进行排序。
这时,就可以非常轻松的使用Decode完成要求了。
select*fromtable_subjectorderbydecode(subject_name,'语文',1,'数学',2,,'外语',3)
Trunc()函数的用法
/**************日期********************/
1.selecttrunc(sysdate)fromdual--2013-01-06今天的日期为2013-01-06
2.selecttrunc(sysdate,'mm')fromdual--2013-01-01返回当月第一天.
3.selecttrunc(sysdate,'yy')fromdual--2013-01-01返回当年第一天
4.selecttrunc(sysdate,'dd')fromdual--2013-01-06返回当前年月日
5.selecttrunc(sysdate,'yyyy')fromdual--2013-01-01返回当年第一天
6.selecttrunc(sysdate,'d')fromdual--2013-01-06(星期天)返回当前星期的第一天
7.selecttrunc(sysdate,'hh')fromdual--2013-01-0617:
00:
00当前时间为17:
35
8.selecttrunc(sysdate,'mi')fromdual--2013-01-0617:
35:
00TRUNC()函数没有秒的精确
/***************数字********************//*
TRUNC(number,num_digits)
Number需要截尾取整的数字。
Num_digits用于指定取整精度的数字。
Num_digits的默认值为0。
TRUNC()函数截取时不进行四舍五入
*/
9.selecttrunc(123.458)fromdual--123
10.selecttrunc(123.458,0)fromdual--123
11.selecttrunc(123.458,1)fromdual--123.4
12.selecttrunc(123.458,-1)fromdual--120
13.selecttrunc(123.458,-4)fromdual--0
14.selecttrunc(123.458,4)fromdual--123.458
15.selecttrunc(123)fromdual--123
16.selecttrunc(123,1)fromdual--123
17.selecttrunc(123,-1)fromdual--120
Coalescue函数
返回表达式列表中第一个非空的值,列表必须都是相同类型,也可以是一个表的同一行、不同列的值进行比较。
SELECTcoalesce(1,NULL,2)FROMdual;--返回1
SELECTcoalesce(NULL,2,1)FROMdual;--返回2
SELECTcoalesce(t.empno,t.mgr)FROMscott.empt;--效果类似
NVL(t.empno,t.mgr)
Greatest函数
返回表达式列表中最大值,列表必须是相同类型,也可以是一个表的同一行、不同列的值进行比较。
当列表中有一个值为null,那么就返回null,这个地方需要注意,如果需要当值为null的时候,可以考虑用nvl转为0来进行比较。
SELECTgreatest(1,3,2)FROMdual;--返回3
SELECTgreatest('A','B','C')FROMdual;--返回C
SELECTgreatest(NULL,'B','C')FROMdual;--返回null
SELECTgreatest(t.empno,t.mgr)FROMscott.empt;--返回empno和mgr较大值
Least函数
返回表达式列表中最小值,列表必须是相同类型,也可以是一个表的同一行、不同列的值进行比较。
当列表中有一个值为null,那么就返回null,这个地方需要注意,如果需要当值为null的时候,可以考虑用nvl转为0来进行比较。
SELECTleast(1,3,2)FROMdual;--返回1
SELECTleast('A','B','C')FROMdual;--返回A
SELECTleast(NULL,'B','C')FROMdual;--返回null
SELECTleast(t.empno,t.mgr)FROMscott.empt;--返回empno和mgr较小值
对于max和min函数,如果处理的列中有null值,那么max和min会忽略null值,但是,如果在该列中,所有行的值都是null,那么max、min的返回值就是null。
ABS函数
格式:
abs(number) 即 abs(数值)
返回数值的绝对值。
例:
abs(5) 返回 5
abs(-5) 返回 5
Ceil函数
格式:
ceil(number) 即 ceil(数值)
根据输入值返回一个数值,输入参数可以是非整数,但返回结果则是大于等于输入参数的最小整数。
例:
ceil(5.1) 返回 6
abs(-5.2) 返回 -5
To_char函数
Postgres格式化函数提供一套有效的工具用于把各种数据类型(日期/时间,int,float,numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成原始的数据类型。
注意:
所有格式化函数的第二个参数是用于转换的模板。
表5-7.格式化函数
函数
返回
描述
例子
to_char(timestamp,text)
text
把timestamp转换成string
to_char(timestamp'now','HH12:
MI:
SS')
to_char(int,text)
text
把int4/int8转换成string
to_char(125,'999')
to_char(float,text)
text
把float4/float8转换成string
to_char(125.8,'999D9')
to_char(numeric,text)
text
把numeric转换成string
to_char(numeric'-125.8','999D99S')
to_date(text,text)
date
把string转换成date
to_date('05Dec2000','DDMonYYYY')
to_timestamp(text,text)
date
把string转换成timestamp
to_timestamp('05Dec2000','DDMonYYYY')
to_number(text,text)
numeric
把string转换成numeric
to_number('12,454.8-','99G999D9S')
表5-8.用于date/time转换的模板
模板
描述
HH
一天的小时数(01-12)
HH12
一天的小时数(01-12)
HH24
一天的小时数(00-23)
MI
分钟(00-59)
SS
秒(00-59)
SSSS
午夜后的秒(0-86399)
AMorA.M.orPMorP.M.
正午标识(大写)
amora.m.orpmorp.m.
正午标识(小写)
Y,YYY
带逗号的年(4和更多位)
YYYY
年(4和更多位)
YYY
年的后三位
YY
年的后两位
Y
年的最后一位
BCorB.C.orADorA.D.
年标识(大写)
bcorb.c.oradora.d.
年标识(小写)
MONTH
全长大写月份名(9字符)
Month
全长混合大小写月份名(9字符)
month
全长小写月份名(9字符)
MON
大写缩写月份名(3字符)
Mon
缩写混合大小写月份名(3字符)
mon
小写缩写月份名(3字符)
MM
月份(01-12)
DAY
全长大写日期名(9字符)
Day
全长混合大小写日期名(9字符)
day
全长小写日期名(9字符)
DY
缩写大写日期名(3字符)
Dy
缩写混合大小写日期名(3字符)
dy
缩写小写日期名(3字符)
DDD
一年里的日子(001-366)
DD
一个月里的日子(01-31)
D
一周里的日子(1-7;SUN=1)
W
一个月里的周数
WW
一年里的周数
CC
世纪(2位)
J
Julian日期(自公元前4712年1月1日来的日期)
Q
季度
RM
罗马数字的月份(I-XII;I=JAN)-大写
rm
罗马数字的月份(I-XII;I=JAN)-小写
所有模板都都允许使用前缀和后缀修改器。
模板里总是允许使用修改器。
前缀 'FX'只是一个全局修改器。
表5-9.用于日期/时间模板to_char()的后缀
后缀
描述
例子
FM
填充模式前缀
FMMonth
TH
大写顺序数后缀
DDTH
th
小写顺序数后缀
DDTH
FX
固定模式全局选项(见下面)
FXMonthDDDay
SP
拼写模式(还未实现)
DDSP
用法须知:
如果没有使用FX选项,to_timestamp和to_date忽略空白。
FX必须做为模板里的第一个条目声明。
反斜杠("\")必须用做双反斜杠("\\"),例如'\\HH\\MI\\SS'。
双引号('"')之间的字串被忽略并且不被分析。
如果你想向输出写双引号,你必须在双引号前面放置一个双反斜杠('\\'),例如'\\"YYYYMonth\\"'。
to_char支持不带前导双引号('"')的文本,但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释(例如:
'"HelloYear:
"YYYY')。
表5-10.用于to_char(numeric)的模板
模板
描述
9
带有指定位数的值
0
前导零的值
.(句点)
小数点
(逗号)
分组(千)分隔符
PR
尖括号内负值
S
带负号的负值(使用本地化)
L
货币符号(使用本地化)
D
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 函数