资源描述:
《【优秀资料】常用Oracle的系统函数、过程和包.doc》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、*SQL常用函数:数值函数:*ABSPurpose返冋绝对值Returnstheabsolutevalueofn.ExampleSELECTABS(-15)"Absolute"FROMDUAL;Absolute15*CEILPurpose取最小整数Returnssmallestintegergreaterthanorequalton.ExampleSELECTCETL(15.7)"Ceiling"FROMDUAL;Ceiling16*MODSyntaxMOD(m,n)Purpose取余Returnsremainder
2、ofmdividedbyn.Returnsmifnis0.ExampleSELECTMOD(I1,4)"Modulus"FROMDUAL;Modulus3*ROUNDSyntaxROUND(n[,m])Purpose取四舍五入信息Returnsnroundedtomplacesrightofthedecimalpoint;ifmisomitted,to0places,mcanbenegativetoroundoffdigitsleftofthedecimalpointsmmustbeaninteger.Example
3、1SELECTROUND(15.193,1)"Round"FROMDUAL;Round15.2Example2SELECTROUND(15.193,-1)"Round"FROMDUAL;Round*TRUNCPurpose取截取后的信息Returnsntruncatedtomdecimalplaces;ifmisomitted,to0places,mcanbenegativetotruncate(makezero)mdigitsleftofthedecimalpoint.ExamplesSELECTTRUNC(15.
4、79,1)"Truncate"FROMDUAL;Truncate15.7SELECTTRUNC(15.79,-1)"Truncate"FROMDUAL;Truncate10字符函数:*CONCATSyntaxCONCAT(char1,char2)Purpose合并字符串,相当于“
5、
6、”Returnscharlconcatenatedwithchar2.Thisfunctionisequivalenttotheconcatenationoperator(
7、
8、).Forinformationonthisoperator,
9、see^ConcatenationOperator77.ExampleThisexampleusesnestingtoconcatenatethreecharacterstrings:SELECTCONCAT(CONCAT(ename,'is8'),job)〃Job〃FROMempWHEREempno=7900;JobJAMESisaCLERK*LOWERPurpose变为小写Returnschar,withallletterslowercase.TheTeturnvaluehasthesamedatatypeast
10、heargumentchar(CHARorVARCHAR2).ExampleSELECTLOWER(,MR.SCOTTMCMILLAN,)"Lowercase"FROMDUAL;Lowercasemr.scottmcmiIlan*LPADPurpose左填充Returnscharl,left-paddedtolengthnwiththesequenceofcharactersinchar2;char2defaultstoasingleblank.Ifcharlislongerthann,thisfunctionret
11、urnstheportionofcharlthatfitsinn.Theargumentnisthetotal1engthofthereturnvalueasitisdisplayedonyourterminalscreen.Inmostcharactersets,thisisalsothenumberofcharactersinthereturnvalue.However,insomemultibytecharactersets,thedisplaylengthofacharacterstringcandiffer
12、fromthenumberofcharactersinthestring.ExampleSELECTLPADCPagel',15,'*・')〃LPADexample"FROMDUAL;LPADexample*•*•*•*•*Page1*LTRIMSyntaxLTRIM(char[,set])Purpose左截取Removescharacters