资源描述：
《常用oracle的系统函数、过程和包》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、*SQL常用函数：数值函数：*ABSPurpose返回绝对值Returnstheabsolutevalueofn.ExampleSELECTABS(-15)"Absolute"FROMDUAL;Absolute----------15*CEILPurpose取最小整数Returnssmallestintegergreaterthanorequalton.ExampleSELECTCEIL(15.7)"Ceiling"FROMDUAL;Ceiling----------16*MODSyntaxMOD(m,n)Purpose取余Returnsremainderofmd
2、ividedbyn.Returnsmifnis0.ExampleSELECTMOD(11,4)"Modulus"FROMDUAL;Modulus----------3*ROUNDSyntaxROUND(n[,m])Purpose取四舍五入信息Returnsnroundedtomplacesrightofthedecimalpoint;ifmisomitted,to0places.mcanbenegativetoroundoffdigitsleftofthedecimalpoint.mmustbeaninteger.Example1SELECTROUND(15.19
3、3,1)"Round"FROMDUAL;Round----------15.2Example2SELECTROUND(15.193,-1)"Round"FROMDUAL;Round----------20 *TRUNCPurpose取截取后的信息Returnsntruncatedtomdecimalplaces;ifmisomitted,to0places.mcanbenegativetotruncate(makezero)mdigitsleftofthedecimalpoint.ExamplesSELECTTRUNC(15.79,1)"Truncate"FROM
4、DUAL;Truncate----------15.7SELECTTRUNC(15.79,-1)"Truncate"FROMDUAL;Truncate----------10字符函数：*CONCATSyntaxCONCAT(char1,char2)Purpose合并字符串，相当于“
5、
6、”Returnschar1concatenatedwithchar2.Thisfunctionisequivalenttotheconcatenationoperator(
7、
8、).Forinformationonthisoperator,see"ConcatenationOperat
9、or".ExampleThisexampleusesnestingtoconcatenatethreecharacterstrings:SELECTCONCAT(CONCAT(ename,'isa'),job)"Job"FROMempWHEREempno=7900;Job-----------------JAMESisaCLERK *LOWERPurpose变为小写Returnschar,withallletterslowercase.Thereturnvaluehasthesamedatatypeastheargumentchar(CHARorVARCHAR2)
10、.ExampleSELECTLOWER('MR.SCOTTMCMILLAN')"Lowercase"FROMDUAL;Lowercase--------------------mr.scottmcmillan*LPADPurpose左填充Returnschar1,left-paddedtolengthnwiththesequenceofcharactersinchar2;char2defaultstoasingleblank.Ifchar1islongerthann,thisfunctionreturnstheportionofchar1thatfitsinn.T
11、heargumentnisthetotallengthofthereturnvalueasitisdisplayedonyourterminalscreen.Inmostcharactersets,thisisalsothenumberofcharactersinthereturnvalue.However,insomemultibytecharactersets,thedisplaylengthofacharacterstringcandifferfromthenumberofcharactersinthestring.ExampleSELECTLPAD('Pa
12、ge1',
