Oracle内值函数.docx

资源描述

Oracle内值函数.docx

《Oracle内值函数.docx》由会员分享，可在线阅读，更多相关《Oracle内值函数.docx（37页珍藏版）》请在冰点文库上搜索。

Oracle内值函数.docx

Oracle内值函数

一、数学运算函数

ABS（n）绝对值

SELECTabs（-3.12）FROMDUAL;执行结果：

3.12

CEIL（n）无条件进位的整数

SELECTceil（3.12）FROMDUAL;执行结果：

FLOOR（n）无条件舍去的整数

SELECTfloor（3.6）FROMDUAL;执行结果：

MOD（m，n）求m/n整数的余数

SELECTmod（12，5）FROMDUAL;执行结果：

POWER（m，n）返回m的n次方

SELECTpower（2，3）FROMDUAL;执行结果：

ROUND（m[，n]）m四舍五入，取到小数点第n位[ndefault=0]

SELECTround（3.16，1）FROMDUAL;执行结果：

3.2

SQRT（n）返回n开平方根的值

SELECTsqrt（9）FROMDUAL;执行结果：

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;执行结果：

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;执行结果：

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;执行结果：

nCHR（n）返回ascii码n对应的字符，可以获得汉字

SELECTnChr（19968）FROMDUAL;执行结果：

一

Ascii（字符）返回字符的ascii码

SELECTascii（‘A’）FROMDUAL;执行结果：

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;执行结果：

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;执行结果：

INSTR（m，n，[pos]）返回n字[串]在m字符串中的位置

SELECTinstr（'book'，'o'）FROMDUAL;执行结果：

INSTRB（m，n）以byte返回n字[串]在m字符串中的位置

SELECTinstrb（'book'，'k'）FROMDUAL;执行结果：

LENGTH（n）返回字符串（或字段）的长度

SELECTlength（ltrim（'tom'））FROMDUAL;执行结果：

selectlength（'中国c'）,lengthB（'中国c'）fromdual结果为3,5

Selectlength（city）fromcityInfo查询表中所有记录的city字段长度

LENGTHB（n）以byte方式返回字符串的长度

SELECTlengthb（'tom'）FROMDUAL;执行结果：

四、日期函数

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:

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;执行结果：

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;执行结果：

LEAST（m1，m2，m3…….mn）m可为数字或字符串，返回最小值

实例.SELECTleast（3，5，2，8，34）FROMDUAL;执行结果：

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;执行结果：

USER返回用户名称

实例.SELECTuserFROMDUAL;scott

USERENV（m）返回用户环境参数的信息，参数m=ISDBA，LANGUAGE，TERMINAL，SESSIONID，ENTRYID，LANG，INSTANCE，CLIENT_INFO

VSIZE（m）返回字符串的bytes数

实例.SELECTvsize（'book'）FROMDUAL;执行结果：

七、统计函数

AVG（n）返回平均值

实例.SELECTavg（sal）FROMemp;执行结果：

2073.2143

COUNT（n）返回总个数

实例.SELECTcount（*）FROMemp;执行结果：

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:

展开阅读全文