oracle之nvl、nvl2、decode函数整理

1. nvl(eExpression1, eExpression2)

若eExpression1 is null,则return(eExpression2),否则return(eExpression1);

nvl(表达式, 表达式为空设值)

select nvl(a.address, '不详') as address from student a ;


2. nvl2(eExpression1, eExpression2, eExpression3)

若eExpression1 is null,则return(eExpression3),否则return(eExpression2);

nvl2(表达式, 表达式不为空设值, 表达式为空设值)

select nvl2(a.address, '确切地址：' || a.address, '不详') as address from student a ;


3.decode(eExpression, value1, returnVal1,  value2, returnVal2,...valueN, returnValN, defaultVal)

若eExpression = value1,则return(returnVal1);
若eExpression = value2,则return(returnVal2);
...
若eExpression无匹配值,则return(defaultVal);

select id, username, age, decode(sex,0,'男',1,'女') from users;