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
解决方案 »
- 求助
- 我修改了计算机名称后,oracle8.1.6数据库登录不进去了
- 各位大侠们,请问在oracle中用sql语句实现千分位怎么办?在线等待
- 多表查询时怎么用COUNT函数,并把查询结果如何赋值给一个变量!
- oracle用plsql打开后,package里有sys.dbms_##的东东,是什么?还有plsql中packages里面一般会发些什么用途东东
- 几个Orcale的存储过程,怎样才能改写为MSSQL的,分不够再开贴
- 请问怎样关闭SQL_TRACE?
- 我是初学者,除了CSDN外,还有哪些好的Oracle学习网站?
- 关于权限的疑问的两个难题
- tnsping 錯誤
- WIN2000/Oracle9i中Oracle Management Server启动报错
- 我给用户什么权限可以对视图操作?
FROM drug_export_detail
其中location 不是表中字段