oracle常用函数总结

1 在Oracle中，不等号有三种：<>，!=，^=

例如：select * from test where name<>'xn';

返回的结果是name不为xn，且name不空的记录。我们的目的是得到name为xn的全部记录，也包括name为空的记录。为了解决这个问题，我们可以采用以下两种方案：

select * from test where instr(concat(name,'xx'),'xn') = 0 ;

select * from test where nvl(name,'xx')<>'xn' ;

备注：null只能通过is null或者is not null来判断，其它操作符与null操作都是false。

各数据库中的字符串连接方法

1）MySQL：CONCAT()

2）Oracle：CONCAT()，||

3）SQL Server： +

例如：

SELECT 'this is '+'a test'; 返回值this a test

SELECT CONCAT('this is ','a test') from dual; 返回值this a test

SELECT 'this is '||'a test' from dual; 返回值this a test

2 instr函数:判断某个字符串是否含有指定的字符,在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。

语法：instr(sourceString,destString,start,appearPosition)

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

其中sourceString代表源字符串；

destString代表要从源字符串中查找的子串；

start代表查找的开始位置，这个参数可选的，默认为1；

appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的，默认为1

如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。

返回值为：查找到的字符串的位置。 例如：select instr('abcdefghbc','bc',3) position from dual; --(从第3个字符开始算起第3个字符是c，所以从3开始以后的字符串找查找bc,返回9)

POSITION ---------- 9 select instr('qinyinglianqin','qin', 1, 2) position from dual;--(从第1个字符开始，查找第2次出现子串的位置 )

POSITION ---------- 12

3 通用函数：NVL,NVL2,NULLIF,COALESCE

NVL函数的格式如下：NVL(expr1,expr2) 如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2函数的格式如下：NVL2(expr1,expr2,expr3) 如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NULLIF(exp1,expr2) 函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

Coalesce(expr1, expr2, expr3?.. exprn) 所有表达式必须是相同类型，或者可以隐性转换为相同的类型。返回表达式中第一个非空表达式， 如有以下语句： SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 其返回结果为：3

如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。

4 Round 函数 (四舍五入)

描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL 参数:

number : 欲处理之数值

decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ),如果decimal_places小于0则四舍五入到小数点向左第decimal_places位。 Sample :

select round(123.456, 0) from dual; 回传 123 select round(123.456, 1) from dual; 回传 123.5 select round(123.456, 3) from dual; 回传 123.456 select round(-123.456, 2) from dual; 回传 -123.46

select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual; 返回：5555.67 , 5600 , 5556

【相近】trunc(x[,y]) 返回截取后的值，用法同round(x[,y]),只是不四舍五入

5 trunc(x[,y])

【功能】返回x按精度y截取后的值

【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。

【返回】数字

【示例】 select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;

返回：5555.66 5500 5555

【相近】round(x[,y]) 返回截取后的值，用法同trunc(x[,y]),只是要做四舍五入

6 substr与substrb区别

说明:substr,substrb均为字符串截取函数，都带有三个参数，第一个参数为所要截取的字符串，第二个参数为strart（索引均从1开始），第三个参数为length。

SQL> select substr('今天是个好日子',3,5) from dual; ---------- 是个好日子

SQL> select substrb('今天是个好日子',3,5) from dual; ----- 天是

结论:substr是按照字来算的，而substrb()是按照字节来算的。看下面的例子：

SQL> select substr('abcdef',3,4) from dual; ----

cdef

SQL> select substrb('abcdef',3,4) from dual; ---- cdef

分析：对于字母来说，substr与substrb作用时一样的，但对于汉字来说，substr是按字来取值，而substrb是按字节来取值，当所取长度为奇数时，则自动舍弃最后一位字节。 类似的还有，

length与lengthb长度计算函数

select length('你好') from dual ----output:2 select lengthb('你好') from dual ----output :4

Instr与Instrb字符串查找函数instr(原字符串,查的字符串,起始位置,第几个匹配) 返回字符串位置,找不到返回0 .

select instr('日日花前长病酒','花前',1,1) from dual ----output:3 select instrb('日日花前长病酒','花前',1,1) from dual ----output:5

7 DECODE函数

DECODE有什么用途呢？先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2),salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。 oracle decode函数使用方法 该函数的含义如下： IF 条件=值1 THEN

RETURN(翻译值1) ELSIF 条件=值2 THEN

RETURN(翻译值2) ......

ELSIF 条件=值n TH …… 此处隐藏：2958字，全部文档内容请下载后查看。喜欢就下载吧 ……