Oracle内值函数.docx
- 文档编号:11086656
- 上传时间:2023-05-29
- 格式:DOCX
- 页数:37
- 大小:35.03KB
Oracle内值函数.docx
《Oracle内值函数.docx》由会员分享,可在线阅读,更多相关《Oracle内值函数.docx(37页珍藏版)》请在冰点文库上搜索。
Oracle内值函数
Oracle内值函数
一、数学运算函数
ABS(n)绝对值
SELECTabs(-3.12)FROMDUAL;执行结果:
3.12
CEIL(n)无条件进位的整数
SELECTceil(3.12)FROMDUAL;执行结果:
4
FLOOR(n)无条件舍去的整数
SELECTfloor(3.6)FROMDUAL;执行结果:
3
MOD(m,n)求m/n整数的余数
SELECTmod(12,5)FROMDUAL;执行结果:
2
POWER(m,n)返回m的n次方
SELECTpower(2,3)FROMDUAL;执行结果:
8
ROUND(m[,n])m四舍五入,取到小数点第n位[ndefault=0]
SELECTround(3.16,1)FROMDUAL;执行结果:
3.2
SQRT(n)返回n开平方根的值
SELECTsqrt(9)FROMDUAL;执行结果:
3
dbms_random.value(low,high)
产生low—high之间的随机浮点数,重复的几率很小。
如果要产生整数,需要配合取整函数
例如:
selecttrunc(dbms_random.value(5,100))fromdual;返回5,100之间的整数
dbms_random.random产生随机整数,重复的几率很小。
例如:
下列语句能够对表xhy的结果集进行随机排序。
Selectdbms_random.random,b.*fromxhyborderby1
Selectb.*fromxhyborderbydbms_random.random
ACOS(n)逆三角余弦值
SELECTACOS(0.5)FROMDUAL;执行结果:
1.04719756
ATAN(n)逆三角正切值(径,p/2~-p/2)
SELECTATAN(0.5)FROMDUAL;执行结果:
0.46364761
ATAN2(m,n)逆三角正切值(径,p~-p)
ATAN2(0.5,0.3)=1.03037682652
COS(n)返回n的余弦值
SELECTcos(30*3.141592653589/180)FROMDUAL;执行结果:
0.866
COSH(n)返回n的双曲线余弦值
SELECTcosh(0.5)FROMDUAL;
EXP(n)返回自然对数e的n次方值
EXP(5)=148.413159103
LN(n)自然对数
SELECTln
(2)FROMDUAL;执行结果:
0.69315
LOG(m,n)m进位的n对数
SELECTlog(10,2)FROMDUAL;执行结果:
0.30103
SIGN(n)信号的返回,n<0则-1,n=0则0,n>0则1
SELECTsign(0.5)FROMDUAL;执行结果:
1
SIN(n)返回n的正弦值
SELECTsin(30*3.141592653589/180)FROMDUAL;执行结果:
0.5
SINH(n)返回n的双曲线的正弦值
SELECTsinh
(2)FROMDUAL;执行结果:
3.6268604
TAN(n)返回n的正切值
SELECTtan(45*3.141592653589/180)FROMDUAL;执行结果:
1
TANH返回n的双曲线的正切值
SELECTtanh
(2)FROMDUAL;执行结果:
0.96402758
TRUNC(m[,n])将m截断到n位[ndefault=0],n=-1-1表示十位,截断个位为0
SELECTtrunc(15.79,1)FROMDUAL;执行结果:
15.7
GREATEST返回一组表达式中的最大值,即比较字符的编码大小.
selectgreatest(’AA’,’AB’,’AC’)fromdual;返回AC
selectgreatest(’啊’,’安’,’天’)fromdual;返回天
selectgreatest(1,9,5)fromdual返回9;
LEAST返回一组表达式中的最小值
selectleast(’啊’,’安’,’天’)fromdual;返回啊
selectleast(1,9,-7,5)fromdual返回-7;
Trunc(x,n)返回x取n位的数值(不进行任何舍入)
例如:
trunc(15.79,1)返回:
15.7
Round(x,n)返回x取n位的数值,并进行四舍五入。
例如:
round(15.79,1)返回:
15.8
二、返回类型为字符串的函数
CHR(n)Chr十进制值,返回ascii码
SELECTchr(65)FROMDUAL;执行结果:
A
nCHR(n)返回ascii码n对应的字符,可以获得汉字
SELECTnChr(19968)FROMDUAL;执行结果:
一
Ascii(字符)返回字符的ascii码
SELECTascii(‘A’)FROMDUAL;执行结果:
65
CONCAT(m,n)不论数字或数字,都以字符串方式相加
SELECTconcat('abc',123)FROMDUAL;执行结果:
abc123
INITCAP(n)将句子n的每一个单词的首字母改为大写
SELECTinitcap('thisisabook')FROMDUAL;执行结果:
ThisIsABook
LOWER将字符串改为小写
SELECTlower('ThisIsABook')FROMDUAL;执行结果:
thisisabook
UPPER(n)将n中的每个字符转为大写
SELECTupper('ThisIsABook')FROMDUAL;执行结果:
THISISABOOK
LPAD(m,n,z)以n减length(m)的z字符数填入m的左边
SELECTlpad('
(1)',8,'-')FROMDUAL;执行结果:
-----
(1)
LTRIM(n)去除字符串左边的空格字符
SELECTltrim('tom')FROMDUAL;执行结果:
tom
RTRIM(n)去除字符串右边的空格字符
SELECTrtrim('tom')FROMDUAL;执行结果:
tom
TRIM(n)去除字符串左、右边的空格字符
SELECTlength(trim('tom'))FROMDUAL;执行结果:
3
Trim([options]aCharfromstring)
Options可取下列关键字:
leading,trailing,both(缺省)分别表示去掉字符串前面
或后面或前面和后面的所有字符aChar
例1:
selectTrim(‘A’from‘AEastAA’)fromdual;返回East
例2:
selectTrim(leading‘A’from‘AEastAA’)fromdual;返回EastAA
例3:
selectTrim(trailing‘A’from‘AEastAA’)fromdual;返回Aeast
RPAD(m,n,z)以n减length(m)的z字符数填入m的右边
SELECTrpad('tom',8,'*')FROMDUAL;执行结果:
tom*****
NLS_INITCAP(m[,n])返回字句一个字为大写(依照国家语言不同)
NLS_LOWER返回每一字为小写(依照国家语言不同)
NLSSORT决定排列顺序(依照国家语言不同)
NLS_UPPER返回每一字为大写(依照国家语言不同)
REPLACE(m,n[,z])M字符串中去除n字[串]以z字[串]取代
SELECTreplace('thididabook','d','s')FROMDUAL;执行结果:
thisisabook
SOUNDEX(m)返回以字符串m声音.(可做文字发音比较)
SELECTenameFROMempWHEREsoundex(ename)=soundex('MILER');
执行结果:
MILLER
SUBSTR(m,n,z)m字符串中从头n数字开始取,取z个字符
SELECTsubstr('thisisabook',11,4)FROMDUAL;执行结果:
book
SUBSTRB(m,n,z)以byte方式,在字符串m中从头n数字开始取,取z个字符
SELECTsubstrb('thisisabook',-4,4)FROMDUAL;执行结果:
book
TRANSLATE(m,n[,z])以n字符串寻找m字符串,以z字符串取代
SELECTtranslate('abcdefg','cde','ab')FROMDUAL;执行结果:
ababfg
NVL(k,v)如果k为空值,则返回v,否则,返回k
NVL2(k,v1,v2)如果k为空值,则返回v2,否则,返回v1
Decode(表达式|列名,数据值1,显示值1,数据值2,显示值2,。
。
。
defaultValue)
当表达式或列名与数据值n匹配时,返回显示值n,如果没有任何匹配,则返回defualtValue值
Selectname,decode(sex,‘1’,‘男’,‘2’,‘女’,’不明’)
Case函数有两种形式:
形式一:
Case表达式或列名
When值1thenreturn_值1
When值2thenreturn_值2
……
Elseelse_值
End
当表达式或列名与值n匹配时,返回return_值n
该表达式的功能与函数Decode()的功能相同
形式二:
Case
When条件1then值1
When条件2then值2
……
When条件nthen值n
Elseelse_值
End
当条件n满足时,返回值n,否则,返回else_值,相当于java语言的case语句功能
三、返回数值的字符函数
ASCII(n)返回字符的Ascii的十进制值
SELECTascii('A')FROMDUAL;执行结果:
65
INSTR(m,n,[pos])返回n字[串]在m字符串中的位置
SELECTinstr('book','o')FROMDUAL;执行结果:
2
INSTRB(m,n)以byte返回n字[串]在m字符串中的位置
SELECTinstrb('book','k')FROMDUAL;执行结果:
4
LENGTH(n)返回字符串(或字段)的长度
SELECTlength(ltrim('tom'))FROMDUAL;执行结果:
5
selectlength('中国c'),lengthB('中国c')fromdual结果为3,5
Selectlength(city)fromcityInfo查询表中所有记录的city字段长度
LENGTHB(n)以byte方式返回字符串的长度
SELECTlengthb('tom')FROMDUAL;执行结果:
6
四、日期函数
ADD_MONTHS(d,n)返回日期d加上n个月的日期,n可以为负整数
例.假设当前日期为2008-11-10,则下列语句将返回2009-3-10
SELECTadd_months(sysdate,4)FROMDUAL;执行结果:
2009-3-10
LAST_DAY(d)返回该月的最后一天的日期
例.假设当前日期为2009-2-10,则下列语句将返回2009-2-28
SELECTlast_day(sysdate)FROMDUAL;执行结果:
2009-2-28
MONTHS_BETWEEN(m,n)返回两个日期之间的月份数
例.
SELECTmonths_between(sysdate,to_date('2009-11-10','yyyy-mm-dd'))FROMDUAL;
;执行结果:
2.912
NEW_TIME(m,n,z)n时区的日期、时间是m,返回在z时区的日期时间
实例.SELECTnew_time(sysdate,'CST','PST')FROMDUAL;
NEXT_DAY(m,week)返回日期m后的由week指定的星期几所对应的日期。
week可以取文字串:
’星期二’,’星期三’,。
。
。
’星期日’
week也可以取数字:
1-表示星期日,2—表示星期一,7---表示星期六
例:
假设当前日期是2010-2-7星期日.,那么,下列语句将返回:
2010-2-86:
59:
18
SELECTnext_day(sysdate,'星期一')FROMDUAL;
ROUND(d)日期四舍五入以中午为界。
实例.假设当前日期是2010-2-7上午9点,则
SELECTround(sysdate)FROMDUAL;返回2010-2-7
假设当前日期是2010-2-7下午17点,则
SELECTround(sysdate)FROMDUAL;返回2010-2-8
SYSDATE返回系统当前日期2010-2-7
实例.SELECTsysdateFROMDUAL;
TRUNC(d[,n])将d截断到n,n取值如下:
’yyyy’表示取整到当前年1月1日
’mm’表示取整到当前月1日
’dd’,表示取整到当前日
n缺省为’dd’
selectsysdate,trunc(sysdate),trunc(sysdate,'mm'),trunc(sysdate,'yyyy')fromdual
实例.SELECTtrunc(sysdate)FROMDUAL;执行结果:
07-NOV-00
Extract([year]|[month]|[day]From日期表达式)
返回日期表达式中的某部分year|month|day的整数值
SelectExtract(yearfromsysdate),
Extract(monthfromsysdate),
Extract(dayfromsysdate),
to_char(sysdate,'hh24')—返回小时部分
fromdual
Selectextract(monthfromsysdate)fromdual返回数据库服务器的当前月份
五、类型转换函数
ASCIISTR(x)将字符类型数据x转换为一个ASCII串,字符串x中的ASCII字符保持
不变,而其中的非ASCII字符则转换为ASCII。
SelectASCIISTR(‘Oracle数据库’)fromdual;
显示结果:
Oracle\6570\636E\5E93
CAST(xASType_name)
将x的值从一种数据类型转换为由type_name指定的类型
SelectCAST(to_char(’08-8-2010’,’dd-mm-yy’)ASvarchar2(15))fromdual;
显示结果:
08-8-2010
CHARTOROWID将字符类型转为ROWID类型
实例.SELECTenameFROMempWHERErowid=hartorowid('AAACqqAACAAAAEHAAN');执行结果:
MILLER
CONVERT(m,n,z)将字符串转换另一国家字符设定值
实例.SELECTconvert('Grθe','US7ASCII','WE8HP')FROMDUAL;执行结果:
GrEce
HEXTORAW(m)将十六进制转换为原始值
实例.SELECThextoraw('41')FROMDUAL;
RAWTOHEX(m)将原始值(例如.A=65)进位转换为十六进制
实例.SELECTrawtohex('A')FROMDUAL;执行结果:
41
ROWIDTOCHAR(m)将ROWID类型转为字符类型
实例.SELECTenameFROMempWHERErowidtochar(rowid)='AAACqqAACAAAAEHAAN';执行结果:
MILLER
TO_CHAR(d,t)将日期转换为字符串,d=日期,t=日期类型格式
实例.SELECT'今日是'||to_char(sysdate,'yyyy/mm/dd')FROMDUAL;执行结果:
今日是2000/11/07
日期类型格式说明:
YYYY表示年份,MM表示月份
DD表示一月中的第几天,DDD表示一年中的第几天
HH表示12小时制,HH24表示24小时制
MI表示分钟SS表示秒
TO_CHAR(n)将数字格式转换为字符串
TO_CHAR(n,fmt)将数字按格式转换为字符串
Fmt常用格式及其含义:
0前导0,例如selectTO_CHAR(12,’0000’)fromdual输出0012
9指定数据位数,不足前面补空格。
例如selectTO_CHAR(12,’999’)fromdual输出‘12’
实例.SELECTto_char(123)||'省时省力'FROMDUAL;
TO_DATE(m,t)将字符串转换为日期,m=字符串,t=日期类型格式
实例.SELECTto_date('2000/11/07','yyyy/mm/dd')FROMDUAL;执行结果:
07-NOV-00
TO_LOB(m)将LONG的数据类型转换成LOB的数据类型
TO_MULTI_BYTE(m)将CHAR的数据类型转换成多位的字体
TO_NUMBER(m)将字符串转成数字
实例.SELECTto_number('123')+2FROMDUAL;
TO_SINGLE_BYTE(m)将宽字符转换成CHAR
TRANSLATE(musingn)将char转成nchar
实例.SELECTtranslate('m'usingn)FROMDUAL;
例:
selectto_char(128,'XX')fromdual返回十六进制数80
可以使用格式串X/x将十进制数转换为十六进制字符串
六、其它单个记录用的函数
BFILENAME(m,n)返回外部文件值,m=目录字符串,n=文件字符串
实例.insertintotest(a1,a2)values(1,bfilename('c:
\document','dc001.bmp'));
DUMP(m,n,n1,n2……)返回m的长度及内码,n=进位数(默认值=10进位),n1=m的开始位置,n2=取的位数
实例.SELECTdump('a')FROMDUAL;
DECODE(c|n,v1,r1,v2,r2d1)变量c字符串或n数字,=v1则值=r1;=v2则值=v2,或=d1
实例.SELECTdecode('a','1','123','a','abc','123abc')FROMDUAL;执行结果:
abc
EMPTY_[B|C]LOB将BLOB或CLOB初始化,就是返回BLOB或CLOB的空指针
实例.Updatetabletestsetclob_col=empty_clob()WHERErownum=1
GREATEST(m)m可为数字或字符串,返回最大值
实例.SELECTgreatest(3,5,2,8,34)FROMDUAL;执行结果:
34
LEAST(m1,m2,m3…….mn)m可为数字或字符串,返回最小值
实例.SELECTleast(3,5,2,8,34)FROMDUAL;执行结果:
2
NLS_CHARSET_DECL_LEN(m,n)返回所要的字符集对应当前数据库的字符集,m=字体数,n=字符集(如:
'ZHT16BIG5')
NLS_CHARSET_ID(m)返回字符集的ID,m=字符集
实例.SELECTNLS_CHARSET_ID('ZHT16BIG5')FROMDUAL;
NLS_CHARSET_NAME(n)返回字符集,n=字符集的ID
实例.SELECTNLS_CHARSET_NAME(865)FROMDUAL;
NVL(m,n)m为NULL则返回n值,否则返回m值
实例.SELECTnvl(NULL,'5')FROMDUAL;
SYS_CONTEXT(m,n)返回与某一个context名称关联的Packagespec定义的变量的值,m=context的名称,n=变量名称
SYS_GUID返回全球独一无二的ID
UID返回目前的用户的唯一ID
实例.SELECTuidFROMDUAL;执行结果:
26
USER返回用户名称
实例.SELECTuserFROMDUAL;scott
USERENV(m)返回用户环境参数的信息,参数m=ISDBA,LANGUAGE,TERMINAL,SESSIONID,ENTRYID,LANG,INSTANCE,CLIENT_INFO
VSIZE(m)返回字符串的bytes数
实例.SELECTvsize('book')FROMDUAL;执行结果:
4
七、统计函数
AVG(n)返回平均值
实例.SELECTavg(sal)FROMemp;执行结果:
2073.2143
COUNT(n)返回总个数
实例.SELECTcount(*)FROMemp;执行结果:
14
GROUPING(n)返回群集
实例.
MAX(n)返回最大值
实例.SELECTmax(sal)FROMemp;执行结果:
5000
MIN(n)返回最小值
实例.SELECTmin(sal)FROMemp;执行结果:
800
STDDEV(n)返回标准差
实例.SELECTstddev(sal)FROMemp;执行结果:
1182.5032
SUM(n)返回加总
实例.SELECTsum(sal)FROMemp;执行结果:
29025
VARIANCE(n)返回变异量
八、按汉字的拼音排序函数
Oracle9i之前,中文是按照二进制编码进行排序的。
现在可以使用NLSSORT函数进行如下三种方式的排序。
语法:
NLSSORT(expression,sortFlag);
参数说明:
Expression:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 函数