bianle
6/23/2016 - 9:52 AM
oracle函数整理
oracle函数整理
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。
1、MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。
例如：SELECT MOD(24,5) FROM DUAL;
2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。
例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。
例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
(二).字符型函数返回字符值(Character Functions Returning Character Values)
该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节；
返回的VCHAR2类型值长度不超过4000字节；
如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G；
对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。
1、LOWER(c) 将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如：SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如：SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点：
如果n<c1.length则从右到左截取指定长度返回；
如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；
如果n>c1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回；
例如：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;
最后大家再猜一猜，如果n<0，结果会怎么样
4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；
例如：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;
5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。
看起来很复杂，理解起来很简单：
如果没有指定任何参数则oracle去除c1头尾空格
例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;
如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)
例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了leading参数则会去掉c1头部c2
例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了trailing参数则会去掉c1尾部c2
例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)
例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
注意：c2长度=1
6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。
例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;
7、RTRIM(c1,c2)与上同，不过方向相反
例如：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;
8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。
例如：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;
9、SOUNDEX(c) 神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：
保留字符串首字母，但删除a、e、h、i、o、w、y。
将下表中的数字赋给相对应的字母：
1：b、f、p、v
2：c、g、k、q、s、x、z
3：d、t
4：l
5：m、n
6：R
如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个；
只返回前4个字节，不够用0填充
例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度；
n2=截取的字符串长度，如果为空，默认截取到字符串结尾；
如果n1=0 then n1=1
如果n1>0，则oracle从左向右确认起始位置截取
例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;
如果n1<0，则oracle从右向左数确认起始位置
例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;
如果n1>c1.length则返回空
例如：SELECT SUBSTR('What is this',50,3) FROM DUAL;
然后再请你猜猜，如果n2<1，会如何返回值呢
11、TRANSLATE(c1,c2,c3) 就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。什么是绝对匹配替换呢？简单的说，是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解，我们通过几具实例来说明：
例如：
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
结果都是空。来试试这个：
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
再来看这个：
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
是否明白了点呢？Replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了？Replace是替换，而translate则像是过滤
(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
c1:原字符串
c2:要寻找的字符串
n1:查询起始位置，正值表示从左到右，负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0
n2:第几个匹配项。大于0
例如：SELECT INSTR('abcdefg','e',-3) FROM DUAL;
2、LENGTH(c) 返回指定字符串的长度。如果
例如：SELECT LENGTH('A123中') FROM DUAL;
猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么
(四).日期函数(Datetime Functions)
本类函数中，除months_between返回数值外，其它都将返回日期。
1、ADD_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。
例如：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE 返回当前session所在时区的默认时间
例如：
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
3、SYSDATE 功能与上相同，返回当前session所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好，深入浅出)的短暂交流，我们认为current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。
例如：SELECT SYSDATE,CURRENT_DATE FROM DUAL;
4、LAST_DAY(d) 返回指定时间所在月的最后一天
例如：SELECT last_day(SYSDATE) FROM DUAL;
5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。
例如：三思用的中文nt，nls_language值为SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;
两种方式都可以取到正确的返回，但是：
SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
则会执行出错，提供你说周中的日无效，就是这个原因了。
6、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0
例如：
SELECT months_between(SYSDATE, sysdate),
months_between(SYSDATE, add_months(sysdate, -1)),
months_between(SYSDATE, add_months(sysdate, 1))
FROM DUAL;
7、ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt参数，则默认返回距离指定日期最近的日期。
例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
8、TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。
例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。
1、TO_CHAR() 本函数又可以分三小类，分别是
转换字符->字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型；
例如：SELECT TO_CHAR('AABBCC') FROM DUAL;
转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；
例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；
例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。
例如：
SELECT TO_DATE(2454336, 'J') FROM DUAL;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
为什么公元制的话，c的值必须不大于5373484呢？因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句：
SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。
毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。
例如：select decode('a2','a1','true1','a2','true2','default') from dual;
2、GREATEST(n1,n2,...n) 返回序列中的最大值
例如：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;
3、LEAST(n1,n2....n) 返回序列中的最小值
例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;
4、NULLIF(c1,c2)
Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。
例如：SELECT NVL(null, '12') FROM DUAL;
6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3
例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;
7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。
例如：
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);
insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');
select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;
8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
Oracle9i提供内置了一个命名空间USERENV，描述了当前session的各项信息，其拥有下列参数：
CURRENT_SCHEMA:当前模式名；
CURRENT_USER:当前用户；
IP_ADDRESS:当前客户端IP地址；
OS_USER:当前客户端操作系统用户；
等等数十项，更详细的参数列还请大家直接参考Oracle Online Documents
例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
注：N表示数字型，C表示字符型，D表示日期型，[]表示内中参数可被忽略，fmt表示格式。
单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。
1、ABS(n) 返回数字的绝对值
例如：SELECT ABS(-1000000.01) FROM DUAL;
2、COS(n) 返回n的余弦值
例如：SELECT COS(-2) FROM DUAL;
3、ACOS(n) 反余弦函数，n between -1 and 1，返回值between 0 and pi。
例如：SELECT ACOS(0.9) FROM DUAL;
4、BITAND(n1,n2) 位与运算，这个太有意思了，虽然没想到可能用到哪里，详细说明一下：
假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，则结果是0001，转换成10进制数为1。
例如：SELECT BITAND(3,9) FROM DUAL;
5、CEIL(n) 返回大于或等于n的最小的整数值
例如：SELECT ceil(18.2) FROM DUAL;
考你一下，猜猜ceil(-18.2)的值会是什么呢
6、FLOOR(n) 返回小于等于n的最大整数值
例如：SELECT FLOOR(2.2) FROM DUAL;
再猜猜floor(-2.2)的值会是什么呢
7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制
例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;
8、SIN(n) 返回n的正玄值,n为弧度。
例如：SELECT SIN(10) FROM DUAL;
9、SINH(n) 返回n的双曲正玄值,n为弧度。
例如：SELECT SINH(10) FROM DUAL;
10、ASIN(n) 反正玄函数，n between -1 and 1，返回值between pi/2 and -pi/2。
例如：SELECT ASIN(0.8) FROM DUAL;
11、TAN(n) 返回n的正切值,n为弧度
例如：SELECT TAN(0.8) FROM DUAL;
12、TANH(n) 返回n的双曲正切值,n为弧度
例如：SELECT TANH(0.8) FROM DUAL;
13、ATAN(n) 反正切函数，n表示弧度，返回值between pi/2 and -pi/2。
例如：SELECT ATAN(-444444.9999999) FROM DUAL;
14、EXP(n) 返回e的n次幂，e = 2.71828183 ...
例如：SELECT EXP(3) FROM DUAL;
15、LN(n) 返回n的自然对数，n>0
例如：SELECT LN(0.9) FROM DUAL;
16、LOG(n1,n2) 返回以n1为底n2的对数，n1 >0 and not 1 ，n2>0
例如：SELECT LOG(1.1,2.2) FROM DUAL;
17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值，不过如果m是负数，则n必须为整数
例如：SELECT POWER(2.2,2.2) FROM DUAL;
18、SIGN(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.
例如：SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;
19、SQRT(n) 返回n的平方根,n为弧度。n>=0
例如：SELECT SQRT(0.1) FROM DUAL;
(二).字符型函数返回字符值(Character Functions Returning Character Values)
该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节；
返回的VCHAR2类型值长度不超过4000字节；
如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G；
对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。
1、CHR(N[ USING NCHAR_CS]) 返回指定数值在当前字符集中对应的字符
例如：SELECT CHR(95) FROM DUAL;
2、CONCAT(c1,c2) 连接字符串，等同于||
例如：SELECT concat('aa','bb') FROM DUAL;
3、INITCAP(c) 将字符串中单词的第一个字母转换为大写，其它则转换为小写
例如：SELECT INITCAP('whaT is this') FROM DUAL;
4、NLS_INITCAP(c) 返回指定字符串，并将字符串中第一个字母变大写，其它字母变小写
例如：SELECT NLS_INITCAP('中华miNZHu') FROM DUAL;
它还具有一个参数：Nlsparam用来指定排序规则，可以忽略，默认状态该参数为当前session的排序规则。
(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、ASCII(c) 与chr函数的用途刚刚相反，本函数返回指定字符在当前字符集下对应的数值。
例如：SELECT ASCII('_') FROM DUAL;
(四).日期函数(Datetime Functions)
本类函数中，除months_between返回数值外，其它都将返回日期。
1、CURRENT_TIMESTAMP([n]) 返回当前session所在时区的日期和时间。n表示毫秒级的精度，不大于6
例如：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;
2、LOCALTIMESTAMP([n]) 与上同，返回当前session所在时区的日期和时间。n表示毫秒级的精度，不大于6
例如：SELECT LOCALTIMESTAMP(3) FROM DUAL;
3、SYSTIMESTAMP([n]) 与上同，返回当前数据库所在时区的日期和时间，n表示毫秒级的精度，>0 and <6
例如：SELECT SYSTIMESTAMP(4) FROM DUAL;
4、DBTIMEZONE 返回数据库的当前时区
例如：SELECT DBTIMEZONE FROM DUAL;
5、SESSIONTIMEZONE 返回当前session所在时区
例如：SELECT SESSIONTIMEZONE FROM DUAL;
6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 从指定时间提到指定日期列
例如：SELECT EXTRACT(year from sysdate) FROM DUAL;
7、TO_TIMESTAMP(c1[,fmt]) 将指定字符按指定格式转换为timestamp格式。
例如：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。
1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。
例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
2、CAST(c as newtype) 将指定字串转换为指定类型，基本只对字符类型有效，比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型，此处就不作酹述。
例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;
3、CHARTOROWID(c) 将字符串转换为rowid类型
例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;
4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。
例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;
5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串
例如：SELECT TO_MULTI_BYTE('ABC abc 中华') FROM DUAL;
6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串
例如：SELECT TO_SINGLE_BYTE('ＡＢＣ ａｂｃ中华') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值
例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;
2、DUMP(exp[,fmt[,start[,length]]])
dump是个功能非常强悍的函数，对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法，不对其功能做深入分析。
如上所示，dump拥有不少参数。其本质是以指定格式，返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式：8||10||16||17，分别表示8进制，10进制，16进制和单字符，默认为10进制。start参数表示开始位置，length表示以,分隔的字串数。
例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;
3、EMPTY_BLOB,EMPTY_CLOB 这两个函数都是返回空lob类型，通常被用于insert和update等语句以初始化lob列，或者将其置为空。EMPTY表示LOB已经被初始化，只不过还没有用来存储数据。
4、NLS_CHARSET_NAME(n) 返回指定数值对应的字符集名称。
例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;
5、NLS_CHARSET_ID(c) 返回指定字符对应的字符集id。
例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一个NCHAR值的声明宽度(以字符为单位).n1是该值以字节为单位的长度，n2是该值的字符集ID
例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;
7、SYS_EXTRACT_UTC(timestamp) 返回标准通用时间即格林威治时间。
例如：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;
8、SYS_TYPEID(object_type) 返回对象类型对应的id。
例如：这个这个，没有建立过自定义对象，咋做示例？
9、UID 返回一个唯一标识当前数据库用户的整数。
例如：SELECT UID FROM DUAL;
10、USER 返回当前session用户
例如：SELECT USER FROM DUAL;
11、USERENV(c) 该函数用来返回当前session的信息，据oracle文档的说明，userenv是为了保持向下兼容的遗留函数。oracle公司推荐你使用sys_context函数调用USERENV命名空间来获取相关信息，所以大家了解下就行了。
例如：SELECT USERENV('LANGUAGE') FROM DUAL;
12、VSIZE(c) 返回c的字节数。
例如：SELECT VSIZE('abc中华') FROM DUAL; SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
58.USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950