oracle常用函数过程说明.docx
- 文档编号:24450811
- 上传时间:2023-05-27
- 格式:DOCX
- 页数:29
- 大小:24.16KB
oracle常用函数过程说明.docx
《oracle常用函数过程说明.docx》由会员分享,可在线阅读,更多相关《oracle常用函数过程说明.docx(29页珍藏版)》请在冰豆网上搜索。
oracle常用函数过程说明
Oracle常用函数/过程说明
主要介绍Oracle的系统函数、过程和包。
*SQL常用函数:
数值函数:
*ABS
Purpose返回绝对值
Returnstheabsolutevalueofn.
Example
SELECTABS(-15)"Absolute"FROMDUAL;
Absolute
----------
15
*CEIL
Purpose取最小整数
Returnssmallestintegergreaterthanorequalton.
Example
SELECTCEIL(15.7)"Ceiling"FROMDUAL;
Ceiling
----------
16
*MOD
Syntax
MOD(m,n)
Purpose取余
Returnsremainderofmdividedbyn.Returnsmifnis0.
Example
SELECTMOD(11,4)"Modulus"FROMDUAL;
Modulus
----------
3
*ROUND
Syntax
ROUND(n[,m])
Purpose取四舍五入信息
Returnsnroundedtomplacesrightofthedecimalpoint;ifmisomitted,to0places.mcanbenegativetoroundoffdigitsleftofthedecimalpoint.mmustbeaninteger.
Example1
SELECTROUND(15.193,1)"Round"FROMDUAL;
Round
----------
15.2
Example2
SELECTROUND(15.193,-1)"Round"FROMDUAL;
Round
----------
20
*TRUNC
Purpose取截取后的信息
Returnsntruncatedtomdecimalplaces;ifmisomitted,to0places.mcanbenegativetotruncate(makezero)mdigitsleftofthedecimalpoint.
Examples
SELECTTRUNC(15.79,1)"Truncate"FROMDUAL;
Truncate
----------
15.7
SELECTTRUNC(15.79,-1)"Truncate"FROMDUAL;
Truncate
----------
10
*SINGN(X)
若X为一个正数则返回1,若为负数则返回-1。
*SIN(X)
*POWER(X,Y)和EXP(X)
*LOG(X,Y)和LN(X)
字符函数:
*CONCAT
Syntax
CONCAT(char1,char2)
Purpose合并字符串,相当于“||”
Returnschar1concatenatedwithchar2.Thisfunctionisequivalenttotheconcatenationoperator(||).Forinformationonthisoperator,see"ConcatenationOperator".
Example
Thisexampleusesnestingtoconcatenatethreecharacterstrings:
SELECTCONCAT(CONCAT(ename,'isa'),job)"Job"
FROMemp
WHEREempno=7900;
Job
-----------------
JAMESisaCLERK
*LOWER
Purpose变为小写
Returnschar,withallletterslowercase.Thereturnvaluehasthesamedatatypeastheargumentchar(CHARorVARCHAR2).
Example
SELECTLOWER('MR.SCOTTMCMILLAN')"Lowercase"
FROMDUAL;
Lowercase
--------------------
mr.scottmcmillan
*LPAD
Purpose左填充
Returnschar1,left-paddedtolengthnwiththesequenceofcharactersinchar2;char2defaultstoasingleblank.Ifchar1islongerthann,thisfunctionreturnstheportionofchar1thatfitsinn.
Theargumentnisthetotallengthofthereturnvalueasitisdisplayedonyourterminalscreen.Inmostcharactersets,thisisalsothenumberofcharactersinthereturnvalue.However,insomemultibytecharactersets,thedisplaylengthofacharacterstringcandifferfromthenumberofcharactersinthestring.
Example
SELECTLPAD('Page1',15,'*.')"LPADexample"
FROMDUAL;
LPADexample
---------------
*.*.*.*.*Page1
*LTRIM
Syntax
LTRIM(char[,set])
Purpose左截取
Removescharactersfromtheleftofchar,withalltheleftmostcharactersthatappearinsetremoved;setdefaultstoasingleblank.Oraclebeginsscanningcharfromitsfirstcharacterandremovesallcharactersthatappearinsetuntilreachingacharacternotinsetandthenreturnstheresult.
Example
SELECTLTRIM('xyxXxyLASTWORD','xyXLA')"LTRIMexample"
FROMDUAL;
LTRIMexampl
------------
STWORD
*REPLACE
Syntax
REPLACE(char,search_string[,replacement_string])
Purpose替换
Returnscharwitheveryoccurrenceofsearch_stringreplacedwithreplacement_string.Ifreplacement_stringisomittedornull,alloccurrencesofsearch_stringareremoved.Ifsearch_stringisnull,charisreturned.ThisfunctionprovidesasupersetofthefunctionalityprovidedbytheTRANSLATEfunction.TRANSLATEprovidessingle-character,one-to-onesubstitution.REPLACEallowsyoutosubstituteonestringforanotheraswellastoremovecharacterstrings.
Example
SELECTREPLACE('JACKandJUE','J','BL')"Changes"
FROMDUAL;
Changes
--------------
BLACKandBLUE
*RPAD
Syntax
RPAD(char1,n[,char2])
Purpose右填充
Returnschar1,right-paddedtolengthnwithchar2,replicatedasmanytimesasnecessary;char2defaultstoasingleblank.Ifchar1islongerthann,thisfunctionreturnstheportionofchar1thatfitsinn.
Theargumentnisthetotallengthofthereturnvalueasitisdisplayedonyourterminalscreen.Inmostcharactersets,thisisalsothenumberofcharactersinthereturnvalue.However,insomemultibytecharactersets,thedisplaylengthofacharacterstringcandifferfromthenumberofcharactersinthestring.
Example
SELECTRPAD('MORRISON',12,'ab')"RPADexample"
FROMDUAL;
RPADexample
-----------------
MORRISONabab
*RTRIM
Syntax
RTRIM(char[,set]
Purpose
Returnschar,withalltherightmostcharactersthatappearinsetremoved;setdefaultstoasingleblank.RTRIMworkssimilarlytoLTRIM.
Example
SELECTRTRIM('BROWNINGyxXxy','xy')"RTRIMe.g."
FROMDUAL;
RTRIMe.g
-------------
BROWNINGyxX
*SUBSTR
Syntax
SUBSTR(char,m[,n])
Purpose截取字符串
Returnsaportionofchar,beginningatcharacterm,ncharacterslong.Ifmis0,itistreatedas1.Ifmispositive,Oraclecountsfromthebeginningofchartofindthefirstcharacter.Ifmisnegative,Oraclecountsbackwardsfromtheendofchar.Ifnisomitted,Oraclereturnsallcharacterstotheendofchar.Ifnislessthan1,anullisreturned.
Floating-pointnumberspassedasargumentstosubstrareautomaticallyconvertedtointegers.
Example1
SELECTSUBSTR('ABCDEFG',3,4)"Subs"
FROMDUAL;
Subs
----
CDEF
Example2
SELECTSUBSTR('ABCDEFG',-5,4)"Subs"
FROMDUAL;
Subs
----
CDEF
*TRANSLATE
Syntax
TRANSLATE(char,from,to)
Purpose在一定范围内转换字符
Returnscharwithalloccurrencesofeachcharacterinfromreplacedbyitscorrespondingcharacterinto.Charactersincharthatarenotinfromarenotreplaced.Theargumentfromcancontainmorecharactersthanto.Inthiscase,theextracharactersattheendoffromhavenocorrespondingcharactersinto.Iftheseextracharactersappearinchar,theyareremovedfromthereturnvalue.Youcannotuseanemptystringfortotoremoveallcharactersinfromfromthereturnvalue.Oracleinterpretstheemptystringasnull,andifthisfunctionhasanullargument,itreturnsnull.
Example1
Thefollowingstatementtranslatesalicensenumber.Allletters'ABC...Z'aretranslatedto'X'andalldigits'012...9'aretranslatedto'9':
SELECTTRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')"License"
FROMDUAL;
License
--------
9XXX999
Example2
Thefollowingstatementreturnsalicensenumberwiththecharactersremovedandthedigitsremaining:
SELECTTRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789')
"Translateexample"
FROMDUAL;
Translateexample
-----------------
2229
*UPPER
Syntax
UPPER(char)
Purpose大写
Returnschar,withalllettersuppercase.Thereturnvaluehasthesamedatatypeastheargumentchar.
Example
SELECTUPPER('Large')"Uppercase"
FROMDUAL;
Upper
-----
LARGE
*INITCAP(x)
首字母大写
*ASCII和chr()
Syntax
ASCII(char)
Purpose取字符的ASCII值
Returnsthedecimalrepresentationinthedatabasecharactersetofthefirstcharacterofchar.Ifyourdatabasecharactersetis7-bitASCII,thisfunctionreturnsanASCIIvalue.IfyourdatabasecharactersetisEBCDICCodePage500,thisfunctionreturnsanEBCDICvalue.NotethatthereisnosimilarEBCDICcharacterfunction.
Example
SELECTASCII('Q')
FROMDUAL;
ASCII('Q')
----------
81
chr()是其反函数。
*INSTR
Syntax
INSTR(char1,char2[,n[,m]])
Purpose从char1中第n个字符开始char2第m次出现的位置
Searcheschar1beginningwithitsnthcharacterforthemthoccurrenceofchar2andreturnsthepositionofthecharacterinchar1thatisthefirstcharacterofthisoccurrence.Ifnisnegative,Oraclecountsandsearchesbackwardfromtheendofchar1.Thevalueofmmustbepositive.Thedefaultvaluesofbothnandmare1,meaningOraclebeginssearchingatthefirstcharacterofchar1forthefirstoccurrenceofchar2.Thereturnvalueisrelativetothebeginningofchar1,regardlessofthevalueofn,andisexpressedincharacters.Ifthesearchisunsuccessful(ifchar2doesnotappearmtimesafterthenthcharacterofchar1)thereturnvalueis0.
Example1
SELECTINSTR('CORPORATEFLOOR','OR',3,2)
"Instring"FROMDUAL;
Instring
----------
14
Example2
SELECTINSTR('CORPORATEFLOOR','OR',-3,2)
"ReversedInstring"
FROMDUAL;
ReversedInstring
-----------------
2
*LENGTH
Syntax
LENGTH(char)
Purpose取字符串的长度
Returnsthelengthofcharincharacters.IfcharhasdatatypeCHAR,thelengthincludesalltrailingblanks.Ifcharisnull,thisfunctionreturnsnull.
Example
SELECTLENGTH('CANDIDE')"Lengthincharacters"
FROMDUAL;
Lengthincharacters
--------------------
7
*ADD_MONTHS
Syntax
ADD_MONTHS(d,n)
Purpose取N个月后的日期
Returnsthedatedplusnmonths.Theargumentncanbeanyinteger.Ifdisthelastdayofthemonthoriftheresultingmonthhasfewerdaysthanthedaycomponentofd,thentheresultisthelastdayoftheresultingmonth.Otherwise,theresulthasthesamedaycomponentasd.
Example
SELECTTO_CHAR(
ADD_MONTHS(hiredate,1),
'DD-MON-YYYY')"Nextmonth"
FROMemp
WHEREename='SMITH';
NextMonth
-----------
17-JAN-1981
*LAST_DAY
Syntax
LAST_DAY(d)
Purpose取D所在月份的最后一天
Returnsthedateofthelastdayofthemonththatcontainsd.Youmightusethisfunctiontodeterminehowmanydaysareleftinthecurrentmonth.
Example1
SELECTSYSDATE,
LAST_DAY(SYSDATE)"Last",
LAST_DAY(SYSDATE)-SYSDATE"DaysLeft"
FROMDUAL;
SYSDATELastDaysLeft
----------------------------
23-OCT-9731-OCT-978
Example2
SELECTTO_CHAR(
ADD_MONTHS(
LAST_DAY(hiredate),5),
'DD-MON-YYYY')"Fivemonths"
FROMemp
WHEREename='MARTIN';
Fivemonths
---
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 常用 函数 过程 说明