oracle函数.docx
- 文档编号:4385249
- 上传时间:2022-12-01
- 格式:DOCX
- 页数:37
- 大小:65.24KB
oracle函数.docx
《oracle函数.docx》由会员分享,可在线阅读,更多相关《oracle函数.docx(37页珍藏版)》请在冰豆网上搜索。
oracle函数
ORACLE函数介绍
第一篇著名函数之单值函数2007.8.13
注:
N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。
单值函数在查询中返回单个值,可被应用到select,where子句,startwith以及connectby子句和having子句。
(1).数值型函数(NumberFunctions)
数值型函数输入数字型参数并返回数值型的值。
多数该类函数的返回值支持38位小数点,诸如:
COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,andTANH支持36位小数点。
ACOS,ASIN,ATAN,andATAN2支持30位小数点。
1、MOD(n1,n2)返回n1除n2的余数,如果n2=0则返回n1的值。
例如:
SELECTMOD(24,5)FROMDUAL;
2、ROUND(n1[,n2])返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracledocuments上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。
例如:
SELECTROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1)FROMDUAL;
3、TRUNC(n1[,n2]返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。
例如:
SELECTTRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1)FROMDUAL;
(2).字符型函数返回字符值(CharacterFunctionsReturningCharacterValues)
该类函数返回与输入类型相同的类型。
●返回的CHAR类型值长度不超过2000字节;
●返回的VCHAR2类型值长度不超过4000字节;
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。
●返回的CLOB类型值长度不超过4G;
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。
1、LOWER(c)将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如:
SELECTLOWER('WhaTistHis')FROMDUAL;
2、UPPER(c)将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如:
SELECTUPPER('WhaTistHis')FROMDUAL;
3、LPAD(c1,n[,c2])返回指定长度=n的字符串,需要注意的有几点:
●如果n ●如果n>c1.lengthandc2isnull,以空格从左向右补充字符长度至n并返回; ●如果n>c1.lengthandc2isnotnull,以指定字符c2从左向右补充c1长度至n并返回; 例如: SELECTLPAD('WhaTistHis',5),LPAD('WhaTistHis',25),LPAD('WhaTistHis',25,'-')FROMDUAL; 最后大家再猜一猜,如果n<0,结果会怎么样 4、RPAD(c1,n[,c2])返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反; 例如: SELECTRPAD('WhaTistHis',5),RPAD('WhaTistHis',25),RPAD('WhaTistHis',25,'-')FROMDUAL; 5、TRIM([[LEADING||TRAILING||BOTH]c2FROM]c1)哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。 看起来很复杂,理解起来很简单: ●如果没有指定任何参数则oracle去除c1头尾空格 例如: SELECTTRIM('WhaTistHis')FROMDUAL; ●如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟) 例如: SELECTTRIM('W'FROM'WhaTistHiswW')FROMDUAL; ●如果指定了leading参数则会去掉c1头部c2 例如: SELECTTRIM(leading'W'FROM'WhaTistHiswW')FROMDUAL; ●如果指定了trailing参数则会去掉c1尾部c2 例如: SELECTTRIM(trailing'W'FROM'WhaTistHiswW')FROMDUAL; ●如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗? 没区别! ) 例如: SELECTTRIM(both'W'FROM'WhaTistHiswW')FROMDUAL; 注意: c2长度=1 6、LTRIM(c1[,c2])千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。 如果c2为空则默认截取空格。 例如: SELECTLTRIM('WWhhhhhaTistHiswW','Wh')FROMDUAL; 7、RTRIM(c1,c2)与上同,不过方向相反 例如: SELECTRTRIM('WWhhhhhaTistHiswW','Ww')FROMDUAL; 8、REPLACE(c1,c2[,c3])将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。 例如: SELECTREPLACE('WWhhhhhaTistHiswW','W','-')FROMDUAL; 9、SOUNDEX(c)神奇的函数啊,该函数返回字符串参数的语音表示形式,对于比较一些读音相同,但是拼写不同的单词非常有用。 计算语音的算法如下: ●保留字符串首字母,但删除a、e、h、i、o、w、y。 ●将下表中的数字赋给相对应的字母: 1: b、f、p、v 2: c、g、k、q、s、x、z 3: d、t 4: l 5: m、n 6: R ●如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f),或者只有h或w,则删除其他的,只保留1个; ●只返回前4个字节,不够用0填充 例如: SELECTSOUNDEX('dog'),soundex('boy')FROMDUAL; 10、SUBSTR(c1,n1[,n2])截取指定长度的字符串。 稍不注意就可能充满了陷阱的函数。 n1=开始长度; n2=截取的字符串长度,如果为空,默认截取到字符串结尾; ●如果n1=0thenn1=1 ●如果n1>0,则oracle从左向右确认起始位置截取 例如: SELECTSUBSTR('Whatisthis',5,3)FROMDUAL; ●如果n1<0,则oracle从右向左数确认起始位置 例如: SELECTSUBSTR('Whatisthis',-5,3)FROMDUAL; ●如果n1>c1.length则返回空 例如: SELECTSUBSTR('Whatisthis',50,3)FROMDUAL; 然后再请你猜猜,如果n2<1,会如何返回值呢 11、TRANSLATE(c1,c2,c3)就功能而言,此函数与replace有些相似。 但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。 什么是绝对匹配替换呢? 简单的说,是将字符串c1中按一定的格式c2替换为c3。 如果文字形容仍然无法理解,我们通过几具实例来说明: 例如: SELECTTRANSLATE('Whatisthis','','-')FROMDUAL; SELECTTRANSLATE('Whatisthis','-','')FROMDUAL; 结果都是空。 来试试这个: SELECTTRANSLATE('Whatisthis','','')FROMDUAL; 再来看这个: SELECTTRANSLATE('Whatisthis','ait','-*')FROMDUAL; 是否明白了点呢? Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。 而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。 明白了? Replace是替换,而translate则像是过滤。 (3).字符型函数返回数字值(CharacterFunctionsReturningNumberValues) 本类函数支持所有的数据类型 1、INSTR(c1,c2[,n1[,n2]])返回c2在c1中位置 ●c1: 原字符串 ●c2: 要寻找的字符串 ●n1: 查询起始位置,正值表示从左到右,负值表示从右到左(大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。 黑黑,如果为0的话,则返回的也是0 ●n2: 第几个匹配项。 大于0 例如: SELECTINSTR('abcdefg','e',-3)FROMDUAL; 2、LENGTH(c)返回指定字符串的长度。 如果 例如: SELECTLENGTH('A123中')FROMDUAL; 猜猜SELECTLENGTH('')FROMDUAL;的返回值是什么 (4).日期函数(DatetimeFunctions) 本类函数中,除months_between返回数值外,其它都将返回日期。 1、ADD_MONTHS()返回指定日期月份+n之后的值,n可以为任何整数。 例如: SELECTADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12)FROMDUAL; 2、CURRENT_DATE返回当前session所在时区的默认时间 例如: SQL>altersessionsetnls_date_format='mm-dd-yyyy'; SQL>selectcurrent_datefromdual; 3、SYSDATE功能与上相同,返回当前session所在时区的默认时间。 但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。 经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。 同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。 例如: SELECTSYSDATE,CURRENT_DATEFROMDUAL; 4、LAST_DAY(d)返回指定时间所在月的最后一天 例如: SELECTlast_day(SYSDATE)FROMDUAL; 5、NEXT_DAY(d,n)返回指定日期后第一个n的日期,n为一周中的某一天。 但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。 例如: 三思用的中文nt,nls_language值为SIMPLIFIEDCHINESE SELECTNEXT_DAY(SYSDATE,5)FROMDUAL; SELECTNEXT_DAY(SYSDATE,'星期四')FROMDUAL; 两种方式都可以取到正确的返回,但是: SELECTNEXT_DAY(SYSDATE,'Thursday')FROMDUAL; 则会执行出错,提供你说周中的日无效,就是这个原因了。 6、MONTHS_BETWEEN(d1,d2)返回d1与d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0 例如: SELECTmonths_between(SYSDATE,sysdate), months_between(SYSDATE,add_months(sysdate,-1)), months_between(SYSDATE,add_months(sysdate,1)) FROMDUAL; 7、ROUND(d[,fmt])前面讲数值型函数的时候介绍过ROUND,此处与上功能基本相似,不过此处操作的是日期。 如果不指定fmt参数,则默认返回距离指定日期最近的日期。 例如: SELECTROUND(SYSDATE,'HH24')FROMDUAL; 8、TRUNC(d[,fmt])与前面介绍的数值型TRUNC原理相同,不过此处也是操作的日期型。 例如: SELECTTRUNC(SYSDATE,'HH24')FROMDUAL; (5).转换函数(ConversionFunctions) 转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例: 函数名称后面跟着待转换类型以及输出类型。 1、TO_CHAR()本函数又可以分三小类,分别是 ●转换字符->字符TO_CHAR(c): 将nchar,nvarchar2,clob,nclob类型转换为char类型; 例如: SELECTTO_CHAR('AABBCC')FROMDUAL; ●转换时间->字符TO_CHAR(d[,fmt]): 将指定的时间(data,timestamp,timestampwithtimezone)按照指定格式转换为varchar2类型; 例如: SELECTTO_CHAR(sysdate,'yyyy-mm-ddhh24: mi: ss')FROMDUAL; ●转换数值->字符TO_CHAR(n[,fmt]): 将指定数值n按照指定格式fmt转换为varchar2类型并返回; 例如: SELECTTO_CHAR(-100,'L99G999D99MI')FROMDUAL; 2、TO_DATE(c[,fmt[,nls]])将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。 注意这里的fmt参数。 如果ftm为'J'则表示按照公元制(Julianday)转换,c则必须为大于0并小于5373484的正整数。 例如: SELECTTO_DATE(2454336,'J')FROMDUAL; SELECTTO_DATE('2007-8-2323: 25: 00','yyyy-mm-ddhh24: mi: ss')FROMDUAL; 3、TO_NUMBER(c[,fmt[,nls]])将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。 例如: SELECTTO_NUMBER('-100.00','9G999D99')FROMDUAL; (6).其它辅助函数(MiscellaneousSingle-RowFunctions) 1、DECODE(exp,s1,r1,s2,r2..s,r[,def])可以把它理解成一个增强型的ifelse,只不过它并不通过多行语句,而是在一个函数内实现ifelse的功能。 exp做为初始参数。 s做为对比值,相同则返回r,如果s有多个,则持续遍历所有s,直到某个条件为真为止,否则返回默认值def(如果指定了的话),如果没有默认值,并且前面的对比也都没有为真,则返回空。 毫无疑问,decode是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记和熟练使用。 例如: selectdecode('a2','a1','true1','a2','true2','default')fromdual; 2、GREATEST(n1,n2,...n)返回序列中的最大值 例如: SELECTGREATEST(15,5,75,8)"Greatest"FROMDUAL; 3、LEAST(n1,n2....n)返回序列中的最小值 例如: SELECTLEAST(15,5,75,8)LEASTFROMDUAL; 4、NULLIF(c1,c2) Nullif也是个很有意思的函数。 逻辑等价于: CASEWHENc1=c2THENNULLELSEc1END 例如: SELECTNULLIF('a','b'),NULLIF('a','a')FROMDUAL; 5、NVL(c1,c2)逻辑等价于IFc1isnullTHENc2ELSEc1END。 c1,c2可以是任何类型。 如果两者类型不同,则oracle会自动将c2转换为c1的类型。 例如: SELECTNVL(null,'12')FROMDUAL; 6、NVL2(c1,c2,c3)大家可能都用到nvl,但你用过nvl2吗? 如果c1非空则返回c2,如果c1为空则返回c3 例如: selectnvl2('a','b','c')isNull,nvl2(null,'b','c')isNotNullfromdual; 7、SYS_CONNECT_BY_PATH(col,c)该函数只能应用于树状查询。 返回通过c1连接的从根到节点的路径。 该函数必须与connectby子句共同使用。 例如: createtabletmp3( rootcolvarchar2(10), nodecolvarchar2(10) ); insertintotmp3values('','a001'); insertintotmp3values('','b001'); insertintotmp3values('a001','a002'); insertintotmp3values('a002','a004'); insertintotmp3values('a001','a003'); insertintotmp3values('a003','a005'); insertintotmp3values('a005','a008'); insertintotmp3values('b001','b003'); insertintotmp3values('b003','b005'); selectlpad('',level*10,'=')||'>'||sys_connect_by_path(nodecol,'/') fromtmp3 startwithrootcol='a001' connectbypriornodecol=rootcol; 8、SYS_CONTEXT(c1,c2[,n])将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。 Oracle9i提供内置了一个命名空间USERENV,描述了当前session的各项信息,其拥有下列参数: ●CURRENT_SCHEMA: 当前模式名; ●CURRENT_USER: 当前用户; ●IP_ADDRESS: 当前客户端IP地址; ●OS_USER: 当前客户端操作系统用户; 等等数十项,更详细的参数列还请大家直接参考OracleOnlineDocuments 例如: SELECTSYS_CONTEXT('USERENV','SESSION_USER')FROMDUAL; ORACLE函数介绍 第一篇著名函数之单值函数2007.8.13 注: N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。 单值函数在查询中返回单个值,可被应用到select,where子句,startwith以及connectby子句和having子句。 (7).数值型函数(NumberFunctions) 数值型函数输入数字型参数并返回数值型的值。 多数该类函数的返回值支持38位小数点,诸如: COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,andTANH支持36位小数点。 ACOS,ASIN,ATAN,andATAN2支持30位小数点。 4、MOD(n1,n2)返回n1除n2的余数,如果n2=0则返回n1的值。 例如: SELECTMOD(24,5)FROMDUAL; 5、ROUND(n1[,n2])返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracledocuments上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。 例如: SELECTROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1)FROMDUAL; 6、TRUNC(n1[,n2]返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。 例如: SELECTTRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1)FROMDUAL; (8).字符型函数返回字符值(CharacterFunctionsReturningCharacterValues) 该类函数返回与输入类型相同的类型。 ●返回的CHAR类型值长度不超过2000字节; ●返回的VCHAR2类型值长度不超过4000字节; 如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。 ●返回的CLOB类型值长度不超过4G; 对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。 12、LOWER(c)将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如: SELECTLOWER('WhaTistHis')FROMDUAL; 13、UPPER(c)将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如: SELECTUPPER('WhaTistHis')FROMDUAL; 14、LPAD(c1,n[,c2])返回指定长度=n的字符串,需要注意的有几点: ●如果n ●如果n>c1.lengthandc2isnull,以空格从左
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 函数