Oracle single-row and multi-row function instances

Oracle single-row and multi-row function instances
Single-row and multi-row functions:

Single-row functions can be divided into five types: character functions, numeric functions, date functions, conversion functions, and general functions.

Single Row function:
-- Case-sensitive functions
Select lower ('Hello World') to lowercase, and upper ('Hello World') to uppercase from dual;
-- Initcap: uppercase letters
Select initcap ('Hello World') with uppercase letters from dual;
-- Character Control Function
-- Concat: character concatenation function, equivalent to |
Select concat ('hello', 'World') from dual;
-- Substr: Find a substring in the parent string
Select substr ('Hello world', 3) from dual;
Select substr ('Hello world', 3,4) from dual;
-- Length and lengthb: number of characters and number of segments
Select length ('China') characters, lengthb ('China') bytes from dual;
-- Instr: locate the substring in the parent string
Select instr ('Hello world','ll ') from dual;
-- Lpad, rpad: Left and Right fill, fill the abcd with * to 10 digits
Select lpad ('abc', 10, '*') left fill, rpad ('abc', 10, '*') Right fill from dual;
-- Trim: removes the specified characters before and after the string.
Select trim ('H' from 'Hello World') from dual;
-- Replace: String replacement function
Select replace ('Hello Wordl ', 'l',' * ') from dual;
-- Numeric Functions
Select round (45.926, 2) Rounding, trunc (45.926, 2) truncation, mod (1600,300) Request from dual;
-- ROUND Function
Select round (45.923, 0) integer, round (45.923,-1) ten digits, round (45.923,-2) hundred digits from dual;
-- Date function
-- Display the current date
Select sysdate from dual;
-- Display time
Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;
-- Display yesterday, today and tomorrow. The addition and subtraction numbers are not yet updated.
Select sysdate-1 yesterday, sysdate today, sysdate + 1 tomorrow from dual;
-- Two dates are subtracted, and the result is the number of days of difference. The employee information is queried and the length of service of the employee is displayed. Two dates cannot be added.
Select empno, ename, sysdate-hiredate days from emp;
-- Query the employee information and display the length of service of the employee by day, week, or month
Select empno, ename, sysdate-hiredate, (sysdate-hiredate)/7 weeks, (sysdate-hiredate)/30 months from emp;
-- Months_between: number of months with different dates

Select (sysdate-hiredate)/30 method 1, months_between (sysdate, hiredate) method 2 from emp;
-- Add_months: add several months to the specified date
Select add_months (sysdate, 1) next month, add_months (sysdate, 123) "123 months later" from dual
-- Last_day: the last day of a month
Select last_day (sysdate) from dual;
-- Next_day: next Saturday
Select next_day (sysdate, 'Friday') from dual;
-- Round the date
Select round (sysdate, 'month') MONTH, round (sysdate, 'Year') from dual;
-- Truncate the date
Select trunc (sysdate, 'month') MONTH, trunc (sysdate, 'Year') from dual;
-- Date Format
Select * from emp where hiredate = to_date ('2017-01-23 ', 'yyyy-mm-dd ');
-- Query current date: displayed: 15:12:15 Today is Saturday
Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss "today is" Day') from dual;
-- Query employee information and display employee ID, name, monthly salary, currency code (L), thousands of characters (,), decimal point (.),
Select empno, ename, to_char (sal, 'bucket 999.99 ') from emp;
-- Common functions
-- Nvl (exp1, exp2): If exp1 is null, exp2 is returned.
-- Nvl2 (exp1, exp2, exp3): If exp1 is null, exp3 is returned; otherwise, exp2 is returned.
Select ename, sal * 12 + nvl2 (comm, comm, 0) annual income from emp;
-- NULLIF (expr1, expr2). If expr1 = expr2, null is returned. Otherwise, expr1 is returned.
Select nullif ('abc', 'abc') from dual;
Select nullif ('abc', 'abcaa ') from dual;
-- COALESCE: Find the first value not empty in the parameter list.
Select ename, comm, sal, COALESCE (comm, sal) from emp;
-- Raise the salary for employees. According to the position increase, the President increases by 1000, and the manager increases by 600. Other employees increase by 400.
Select ename, job, sal salary before going up, case job when 'President 'then sal + 1000
When 'manager' then sal + 600
Else sal + 400
Salary after end increase
From emp;
Select ename, job, sal salary before going up, decode (job, 'President ', sal + 1000,
'Manager', sal + 600,
Sal + 400) Post-rise salary
From emp;
Multiline Functions
Compared with single-row functions, oracle provides a wide range of group-based, multi-row functions. These functions can be used in select or select having clauses. They are often used together with group by when used for select substrings. A multiline function receives multiple inputs and returns one output.
Group function:
-- Sum of employees' salaries
Select sum (sal) from emp;
-- Calculate the number
Select count (*) from emp;
-- Average salary
Select sum (sal)/count (*) method 1, avg (sal) method 2 from emp;
-- Null value: the group function automatically filters null values.
Select count (*), count (comm) from emp;
-- Max and min: Maximum Wage and Minimum Wage
Select max (sal) highest salary, min (sal) Minimum wage from emp;
-- Grouping data: average salary of each department
Select deptno, avg (sal) from emp group by deptno;
-- Group by applies to multiple columns: average salary is calculated by department and different types of work.
-- Group by applies to multiple columns: group by the first column; if the group is the same, group by the second column.
Select deptno, job, avg (sal) from emp group by deptno, job;
--: For departments with an average salary greater than 2000
Select deptno, avg (sal) from emp group by deptno having avg (sal)> 2000;
-- Group by Enhancement
Select deptno, job, sum (sal) from emp group by rollup (deptno, job );
-- Different deptno empty two rows/unset
Break on deptno skip 2/break on null