General functions (learning notes), general function learning notes

General functions (learning notes), general function learning notes

-- **************** Common functions
-- NVL processing NULL
-- NVL (number | column, default value). If the displayed number is NULL, the default value is used.
-- NVL2 (number | column, default value: 1; default value: 2) if the column is not NULL, result 1 is returned. If it is NULL, result 2 is returned.
-- NULLIF (expression 1, expression 2) comparison expression 1, and expression 2, whether the result is equal, if equal, return NULL, if not equal, return expression 1
-- DECODE (column | value, judgment value 1, display result 1, judgment value 2, display result 2 .............)
-- CASE column | value WHEN expression 1 THEN display result 1, else expression n. end
-- COALESCE (expression 1, expression 2 ,.... Expression n)
-- Use NVL to process NULL
-- Query employee ID, name, position, employment date and annual salary (monthly salary + comm) * 12
-- Verify NVL

SELECT NVL(NULL,0),NVL(3,0) FROM dual;

Result: 0, 3

SELECT e. empno, e. ename, e. job, e. hiredate, (e. sal + NVL (e. comm, 0) * 12 annual salary FROM emp e;

 

-- Use NVL2 Functions

SELECT e. empno, e. ename, e. job, e. hiredate, (e. sal + NVL2 (e. comm, comm, 0) * 12 annual salary from emp e;

 

SELECT e. empno, e. ename, e. job, e. hiredate, e. comm, NVL2 (e. comm, e. sal + e. comm, e. sal) * 12 annual salary from emp e;

 

-- The NULLIF function compares two expressions. If they are equal, NULL is returned. If they are not equal, expression 1 is returned.

SELECT NULLIF(1,1),NULLIF(1,2) FROM dual;

Result: null, 1
-- Compare the name length and position Length

SELECT e. empno, e. ename, e. job, LENGTH (e. ename), LENGTH (e. job), NULLIF (LENGTH (e. ename), LENGTH (e. job) whether the length is the same FROM emp e;

-- Decode uses DECODE to make all judgments. If no judgment is made, NULL is displayed.

SELECT e. ename, e. sal, DECODE (job, 'cler', 'salesman', 'manager', 'manager', 'analyst', 'analysts ', 'President ', 'President') FROM emp e;

 

-- CASE expression
-- Display the name, salary, and position of each employee, and display the new salary (new salary increases by 10% for sales staff, 20% for sales staff, 30% for managers, and 50% for other positions)

SELECT e. ename, e. sal, e. job, CASE e. job WHEN 'cler' THEN e. sal * 1.1 WHEN 'salesman' THEN e. sal * 1.2 WHEN 'salseman 'THEN e. sal * 1.3 ELSE e. sal * 1.5END new salary FROM emp e;

--- Coalesce (expression 1, expression 2 ,.... Expression n). If expression 1 is NULL, expression 2 is displayed. If expression 2 is NULL, expression 3 is displayed ......

SELECT e.ename,e.sal,e.comm,COALESCE(e.comm,100,2000),COALESCE(e.comm,NULL,NULL)FROM emp e;