ORACLE 8i常用函数

 

ORACLE 8i 常用函数

     一.              数值（学）型函数

1.         abs(x) 函数

x的绝对值

2.         sign(x) 函数

返回x的正负值

x > 0 ，返回1

x < 0 ，返回-1

x = 0 ，返回0

3.         ceil(x) 函数和 floor(x) 函数

Ø ceil(x)返回大于或等于x的最小整数

select ceil(2.5) from dual;

结果：3

Ø floor(x) 返回小于或等于x的最大整数

select floor(2.5) from dual;

结果：2

4.         三角函数

Ø sin(x) 求x的正弦值

Ø sinh(x) 求x的正弦的16进制

Ø cos(x) 求x的余弦值

Ø cosh(x) 求x的双曲余弦

Ø tan(x) 求x的正切值

Ø tanh(x) 求x的正切的16进制

5.         power(x,y) 函数和 exp(x) 函数

Ø power(x,y)函数

x的y次方

Ø exp(x)函数]

e的x次方

6.         log(x,y) 函数和 ln(x) 函数

Ø log(x,y)函数

以x为底的y的对数 备注：x,y必须均大于“0”

Ø ln (x)函数]

以e为底的y的对数

7.         mod(x,y) 函数

x被y除后的余数

8.         round(x[,y]) 函数和 trunc(x)[,y]) 函数

Ø round(x[,y])函数 四舍五入函数 默认 y=0

y > 0 ，x四舍五入为y为小数

y = 0 ，x四舍五入为整数

y < 0 ，从小数点向左第y位开始四舍五入x

举例:

select round ( 5678.1234,-2 ) from dual;

结果: 5700

trunc(x)[,y])函数同round(x[,y])函数，只是不作四舍五入判断，直接截取不需要的部分

9.         sqrt(x) 函数

返回x的平方根函数

 

     二.              字符型函数

1.         ascii(x) 函数和 chr(x) 函数

Ø ascii(x) 函数

返回x字符的十进制数，即x的ASCII的码值

举例

select ASCII('*' ) from dual

结果 : 42

Ø chr(x)函数

返回 ASCII的码为x的字符

举例

select chr(42) from dual

结果 : *

2.         concat(x,y) 函数

串连接函数可以和 || 一起使用

返回 x = x + y + z

举例

select concat(‘Micro’,’sof’) || ‘t’ from dual;

结果 ：’Microsoft’

3.         initcap(x) 函数

返回串x的每个单词的首字母大写，所有其他字母小写

举例

select initcap(‘micor soFT’) form dual;

结果：Micro Soft

4.         lower(x) 函数

返回串x的所有字母都小写

5.         upper(x) 函数

返回串x的所有字母都大写

6.         instr(w,x[,y[,z]]) 函数和 instrb(w,x[,y[,z]]) 函数

Ø instr(w,x[,y[,z]]) 函数

查找串x在串w中的位置，如不在w串中，返回0.若指定了y，则从第y个字符开始查找；若指定了z，表示查找串x在串w中第z次出现的位置

举例

select instr(‘This is a example,isn’’t it?’,’is’,4,2) from dule;

结果：19

y,z默认值为1

Ø instrb(w,x[,y[,z]])函数用于多字节字符中

7.         length(x) 函数

返回x中的字符数，即字符串的长度

8.         lpad(x[,y[,z]]) 函数和 rpad(x[,y[,z]]) 函数

Ø lpad(x[,y[,z]]) 函数

在串x的左边用串z来填充，直到长度为y时为止

举例

select lpad(‘Very Good!’,30,’OK!’) from dual;

结果：OK! OK! OK! OK! OK! OK! OKVery Good!

Ø rpad(x[,y[,z]])函数

在串x的右边用串z来填充，直到长度为y时为止

9.         ltrim(x[,y]) 函数和 rtrim(x[,y]) 函数

Ø ltrim(x[,y])函数

返回x, x为从左边删除y中字符直到第一个不是y中的字符。缺省时，指的是空格

Ø rtrim(x[,y])函数

返回x, x为从右边删除y中字符直到第一个不是y中的字符。缺省时，指的是空格

10.     replace(x,y[,z]) 函数

返回x,将串x中的y串用z串替换后的结果，若省略z参数，则将串x中为y串的地方删除。

举例

select replace(‘This is an example.’,‘example’,’apple’) form dual;

结果：This is an apple.

11.     soundex(x) 函数

返回串x的语言描述，此描述由四个字符构成，说明串x的第一部分的发音。

举例

select soundex(‘Smith’) from dual;

结果：S530

12.     substr ( x [,y [,z ] ] ) 函数和 substrb ( x[,y [,z ] ] ) 函数

Ø substr ( x [,y [,z ] ] ) 函数

返回串x中从第y个字符开始到第z个字符结束的字符串。若不指定z，则返回从第y个字符知道结束的子串。

例如

select substr(‘This is an example’,12,4) from dual;

结果：exam

Ø substrb ( x[,y [,z ] ] )函数

在多字符时运用

13.     translate(x,y,z) 函数

返回将x串中每个字符按它在y串中出现的位置翻译成z串中对应位置的字符后的结果。

举例

select translate(‘This is an example’,’my is’, ‘*****’) from dual;

结果：Th****** an*exa*ple

     三.              日期型函数

1.         add_months(x,y)函数

返回在日期x的基础上再加上y个月后新的日期。

举例：

select add_months(sysdate,3) from dual;

结果： 2005-4-17

2.         last_day(x)函数

包含x的月份的最后一天的日期

3.         months_between(x,y)函数

日期x与y之间的月数，y先于x

sysdate 为2005-01-18

举例

select months_between ( sysdate, to_date ( '20050418' , 'yyyy-mm-dd' ) ) from dual

结果： -3

select months_between ( sysdate, to_date ( '20050417' , 'yyyy-mm-dd' ) ) from dual

结果：-2.95300440561529

4.         new_time(x,y,z)函数（不常用）

返回y时区的日期和时间x在z时区的日期和时间

5.         next_day(x,y) 函数

返回日期x之后第一个名为y的那天的日期

举例 sysdate 为 2005-01-18

select next_day(sysdate,4 ) from dual;

结果

2005-01-19

6.         greatest(d1[,d2,...dn]) 

给出的日期列表中最后的日期

举例 sysdate 为 2005-01-18

select greatest ( sysdate, add_months (sysdate,3 ) , add_months ( sysdate,-2 ) )    from dual

结果： 2005-4-18

备注：此函数也可用于非日期的比较

举例

select greatest('aa','bb',22.5) from dual

结果：bb

7.         least(d1[,d2,...dn])函数

给出的日期列表中最小的日期

举例 sysdate 为 2005-01-18

select least ( sysdate, add_months (sysdate,3 ) , add_months ( sysdate,-2 ) )    from dual

结果： 2005-11-18

备注：此函数也可用于非日期的比较

举例

select least ('aa','bb',22.5) from dual

结果：22.5

8.         sysdate 函数

取系统当前日期

     四.              转换型函数

1.         chartorowid(x) 函数和 rowidtochar(x) 函数

Ø chartorowid(x) 函数

将字符串转换成一个rowid类型的值，格式必须按照rowid数据类型格式

Ø rowidtochar(x) 函数

将rowid数据类型转换成字符串

2.         convert(x,y[,z]) 函数

用字符集y来转换字符串x，参数z来说明原来的字符集。

3.         hextoraw(x) 函数和 rawtohex(x) 函数

Ø hextoraw(x)函数

将16进制字符串转换成二进制数据

Ø rawtohex(x) 函数

将二进制字符串转换成16进制数据

4.         to_char(x[,y]) 函数

将日期型函数转换成字符串，也可用于格式化字符串输出，将串x按照格式y的形式转换输出

to_char()函数的格式：

---------------------------------

符号    说明

---------------------------------

9       每个9代表结果中的一位数字

0       代表要显示的先导0

$       美元符号打印在数的左边

L       任意的当地货币符号

.       打印十进制的小数点

,       打印代表千分位的逗号

---------------------------------

举例

select to_char(sysdate, ‘yyyy/mm/dd’) from dual

结果：2005/01/18

 

select to_char(987654321) from dual

结果：987654321

 

select to_char (123 , '$9,999,999' ) a, to_char (54321 , '$9,999,999' ) b, to_char ( 9874321 , '$9,999,999' )  c  from  dual

结果：

a          b             c

-------  ----------   -----------

$123     $54,321     $9,874,321

 

select to_char ( 1234.1234 , '999,999.999' ) a , to_char ( 0.4567 , '999,999.999' ) b , to_char ( 1.1 , '999,999.999' ) from dual

a              b            c

---------    ----------   ------------

1,234.123 .457         1.100

5.         to_date(x[,y]) 函数

把一个字符串转换成日期，其格式须由y参数指定，系统不会自动识别

举例

select to_date(‘2005,3,22’,’yyyy-mm-dd’) from dual;

结果：2005-03-22

6.         to_label(x[,y]) 函数

按格式y将字符串x转换成MLSLABEL类型的一个值，若缺省y，则按缺省格式进行转换

7.         to_number(x,y[,z]) 函数

将字符串x转换成一个数字型值，其格式为转换成一个数字型值，其格式为y。若缺省格式说明，则按缺省格式来做。

举例

select to_number( '011' , '999' ) from dual;

结果：11

8.         to_multi_byte(x,y[,z]) 函数和 to_single_byte(x,y[,z]) 函数

Ø to_multi_byte(x,y[,z]) 函数

将单字节字符的字符串转换成多字节字符的字符串

Ø to_single_byte(x,y[,z]) 函数

将多字节字符的字符串转换成单字节字符的字符串

     五.              分组函数

1.         avg([distinct | all]x) 函数和 sum([distinct | all]x) 函数

Ø avg([distinct | all]x)函数

用来求x列的平均值，当指定distinct参数时，相等的行作为一行；若指定了all参数，则计算所有行的平均值。系统默认all

举例

select avg(HIGH) ‘avg-high’ from persons,check_body where persons.no = check_body.no and persons.sex=’M’

结果：

avg-high

171.5632

Ø sum([distinct | all]x)函数

用来求x列的和

2.         stddev([distinct | all]x) 函数和 variance([distinct | all]x) 函数

Ø stddev([distinct | all]x)函数

计算列x的标准误差

Ø variance([distinct | all]x)函数

计算列x的方差

3.         count(* | [distinct | all]) 函数

返回查询所得到的行数，若用*作参数，则返回满足条件的所有行，包括值为NULL的列

若指定列x，则查找列x值不为NULL行数

4.         glb([distinct | all]x) 函数和 lub([distinct | all]x) 函数

Ø glb([distinct | all]x)函数

返回标签x的最大上限

Ø lub([distinct | all]x)函数

返回标签x的最小上限

5.         max([distinct | all]x) 函数和 min([distinct | all]x) 函数

Ø max([distinct | all]x)函数

求列x的最大值

Ø min([distinct | all]x)函数

求列x的最小值

     六.              其他函数

1.         dump(w,x[,y[,z]]) 函数

此函数参数具体意义如下：

dump(expr [ , return_format [ , start_position [, length]]])

返回字符串expr的数据类型、字节长度和在内部的存储位置

举例

select dump(cla_id,10) from dual;

结果

type=2 len=2: 193,2

2.         nvl(x,y) 函数

当x为空(NULL)时，则返回y的值，否则返回x的值

3.         user 函数和 uid 函数

Ø user函数

返回当前用户的数据库用户名

Ø uid函数

返回唯一标识当前用户的整数

                            上面两个函数在完整性约束检查时会用到

                            举例

                            select user,uid from dual;

                            结果 wangzixi 45

4.         userenv(x) 函数

返回当前会话的一些信息，由x指定返回何种信息。

在写一个指定应用的审计测试表或决定为当前回话指定何种语言时会用到，但在完整性约束时不能用。

参数列表

会话参数	说 明
Entryid	返回有效地审计条目标识
Label	返回当前会话的标签
Language	以“语言.字符集”形式返回所用的语言和字符集
Sessionid	返回正在使用的审计会话号
terminal	返回当前会话终端所用的操作系统 或计算机返回操作系统的标示符
CLIENT_INFO	返回登陆的用户名

 

5.         decode(e,s1,t1[,s2,t2]…default) 函数

此函数实现了PL/SQL程序控制语句if … else if … end if的功能

举例

select NAME,decode ( SEX,’m’,’is man’, ‘w’,’is woman’) SEX from dual WHERE NAME IN (‘Tom’,’Mary’,’Smith’)

结果

NAME               SEX

Tom                              is man

Mary                             is woman

Smith                            is man