- 浏览: 1429954 次
文章分类
最新评论
-
shrimp0526:
大神您写的这个工具还在么?能不能提供一个国内的下载地址?
Java版文件编码转换工具 -
流逝记忆:
感谢分享
Android音乐播放器【安卓进化二十】 -
hgq0011:
java能调用吗?谢谢
利用微软的SAPI进行语音合成 -
penkee:
怎样不弹出 视频压缩 选择压缩程序的窗口求教
C#将图像文件压缩为AVI文件播放 -
流动的阳光:
有用,mark!
Android中使用自定义的字体
oracle函数大全
ORACLE函数大全(本人收藏)
SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL>selectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;
AAZEROSPACE
------------------------------------
65974832
2.CHR
给出整数,返回对应的字符;
SQL>selectchr(54740)zhao,chr(65)chr65fromdual;
ZHC
---
赵A
3.CONCAT
连接两个字符串;
SQL>selectconcat('010-','88888888')||'转23'高乾竞电话fromdual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL>selectinitcap('smith')uppfromdual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1被搜索的字符串
C2希望搜索的字符串
I搜索的开始位置,默认为1
J出现的位置,默认为1
SQL>selectinstr('oracletraning','ra',1,2)instringfromdual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL>selectname,length(name),addr,length(addr),sal,length(to_char(sal))fromgao.nchar_tst;
NAMELENGTH(NAME)ADDRLENGTH(ADDR)SALLENGTH(TO_CHAR(SAL))
---------------------------------------------------------------------------
高乾竞3北京市海锭区69999.997
7.LOWER
返回字符串,并将所有的字符小写
SQL>selectlower('AaBbCcDd')AaBbCcDdfromdual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL>selectupper('AaBbCcDd')upperfromdual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD在列的右边粘贴字符
LPAD在列的左边粘贴字符
SQL>selectlpad(rpad('gao',10,'*'),17,'*')fromdual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM删除左边出现的字符串
RTRIM删除右边出现的字符串
SQL>selectltrim(rtrim('gaoqianjing',''),'')fromdual;
LTRIM(RTRIM('
-------------
gaoqianjing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL>selectsubstr('13088888888',3,fromdual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string希望被替换的字符或变量
s1被替换的字符串
s2要替换的字符串
SQL>selectreplace('heloveyou','he','i')fromdual;
REPLACE('H
----------
iloveyou
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL>createtabletable1(xmvarchar();
SQL>insertintotable1values('weather');
SQL>insertintotable1values('wether');
SQL>insertintotable1values('gao');
SQL>selectxmfromtable1wheresoundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s'from'string')
LEADING剪掉前面的字符
TRAILING剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL>selectabs(100),abs(-100)fromdual;
ABS(100)ABS(-100)
------------------
100100
16.ACOS
给出反余弦的值
SQL>selectacos(-1)fromdual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL>selectasin(0.5)fromdual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL>selectatan(1)fromdual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL>selectceil(3.1415927)fromdual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL>selectcos(-3.1415927)fromdual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL>selectcosh(20)fromdual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL>selectexp(2),exp(1)fromdual;
EXP(2)EXP(1)
------------------
7.38905612.7182818
23.FLOOR
对给定的数字取整数
SQL>selectfloor(2345.67)fromdual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL>selectln(1),ln(2),ln(2.7182818)fromdual;
LN(1)LN(2)LN(2.7182818)
-------------------------------
0.69314718.99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL>selectlog(2,1),log(2,4)fromdual;
LOG(2,1)LOG(2,4)
------------------
02
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL>selectmod(10,3),mod(3,3),mod(2,3)fromdual;
MOD(10,3)MOD(3,3)MOD(2,3)
---------------------------
102
27.POWER
返回n1的n2次方根
SQL>selectpower(2,10),power(3,3)fromdual;
POWER(2,10)POWER(3,3)
---------------------
102427
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL>selectround(55.5),round(-55.4),trunc(55.5),trunc(-55.5)fromdual;
ROUND(55.5)ROUND(-55.4)TRUNC(55.5)TRUNC(-55.5)
----------------------------------------------
56-5555-55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL>selectsign(123),sign(-100),sign(0)fromdual;
SIGN(123)SIGN(-100)SIGN(0)
----------------------------
1-10
30.SIN
返回一个数字的正弦值
SQL>selectsin(1.57079)fromdual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL>selectsin(20),sinh(20)fromdual;
SIN(20)SINH(20)
------------------
.91294525242582598
32.SQRT
返回数字n的根
SQL>selectsqrt(64),sqrt(10)fromdual;
SQRT(64)SQRT(10)
------------------
83.1622777
33.TAN
返回数字的正切值
SQL>selecttan(20),tan(10)fromdual;
TAN(20)TAN(10)
------------------
2.2371609.64836083
34.TANH
返回数字n的双曲正切值
SQL>selecttanh(20),tan(20)fromdual;
TANH(20)TAN(20)
------------------
12.2371609
35.TRUNC
按照指定的精度截取一个数
SQL>selecttrunc(124.1666,-2)trunc1,trunc(124.16666,2)fromdual;
TRUNC1TRUNC(124.16666,2)
---------------------------
100124.16
36.ADD_MONTHS
增加或减去月份
SQL>selectto_char(add_months(to_date('199912','yyyymm'),2),'yyyymm')fromdual;
TO_CHA
------
200002
SQL>selectto_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm')fromdual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL>selectto_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')fromdual;
TO_CHAR(SYTO_CHAR((S
--------------------
2004.05.092004.05.10
SQL>selectlast_day(sysdate)fromdual;
LAST_DAY(S
----------
31-5月-04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL>selectmonths_between('19-12月-1999','19-3月-1999')mon_betweenfromdual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd'))mon_betwfromdual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL>selectto_char(sysdate,'yyyy.mm.ddhh24:mi:ss')bj_time,to_char(new_time
2(sysdate,'PDT','GMT'),'yyyy.mm.ddhh24:mi:ss')los_anglesfromdual;
BJ_TIMELOS_ANGLES
--------------------------------------
2004.05.0911:05:322004.05.0918:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL>selectnext_day('18-5月-2001','星期五')next_dayfromdual;
NEXT_DAY
----------
25-5月-01
41.SYSDATE
用来得到系统的当前日期
SQL>selectto_char(sysdate,'dd-mm-yyyyday')fromdual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL>selectto_char(trunc(sysdate,'hh'),'yyyy.mm.ddhh24:mi:ss')hh,
2to_char(trunc(sysdate,'mi'),'yyyy.mm.ddhh24:mi:ss')hhmmfromdual;
HHHHMM
--------------------------------------
2004.05.0911:00:002004.05.0911:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL>selectrowid,rowidtochar(rowid),enamefromscott.emp;
ROWIDROWIDTOCHAR(ROWID)ENAME
----------------------------------------------
AAAAfKAACAAAAEqAAAAAAAfKAACAAAAEqAAASMITH
AAAAfKAACAAAAEqAABAAAAfKAACAAAAEqAABALLEN
AAAAfKAACAAAAEqAACAAAAfKAACAAAAEqAACWARD
AAAAfKAACAAAAEqAADAAAAfKAACAAAAEqAADJONES
43.CONVERT(c,dset,sset)
将源字符串sset从一个语言字符集转换到另一个目的dset字符集
SQL>selectconvert('strutz','we8hp','f7dec')"conversion"fromdual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL>selectto_char(sysdate,'yyyy/mm/ddhh24:mi:ss')fromdual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/0921:14:41
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL>selectto_multi_byte('高')fromdual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL>selectto_number('1999')yearfromdual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insertintofile_tb1values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL>selectsid,serial#,username,decode(command,
20,'none',
32,'insert',
43,
5'select',
66,'update',
77,'delete',
88,'drop',
9'other')cmdfromv$sessionwheretype!='background';
SIDSERIAL#USERNAMECMD
------------------------------------------------------
11none
21none
31none
41none
51none
61none
71275none
81275none
920GAOselect
1040GAOnone
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL>colglobal_namefora30
SQL>coldump_stringfora50
SQL>setlin200
SQL>selectglobal_name,dump(global_name,1017,8,5)dump_stringfromglobal_name;
GLOBAL_NAMEDUMP_STRING
--------------------------------------------------------------------------------
ORACLE.WORLDTyp=1Len=12CharacterSet=ZHS16GBK:W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL>selectgreatest('AA','AB','AC')fromdual;
GR
--
AC
SQL>selectgreatest('啊','安','天')fromdual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL>selectleast('啊','安','天')fromdual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL>showuser
USER为"GAO"
SQL>selectusername,user_idfromdba_userswhereuser_id=uid;
USERNAMEUSER_ID
---------------------------------------
GAO25
58.USER
返回当前用户的名字
SQL>selectuserfromdual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA查看当前用户是否是DBA如果是则返回true
SQL>selectuserenv('isdba')fromdual;
USEREN
------
FALSE
SQL>selectuserenv('isdba')fromdual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL>selectuserenv('sessionid')fromdual;
USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL>selectuserenv('entryid')fromdual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL>selectuserenv('instance')fromdual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL>selectuserenv('language')fromdual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIEDCHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL>selectuserenv('lang')fromdual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL>selectuserenv('terminal')fromdual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL>selectvsize(user),userfromdual;
VSIZE(USER)USER
-----------------------------------------
6SYSTEM
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS>createtabletable3(xmvarchar(,salnumber(7,2));
语句已处理。
SQLWKS>insertintotable3values('gao',1111.11);
SQLWKS>insertintotable3values('gao',1111.11);
SQLWKS>insertintotable3values('zhu',5555.55);
SQLWKS>commit;
SQL>selectavg(distinctsal)fromgao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL>selectavg(allsal)fromgao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL>selectmax(distinctsal)fromscott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL>selectmin(allsal)fromgao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL>selectstddev(sal)fromscott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL>selectstddev(distinctsal)fromscott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL>selectvariance(sal)fromscott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUPBY
主要用来对一组数进行统计
SQL>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;
DEPTNOCOUNT(*)SUM(SAL)
---------------------------
1038750
20510875
3069400
66.HAVING
对分组统计再加限制条件
SQL>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;
DEPTNOCOUNT(*)SUM(SAL)
---------------------------
20510875
3069400
SQL>selectdeptno,count(*),sum(sal)fromscott.emphavingcount(*)>=5groupbydeptno;
DEPTNOCOUNT(*)SUM(SAL)
---------------------------
20510875
3069400
67.ORDERBY
用于对查询到的结果进行排序输出
SQL>selectdeptno,ename,salfromscott.emporderbydeptno,saldesc;
DEPTNOENAMESAL
----------------------------
10KING5000
10CLARK2450
10MILLER1300
20SCOTT3000
20FORD3000
20JONES2975
20ADAMS1100
20SMITH800
30BLAKE2850
30ALLEN1600
30TURNER1500
30WARD1250
30MARTIN1250
30JAMES950
*SQLGroupFunction
*
s(numcanbeacolumnorex
pression)
(nullvaluesareign
*
ored,defaultbetweendistin
ctandallisall)
********************
***************
****************************
****************
AVG([distinctorall]num)
--averagevalue
COUNT(distinctorall]num)
--numberofvalues
MAX([distinctorall
]num) --maximumvalue
MAX([distinctorall]num)
--minimumvalue
STDDEV([distinctor
all]num) --standarddevi
ation
SUM([distinctorall
]num) --sumofvalues
VARIANCE([distincto
rall]num)--varianceofv
alues
********************************
***********************
************************
*MiscellaneaousFunctions:
*
********************
***************
****************************
****************
DECODE(expr,srch1,
return1[,srch2,return2...]
,default]
--ifnosearchmatchest
heexpressionthenthedefaultisreturned,
--otherwise,
thefirstsearchthatmatch
eswillcause
--thecorres
pondingreturnvaluetober
eturned
DUMP(column_name[,fmt[,start_p
os[,length]]])
--returnsan
column
internaloracleformat,used
forgettinginfoabouta
--formatoptions:8=oc
tal,10=decimel,16=hex,17=characters
--returntype
codes:1=varchar2,2=n
umber,8=long,12=date,
-- 23=raw,
24=longraw,69=rowid,
96=char,106=mlslabel
GREATEST(expr[,expr2[,expr3...]]
--returnsthelargestval
ueofallexpressions
LEAST(expr[,expr2[,expr3...]]
--returnsthe
smallestvalueofallexpre
ssions
NVL(expr1,expr2
--ifexpr1isnotnull,i
tisreturned,otherwiseexpr2isreturned
SQLCODE
--returnssqlerrorcode
query,
oflasterror. Cannotbeuseddirectlyin
--valuemust
besettolocalvariablefir
st
SQLERRM
--returnssql
inquery,
errormessageoflasterror
. Cannotbeuseddirectly
--valuemustbesettolo
calvariablefirst
UID
--returnstheuseridof
theuseryouareloggedonas
--usefulins
electinginformationfromlo
wlevelsystables
USER
--returnsthe
usernameoftheuseryoua
reloggedonas
USERENV('option')
--returnsinf
ormationabouttheuseryou
areloggedonas
--options:E
NTRYID,SESSIONID,TERMINAL,
LANGUAGE,LABEL,OSDBA
-- (
alloptionsnotavailablein
allOracleversions)
VSIZE(expr)
--returnsthenumberofb
ytesusedbytheexpression
--usefulins
electinginformationaboutt
ablespacerequirements
********************
***************
****************************
****************
*SQLDateFunctions(dtreprese
*
ntsoracledateandtime)
*(functionsreturn
*
anoracledateunlessotherw
isespecified)
********************************
***********************
************************
ADD_MONTHS(dt,num)
--addsnummonthsto
dt(numcanbenegative)
LAST_DAY(dt)
--lastdayofmonthin
monthcontainingdt
MONTHS_BETWEEN(dt1,dt2)--retu
dt2
rnsfractionalvalueofmonthsbetweendt1,
NEW_TIME(dt,tz1,tz
zone2
2) --dt=dateintimezo
ne1,returnsdateintime
NEXT_DAY(dt,str) --date
etc..)
offirst(str)afterdt(str='Monday',
SYSDATE --presentsystemdate
ROUND(dt[,fmt] --roun
dsdtasspecifiedbyformatfmt
TRUNC(dt[,fmt]
--truncatesdtasspe
cifiedbyformatfmt
********************************
***********************
************************
*NumberFunctions:
*
********************************
***********************
************************
ABS(num) --absolute
valueofnum
CEIL(num) --smallestinteger>or=num
COS(num) --cosine(n
um),numinradians
COSH(num)
--hyperboliccosine(num)
EXP(num)
--eraisedtothenumpowe
r
FLOOR(num) --largest
integer<or=num
LN(num) --natural
logarithmofnum
LOG(num2,num1) --logarith
mbasenum2ofnum1
MOD(num2,num1) --remainde
rofnum2/num1
POWER(num2,num1)
--num2raisedtothenum1
power
ROUND(num1[,num2] --num1rou
ndedtonum2decimelplaces(default0)
SIGN(num) --signof
num*1,0ifnum=0
SIN(num)
--sin(num),numinradians
SINH(num) --hyperbolicsine(num)
SQRT(num) --squarerootofnum
TAN(num) --tangent(
num),numinradians
TANH(num)
--hyperbolictangent(num)
TRUNC(num1[,num2] --truncate
num1tonum2decimelplaces(default0)
********************************
***********************
************************
*StringFunctions,
*
StringResult:
********************************
***********************
************************
(num) --ASCII
characterfornum
CHR(num)
--ASCIIcharacterforn
um
CONCAT(str1,str2) --str1
concatenatedwithstr2(sameasstr1||str2)
INITCAP(str)
--capitalizefirstlett
erofeachwordinstr
LOWER(str) --strw
ithalllettersinlowercase
LPAD(str1,num[,str2])--left
spaces)
padstr1tolengthnumwithstr2(default
LTRIM(str[,set])
--removesetfromleft
sideofstr(defaultspaces)
NLS_INITCAP(str[,nl
s_val])--sameasinitcapf
ordifferentlanguages
NLS_LOWER(str[,nls_
val]) --sameaslowerfor
differentlanguages
REPLACE(str1,str2[,str3])--r
eplacesstr2withstr3instr1
--
deletesstr2fromstr1ifstr3isomitted
RPAD(str1,num[,str
(defaultspaces)
2]) --rightpadstr1to
lengthnumwithstr2
RTRIM(str[,set])
spaces)
--removesetfrom
rightsideofstr(default
SOUNDEX(str)
--phoneticrepresen
tationofstr
SUBSTR(str,num2[,n
um1]) --substringofstr,
startingwithnum2,
--
omitted)
num1characters(toendofstrifnum1is
SUBSTRB(str,num2[,
bytes
num1]) --sameassubstrbu
tnum1,num2expressedin
TRANSLATE(str,set1,
set2) --replacesset1in
strwithset2
--
truncated
ifset2islongerthanset1,itwillbe
UPPER(str)
--strwithalllett
ersinuppercase
********************
***************
****************************
****************
*StringFunctions,
*
NumericResult:
********************************
***********************
************************
ASCII(str)
--ASCIIvalueofstr
INSTR(str1,str2[,num1[,num2]]
)--positionofnum2thoccurrenceof
--str2instr1,startingatnum1
--(num1,num2defaultto1)
INSTRB(str1,str2[,num1[num2]]
)--sameasinstr,bytevaluesfornum1,num2
LENGTH(str)
--numberof
charactersinstr
LENGTHB(str)
--numberofbytesinstr
NLSSORT(str[,nls_val])
--nls_valbytevalueofstr
********************************
***********************
************************
*SQLConversionFunctions
*
********************************
***********************
************************
CHARTOROWID(str)
--convertsstrtoROWID
CONVERT(str,chr_set2[,chr_set1
])--convertsstrtochr_set2
characterset
--chr_set1
defaultisthedatbase
HEXTORAW(str)
--convertshexstringva
luetointernalrawvalues
RAWTOHEX(raw_val) --convert
srawhexvaluetohexstringvalue
ROWIDTOCHAR(rowid)
--convertsrowidto18ch
aracterstringformat
TO_CHAR(expr[,fmt])
fmt
--convertsexpr(dateorn
umber)toformatspecifiedby
TO_DATE(str[,fmt])
--convertsstringtodat
e
TO_MULTI_BYTE(str) --convert
ssinglebytestringtomultibytestring
TO_NUMBER(str[,fmt])--convert
sstrtoanumberformattedbyfmt
TO_SINGLE_BYTE(str)
--convertsmultibytest
ringtosinglebytestring
********************************
***********************
************************
*SQLDateFormats
*
********************
***************
****************************
****************
BC,B.C. BCindicator
AD,A.D. ADindicator
CC,SCC Cent
uryCode(SCCincludesspace
or-sign)
YYYY,SYYYY 4digityear(SY
YYYincludesspaceor-sign)
IYYY 4digitISOyear
Y,YYY 4digityearwithcomma
YYY,YY,orY last3,2,or1
digitofyear
YEAR,SYEAR yearspelledout
(SYEARincludesspaceor-sign)
RR last2digitsof
yearinpriorornextcentury
Q quarteroryear,1to4
MM month-from01to12
MONTH monthspelledout
MON month3letterabbreviation
RM romannumeralformonth
WW weekofyear,1to53
IW ISOweekofyear
,1to52or1to53
W weekofmonth,1
to5(week1begins1stdayofthemonth)
D dayofweek,1to7
DD dayofmonth,1to31
DDD dayofyear,1to366
DAY dayofweekspel
ledout,ninecharactersrightpadded
DY dayabbreviation
J #of
dayssinceJan1,4712BC
HH,HH12 hourofday,1to12
HH24 hourofday,0to23
MI minuteofhour,0to59
SS secondofminute,0to59
SSSSS seco
ndspastmidnight,0to8639
9
AM,A.M. amindicator
PM,P.M. pmindicator
anypuctuation punc
tuationbetweenformatitems
,asin'DD/MM/YY'
anytext textbetweenformatitems
TH conv
erts1to'1st',2to'2nd',
andsoon
SP converts1to'o
ne',2to'two',andsoon
SPTH converts1to'F
IRST',2to'SECOND',andsoon
FX fill
exact:usesexactpattern
matching
FM fillmode :tog
glessuppressionofblanksinoutput
SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL>selectascii('A')A,ascii('a')a,ascii('0')zero,ascii('')spacefromdual;
AAZEROSPACE
------------------------------------
65974832
2.CHR
给出整数,返回对应的字符;
SQL>selectchr(54740)zhao,chr(65)chr65fromdual;
ZHC
---
赵A
3.CONCAT
连接两个字符串;
SQL>selectconcat('010-','88888888')||'转23'高乾竞电话fromdual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL>selectinitcap('smith')uppfromdual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1被搜索的字符串
C2希望搜索的字符串
I搜索的开始位置,默认为1
J出现的位置,默认为1
SQL>selectinstr('oracletraning','ra',1,2)instringfromdual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL>selectname,length(name),addr,length(addr),sal,length(to_char(sal))fromgao.nchar_tst;
NAMELENGTH(NAME)ADDRLENGTH(ADDR)SALLENGTH(TO_CHAR(SAL))
---------------------------------------------------------------------------
高乾竞3北京市海锭区69999.997
7.LOWER
返回字符串,并将所有的字符小写
SQL>selectlower('AaBbCcDd')AaBbCcDdfromdual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL>selectupper('AaBbCcDd')upperfromdual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD在列的右边粘贴字符
LPAD在列的左边粘贴字符
SQL>selectlpad(rpad('gao',10,'*'),17,'*')fromdual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM删除左边出现的字符串
RTRIM删除右边出现的字符串
SQL>selectltrim(rtrim('gaoqianjing',''),'')fromdual;
LTRIM(RTRIM('
-------------
gaoqianjing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL>selectsubstr('13088888888',3,fromdual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string希望被替换的字符或变量
s1被替换的字符串
s2要替换的字符串
SQL>selectreplace('heloveyou','he','i')fromdual;
REPLACE('H
----------
iloveyou
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL>createtabletable1(xmvarchar();
SQL>insertintotable1values('weather');
SQL>insertintotable1values('wether');
SQL>insertintotable1values('gao');
SQL>selectxmfromtable1wheresoundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s'from'string')
LEADING剪掉前面的字符
TRAILING剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL>selectabs(100),abs(-100)fromdual;
ABS(100)ABS(-100)
------------------
100100
16.ACOS
给出反余弦的值
SQL>selectacos(-1)fromdual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL>selectasin(0.5)fromdual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL>selectatan(1)fromdual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL>selectceil(3.1415927)fromdual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL>selectcos(-3.1415927)fromdual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL>selectcosh(20)fromdual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL>selectexp(2),exp(1)fromdual;
EXP(2)EXP(1)
------------------
7.38905612.7182818
23.FLOOR
对给定的数字取整数
SQL>selectfloor(2345.67)fromdual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL>selectln(1),ln(2),ln(2.7182818)fromdual;
LN(1)LN(2)LN(2.7182818)
-------------------------------
0.69314718.99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL>selectlog(2,1),log(2,4)fromdual;
LOG(2,1)LOG(2,4)
------------------
02
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL>selectmod(10,3),mod(3,3),mod(2,3)fromdual;
MOD(10,3)MOD(3,3)MOD(2,3)
---------------------------
102
27.POWER
返回n1的n2次方根
SQL>selectpower(2,10),power(3,3)fromdual;
POWER(2,10)POWER(3,3)
---------------------
102427
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL>selectround(55.5),round(-55.4),trunc(55.5),trunc(-55.5)fromdual;
ROUND(55.5)ROUND(-55.4)TRUNC(55.5)TRUNC(-55.5)
----------------------------------------------
56-5555-55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL>selectsign(123),sign(-100),sign(0)fromdual;
SIGN(123)SIGN(-100)SIGN(0)
----------------------------
1-10
30.SIN
返回一个数字的正弦值
SQL>selectsin(1.57079)fromdual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL>selectsin(20),sinh(20)fromdual;
SIN(20)SINH(20)
------------------
.91294525242582598
32.SQRT
返回数字n的根
SQL>selectsqrt(64),sqrt(10)fromdual;
SQRT(64)SQRT(10)
------------------
83.1622777
33.TAN
返回数字的正切值
SQL>selecttan(20),tan(10)fromdual;
TAN(20)TAN(10)
------------------
2.2371609.64836083
34.TANH
返回数字n的双曲正切值
SQL>selecttanh(20),tan(20)fromdual;
TANH(20)TAN(20)
------------------
12.2371609
35.TRUNC
按照指定的精度截取一个数
SQL>selecttrunc(124.1666,-2)trunc1,trunc(124.16666,2)fromdual;
TRUNC1TRUNC(124.16666,2)
---------------------------
100124.16
36.ADD_MONTHS
增加或减去月份
SQL>selectto_char(add_months(to_date('199912','yyyymm'),2),'yyyymm')fromdual;
TO_CHA
------
200002
SQL>selectto_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm')fromdual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL>selectto_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')fromdual;
TO_CHAR(SYTO_CHAR((S
--------------------
2004.05.092004.05.10
SQL>selectlast_day(sysdate)fromdual;
LAST_DAY(S
----------
31-5月-04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL>selectmonths_between('19-12月-1999','19-3月-1999')mon_betweenfromdual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd'))mon_betwfromdual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL>selectto_char(sysdate,'yyyy.mm.ddhh24:mi:ss')bj_time,to_char(new_time
2(sysdate,'PDT','GMT'),'yyyy.mm.ddhh24:mi:ss')los_anglesfromdual;
BJ_TIMELOS_ANGLES
--------------------------------------
2004.05.0911:05:322004.05.0918:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL>selectnext_day('18-5月-2001','星期五')next_dayfromdual;
NEXT_DAY
----------
25-5月-01
41.SYSDATE
用来得到系统的当前日期
SQL>selectto_char(sysdate,'dd-mm-yyyyday')fromdual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL>selectto_char(trunc(sysdate,'hh'),'yyyy.mm.ddhh24:mi:ss')hh,
2to_char(trunc(sysdate,'mi'),'yyyy.mm.ddhh24:mi:ss')hhmmfromdual;
HHHHMM
--------------------------------------
2004.05.0911:00:002004.05.0911:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL>selectrowid,rowidtochar(rowid),enamefromscott.emp;
ROWIDROWIDTOCHAR(ROWID)ENAME
----------------------------------------------
AAAAfKAACAAAAEqAAAAAAAfKAACAAAAEqAAASMITH
AAAAfKAACAAAAEqAABAAAAfKAACAAAAEqAABALLEN
AAAAfKAACAAAAEqAACAAAAfKAACAAAAEqAACWARD
AAAAfKAACAAAAEqAADAAAAfKAACAAAAEqAADJONES
43.CONVERT(c,dset,sset)
将源字符串sset从一个语言字符集转换到另一个目的dset字符集
SQL>selectconvert('strutz','we8hp','f7dec')"conversion"fromdual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL>selectto_char(sysdate,'yyyy/mm/ddhh24:mi:ss')fromdual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/0921:14:41
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL>selectto_multi_byte('高')fromdual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL>selectto_number('1999')yearfromdual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insertintofile_tb1values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL>selectsid,serial#,username,decode(command,
20,'none',
32,'insert',
43,
5'select',
66,'update',
77,'delete',
88,'drop',
9'other')cmdfromv$sessionwheretype!='background';
SIDSERIAL#USERNAMECMD
------------------------------------------------------
11none
21none
31none
41none
51none
61none
71275none
81275none
920GAOselect
1040GAOnone
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL>colglobal_namefora30
SQL>coldump_stringfora50
SQL>setlin200
SQL>selectglobal_name,dump(global_name,1017,8,5)dump_stringfromglobal_name;
GLOBAL_NAMEDUMP_STRING
--------------------------------------------------------------------------------
ORACLE.WORLDTyp=1Len=12CharacterSet=ZHS16GBK:W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL>selectgreatest('AA','AB','AC')fromdual;
GR
--
AC
SQL>selectgreatest('啊','安','天')fromdual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL>selectleast('啊','安','天')fromdual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL>showuser
USER为"GAO"
SQL>selectusername,user_idfromdba_userswhereuser_id=uid;
USERNAMEUSER_ID
---------------------------------------
GAO25
58.USER
返回当前用户的名字
SQL>selectuserfromdual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA查看当前用户是否是DBA如果是则返回true
SQL>selectuserenv('isdba')fromdual;
USEREN
------
FALSE
SQL>selectuserenv('isdba')fromdual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL>selectuserenv('sessionid')fromdual;
USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL>selectuserenv('entryid')fromdual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL>selectuserenv('instance')fromdual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL>selectuserenv('language')fromdual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIEDCHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL>selectuserenv('lang')fromdual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL>selectuserenv('terminal')fromdual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL>selectvsize(user),userfromdual;
VSIZE(USER)USER
-----------------------------------------
6SYSTEM
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS>createtabletable3(xmvarchar(,salnumber(7,2));
语句已处理。
SQLWKS>insertintotable3values('gao',1111.11);
SQLWKS>insertintotable3values('gao',1111.11);
SQLWKS>insertintotable3values('zhu',5555.55);
SQLWKS>commit;
SQL>selectavg(distinctsal)fromgao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL>selectavg(allsal)fromgao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL>selectmax(distinctsal)fromscott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL>selectmin(allsal)fromgao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL>selectstddev(sal)fromscott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL>selectstddev(distinctsal)fromscott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL>selectvariance(sal)fromscott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUPBY
主要用来对一组数进行统计
SQL>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;
DEPTNOCOUNT(*)SUM(SAL)
---------------------------
1038750
20510875
3069400
66.HAVING
对分组统计再加限制条件
SQL>selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;
DEPTNOCOUNT(*)SUM(SAL)
---------------------------
20510875
3069400
SQL>selectdeptno,count(*),sum(sal)fromscott.emphavingcount(*)>=5groupbydeptno;
DEPTNOCOUNT(*)SUM(SAL)
---------------------------
20510875
3069400
67.ORDERBY
用于对查询到的结果进行排序输出
SQL>selectdeptno,ename,salfromscott.emporderbydeptno,saldesc;
DEPTNOENAMESAL
----------------------------
10KING5000
10CLARK2450
10MILLER1300
20SCOTT3000
20FORD3000
20JONES2975
20ADAMS1100
20SMITH800
30BLAKE2850
30ALLEN1600
30TURNER1500
30WARD1250
30MARTIN1250
30JAMES950
*SQLGroupFunction
*
s(numcanbeacolumnorex
pression)
(nullvaluesareign
*
ored,defaultbetweendistin
ctandallisall)
********************
***************
****************************
****************
AVG([distinctorall]num)
--averagevalue
COUNT(distinctorall]num)
--numberofvalues
MAX([distinctorall
]num) --maximumvalue
MAX([distinctorall]num)
--minimumvalue
STDDEV([distinctor
all]num) --standarddevi
ation
SUM([distinctorall
]num) --sumofvalues
VARIANCE([distincto
rall]num)--varianceofv
alues
********************************
***********************
************************
*MiscellaneaousFunctions:
*
********************
***************
****************************
****************
DECODE(expr,srch1,
return1[,srch2,return2...]
,default]
--ifnosearchmatchest
heexpressionthenthedefaultisreturned,
--otherwise,
thefirstsearchthatmatch
eswillcause
--thecorres
pondingreturnvaluetober
eturned
DUMP(column_name[,fmt[,start_p
os[,length]]])
--returnsan
column
internaloracleformat,used
forgettinginfoabouta
--formatoptions:8=oc
tal,10=decimel,16=hex,17=characters
--returntype
codes:1=varchar2,2=n
umber,8=long,12=date,
-- 23=raw,
24=longraw,69=rowid,
96=char,106=mlslabel
GREATEST(expr[,expr2[,expr3...]]
--returnsthelargestval
ueofallexpressions
LEAST(expr[,expr2[,expr3...]]
--returnsthe
smallestvalueofallexpre
ssions
NVL(expr1,expr2
--ifexpr1isnotnull,i
tisreturned,otherwiseexpr2isreturned
SQLCODE
--returnssqlerrorcode
query,
oflasterror. Cannotbeuseddirectlyin
--valuemust
besettolocalvariablefir
st
SQLERRM
--returnssql
inquery,
errormessageoflasterror
. Cannotbeuseddirectly
--valuemustbesettolo
calvariablefirst
UID
--returnstheuseridof
theuseryouareloggedonas
--usefulins
electinginformationfromlo
wlevelsystables
USER
--returnsthe
usernameoftheuseryoua
reloggedonas
USERENV('option')
--returnsinf
ormationabouttheuseryou
areloggedonas
--options:E
NTRYID,SESSIONID,TERMINAL,
LANGUAGE,LABEL,OSDBA
-- (
alloptionsnotavailablein
allOracleversions)
VSIZE(expr)
--returnsthenumberofb
ytesusedbytheexpression
--usefulins
electinginformationaboutt
ablespacerequirements
********************
***************
****************************
****************
*SQLDateFunctions(dtreprese
*
ntsoracledateandtime)
*(functionsreturn
*
anoracledateunlessotherw
isespecified)
********************************
***********************
************************
ADD_MONTHS(dt,num)
--addsnummonthsto
dt(numcanbenegative)
LAST_DAY(dt)
--lastdayofmonthin
monthcontainingdt
MONTHS_BETWEEN(dt1,dt2)--retu
dt2
rnsfractionalvalueofmonthsbetweendt1,
NEW_TIME(dt,tz1,tz
zone2
2) --dt=dateintimezo
ne1,returnsdateintime
NEXT_DAY(dt,str) --date
etc..)
offirst(str)afterdt(str='Monday',
SYSDATE --presentsystemdate
ROUND(dt[,fmt] --roun
dsdtasspecifiedbyformatfmt
TRUNC(dt[,fmt]
--truncatesdtasspe
cifiedbyformatfmt
********************************
***********************
************************
*NumberFunctions:
*
********************************
***********************
************************
ABS(num) --absolute
valueofnum
CEIL(num) --smallestinteger>or=num
COS(num) --cosine(n
um),numinradians
COSH(num)
--hyperboliccosine(num)
EXP(num)
--eraisedtothenumpowe
r
FLOOR(num) --largest
integer<or=num
LN(num) --natural
logarithmofnum
LOG(num2,num1) --logarith
mbasenum2ofnum1
MOD(num2,num1) --remainde
rofnum2/num1
POWER(num2,num1)
--num2raisedtothenum1
power
ROUND(num1[,num2] --num1rou
ndedtonum2decimelplaces(default0)
SIGN(num) --signof
num*1,0ifnum=0
SIN(num)
--sin(num),numinradians
SINH(num) --hyperbolicsine(num)
SQRT(num) --squarerootofnum
TAN(num) --tangent(
num),numinradians
TANH(num)
--hyperbolictangent(num)
TRUNC(num1[,num2] --truncate
num1tonum2decimelplaces(default0)
********************************
***********************
************************
*StringFunctions,
*
StringResult:
********************************
***********************
************************
(num) --ASCII
characterfornum
CHR(num)
--ASCIIcharacterforn
um
CONCAT(str1,str2) --str1
concatenatedwithstr2(sameasstr1||str2)
INITCAP(str)
--capitalizefirstlett
erofeachwordinstr
LOWER(str) --strw
ithalllettersinlowercase
LPAD(str1,num[,str2])--left
spaces)
padstr1tolengthnumwithstr2(default
LTRIM(str[,set])
--removesetfromleft
sideofstr(defaultspaces)
NLS_INITCAP(str[,nl
s_val])--sameasinitcapf
ordifferentlanguages
NLS_LOWER(str[,nls_
val]) --sameaslowerfor
differentlanguages
REPLACE(str1,str2[,str3])--r
eplacesstr2withstr3instr1
--
deletesstr2fromstr1ifstr3isomitted
RPAD(str1,num[,str
(defaultspaces)
2]) --rightpadstr1to
lengthnumwithstr2
RTRIM(str[,set])
spaces)
--removesetfrom
rightsideofstr(default
SOUNDEX(str)
--phoneticrepresen
tationofstr
SUBSTR(str,num2[,n
um1]) --substringofstr,
startingwithnum2,
--
omitted)
num1characters(toendofstrifnum1is
SUBSTRB(str,num2[,
bytes
num1]) --sameassubstrbu
tnum1,num2expressedin
TRANSLATE(str,set1,
set2) --replacesset1in
strwithset2
--
truncated
ifset2islongerthanset1,itwillbe
UPPER(str)
--strwithalllett
ersinuppercase
********************
***************
****************************
****************
*StringFunctions,
*
NumericResult:
********************************
***********************
************************
ASCII(str)
--ASCIIvalueofstr
INSTR(str1,str2[,num1[,num2]]
)--positionofnum2thoccurrenceof
--str2instr1,startingatnum1
--(num1,num2defaultto1)
INSTRB(str1,str2[,num1[num2]]
)--sameasinstr,bytevaluesfornum1,num2
LENGTH(str)
--numberof
charactersinstr
LENGTHB(str)
--numberofbytesinstr
NLSSORT(str[,nls_val])
--nls_valbytevalueofstr
********************************
***********************
************************
*SQLConversionFunctions
*
********************************
***********************
************************
CHARTOROWID(str)
--convertsstrtoROWID
CONVERT(str,chr_set2[,chr_set1
])--convertsstrtochr_set2
characterset
--chr_set1
defaultisthedatbase
HEXTORAW(str)
--convertshexstringva
luetointernalrawvalues
RAWTOHEX(raw_val) --convert
srawhexvaluetohexstringvalue
ROWIDTOCHAR(rowid)
--convertsrowidto18ch
aracterstringformat
TO_CHAR(expr[,fmt])
fmt
--convertsexpr(dateorn
umber)toformatspecifiedby
TO_DATE(str[,fmt])
--convertsstringtodat
e
TO_MULTI_BYTE(str) --convert
ssinglebytestringtomultibytestring
TO_NUMBER(str[,fmt])--convert
sstrtoanumberformattedbyfmt
TO_SINGLE_BYTE(str)
--convertsmultibytest
ringtosinglebytestring
********************************
***********************
************************
*SQLDateFormats
*
********************
***************
****************************
****************
BC,B.C. BCindicator
AD,A.D. ADindicator
CC,SCC Cent
uryCode(SCCincludesspace
or-sign)
YYYY,SYYYY 4digityear(SY
YYYincludesspaceor-sign)
IYYY 4digitISOyear
Y,YYY 4digityearwithcomma
YYY,YY,orY last3,2,or1
digitofyear
YEAR,SYEAR yearspelledout
(SYEARincludesspaceor-sign)
RR last2digitsof
yearinpriorornextcentury
Q quarteroryear,1to4
MM month-from01to12
MONTH monthspelledout
MON month3letterabbreviation
RM romannumeralformonth
WW weekofyear,1to53
IW ISOweekofyear
,1to52or1to53
W weekofmonth,1
to5(week1begins1stdayofthemonth)
D dayofweek,1to7
DD dayofmonth,1to31
DDD dayofyear,1to366
DAY dayofweekspel
ledout,ninecharactersrightpadded
DY dayabbreviation
J #of
dayssinceJan1,4712BC
HH,HH12 hourofday,1to12
HH24 hourofday,0to23
MI minuteofhour,0to59
SS secondofminute,0to59
SSSSS seco
ndspastmidnight,0to8639
9
AM,A.M. amindicator
PM,P.M. pmindicator
anypuctuation punc
tuationbetweenformatitems
,asin'DD/MM/YY'
anytext textbetweenformatitems
TH conv
erts1to'1st',2to'2nd',
andsoon
SP converts1to'o
ne',2to'two',andsoon
SPTH converts1to'F
IRST',2to'SECOND',andsoon
FX fill
exact:usesexactpattern
matching
FM fillmode :tog
glessuppressionofblanksinoutput
相关推荐
oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全
oracle 函数大全 参考函数 手册 速查 chm格式。。。
oracle函数大全.chm oracle函数大全.chm
oracle函数大全 oracle函数大全 oracle函数大全
oracle函数大全.doc oracle函数大全.doc oracle函数大全.doc oracle函数大全.doc
Ora9iSQL参考手册 oracle函数大全 分类显示 Oracle函数大全 Oracle函数手册 ORACLE九阴真经 oracle知识库 SQLCodes Oracle错误代码与消息解释 SQL语言参考大全
oracle函数大全.pdf oracle函数大全.pdf
oracle 函数大全
Oracle 函数大全.txt 感谢整理人,给了我们这么好的学习机会。
oracle函数大全,oracle函数大全,oracle函数大全,oracle函数大全,
最全的oracle函数大全,基本包含了所有的oracle函数
自已以前学习的时候收集的! 传上来和大家分享
包含: 1、oracle函数大全.doc 2、oracle函数大全.chm