Oracle函数大全

函数：
以下ch表示字符，str表示字符串，num表示数字，column_name表示字段，table_name表示表名
举一个例子，其他函数的使用方法类似
select ascii('a') from dual;--这个返回a对应的ASCII码
函数：
ascii(ch);--返回字符ch的对应的十进制数
asciistr(ch);
union;--该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
union all;--该操作符与union相似，但是它不会取消重复行，而且不会排序。
intersect; --交集查询，使用该操作符用于取得两个结果集的交集。（相当于in，相当于exists）
minus;--差集查询，前面减去两者的交集，使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据，（相当于not in，not exists）。

转换函数：

to_char(date,format);--将日期或数字转换为字符串
例：select to_char(to_date('1999-12-01','yyyy-mm-dd'),'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
to_number(ch);--将给出的字符转换为数字
to_date(string,format);--将字符串转化为Oracle中的一个日期
例：select to_date('2014-10-14 09:12:12','yyyy-mm-dd hh24:mi:ss') from dual;
/*yy：两位数字的年份 2004-->04
yyyy：四位数字的年份 2004年
mm：两位数字的月份 8月-->08
dd：两位数字的天 30号-->30
hh24： 8点-->20
hh12：8点-->08
mi、ss-->显示分钟\秒
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元
L：在数字前面加本地货币符号
C：在数字前面加国际货币符号
G：在指定位置显示组分隔符、
newtype：在指定位置显示小数点符号(.)
*/
lower(str);--返回字符串，并将所有的字符小写
upper(str);--返回字符串，并将所有的字符大写
length(str);--返回字符串的长度
substr(str,start,count);--取子字符串，从start开始，取count个
||--合并
initcap(str);--返回字符串并将字符串的第一个字母变为大写
coalesce(str1,str2,...);--返回第一个非null的表达式
nullif(str1,str2);--判断str1和str2是否相等，相等返回null，不等返回str1
nvl(str1,str2);--如何处理空值null，如果str1不为null返回str1，否则返回str2
nvl2(str1,str2,str3);--如果str1为null返回str3，否则返回str2，str2和str3类型不同的话，str3会转换为str2的类型
instr(str1,str2,i,j)
/*在一个字符串中搜索指定的字符，返回发现指定的字符的位置;
str1    被搜索的字符串
str2    希望搜索的字符串
i     搜索的开始位置，默认为1
j    出现的位置，默认为1
*/
decode(column_name,str1,return1,str2,return2...);--如果str1等于column_name，则返回return1，否则返回null，同理，可判断str2与column_name是否相等，依此类推
rpad(str1,num,str2); -- 在列的右边不断粘贴字符，直到总字符达到num
lpad(str1,num,str2);  --在列的左边不断粘贴字符，直到总字符达到num
trim(str);--删除两边的空格
特例：select trim (leading from ' DWEYE D') result from dual;--去掉前面空格，返回DWEYE D
select trim (trailing from ' DWEYE ') result from dual;--去掉后面空格，返回 DWEYE
select trim (both 'x' from 'xxxxxxDWEYExxxxxxx') result from dual;--去掉前后的x，返回DWEYE
ltrim(str);  --删除左边出现的空格
rtrim(str);  --删除右边出现的空格
特例：select rtrim('xyxxDWEYExyyx','xy') result from dual;--返回xyxxDWEYE
--“xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作。其它同上，这里就不一一写出了。
replace(str1,str2,str3);
--str1希望被替换的字符或变量，str2被替换的字符串，str3要替换的字符串
soundex(str); --返回一个与给定的字符串读音相同的字符串
例：select m from table_name where soundex(m)=soundex('weather');
concat(str1,str2);--连接两个字符串
bin_to_num(b1[,b2][,b3]...);--返回b1、b2、b3构成的二进制转换为number数（十进制）
cast(num1 as type_name);--将num1的值从一种数据类型转换为由type_name指定的数据类型
chartorowid(str);--将字符串str转换为rowid类型数据。
hextoraw(str);--将十六进制数的字符str转换为raw数据类型（二进制）
rawtohex(num);--将num的值从raw类型转换为字符varchar2类型的字符
rawtonhext(str);--将一个二进制构成的字符串str转换为十六进制
rawtonhex(num);--将num的值从raw类型转换为字符varchar2类型的字符
rowidtochar(num);--将num的值从rowid类型转换为字符varchar2类型
rowidtonchar(num);--将num的值rowid类型转换为字符varchar2类型
to_binary_double(num);--将num转换为一个binary_double类型值
to_binary_float(num);--将num转换为一个binary_float类型值
convert(str,desc,source);--将x字段或变量的源source转换为desc
例：select convert('strutz','we8hp','f7dec') "conversion" from dual; --返回strutz
to_multi_byte(ch);--将字符串中的单字节字符转化为多字节字符
bfilename(dir,file);--指定一个外部二进制文件
dump(s,fmt,start,length)
dump函数以fmt指定的内部数字格式返回一个varchar2类型的值
例：select dump('tufujienhelloworld',1017,8,5) dump_string from dual;--返回Typ=96 Len=18 CharacterSet=AL32UTF8: n,h,e,l,l
empty_blob()和empty_clob();--这两个函数都是用来对大数据类型字段进行初始化操作的函数
to_single_byte;--将字符串c中的多字节字符转化成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用
select sysdate+to_yminterval('0-1') from dual;
translate(c1,c2,c3);--x,y,z都可以为数字串或字符串。将c1中与c2相同的字符以c3代替
select translate('fumble','uf','ar') test from dual;

数学函数：

max(num);--最大值
max(distinct|all)
求最大值，all表示对所有的值求最大值，distinct表示对不同的值求最大值，相同的只取一次
min(num);--最小值
min(distinct|all)
求最小值，all表示对所有的值求最小值，distinct表示对不同的值求最小值,相同的只取一次
avg(num);--平均值
avg(distinct|all);
all表示对所有的值求平均值，distinct只对不同的值求平均值
sum(num);--求和
count(num);--求总数
abs(num);--返回指定值的绝对值
sin(num);--返回数字num的正弦值
asin(num);--给出数字num的反正弦值
cos(num);--返回数字num的余弦
cosh(num);--返回数字num的反余弦值
acos(num);--给出数字num反余弦的值
tan(num);--返回数字num的正切值
tanh(num);--返回数字num的双曲正切值
atan(num);--返回数字num的反正切值
atan2(num1,num2);--返回num1除以num2的反正切值
sign(num);--取数字n的符号，若num为负数，则返回-1；若num为正数，则返回1；若num为0，则返回0
sigh(num);--返回num的双曲正弦值
bitand(num1,num2);--返回num1和num2进行按位于（and）操作的结果
round(num1,num2);--按照指定的精度进行舍入（num1四舍五入，保留num2位）
trunc(num1,num2);--按照指定的精度进行舍入（截取数字，保留num2位，忽略余数）
mod(num1,num2);--对num1取模num2(返回一个num1除以num2的余数)
floor(num);--对给定的数字取整数（返回小于或者等于num的最大整数，忽略余数）
ceil(num);--返回大于或者等于num的最小整数
chr(num); --给出整数num,返回对应的字符;
exp(num);--返回一个数字e的num次方根
greatest(str1,str2,str3);--返回一组表达式中的最大值,即比较字符的编码大小
least(str1,str2,str3);--返回一组表达式中的最小值,即比较字符的编码大小
ln(num);--返回数字num的自然对数值
log(num1,num2);--返回一个以num1为底num2的对数
power(num1,num2);--返回num1的num2次方根
sqrt(num);--返回数字num的根
stddev(distinct|all);--求标准差，all表示对所有的值求标准差，distinct表示只对不同的值求标准差
variance(distinct|all);--求协方差

日期函数：

select sysdate from dual;--用来得到系统的当前日期
systimestamp;--该函数用于返回当前系统的日期时间及时区。
add_months(date,num);--增加月份
months_between(date2,date1);--返回date1和date2之间月的数目，如果date1和date2的日期相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个小数。给出date2-date1的月份
round(sysdate,format);--format可以是yyyy，则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。如果format为mm，则舍入到某月的1日，即前月舍去，后半月作为下一月。默认为dd，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。如果format为dd则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
trunc与round非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天，按照给出的要求将日期截断,如果fmt='yyyy'表示保留年,截断月，依次类推
例：select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
extract(column_name from sysdate);--提取日期中的特定部分
last_day(date);--月份对应的最后一天的日期
next_day(date,'');--给出日期date和星期x之后计算下一个星期的日期
例：select next_day(sysdate,星期二) from dual;
new_time(date,'this','other');--给出在this时区=other时区的日期和时间
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
to_timestamp(char[fmt[,'nls_param']]);--该函数用于将符合特定日期和时间格式的字符串转变为timestamp类型。
to_timestamp_tz;--to_timestamp_tz(char[fmt[,'nls_param']])：该函数是oracle9i新增加的函数，用于将符合特定日期和时间格式的字符串转变为timestamp with time zone类型。
to_yminternal;--to_yminternal(char)该函数用于将字符串转变为internal year to month类型。
tz_offset();--tz_offset(time_zone_name||sessiontimezone||dbtimezone)：该函数是oracle9i新增加的函数，用于返回特定时区与utc(格林威治)相比的时区偏移。示例如下：
select tz_offset('est') from dual;

系统函数：

sys_context(opt);
1）terminal：当前会话客户所对应的终端的标示符
2）lanuage: 语言
3）db_name： 当前数据库名称
4）nls_date_format： 当前会话客户所对应的日期格式
5）session_user： 当前会话客户所对应的数据库用户名
6）current_schema： 当前会话客户所对应的默认方案名
7）host： 返回数据库所在主机的名称
可以查询你正在使用哪个数据库
例：select sys_context('userenv','db_name') from dual;
可以查询你当前的用户
例：select sys_context('userenv','session_user') from dual;
user;--返回当前用户的名字
例：show user；
select user from dual;
uid;--select username,user_id from dba_users where user_id=uid;
userenv(opt);
返回当前用户环境的信息,opt可以是:
entryid（返回会话人口标志 ），sessionid（返回会话标志 ），instance（返回当的instance的标志），terminal（返回用户的终端或机器的标志 ），isdba（查看当前用户是否是DBA如果是则返回true），language（返回当前环境变量），client_info，lang（返回当前环境的语言的缩写）
select userenv('isdba') from dual;
vsize(X);--返回X的大小(字节)数
例：select vsize(user),user from dual;

group by;--主要用来对一组数进行统计
having;--对分组统计再加限制条件
order by;--用于对查询到的结果进行排序输出，默认是升序（asc）,降序是desc