sql and functions commonly used in oracle

1. TO_DATE, EXISTS, CASE... WHEN function
2. LISTAGG function and OVER PARTITION BY function
3. Find out whether a value exists in the whole database in Oracle database
4. oracle sql statement specification, creating columns, adding columns, deleting columns, functions, etc
5. Function that returns the time difference between the specified date and the current date

Oracle functions involved in this paper include: UPPER, LOWER, INITCAP, CONCAT, SUBSTR, LENGTH, REPLACE, INSTR, LPAD, RPAD, TRIM, ROUND, MOD, TRUNC, MONTHS_BETWEEN,ADD_MONTHS,LAST_DAY,NEXT_DAY,TO_CHAR,TO_DATE,TO_NUMBER,NVL,NULLIF,DECODE,NVL2,COALESCE ,CASE WHEN,COUNT,AVG,MAX,MIN,SUM,GROUP BY,Having

1, Single line function - character function

1. UPPER, string to uppercase function

SELECT UPPER ('abcde') FROM dual ; --ABCDE

1. LOWER, string to lowercase function

SELECT LOWER('ABCDE') FROM dual ; --abcde

1. INITCAP, string, capitalize the first letter of each word

SELECT INITCAP('abc, def') FROM dual; --Abc, Def

1. CONCAT, string concatenation function

SELECT CONCAT('a', 'b') FROM dual; --ab
SELECT 'a' || 'b' FROM dual; --ab

1. SUBSTR (string str, int a, int b), string interception function

   when only two parameters are passed in, str is the string to be intercepted, and a is to intercept all subsequent strings from the a subscript. When three parameters are passed in, str is the string to be intercepted, a represents the position of the string intercepted, and b represents the length of the intercepted string.
  note: when a is equal to 0 or 1, it is intercepted from the first position)

SELECT SUBSTR('abcde', 3) FROM dual; --cde
SELECT SUBSTR('abcde', 3, 2) FROM dual; --cd

1. LENGTH to calculate the LENGTH of the string

SELECT LENGTH('abcde') FROM dual; --5

1. REPLACE, string replacement function

SELECT REPLACE('abcde', 'a', 'A') FROM dual; --Abcde

1. INSTR, looking for element subscript

   format 1: instr (string1, string2), instr (source string, target string), indicating the position of the first occurrence of string2 and the initial letter of string2 for the first time.
   format 2: instr (string1, string2, start_position, nth_appearance), instr (source string, target string, start position, matching sequence number), indicating the start from the first of string1_ Position starts, looking for nth_ Appearance: the position sequence number of string2 appears twice.

SELECT INSTR('Hello World', 'or') FROM dual; --Looking at'Hello World'First appeared in'or', And at this time'o'Location serial number of, 8

SELECT INSTR('helloworld', 'l', 2, 2) FROM dual;  --Return result: 4, stay"helloworld"Start searching at position 2 of, and find the second occurrence“ l"Location of
SELECT INSTR('helloworld', 'l', 4, 2) FROM dual;  --Return result: 9, stay"helloworld"Start at position 4 of the and look for the second occurrence“ l"Location of

1. Lpad (string, padded_length, [pad_string]), fill characters to the left to the specified length, rpad (string, padded_length, [pad_string]), and fill characters to the right to the specified length

   string: the string to be filled; padded_length: the length of the filled string. If the value is less than the original string length, it will be intercepted; pad_string: the character to be filled in. If it is not specified, it will fill in spaces by default.

SELECT LPAD('Smith',10,'*') FROM dual; --*****Smith

SELECT RPAD('Smith',10,'*') FROM dual; --Smith*****

1. TRIM, remove the spaces at the beginning and end of the string

SELECT TRIM(' Mr Smith ') FROM dual;

2, Single line function - numeric function

1. ROUND (NUMBER, decimals)

   NUMBER: the value to be processed;
   decimals: indicates that the decimal places after the decimal point are retained and rounded. Optional. If omitted, all decimal places are directly removed and rounded. If the value is negative, it indicates the number of digits to the left from the decimal point. The corresponding integer number is filled with 0 and the decimal is removed.

Note: unlike the TRUNC function, ROUND rounds the processed numbers.

SELECT ROUND(412.513) FROM dual; --413
SELECT ROUND(412.513, -2) FROM dual; --400
SELECT ROUND(412.513, 2) FROM dual; --412.51

1. MOD (m, n)

   MOD returns the remainder of m divided by N. if n is 0, m is returned.

select MOD(23,8), MOD(24,8), MOD(25, 0) from dual; --7, 0, 25

1. TRUNC

   format 1: TRUNC(date, [fmt]), date processing function. Date is a required parameter and fmt is an optional parameter. If it is blank, it indicates 0 point of the specified date.

SELECT TRUNC(sysdate) FROM dual; --2022-01-22, Get current time, Current date 0:00
SELECT TRUNC(sysdate, 'mm') FROM dual; --2022-01-01, Gets the first day of the current month
SELECT TRUNC(sysdate, 'hh') FROM dual; --2022-01-22 19:00:00, Get current time, Accurate to hours

   format 2: TRUNC(number, [decimals]), numerical processing function. Number is a necessary parameter and decimals is an optional parameter. If it is empty, the value after the decimal point will be cut off by default. When it is positive, it means to save the digits after the decimal point; when it is negative, it means to replace the digits on the left of the decimal point with 0, and discard all the digits on the right of the decimal point. Much like the ROUND function, the difference between the two is that TRUNC does not ROUND the processing results.

SELECT TRUNC(123.567, 2) FROM dual; --123.56
SELECT TRUNC(123.567, -2) FROM dual; --100
SELECT TRUNC(123.567) FROM dual; --123

3, Single line function - date function

1. MONTHS_BETWEEN(m, n)), m and N are both date types. Calculate the number of months with decimals between M and n.

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2000-01-15', 'YYYY.MM.DD')) FROM dual; --264.252886798088410991636798088410991637

1. ADD_MONTHS(date, d), date and D are necessary parameters. Add D months to date to get a new time. D can be a negative number.

SELECT SYSDATE FROM dual; --2022-01-22 20:14:47
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual; --2022-02-22 20:14:47
SELECT ADD_MONTHS(SYSDATE, -1) FROM dual; --2021-12-22 20:14:47

1. LAST_DAY(date) returns the last day of the month on the specified date.

SELECT SYSDATE FROM dual; --2022-01-22 20:22:29
SELECT LAST_DAY(SYSDATE) FROM dual; --2022-01-31 20:22:29

1. NEXT_DAY(date, d) returns the next day of the week of the specified date. D is a required parameter. D is replaced by 1 ~ 7. 1 represents Sunday and 2 represents Monday...

SELECT NEXT_DAY(SYSDATE, 2) FROM dual; --2022-01-24 20:26:25, Returns the next Monday of a specified date

4, Single line function - conversion function

1. TO_CHAR (str, fmt) converts the string str in fmt format.

SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; --2022
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual; --2022-01-22
SELECT TO_CHAR('8000.00','L999,999,999') FROM dual; --￥8,000
SELECT TO_CHAR(SYSDATE,'D') FROM dual; --7, Returns the day of the week the specified date is

1. TO_DATE (str, fmt) to convert str to fmt format

SELECT TO_DATE('20090210', 'yyyyMMdd') FROM dual; --2009-02-10

1. TO_NUMBER, you can convert the string to type number for addition, subtraction, multiplication and division

SELECT TO_NUMBER('13') + TO_NUMBER('14') FROM dual; --27

5, Single line function - general function

1. NVL(m, n). If M is empty, n is returned

SELECT NVL('1', 0) FROM dual; --1
SELECT NVL('', 0) FROM dual; --0

1. NULLIF(expr1, expr2). If expr1=expr2, null is returned; if not equal, expr1 is returned. The first parameter cannot be specified as null. For non numeric type parameters, the data type must be consistent.

SELECT NULLIF('1', '1') FROM dual; --null
SELECT NULLIF('1', '0') FROM dual; --1
SELECT NULLIF(1 , '1') FROM dual; --[42000][932] ORA-00932: Inconsistent data type: Should be NUMBER, But I got CHAR

1. Code (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n, default value)

   the meaning of this function is as follows: IF (condition = value 1) THEN RETURN return return value 1 ELSE IF (condition = value 2) THEN RETURN return return value 2... ELSE RETURN default value END IF;

SELECT A00, A0101, A0104 FROM A01;

SELECT A00, A0101, DECODE(A0104, 1, 'male', 2, 'female', 'unknown') FROM A01;

1. NVL2(expr1, expr2, expr3), expr1 is not null, expr2 is returned, otherwise expr3 is returned. If expr2 is different from expr3, expr3 will be converted to expr2.

SELECT NVL2('1', '2', 3) FROM dual; --2
SELECT NVL2('', '2', 3) FROM dual; --3
SELECT DUMP(NVL2('', '2', 3)) FROM dual; --Typ=1 Len=1: 51

DUMP() function can view the type of any value in oracle database. The specific type is represented by Type=t and code. Official document of correspondence between type and code value.

1. COALESCE (expr1, expr2, expr3...)， Return the first parameter that will not be empty. The data types of all parameter lists must be consistent

SELECT COALESCE('', '2', '3') FROM dual; --2
SELECT COALESCE('', '2', 3) FROM dual; --[42000][932] ORA-00932: Inconsistent data type: Should be CHAR, But it won NUMBER

1. CASE WHEN, a branch judgment function, is similar to the DECODE function. CASE WHEN is more suitable for conditions requiring special judgment processing

SELECT A00, A0101, CASE A0104 WHEN '1' THEN 'male' WHEN '2' THEN 'female' ELSE 'unknown' END FROM A01; --62CB8048-97B9-466E-946E-23E2B86180EA, Wu Yuping, female

Note: all functions can be nested.

6, Single line function exercise

1. Find out the employees employed on the penultimate day of each month (e.g. 2021-5-29), and hiredate is the employment date field

SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2 = HIREDATE;

1. Find employees hired 25 years ago

SELECT * FROM EMP WHERE HIREDATE<=ADD_MONTHS(SYSDATE, -25*12); --Find out that the date of employment is earlier than or equal to the current time minus 25 years

1. All employees' names are prefixed with Dear and initialed with capital letters

SELECT 'DEAR ' || INITCAP(ENAME) FROM EMP;

1. Find the employee whose name is five letters

SELECT * FROM EMP WHERE LENGTH(ENAME) = 5;

1. Find employees whose names do not contain the letter R

SELECT * FROM EMP WHERE ENAME NOT LIKE '%R%';

1. Displays the first word of the names of all employees

SELECT SUBSTR(ENAME,0,1) FROM EMP;

1. All employees are displayed in descending order by name. If they are the same, they are sorted in ascending order by salary

SELECT * FROM EMP ORDER BY ENAME desc, SALARY;

1. Assuming that a month is 30 days, find out the daily salary of all employees, excluding decimals

SELECT ENAME, TRUNC(SALARY/30) FROM EMP;

1. Find employees hired in February

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'FMMM') = '2';

1. List the number of months the employee joined the company (rounded)

SELECT ENAME, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)) FROM EMP;

7, Grouping function

1. COUNT is the function to calculate the data volume. If there is no data in the database table, count(*) returns 0 instead of null

SELECT COUNT(*) FROM AZ09; --0

1. Avg, max, min, sum, find the average, maximum, minimum and sum. Note: the grouping function will omit null values in the column. You can use NVL() function to force the grouping function to process null values.

SELECT AVG(NVL(COMM, 0)) FROM EMP;

1. GROUP BY clause, the field appearing in the SELECT list or the field appearing after ORDER BY. If it is not included in the grouping function, the field must also appear in the GROUP BY clause.

   the fields contained in the GROUP BY clause do not have to appear in the SELECT list. You can use the WHERE clause to limit the query conditions and the ORDER BY clause to specify the sorting method. If there is no GROUP BY clause, the mixed use of fields (single line functions) and grouping functions is not allowed in the SELECT list.

SELECT A0101, ZDYXA01013 FROM A01; --legitimate
SELECT AVG(ZDYXA01013) FROM A01; --legitimate
SELECT A0101, INITCAP(A00), AVG(ZDYXA01013) FROM A01; --[42000][937] ORA-00937: Not a single group grouping function

  grouping functions are also not allowed in the WHERE clause.

SELECT A0101, ZDYXA01013 FROM A01 WHERE AVG(ZDYXA01013)>1; --[42000][934] ORA-00934: Grouping functions are not allowed here

1. Having clause to further filter the result set in the grouping function

SELECT A0117 FROM A01 WHERE A0107 < TO_DATE('2000-01-01', 'yyyy-mm-dd') GROUP BY A0117 HAVING A0117='01' ORDER BY A0117;

--[42000][979] ORA-00979: no GROUP BY expression
SELECT A0117 FROM A01 WHERE A0107 < TO_DATE('2000-01-01', 'yyyy-mm-dd') GROUP BY A0104 HAVING A0104='01' ORDER BY A0101;

1. Grouping functions can be nested

SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;