ORACLE内置函数使用手册
 
ASCII()--返回字符的ASCCII码;
--------------------------------------------------------------------------------
CHAR()--当给定ASCII码返回字符;
--------------------------------------------------------------------------------
concat()--连接函数,相当于运算符“||”
e.g:select CONCAT(CONCAT(name,'is a'),job) "Job" From emp
result:James is a recorder
---------------------------------------------------------------------------------
initcap()-返回函数内的单词,每个词首字母大写,其他字母转化为小写,不影响非字母字符;
e.g:select INITCAP('the emp') Form Emp
result:The Emp
---------------------------------------------------------------------------------
INSTR(char1,char2[,n[,m]])--返回char1中包含char2的位置。n为负数时,从右往左查,第m次的结果被返回,返回其下标,缺省值都是1,若没找到,返回0。
e.g: select INSTR('CORPORATE FLOOR','OR',3,2) from table
result:14
select INSTR('CORPORATE FLOOT','OR',-3,2) FROM TABLE
RESULT:2
INSTRB类似INSTR,区别于,对于单字节字符系统,返回的值以字节为单位;

---------------------------------------------------------------------------------
LENGTH(CHAR)--以字节返回字符串char的长度,包括填充的字符。若char有数据型char,则字符串的长度包括其结尾的空格。若char为NULL,则该函数返回NULL;
LENGTHB()--类似LENGTH(),区别于,对于单字节字符集,返回的值以字节为单位;
---------------------------------------------------------------------------------
lower()--转化小写
e.g:select lower('HELLO') from table
result:hello
---------------------------------------------------------------------------------
lpad(char1,length[,char2])--char1左边增长到length,左边补char2。char2未设定时,默认空格,当char2小于length时,仅前几个被复制
例:select lpad('hugo',10,'*#') from table 
result:*#*#*#hugo
---------------------------------------------------------------------------------
LTRIM('char [,set])--删除char左边出现的set字符。
例:Select ltrim('abaabhi','ab') 运行返回:aabhi
---------------------------------------------------------------------------------
NLS_INITCAP(char[,'nlsparams']),和INITCAP相似,其中的NLS参数为大写特殊语言学NLSSORT顺序进行分类;
e.g:select NLS_INITCAP('ijsland','NLS_SORT=XDutch')
---------------------------------------------------------------------------------
NLS_LOWER和NLS_UPPER(CHAR[,'NLSPARAMS'])参数和上面相同
e.g:select nls_uppper('girl','NLS_SORT = XGerman')
---------------------------------------------------------------------------------
REPLACE(char,search_string[,replacement_string])--返回用replacement_string字符串以后的char。若replacement_string没有指示,则可search_string将被删除。
e.g:select replace('back and Jue','b','bl') from talbe
result: black and Jue
---------------------------------------------------------------------------------
RPAD(char1,n [,char2])相似于LPAD,它的右边将会被char2填充
e.g:select rpad('BEAUTY',10,'XY') FROM TABLE
Result:BEAUTYxyxy
---------------------------------------------------------------------------------
RTRIM(char[,set])类似LTRIM,它从右边删除
---------------------------------------------------------------------------------
SOUNDEX(CHAR)返回一个包含声音的字符串,该函数允许比较两个英语拼写不同、但发音类似的单词,所比较的两个词必须以同样的字母开头
e.g:select name form emp where soundex(name)=soundex('Smythe')
result:SMITH
--------------------------------------------------------------------------------
SUBSTR(CHAR,M,[n])返回char中字母位置m开始的n个字符。若M=0,则M当1看待,若M为正数,从左到右算,反之,从右往左数。若n缺省,则设置为整个字符串,若N小于1,则返回null,或M、n用了浮点型,则被转化为整型。
e.g:select substr('abcdefg',3.1,4) from table
result:cdef
--------------------------------------------------------------------------------
SUBSTRB(char,M,[n])类似substr,不过参数m,n用字节表示。
e.g:select substrb('abcdefg',5,4.2) from table
result:cd
--------------------------------------------------------------------------------
TRANSLATE(char,from,to)--和REPLACE相同,只是在字符基础上,而不是字符串基础上进行操作;
--------------------------------------------------------------------------------
TRIM--删除指定字符串中前面或后面的字符,这与同时使用LTRIM和RTRIM函数等价;
e.g:SELECT TRIM (0 FROM 0952700) FROM dual; --删除前部及尾部的0
--------------------------------------------------------------------------------
NLSSORT(char[,'nlsparams'])返回用于排序char的字符串字节。所有的数值都被转换为字节字符串,这样在不同的数据库字符集之间就保持了一致性。NLlparams值应该是:'NLS_SORT =SORT'的形式。
e.g:select name from emp where NLSSORT(name,'NLS_SORT=German')>NLSSORT('S','NLS_SORT=German') Order by Name;
--------------------------------------------------------------------------------
UPPER()--将整个字符串转换为大写,不影响非字母字符;
====================================数值函数=============================
--------------------------------------------------------------------------------
ABS(n)返回N的绝对值,通常为正值,N必须是数字。它是常量数字、数字列名,包含一个合法数字的常量字符串或只包含一个合法数字的字符列
-----------------------------------------------------------------------------
CEIL(N)返回大于或等于X的最小整数值
e.g:select CEIL(11.8) from table
result:12
-----------------------------------------------------------------------------
EXP(x)--求e的X次方,其中的e=2.7182818...
------------------------------------------------------------------------------
FLOOR(N)返回小于或等于X的最大整数值
e.g:select FLOOR(11.8) from table
result:11
------------------------------------------------------------------------------
LN()--返回自然对数;
------------------------------------------------------------------------------
MOD(M,N)返回M 除以N的余数
e.g:select MOD(10,4) from table
result:2
------------------------------------------------------------------------------
ROUND(N[,M])返回传入小数点右边m位的n值。m是一个整数,缺省值为0。负整数取舍给定数值的小数点的左边部份,正整数则舍取小数点的右边部分
e.g:select ROUND(15.193,1) from pubs
result:15.2
e.g:select ROUND(15.193,-1) from pubs
result:20
-------------------------------------------------------------------------------
SIGN(n)如果n为正,SIGN(n)返回1,如果负数返回-1,如果0,返回0
-------------------------------------------------------------------------------
TRUNC(m[,n])--返回到N位小数的M的值。n缺省值为0,结尾为一个整数值,如果n为负数,则结尾到小数点左边相应的位置上
 e.g:  select TRUNC(15.79,1) from pubs
 result:15.7
e.g: select TRUNC(15.79,-1) FROM pubs
result:10
===================================日期函数======================================
ADD_MONTHS(D,N)返回日期D加上N个月后的结果。如果N个月后的日期中不存在D中的日期,则返回该月的月末日期
e.g:select TO_CHAR(ADD_MONTHS(hiredate,1),'dd-mon-yyyy') from table
-----------------------------------------------------------------------------
LAST_DAY(D)给出d所在月的最后一天的日期。该函数可以用来确定当前月份还剩多少天,该函数对于为财会部门编写程序时非常有用;
e.g:select last_day(sysdate)-sysdate from table
result:返回当月还剩几天
e.g:select TO_CHAR(ADD_MONTHS(LAST_DAY(hiredate),5),'dd-mon-yyyy') from table where name='A'
result:返回A的第五个雇用月
-----------------------------------------------------------------------------
MONTHS_BETWEEN(D1,D2)返回D1、D2之间的月份值。若D1<D2,则结果正,反之,结果负。若两个日期同一天或都是所在月的最后一天,则返回结果为整数,否则返回一个包含分数结果;
----------------------------------------------------------------------------
NEXT_DAY(D,CHAR)char为一周的星期几,结果返回D之后的第一个char(星期几)的日期
e.g:select NEXT_DAY('15_MAY_2002','tuesday') from table
------------------------------------------------------------------------------
SYSDATE,返回当前的日期和时间,无参数。
----------------------------------------------------------------------------
TRUNC()--在此可用于剪裁日期,如,要比较两个日期的年份:IF TRUNC(date1,'yyyy')=TRUNC(date2,'yyyy') THEN
---------------------------------------------------------------------------
=================================转换函数======================================
CHARTOROWID----将包含外部语法的ROWID的CHAR或VARCHAR2数值转换为内部的二进制语法,不常用
e.g:select name from emp where rowid=chartorowid('AAAAFZSSSFSDFDFDFF')
------------------------------------------------------------------------------
CONVERT--将字符串CHAR中的字符,从source_char_set标识的字符集转换为由dest_char_set标识的字符集。
用于计算机之间,不能识别字符时,不常用
------------------------------------------------------------------------------
TO_CHAR(d,[,fmt[,'nlsparams']])--日期转换函数,将一个日期转换为VARCHAR2数据类型。转换结果问题总是18位字符长度。nlsparams缺省时为当前系统语言。其中的格式,返回值大小写会相应显示,如:TO_CHAR(SYSTEMDATE,'MON')的结果显示:JAN;TO_CHAR(SYSTEMDATE,'mon')的结果显示:mon
-------------------------------------------------------------------------------
TO_CHAR(n,[,fmt[,'nlsparams']])--数值转换函数,将一个NUMBER型转换成一个VARCHAR2数据类型的值;
e.g:select TO_CHAR(100,'L') FROM table
result: $100
e.g:select TO_CHAR(95270,'0000000000') from dual;
result:0000095270
L--为返回本地货币符的语法,待考究
$--返回以美元开始的语法
--------------------------------------------------------------------------------
TO_DATE(CHAR[,fmt[,'nlsparams']])--该函数将CHAR或VARCHAR2数据类型的值转换为DATE类型。其中fmt为指定字符串输出的日期语法,如果省略了fmt,char就使用缺省的日期语法;另外,日期亦可以相加减;
e.g:INSERT INTO bonus(bonusdate) values (TO_DATE(January 12,1990, 11:00 A.M.','NLS_DATE_LANGUAGE=American') from table
e.g: Mydate:= to_date('092199','mmddyy')+5; --将092199转换为日期型并加5天
--------------------------------------------------------------------------------
TO_NUMBER(char[,fmt[,'nlsparams']])--将函数char或varchar数据类型的字符串(char)按时指定的数值语法转换为NUMBER数据类型值;
e.g: UPDATE emp SET sal = sal + TO_NUMBER('100.00'),'9G999D99') WHERE name= 'Black';
e.g: x:=TO_NUMBER(C,'$999,999,999.99') --将经常要计算的以$打头的字符型的C转换为数字型X,其中的999,999,999.99转换的最大值;
-------------------------------------------------------------------------------
==================================单行函数================================
GREATEST(expr[,expr]...)返回参数中的最大值。这些值可以是列、常量、表达式或char,varchar2,number,date型,但由于不确定性,最好比较前先将表达式转换为所需的类型
LEAST(expr[,expr]...)返回最小值。相似于GREATEST函数
--------------------------------------------------------------------------------
NVL(exp1,exp2)替换空值函数,当exp1为空时,返回exp2,否则,返回exp1
e.g:select nvl(tel,'Noknown') from table
result:当tel字段为空时,返回'Unknown'
-------------------------------------------------------------------------------
UID返回唯一标识当前数据库用户的函数,该函数没有参数。可以在select语句中使用
e.g:SELECT UID FROM TABLE
------------------------------------------------------------------------------
USER用VARCHAR2数据类型返回当前ORACLE用户的名,没有参数
e.g:select user,uid from pubs
------------------------------------------------------------------------------
USERNV返回当前工作环境的信息;
------------------------------------------------------------------------------
VSIZE(expr)返回expr内部表示的字节数。若expr为null,该函数返回null
e.g:SELECT vsize(name) from table
==================================组函数=================================
AVG([distinct|all] n)--返回n的平均值;
COUNT(*|DISTINCT|ALL exp)--返回查询行数。ALL只包括重复行,不含空行。*包含重复行和空行
GLC--MLSLABEL的最大下界;
LUB--MLSLABEL的最小上界;
MAX([distinct|all exp)--略
MIX([distinct|all exp)--略
SUM([distinct|all exp)--略
=====================================其他======================================
DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)--该函数的含义如下:IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ... ELSIF 条件=值n THEN  RETURN(翻译值n) ELSE RETURN(缺省值) END IF
e.g:select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
-----------------------------------------------------------------------------
SQLCODE--根据当前的错误返回错误码;
-----------------------------------------------------------------------------
SQLERRM--返回与Oracle错误码关联的错误信息;