Oracle常用函数

max():最大 min():最小 avg():绝对平均值 count():行的总数 sum():列的总和

DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，
表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else

convert：可以将字符串从一个字符集转换为另一个字符集；convert('今天星期几','UTF8')；
可用的字符集：UTF8、ZHS16GBK、US7ASCII

cast：用于将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型；
select cast('123.4' as int) from dual; 结果123
select cast('123.447654' as decimal(5,2)) from dual; 结果123.45

substr():返回字符串的一部分,即字符串截取;
substr('abcdefghijklm',1,5) 显示 "abcde"
select substr('abcdefghijklm',1,5 ) from dual;

instr():指定一字符串在另一字符串中最先出现的位置,即查找子串位置
select instr('abcfdgfdhd','fd') from dual; =4
replace():在字符串c1中找到字符串c2，替换成c3;若c3为空，则在c1中删除所有c2.
replace('123456','123','456') ='456456'

NVL():如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值
select ename,nvl(string1,replace_with) from emp;
NVL2():如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值,反之则expr2
select ename,nvl2(expr1,expr2,expr3) from emp;
NULLIF():如果exp1和exp2相等则返回空(NULL)，否则返回第一个值
select ename,nullif(exp1,exp2) from emp;

union:自动去掉结果集中的重复行,并以第一列的结果进行升序排序;
union all:不去掉重复行,并且不对结果集进行排序;
intersect:取两个结果集的交集，并且以第一列的结果进行升序排列;

length():计算字符串长度
initcap():字符串首字母变大写
lower():将文本字符串中的所有字母变小写
upper():将文本字符串中的所有字母变大写

||:字符串连接
abs():绝对值
sign():判断数值正负,正数返回为1,负数为-1
ceil():向上取整 ceil(66.6)=67
floor():向下取整 ceil(66.6)=66
power():取幂 power(3,2)= 9
sqrt():求平方根 sqrt(9)=3
mod():求余 mod(9,5)=4
round():四舍五入 round(66.667,2)=66.67
Ascii():返回字符串首字母的Ascii值chr(97)
ascii():返回ascii值对应的字母;chr(97)
user:返回登录的用户名称 select user from dual;
vsize():返回表达式所需的字节数 vsize('HELLO') =6
coalesce():返回列表中第一个非空表达式 coalesce('','','20','40') ='20'

trunc():直接截断 trunc(66.667,2)=66.66
trunc(date,[fmt]):返回以指定元素格式截去一部分的日期值
trunc(sysdate,'yyyy')返回当年第一天. 　trunc(sysdate,'mm') 返回当月第一天. 　　
trunc(sysdate,'d')返回当前星期的第一天.
trunc(number[,decimals])函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去
TRUNC(89.985,2)=89.98 　TRUNC(89.985)=89 (即取整) TRUNC(89.985,-1)=80

ltrim():从左边开始截取直到出现不符合子串中任何字符为止
ltrim('1093200111000991110224323','109') =3200111000991110224323
ltrim('10911200111000991110224323','109') =200111000991110224323
rtrim():从右边开始截取直到出现不符合子串中任何字符为止
trim():去除字符串的行首和行尾的空格
rapd(string,n)函数来用空格补足字段不足长的部分;如果某一个字段是空值,函数就会自动省略掉这个值

translate(a,b,c):REPLACE所提供的功能的一个超集,将b字符串中的每个字符替换为c中的相应字符；
b字符串和c的位置是一一对应的,如果b比c长,那么在b中而不在c中的额外字符将从a中被删除
translate('123456789','123','45') ='45456789'
lpad(string,n,pad_string):左添充,如果n的数值比string的长度要短,将会把字符串截取成从左到右的n个字符;
lpad('func',5,'3') ='3func' lpad('func',3,'3') ='fun'
rpad(string,n,pad_string):右填充,如果n的数值比string的长度要短,将会把字符串截取成从左到右的n个字符;
rpad('func',5,'3') ='func3' lpad('func',3,'3') ='fun'

sys_context():实际上就是Oracle存储和传递参数的容器访问函数,用来返回一个指定namespace下的parameter值
sys_context('userenv','ip_address')

regexp_substr():regexp_substr(:ls_card_no,'[0-9A-Za-z]+')

分组函数:stddev():返回一组值的标准偏差 select deptno,stddev(sal) from emp group by deptno;
variance():返回一组值的标准方差 select deptno,variance(sal) from emp group by deptno;
rollup():按分组的第一个列进行统计和最后的小计select deptno,job ,sum(sal) from emp group by rollup(deptno,job);
cube():按分组的所有列的进行统计和最后的小计select deptno,job ,sum(sal) from emp group by cube(deptno,job);

数据类型:字符；数值；日期时间；RAW/LONG RAW ；LOB
字符数据类型:
当需要固定长度的字符串时，使用 CHAR 数据类型。
CHAR 数据类型存储字母数字值。
CHAR 数据类型的列长度可以是 1 到 2000 个字节。

LONG 数据类型存储可变长度字符数据
LONG 数据类型最多能存储 2GB

VARCHAR2数据类型支持可变长度字符串
VARCHAR2数据类型存储字母数字值
VARCHAR2数据类型的大小在1至4000个字节范围内

数据类型 :
数值数据类型:
可以存储整数、浮点数和实数
数值数据类型的声明语法：NUMBER [( p[, s])] P表示精度，S表示小数点的位数

日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒
主要的日期时间类型有：
DATE - 存储日期和时间部分，精确到整个的秒
TIMESTAMP - 存储日期、时间和时区信息，秒值精确到小数点后6位

LOB 称为“大对象”数据类型，可以存储多达 4GB 的非结构化信息，例如声音剪辑和视频文件等
LOB 数据类型允许对数据进行高效、随机、分段的访问

RAW 数据类型用于存储二进制数据,RAW 数据类型最多能存储 2000 字节
LONG RAW 数据类型用于存储可变长度的二进制数据,LONG RAW 数据类型最多能存储 2 GB

LOB:
CLOB 即 Character LOB（字符 LOB），它能够存储大量字符数据
BLOB 即 Binary LOB（二进制 LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件
BFILE 即 Binary File（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中

oracle中base64的加密解密：
utl_raw.cast_to_raw：解决不同字符集的数据库之间的相互访问,可以正确显示中文字符;
方法一：直接转换
SELECT utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('abc'||to_char(ts)))),
utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('abc'||to_char(ts))))))),
to_char(ts),
to_char(SYSDATE,'yyyy-mm-dd HH24:MI:SS')
FROM (SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS ts FROM dual);

方法一：转换为UTF8
select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(convert('今天星期几','UTF8')))) from dual t;
select convert(utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw('5LuK5aSp5pif5pyf5Yeg'))),'ZHS16GBK', 'UTF8') from dual;