The NVL, or null value function, accepts a set of two expressions as input. The expressions can be of any datatype, but the second expression must be convertible to the first expression's type. The datatype of the first expression is the returned datatype. If the first expression is character data, VARCHAR2 is always returned. If the value of the first expression evaluates to a null value, the second expression is returned. If the expression evaluates to a non-null value, the first expression is returned. This function can be used to protect you during comparisons and mathematical functions where the variables may evaluate to null.The basic format for the NVL function is: value:=NVL(expr1, expr2); Where:value is a variable of the same datatype as expr1 (or VARCHAR2 if expr1 is character in nature)expr1 is the expression that you are evaluatingexpr2 is the value returned if expr1 is nullPL/SQL Example:Check all IN parameters of a procedure and convert NULL values to 0 in the default value setting of the declarations of local variable copies of the parameters:PROCEDURE no_nulls_allowed (number1_in IN NUMBER, number2_in IN NUMBER)
IS
local_number1 NUMBER := NVL (number1_in, 0);
local_number2 NUMBER := NVL (number2_in, 0);
BEGIN
...
END;After fetching the employee information from the database, return the employee抯 commission as 0 whenever it is NULL.DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary, NVL (commission, 0)
commission
FROM employee
WHERE employee_id = :emp.employee_id;
BEGIN
...
END;
SQL 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 APPLICABLE
IS
local_number1 NUMBER := NVL (number1_in, 0);
local_number2 NUMBER := NVL (number2_in, 0);
BEGIN
...
END;After fetching the employee information from the database, return the employee抯 commission as 0 whenever it is NULL.DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, salary, NVL (commission, 0)
commission
FROM employee
WHERE employee_id = :emp.employee_id;
BEGIN
...
END;
SQL 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 APPLICABLE
解决方案 »
- Oracle的TNS连接串中的ADDRESS_LIST和ADDRESS
- 被SQL注入,求解
- Oracle查询TOP N的问题
- 请问像这样返回一个固定行数的语句要怎么写?
- 调用存储时报错
- 请问为什么我运行sqlplus时报错message file sp1<lang>.msb not found
- 如何实现级联删除表中的一条记录?该记录被其它表的记录所引用.而在定义外键约束的时候,没有指定on delete cascade.请各位大哥帮忙.在线
- 关于查询语句问题请教
- 请教高手!8I用表自身触发器检索自身数据的具体实现方法。
- 紧急求助:在NT4上安装了ORACLE数据库,但是安装完瑞星杀毒之后,又把瑞星卸载了,ORACLE就起不来了,哪位大侠知道这是怎么回事?
- WIN2000/Oracle9i中Oracle Management Server启动报错
- 我给用户什么权限可以对视图操作?
FROM drug_export_detail
其中location 不是表中字段