Nvl () function in oracle

In oracle, The nvl () function is used by the oracle nvl function to obtain the total value of a field through a query. If this value is null, a default value select nvl (sum (t. dwxhl), 1) from tb_jhde t where zydm =-1 nvl usage, nvl (arg, value) if the previous arg value is null, the returned value is www.2cto.com, for example: NVL (a, B), which is used to determine whether a is NULL. If not, if the return value of B is used to obtain the total value of a field through the query, if the value is null, a default value is provided.
Another useful method is declare I integer select nvl (sum (t. dwxhl), 1) into I from tb_jhde t where zydm =-1
In this way, the obtained total value can be stored in variable I. If the queried value is null, the value is set to the default Nvl function nvl () in Oracle () the function returns a non-null value from two expressions. Syntax NVL (eExpression1, eExpression2) parameter eExpression1, eExpression2 if eExpression1 calculates a null value, NVL () returns eExpression2. If the calculation result of eExpression1 is not null, eExpression1 is returned. EExpression1 and eExpression2 can be any data type. If both eExpression1 and eExpression2 are null values, NVL () returns. NULL .. The return value of www.2cto.com is numeric, logical, or null, indicating that null or null is not supported, you can use NVL () to remove null values in the calculation or operation. Select nvl (. name, 'void ') as name from student a join school B on. ID = B. ID note: the two parameters must be of the same type. Q: What is NULL? A: When we do not know the specific data, it is also unknown. We can use NULL. We call it NULL. in ORACLE, the column length containing NULL values is zero. ORACLE allows blank fields of any data type, except for the following two cases: 1. primary key field (primary key ), 2. Description of fields with the not null restriction added during definition: 1. It is equivalent to no value and is unknown. 2. NULL and 0, empty strings, and spaces are different. 3. add, subtract, multiply, and divide null values. The result is still null. 4. The NVL function is used for NULL processing. Www.2cto.com 5. For comparison, use the keyword "is null" and "is not null ". 6. null values cannot be indexed. Therefore, some data that meets the query conditions may not be found. In count (*), nvl (column name, 0) is used for processing and then query. 7. Sorting is larger than other data (the index is sorted in descending order by default, small → large), so the NULL value is always at the end. Usage: SQL> select 1 from dual where null = null; no records found SQL> select 1 from dual where null = ''; SQL> select 1 from dual where ''=''; SQL> select 1 from dual where null is null; 1 --------- 1 SQL> select 1 from dual where nvl (null, 0) = nvl (null, 0); 1 --------- 1
Add, subtract, multiply, and divide null values. The result is still null. SQL> select 1 + null from dual; SQL> select 1-null from dual; SQL> select 1 * null from dual; SQL> select 1/null from dual; A record is found. note: This record is the null setting in the SQL statement. Some columns are NULL values. update table1 set column 1 = null where column 1 is not null;
There is an existing product sales table sale. The table structure is: month char (6) -- month sellnumber () -- monthly sales amount create table sale (month char (6), monthly number ); insert into sale values ('20170101', 200001); insert into sale values ('20170101', 1000); insert into sale values ('20170101', 200002 ); insert into sale values ('20170101', 200004); insert into sale values ('20170101', 1300); insert into sale values ('20170101', 200005 ); insert into sale values ('20170101', 200007); insert into sale values ('20170101', 1600); insert into sale values ('20170101', 200101 ); insert into sale values ('20140901', 200301); insert into sale values ('20140901', 1300); insert into sale (month) values ('20140901'); (Note: the region value of this record is null) www.2cto.com
Commit; input 12 records in SQL> select * from sale where region like '% '; MONTH success ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 Query 11 records. result Description: the query results indicate that this SQL statement does not query a field with a column value of NULL. In this case, the field must be NULL. SQL> select * from sale where region like '%' or region is null; SQL> select * from sale where nvl (Region, 0) like '% '; MONTH success ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 200009 Query 12 records. oracle null is used in this way. We 'd better be familiar with its conventions to prevent incorrect results.