Sort common e functions, oracle Functions

Sort common e functions, oracle Functions

Recently I learned about Oracle databases and often encountered Oracle database functions. After careful consideration, I summarized some common Oracle database functions.

 

---------------------------------- ----> Common oracle basic functions (example) <----------------------------------------

------------------------ ----> Common character functions <----------------------------

-- Initap () first letter capital Place Name select initcap ('z Qing l love') from dual

-- Replace all lower () letters with lowercase select lower ('zq l') from dual

-- Convert all upper () letters to lowercase select upper ('zq l') fro m dual

-- Ltrim () left cropping must start from the first matching until the matching is completed. select ltrim ('Life is really lonely, like snow', 'true') from dual

-- Rtrim () Right crop and left crop select rtrim ('Life is really lonely like snow, 'Ah') from dual

-- Translate () select translate by character ('zadqlf ', 'abfd', 'patriotic up') from dual

-- Replace () string replace select replace ('Life is lonely like snow, 'This is', 'ga ga ') from dual

-- Instr () Find the position of the substring select instr ('Life is really lonely like snow', 'snow like ') from dual

-- Substr () obtain the sub-string select substr ('Life is lonely like snow',) from dual

-- Concat () connection string select concat ('My, 'love') from dual

-------------------- ----> Common numeric functions <--------------------------

-- Abs () returns the absolute value select abs (-15) from dual

-- Ceil () rounded up to remove the positive number of the decimal part + 1 negative number + 0 select ceil (-1.1) negative number, ceil (1.1) positive number from dual

---- Floor () rounded down to an integer equal to removing the decimal part positive number-0 negative number-1 select floor (-1.1) negative number, floor (1.1) positive number from dual

-- Sin () sine triangle comparison between the opposite side and the oblique side ----> not commonly used <---- select sin (1.5) from dual

-- Cos () cosine triangle adjacent edge to Oblique Edge ratio ----> not commonly used <---- select cos (1.5) from dual

-- Sign (): select sign (-32) minus sign, sign (32) minus sign from dual

-- Power (m, n) m's n select power (3, 3) from dual

-- Sqrt () returns the square root of a number ----> not commonly used <---- select sqrt (4) from dual

-- Mod () returns the remainder, which is similar to % select mod () from dual in java.

-- Round (m, n) Rounding m-> operand n-> precise to the digits after the decimal number select round (1.116, 2) from dual

-- Trunc () truncation m-> operand n-> retain the digits after the decimal number select trunc (1.116, 2) from dual

---------------------- ----> Common date functions <--------------------------

-- Months_between () returns the month parameter with a difference between two dates. The parameter must be of the date type select months_between (date '04-11-05 ', date '11-1-01') from dual

-- Add_months () add the first parameter of the month to the date, which must be date select add_months (date '2017-2-14 ', 1) from dual

-- Next_day () returns the number of days in a week after the specified date. The parameter for the second week must be in English select next_day (date '2017-5-6 ', 'monday') from dual

-- Round () performs "Rounding" on the date in the specified format. Here, you need to pay attention to the time conversion and the rounding range ----> not commonly used <---- select round (to_date ('2017-2-03 ', 'yyyy-mm-dd'), 'Year') from dual; select round (to_date ('2017-2-17 ', 'yyyy-mm-dd'), 'month ') from dual; select round (to_date ('1970-2-03 ', 'yyyy-mm-dd'), 'day') from dual;

-- Trunc () truncates a date in a specified way. For more information, see select trunc (date '2017-8-1 ', 'Year') from dual.

-- Extract () obtain the time of each part of select extract (month from date '2017-08-01 ') from dual

-- Date 'string' String Conversion TIME select date' 2015-01-02 'from dual

-- To_timestamp () format (time) string ---> not commonly used <--- select to_timestamp ('2017-01-01 ', 'yyyy-mm-dd') from dual

---------------------- ----> Common conversion functions <--------------------------

-- To_char () is converted to a string type. to_char is easy to convert. For example, $9999.9 cannot be written as $9999.9 select to_char (date '2017-01-01 ', 'yyyy "-" mm "-" dd "-" ') from dual select to_char (1234.4,' $9999.9 ') from dual

-- To_date () to date of the time type. System Format: 55/5/6 select to_date ('2017-5-6 ', 'yyyy-mm-dd') from dual

-- To_number () to select to_number ('192. 56') from dual

-------------------- ----> Operation result function <--------------------------

-- Decode () is equivalent to SQL case end select decode (, 'true', 2, 'false') from dual

-- Nvl () replace null value in the specified column select nvl (name, 'unname') from pet -- name is null returns 'unname' instead of null returns the original value

-- Nvl2 () select nvl2 (name, 'unname', 'name') from pet -- name is null returns 'name' instead of null returns 'name'

------------------------ ----> Common multiline functions <----------------------------

-- Sum () sum select sum (love) from pet

-- Avg () returns the average select avg (love) from pet

-- Count () count select count (*) from pet

-- Max () returns the maximum number of select max (love) from pet

-- Min () calculates the minimum number of select min (love) from pet

 

 

Note: The Oracle Database insertion time cannot be directly inserted.
For example
To_date ('2017-12-05 ', 'yyyy-mm-dd '),