在8i和9i下,把双引号换成单引号可以。NVL
Syntax
nvl(expr1,expr2)
Purpose
If expr1 is null, NVL returns expr2; if expr1 is not null, NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2. Example
SELECT ename, NVL(TO_CHAR(COMM), 'NOT APPLICABLE')
"COMMISSION" FROM emp
WHERE deptno = 30;
ENAME COMMISSION
---------- -------------------------------------
ALLEN 300
WARD 500
MARTIN 1400
BLAKE NOT APPLICABLE
TURNER 0
JAMES NOT APPLICABLENVL2
Syntax
nvl(expr1,expr2,expr3)
Purpose
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG. If the datatypes of expr2 and expr3 are different, Oracle converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value's datatype is VARCHAR2. Example
The following example shows whether the income of each employee in department 30 is made up of salary plus commission, or just salary, depending on whether the comm column of emp is null or not. SELECT ename, NVL2(TO_CHAR(COMM), 'SAL & COMM', 'SAL') income
FROM emp WHERE deptno = 30;ENAME INCOME
---------- ----------
ALLEN SAL & COMM
WARD SAL & COMM
MARTIN SAL & COMM
BLAKE SAL
TURNER SAL & COMM
JAMES SAL
Syntax
nvl(expr1,expr2)
Purpose
If expr1 is null, NVL returns expr2; if expr1 is not null, NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2. Example
SELECT ename, NVL(TO_CHAR(COMM), 'NOT APPLICABLE')
"COMMISSION" FROM emp
WHERE deptno = 30;
ENAME COMMISSION
---------- -------------------------------------
ALLEN 300
WARD 500
MARTIN 1400
BLAKE NOT APPLICABLE
TURNER 0
JAMES NOT APPLICABLENVL2
Syntax
nvl(expr1,expr2,expr3)
Purpose
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG. If the datatypes of expr2 and expr3 are different, Oracle converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value's datatype is VARCHAR2. Example
The following example shows whether the income of each employee in department 30 is made up of salary plus commission, or just salary, depending on whether the comm column of emp is null or not. SELECT ename, NVL2(TO_CHAR(COMM), 'SAL & COMM', 'SAL') income
FROM emp WHERE deptno = 30;ENAME INCOME
---------- ----------
ALLEN SAL & COMM
WARD SAL & COMM
MARTIN SAL & COMM
BLAKE SAL
TURNER SAL & COMM
JAMES SAL
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货