Oracle通用函数和条件表达式

Oracle单行函数中涉及的函数大概可以分为五大种，除了字符函数、数值函数、日期函数、转换函数、通用函数。下面来讲解通用函数
一、通用函数
这些函数适用于任何数据类型，同时也适用于空值：
nvl(expr1, expr2)
nvl2(expr1, expr2, expr3)
nullif(expr1, expr2)
coalesce(expr1, expr2, …, exprn)
（1）nvl函数
将空值转换成一个已知的值：
可以使用的数据类型有日期、字符、数字。
函数的一般形式:
nvl(commission_pct,0)
nvl (hire_date,‘01-JAN-97’)
nvl (job_id,‘No Job Yet’)
eg：查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

select last_name,nvl(to_char(manager_id),'No Manager')from employees;

（2）nvl2函数
nvl 2(expr1, expr2, expr3) : expr1不为null，返回expr2；为null，返回expr3。
eg：查询员工的奖金率，若为空，返回0.01；若不为空，返回实际奖金率+0.015。

select last_name, salary, commission_pct,
nvl2(commission_pct,commission_pct+0.015,0.01) bonus
from employees;

（3）nullif函数
nullif (expr1, expr2) : 相等返回null，不等返回expr1
eg：查询员工的姓名first_name和last_name的长度，相等的返回空，不相等返回first_name的长度

select first_name, length(first_name) "expr1",
last_name, length(last_name) "expr2",
nullif(length(first_name), length(last_name)) result
from employees;

（4）coalesce函数
coalesce与 nvl相比的优点在于 coalesce可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行coalesce
eg：查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

select last_name,coalesce(to_char(manager_id),'No Manager')
from employees;

  (5) instr()函数的格式

INSTR( string, substring [, start_position [, th_appearance ] ] )

参数:
string - 要搜索的字符串。字符串可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB等类型。
substring - 要在字符串(string)中搜索的子字符串。 子字符串可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB等类型。
start_position - 可选的。 在搜索将开始的字符串中的位置。 如果省略，则默认为1。字符串中的第一个位置为1.如果start_position为负数，INSTR函数会从字符串末尾开         ，始计算start_position字符数，然后搜索字符串的开头。
th_appearance - 可选的。 子串的第n个出现。 如果省略，则默认为1。
返回值
返回一个数字值。 字符串中的第一个位置是1。如果在字符串中找不到substring，那么INSTR函数将返回0。

格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

  注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

实例

格式一

1、select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
2、select instr('helloworld','lo') from dual; --返回结果：4    即“lo”同时出现，第一个字母“l”出现的位置
3、select instr('helloworld','wo') from dual; --返回结果：6    即“wo”同时出现，第一个字母“w”出现的位置
4、select instr('abc','d') from dual; --找不到返回结果：0  -- 而java中，indexof()找不到返回-1
   select instr('abc','a') from dual; --找到第一个字符返回结果：1  -- 而java中找到第一个字符，"abc".indexof("a") 返回0

 

 

 格式二

1 select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
2 select instr('helloworld','l',3,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
3 select instr('helloworld','l',4,2) from dual;  --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
4 select instr('helloworld','l',-1,1) from dual;  --返回结果：9    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
5 select instr('helloworld','l',-2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第二次出现的“l”的位置
6 select instr('helloworld','l',2,3) from dual;  --返回结果：9    也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
7 select instr('helloworld','l',-2,3) from dual; --返回结果：3    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

 

注：MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果； 如下所示：

MySQL： select * from tableName where name like '%helloworld%';
Oracle：select * from tableName where instr(name,'helloworld')>0;  --这两条语句的效果是一样的

 

 (6) substr()函数的格式

1、substr函数格式   (俗称：字符截取函数)

　　格式1： substr(string string, int a, int b); 

     1、string 需要截取的字符串 
     2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
     3、b 要截取的字符串的长度
示例：

 1、select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
 2、select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
 3、select substr('HelloWorld',2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
 4、select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。

（注：当a等于0或1时，都是从第一位开始截取(如：1和2)） 而Java中的substring(0)是从0索引开始截取的
（注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
（注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符； 只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）

 

　　格式2：substr(string string, int a) ;   

     1、string 需要截取的字符串
     2、a 可以理解为从第a个字符开始截取后面所有的字符串。

示例：

1、select substr('HelloWorld',0) value from dual; //返回结果：HelloWorld，截取所有字符
2、select substr('HelloWorld',1) value from dual; //返回结果：HelloWorld，截取所有字符
3、select substr('HelloWorld',2) value from dual; //返回结果：elloWorld，截取从“e”开始之后所有字符
4、select substr('HelloWorld',-1) value from dual; //返回结果：d，从最后一个“d”开始 往回截取1个字符
5、select substr('HelloWorld',-2) value from dual; //返回结果：ld，从最后一个“d”开始 往回截取2个字符

（注：当只有两个参数时；不管是负几，都是从最后一个开始 往回截取(如：15、16)）

 

java中的substring()方法

public String substring(int beginIndex, int endIndex)
第一个int为开始的索引，对应String数字中的开始位置，
第二个是截止的索引位置，对应String中的结束位置
1、取得的字符串长度为：endIndex - beginIndex;
2、从beginIndex开始取，到endIndex结束，从0开始数，其中不包括endIndex位置的字符
如：
"hamburger".substring(4, 8) returns "urge"
 "smiles".substring(1, 5) returns "mile"
取长度大于等于3的字符串a的后三个子字符串，只需a.subString(a.length()-3, a.length());
 

二、条件表达式
在 SQL 语句中使用if-then-else逻辑
使用两种方法:
case表达式
decode函数
（1）case函数
eg：查询部门号为 10, 20, 30 的员工信息, 若部门号为10, 则打印其工资的 1.5 倍, 20 号部门, 则打印其工资的 1.6倍, 30 号部门打印其工资的 1.8倍数。

select salary,department_id,
case when department_id=10 then salary*1.5
when department_id=20 then salary*1.6
when department_id=30 then salary*1.8
else salary end
from employees;

（2）decode函数
它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。
区别于SQL的其它函数，decode函数还能识别和操作空值。
语法：decode(control_value,value1,result1[,value2,result2…][,default_result]);
含义解释： decode(条件,值1,返回值1,值2,返回值2,…,缺省值)
该函数的含义如下：if条件=值1 则return返回值1，else if条件=值2 则return返回值2… else return (缺省值)
试图处理的数值。decode函数将该数值与后面的一系列的偶序相比较，以决定返回值。
value1 是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字null与之对应。
result1 是一组成序偶的结果值。
default_result 未能与任何一个值匹配时，函数返回的默认值。
例如：

select decode(X,1,'x是1',2,'x是2','其他')from dual;

当x等于1时，则返回’x是1’。
当x等于2时，则返回’x是2’。
否则，返回’其他’。
1、decode+ sign比较大小：
需要比较2个值的时候，可以配合sign()函数一起使用。

select decode(sign(5-6),1,'no',-1,'yes','没有正确值')"值" from dual;

2、decode相当于case when then
所以同样，上题也可以用case实现：

select case sign(5-6)
when 1 then 'no'
when -1 then 'yes'
else '没有正确值' end
from dual ;

3、decode在order by中的使用
此外，还可以在order by中使用decode。
eg：表emp，有ename列。要求按照：姓名首字母进行排序。这时，就可以非常轻松的使用decode完成要求了。

select ename from emp
order by decode(ename,'ALLEN',1,'BLAKE',2,'CLARK',3);

解释：上题在order by 中使用decode，是当ename='ALLEN’的时候，将它排序在第1位，当ename='BLAKE’的时候，将它排序在第2位，当ename='CLARK’的时候，将它排序在第3位，这种做法就是指定它排序在哪一位，而其他的任意排序。
另外补充一下，单行函数也可以作为嵌套函数的使用：
三、嵌套函数
单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。图解：