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

Common Oracle functions: nvl/nullif/case when/wm_concat/replace 1. nvl function nvl converts a null value to an actual value. The data type can be date, number, character, and data type must match: 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, however, the returned value of the converted data type must be of the expr type of the first parameter nvl (expr1, expr2. example: date, number, varchar2 or char: 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 ALLEN 7499 1600 300 WARD 22800 7521 1250 500 JONES 21000 0 7566 MARTIN 2975 35700 7654 1250 blke 1400 0 31800 nvl (arg, value) indicates that if the previous arg value is null, the returned value is the following value, for example, NVL (a, B), which is used to determine whether a is NULL. 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 type is provided: description: If null or null values are not supported, NVL () can be used () to remove the null value 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) description of fields with the not null condition 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. (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 = ''; 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, and 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. an existing product sales table sale has the following table structure: month char (6) -- month sellnumber (200001) -- monthly sales amount create table sale (month char (6), month number); insert into sale values ('123 ', 1000); insert into sale values ('20140901', 200002); I Nsert into sale values ('20140901', 200003); insert into sale (month) values ('20160901'); (Note: the limit value of this record is null) commit; A total of four records are entered: SQL> select * from sale where region like '%'; MONTH limit ------ --------- 200001 1000 200002 1100 200003 1200 query 3 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 limit ------ --------- 200001 1000 200002 1100 200003 1200 200009 4 records are queried. oracle null is used in this way. We 'd better be familiar with its conventions to prevent incorrect results. 2. nvl2 functions nvl2 (expre1, expre2, expre3). If the first parameter is not null, the second parameter is returned. If the first parameter is null, the third parameter is returned. parameter 1 can be of any data type, parameter 2 and parameter 3 can also be of any data type, except for the long type. if the data types of the last two parameters are different, the database converts the Data Type of the third parameter to the Data Type of the second parameter. SQL> select ename, sal, comm, nvl2 (comm, 'sal + comm ', 'sal') income from emp; ename sal comm income ---------- SMITH 800 sal ALLEN 1600 300 sal + comm WARD 1250 500 sal + comm JONES 2975 sal MARTIN 1250 sal + comm blke 1400 sal CLARK 2850 sal SCOTT 2450 sal KING 5000 sal TURNER 1500 0 sal + comm3.nullif function nullif (expre1, expre2) compare two parameters. If parameter 1 and parameter 2 are equal, return null. if they are not equal, the first parameter is returned. The parameter specifies an empty character. SQL> SELECT ENAME, SAL, COMM, NULLIF (SAL, COMM) RESULTS FROM EMP; ename sal comm results ---------- SMITH 800 800 ALLEN 1600 300 1600 WARD 1250 500 1250 here, Smith returns null. Here, nullif is similar to the case expression 4. coalesce function coalesce (expre1, expre2, expre3) parameter 1: If the first parameter is not blank, the first parameter is returned. Parameter 2: If parameter 1 is null, the second parameter is returned. Parameter 3: If parameter 1 and entry 2 are empty, the third parameter is returned. Example: display the employee name. If the bonus is not null, comm is returned. If comm is null, 2 is returned. If both 1 and 2 are null, 10 is returned. SQL> select ename, coalesce (comm, sal, 10) income from emp; ENAME INCOME ---------- SMITH 800 ALLEN 300 WARD 500 JONES 2975 MARTIN 1400 SCOTT 3000 KING 5000 TURNER 0 ADAMS 11005. conditional expression: case when function case expre when comparison_expre1 then return_expre1 [when comparison_expre2 then return_expre2 when comparison_expre3 t Except return_expre3 else else_expres] end must be of the same data type in all expressions: expre, comparison_expre, and return_expre. You can use varchar, varchar2, char, nchar or nvarcharchar2. example: SQL> SELECT ENAME, SAL, JOB, case job when 'manager' then sal + 100 WHEN 'analytic' then sal + 200 WHEN 'salesman' then sal + 300 else sal + 400 END "RESULTS" from emp; ename sal job results ---------- --------- ---------- SMITH 800 CLERK 1200 ALLEN 1600 SALESMAN 1900 WARD 1250 SALESMAN 1550 JONES 2975 MANAGER 3075 MARTIN 1250 SALESMAN 15506. decode function DECODE (col | expression, search1, result1 [, search2, result2] [, default]). if the default value is omitted, a null value is returned if the search value does not match any math value. SQL> SELECT ENAME, JOB, SAL, DECODE (JOB, 'manager', SAL + 100, 'salesman ', SAL + 200, SAL) REVISED_SAL FROM EMP; ename job sal REVISED_SAL ---------- --------- ------------- smith clerk 800 800 allen salesman 1600 1800 ward salesman 1250 1450 jones manager 2975 3075 SALESMAN 1250 1450 blake manager 2850 clark manager 2950 2450 Example 2: calculate the personal income tax rate of employees in 30 departments: select ename, SAL, DECODE (TRUNC (SAL/1 0.00, 0), 0, 0.09, 1, 2.0, 2, 3.0, 1600) TAX_RATE from emp where deptno = 30 ename sal TAX_RATE ---------- ------------ ALLEN. 09 WARD 1250. 09 MARTIN 1250. 09 BLAKE 2850 2 TURNER 1500. 09 JAMES 950 07. the wm_concat function wm_concat (column name) can separate the column values with "," and display them in a row to prepare test data SQL> createtabletest (id number, namevarchar2 (20 )); SQL> insertintotest values (1, 'A'); SQL> insertintotest values (1, 'B'); SQL> insertintotest values (1, 'C'); SQL> insertintotest values (2, 'D'); SQL> insertintotest values (2, 'E'); SQL> commit; effect 1: SQL> selectwm_concat (name) fromtest; WM_CONCAT (NAME) -------------------------------- a, B, c, d, e effect 2: REPLACE the comma in the result with "|" SQL> selectreplace (wm_concat (name), '|') fromtest; REPLACE (WM_CONCAT (NAME ),',', '|') --------------------------------------------------------------------- a | B | c | d | e effect 3: Merge nameSQL> selectid, wm_concat (Name) namefromtest groupbyid; IDNAME ---------- ------------------------------ 1 a, B, c2 d, e case: I want to write a view, similar to "create or replace view as select Field 1 ,... field 50 from tablename ", the base table has more than 50 fields. If it is too difficult to write manually, is there any easy way? Of course, let's see if I apply wm_concat to make this requirement simple. SQL> select 'create or replace view as select' | wm_concat (column_name) | 'from dept' fromuser _ tab_columnswheretable_name = 'dept'; 'createorreplaceviewasselect' | WM_CONCAT (COLUMN_NAME) | 'fromdept 'partition partition, DNAME, LOCfromdept