Common Oracle functions: nvl/nullif/case when/wm_concat/replace

1. nvl Function
 
The nvl function converts a null value to an actual value. The data type can be date, number, character, and must match: for example:
Nvl (commision, 0)
 
Nvl (hiredate, '01-JAN-87 ')
 
Nvl (job_id, 'no manager ')
 
Nvl (to_char (job_id), 'no manager ')
 
Nvl can convert any data type, but the returned value of the converted data type must be the expr type of the first parameter of nvl (expr1, expr2). For example: date, number, varchar2 or char
 
Example: Calculate the annual salary of an employee. If the bonus is null, use 0 instead.
 
SQL> select empno, ename, sal, nvl (comm, 0) comm, (sal + nvl (comm, 0) * 12 annual_sal from emp;
 
Empno ename sal comm ANNUAL_SAL
-----------------------------------------------
7369 SMITH 800 0 9600
7499 ALLEN 1600 300 22800
7521 WARD 1250 500 21000
7566 JONES 2975 0 35700
7654 MARTIN 1250 1400 31800
7698 BLAKE 2850 0 34200
 
Nvl (arg, value) indicates that if the previous arg value is null, the returned value is the following value, for example, NVL (a, B) indicates whether a is NULL, if the value of a is not returned, if the value of B is returned, the total value of a field is obtained through the query. If the value is null, a default value is provided.
 
Return Value Type:
Numeric, currency, logical, or null
Note:
If null or null values are not supported, you can use NVL () to remove null values in the calculation or operation. select nvl (. name, 'null') 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) fields with the not null restriction added during definition
 
Note:
 
(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.
 
(5) use the keyword "is null" and "is not null" for comparison ".
 
(6) The null value cannot be indexed, so 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 = ''; no records found
SQL> select 1 from dual where ''=''; no records found
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 queried. 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 a sales table sale with the following structure:
 
Month char (6) -- month
 
Sellnumber () -- monthly sales amount
 
Create table sale (month char (6), Region number );
 
Insert into sale values ('20140901', 200001 );
 
Insert into sale values ('20140901', 200002 );
 
Insert into sale values ('20140901', 200003 );
 
Insert into sale (month) values ('20140901'); (Note: The region value of this record is null)
 
Commit; 4 records in total
SQL> select * from sale where region like '% ';
MONTH week
 
---------------
 
200001 1000
 
200002 1100
 
200003 1200
 
3 records are queried. Results: The query results indicate that this SQL statement cannot 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 (latency, 0) like '% ';
MONTH week
 
---------------
 
200001 1000
 
200002 1100
 
200003 1200
 
200009
 
4 records are queried. Oracle null is used in this way. We 'd better familiarize ourselves with its conventions to prevent incorrect results.

1 2 3 Next Page