Oracle Study Notes (6) -- Functions

Oracle Study Notes (6) -- Functions
Function functions facilitate statistical processing of data query result functions classification Oracle built-in system functions numeric functions rounded to ROUND (n [, m]) 1, n: 2. m: indicates the number of digits after the decimal point or the first digit. 3. m: 0 4 is omitted. m> 0: example: select round (23.4), round (23.45, 1), round (23.45,-1) from dual; take the maximum value of the entire function: CEIL (n) Take the minimum value: FLOOR (n) Example: select ceil (23.45), floor (23.45) from dual; commonly used calculation 1. Take the absolute value: ABS (n) select abs (23.45), abs (-23), abs (0) from dual; 2. Obtain the remainder: MOD (m, n) if m and n have a null value, the return value is null select mod (5, 2) from dual; select mod (5, null) from dual; 3, POWER (m, n): returns n power select POWER (2, 3) of m ), power (null, 2) from dual; 4. square root: SQRT (m) select sqrt (16) from dual; trigonometric function 1, sine: SIN (n), arcsin: ASIN (n) n represents radians 2, cosine: COS (n), arccosine: ACOS (n) n represents radians 3, tangent: TAN (n), arc tangent: ATAN (n) n indicates the radian select sin (3.124) from dual; Case Conversion Function of the character function UPPER (char): Convert to uppercase LOWER (char): Convert to lowercase INITCAP (char ): example: select upper ('abde'), lower ('ade '), initcap ('asd') from Dual; obtain the substring function SUBSTR (char, [m [, n]) Description: char: Source string, m: Get the starting position of the substring, n: the number of digits n of the substring can be omitted. If n is omitted, it indicates that m is 0 from the position of m to the end of the string, indicating that m is a negative number from the first letter of the string, it indicates that select substr ('abcde', 2,3), substr ("abcde", 2), substr ('abcde',-) from dual is intercepted from the end of the string; obtain the string LENGTH function length (char): select LENGTH ('acd') from dual; String concatenation function CONCAT (char1, char2) like | Operator, select concat ('AB', 'cd') from dual; select 'AB' | 'cd' from dual; remove substring functions 1, TRIM (c2 FR OM c1): This function removes the character c2 select trim ('A' from 'abcde') from dual from string c1; 2. LTRIM (c1 [, c2]): select ltrim ('ababa', 'A') from dual; 3. RTRIM (c1 [, c3]): select rtrim ('ababa', 'A') from dual; 4. TRIM (c1): REPLACE function 1 with spaces at the beginning and end, REPLACE (char, s_string [, r_string]), and REPLACE r_string with spaces. Example: select replace ('abced ', 'A', 'A') from dual; select replace ('abced', 'A') from dual; select replace ('abced ', 'AB', 'A') from dual; Date function system time SYSD ATE default format: DD-MON-RR select sysdate from dual; date operation ADD_MONTHS (date, I): returns the month added on the specified date. I can be any integer. If I decimal, the integer is automatically truncated. If I is a negative number, it is equivalent to the original date minus the month. Select add_months (sysdate, 3), add_months (sysdate,-3) from dual; NEXT_DAY (date, char): If the char value is 'monday ', returns the select next_day (sysdate, 'monday') from dual; LAST_DAY (date): returns the date select last_day (sysdate) of the last day of a month) from dual; MONTHS_BETWEEN (date1, date2): select months_between ('201_february _ 15', '10_february _ 15') from dual; EXTRACT (date FROM datetime): select extract (year from sysdate) from dual; select extract (month from sysdate) from dual; select extract (date from sysdate) from dual;
Select extract (hour from timestamp '2017-10-1 17:25:14 ') from dual; Conversion Function date conversion function TO_CHAR (date [, fmt [, params]) parameter description: date to be converted fmt: Format of the conversion params: language of the date. Usually do not write the default format: DD-MON-RR yy yyyy year mm month dd day HH24 HH12 mi ss example: select to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss') from dual; select to_char (sysdate, 'yyyy-MM-DD HH12: MI: ss') from dual; character conversion to date function TO_DATE (char [, fmt [, params]) Note: to_date () display date example by default format: select to_date ('1970-05-22 ', 'yyyy-MM-DD') from dual; function TO_CHAR (number [, fmt]) 9: show the number and ignore the previous 0 0: show the number, the number is insufficient, fill with 0. or D: show the decimal point, or G: show the thousands of characters $: Dollar sign S: add positive and negative signs (both can be before and after, cannot be added at the same time) select to_char (12345.678, '$99,999.999 ') from dual; select to_char (12345.678, '$99,999,999') from dual; select to_char (12345.678, 's99, 999.999 ') from dual; the TO_NUMBER (char [, fmt]) function for converting characters to numbers is the conversion format. You can omit select to_number ('$1,000', '$9999') from dual; custom functions (not available for the moment) use the function in the query to use the character function in the query. 1. query the employee's birthday in the employee information table (that is, get the employee's birthday according to the employee's province ID number) select substr (cardid) from users; 2. replace all Department numbers 01 with 'info' select replace (deptno, '01', 'info') from users; 3. select mod (age, 10) from users from the age field in the employee information table and the remainder of 10;

Use the date function in the query. 1. Obtain the employee's employment year select extract (year from regdate) from users; 2. query the information of employees who joined the company in March. select * from users where extract (month from regdate) = 5;